【GAS】セルの数式を複数の別シートにコピーする（見積書などの数式のアップデートに）

複数のスプレッドシートで見積書の管理をしてる場合などで、一括で数式を変更したい時とかないですかね？

実際、そういうケースがあって、スプレッドシートを一つずつ開いて数式をコピペするのが面倒だったので、数式を複数のスプレッドシートで更新するGAS(Google Action Script)を書いてみました。

の数式を更新したいとき、GASを共有する方法を試したところうまく権限の共有

1. 数式をコピーするupdateCellFormula関数を作ってコピーできてるか確認

下記のコードでコピーが無事にできるか確認。

function updateCellFormula(targetId=''){
  const sheetName = '本体';//対象のシート名を入力
  const copyCell = 'G14:G14';//コピーするセルを指定

  // コピー元
  var tmplId = '1gs7_....0TmfM';//ファイルIDを入力
  // スプレッドシートURLの
  // https://docs.google.com/spreadsheets/d/ここの文字を入力/edit#gid=11111
  var tmpl =  SpreadsheetApp.openById(tmplId);
  var tmplSheet = tmpl.getSheetByName(sheetName);

  // コピー先
  var trgId = targetId? targetId : '1dSsGK...UvnFpEo';//コピー先のスプレッドシートURLのIDを指定
  var trgFile =  SpreadsheetApp.openById(trgId);
  var trgSheet = trgFile.getSheetByName(sheetName);

  //数式を取得
  var copyFormula = tmplSheet.getRange(copyCell).getCell(1,1).getFormula();

  //console.log(copyFormula);//数式内容を確認

  //数式をコピー
  trgSheet.getRange(copyCell).setFormula(copyFormula);

}

下記のように copyTo使おうとすると、同じシート内の時しか使えないっぽい

copyRange.copyTo(pasteRange,SpreadsheetApp.CopyPasteType.PASTE_FORMULA);

2. Google Driveで対象のシートを取得するcopyCellToFileInFolderという関数を作成

function copyCellToFileInFolder() {
  var folderID = "1AR9...-Y0jP";//GoogleDriveの親フォルダのIDを指定
  var parentFolder = DriveApp.getFolderById(folderID);
  var childFolders = parentFolder.getFolders();

  while(childFolders.hasNext()) {  
    var folder = childFolders.next();
    const folderName = folder.getName();

    // console.log(folderName);

    const files = folder.getFiles();
    while(files.hasNext()) {
      const file = files.next();
      const fileName = file.getName();
      
      //該当ファイルを指定してコピーしたいときはファイル名に含まれてる文字を指定
      if(fileName.indexOf('見積書')!==-1){
        targetId=file.getId();
        console.log(fileName);
        //実際に反映したいとき下のコメントアウトを外す
        //updateCellFormula(targetId);
      }
    };
  };

  return;

}

上記のコードcopyCellToFileInFolderを実行するとgoogleドライブの中の見積書のファイル名の一覧が表示される

3. copyCellToFileInFolderの中のupdateCellFormula関数のコメントアウトを外して実行

その後、フォルダ内のスプレッドシートが実際に更新されてるか確認

まとめ

getFormula()、setFormula()をgetValue(),setValue()にすれば値をコピペできるし、応用効かせられるので便利。