0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

GAS:値のみでスプレッドシートのバックアップ

Last updated at Posted at 2022-07-20

GASで値のみでシートバックアップを取る方法

* 値のみコピーだけで、バックアップ対象シートが新規だと、書式設定されていないセルが日付が数字で表示されてしまうので、書式のみコピーも同時に行った。

  • シート名.Clear()だと書式もクリアされてしまうので、シート名.ClearContents()を使う
// バックアップ
function backup(baseID,baseName,backupID,backupName,format) {
  var baseSheet = SpreadsheetApp.openById(baseID);
  var baseSheetName = baseSheet.getSheetByName(baseName);
  
  var backupSheet = SpreadsheetApp.openById(backupID); 
  var backupSheetName = backupSheet.getSheetByName(backupName);
  
  backupSheetName.clearContents(); // 値のみクリア
  var lastRow = baseSheetName.getLastRow();
  var lastColumn = baseSheetName.getLastColumn();
 
 var baseRange = baseSheetName.getRange(1, 1, lastRow, lastColumn);
  var backupRange =  backupSheetName.getRange(1, 1);

  baseRange.copyTo(backupRange,{contentsOnly:true}); // 値のみコピー
  if(format){
    baseRange.copyTo(backupRange,{formatOnly:true}); // 書式のみコピー
  }

  // baseSheetName.getRange(1, 1, lastRow, lastColumn).copyTo(backupSheetName.getRange(1, 1), {contentsOnly:true});
  // baseSheetName.getRange(1, 1, lastRow, lastColumn).copyTo(backupSheetName.getRange(1, 1), {formatOnly:true});
}

使い方

  baseID = 'シートID';
  baseName = 'シート名'
  backupID = 'バックアップシートID';
  backupName = 'バックアップシート名'
  backup(baseID,baseName,backupID,backupName,true);

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?