【Google Apps Script 通信 vol.8】数式を最終行までコピーしよう！②｜複数シートも一括でコピー

こんにちは。きゅうりです。

前回やったこと

1つのシートに対して、指示をした複数の列の数式をコピーするところまでできましたね！

やること

今回は複数シートも一括でコピーできるように進化させていきましょう！

vol4で作成したシート名取得のスクリプトも活用していきます。

使用するスプレッドシート

前回の指示シートにコピー対象のシートを指定する列を追加しましょう。

シート名取得のシートも追加したサンプルを用意したので、ぜひ参考にしてください。

Copy formulas on multiple sheets

サンプルコード

いつも通りコード内に解説を入れているので、参考にしてください。

// 解説の冒頭に★がついている箇所は後ほど補足します

function Copyformula() {

 // シート情報を取得
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var setSheet = ss.getSheetByName('指示');  
 
 // ★A列の最終行を取得（コピー対象のシート情報を取得）
 var sheetLastRow = setSheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

 // シート数分 繰り返し
 for (var i = 2; i <= sheetLastRow; i++){
 
   //　コピーを実行するシートを取得
   var copySheetName = setSheet.getRange('A'+i).getValue();
   var copySheet = ss.getSheetByName(copySheetName);
     
   //  ★B列の最終行を取得（数式コピーの繰り返し回数を取得）
   var loop = setSheet.getRange(1, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

   // コピーを実行するシートの最終行を取得
   var copyLastrow = copySheet.getLastRow();
 
   // コピー列数分 繰り返し
   // ※このとき繰り返し変数は前段の繰り返しの【i】と違うものを使う必要があるので注意
   //（同じにするとiの加算がおかしくなり、意図通りに動作しません）
   for (var j = 2; j <= loop; j++){　
 
     // コピー対象列を取得（コピー対象列の情報が2行目以降のためiは2からスタート）
     var copyCol = setSheet.getRange('B'+j).getValue();
       
     // 2行目の数式を最終行までコピー
     copySheet.getRange(copyCol+'2').copyTo(copySheet.getRange(copyCol+'3:'+copyCol+copyLastrow));
}}}

これで複数シートも一括でコピーできるようになりました！（私の作業も少しは楽になるといいな、、、）

補足｜最終行の取得について

上記コード解説に★がついている部分。

// ★A列の最終行を取得（コピー対象のシート情報を取得）
 var sheetLastRow = setSheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

     
//  ★B列の最終行を取得（数式コピーの繰り返し回数を取得）
 var loop = setSheet.getRange(1, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

ここでは指示シートのA列、B列それぞれの最終行を取得しています。

これまで最終行を取得するときにはgetLastRow();を使っていましたが、実はこれはシート全体の最終行を取得するコードなのです。

今回の場合はA,B列それぞれの最終行を取得する必要があったので、いつもとは違う方法でアプローチをしています。

このコードの詳細な解説は下記リンクを参照してください。（ちなみに私は詳細まで読んでいません…笑）

【GAS】1行で書ける！特定列の最終行・特定行の最終列を取得する方法｜もりさんのプログラミング手帳

スクリプトが完了したら通知してほしい

コピーのスクリプトは完成しました。

でも大きなデータを扱うとなるとその分、実行時間が長くなります。

数秒で終わるものであれば構いませんが数分かかるものだと「終わった？まだ？終わった？」とそわそわしないといけない。そんなの絶対に嫌だ。

というわけで、次回はスクリプトが完了したらSlack通知してくれるように進化させましょう！