【GAS】スプレッドシートにオートフィルを適応する

GASautofill,autoFillToNeighbor,スプレッドシート

Google Apps Script（GAS）でスプレッドシートにオートフィルを適応する方法をご紹介します。

オートフィルとは

オートフィルはスプレッドシート・Excelでおなじみの、既に参照されているセルの値や関数の規則に従ってセルに連続で記入してくれる機能です。「1,2,3」と記入されていれば「4,5,6..」、「1,4,7」と記入されていれば「10,13,16…」を自動で入力できます。

GASでもこのオートフィルを適用するためのメソッドが用意されています。

基本構文

sourceRange.autoFill(destination, series);

  • sourceRange：オートフィル元となる範囲（Rangeオブジェクト、上記の例だと’A1:A3′)
  • destination：sourceRangeを含んだオートフィル実行適応範囲（Rangeオブジェクト、上記の例だと’A1:A10′)
    *上方向、下方向、右方向、左方向のいずれかの一方向のみ指定可能
  • series：適応するオートフィルのタイプ

autoFillメソッドは参照元範囲と実行範囲をそれぞれ指定してオートフィルを適応します。下記は冒頭の例を再現するコードです。

  let sheet = SpreadsheetApp.getActiveSheet();

  let sourceRange = sheet.getRange("A1:A3");
  let destination = sheet.getRange("A1:A10");

  // A1:A3を元にA10まで新しい値を挿入する
  sourceRange.autoFill(destination, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

sourceRange.autoFillToNeighbor(series)

  • sourceRange：オートフィル元となる範囲
  • series：適応するオートフィルのタイプ

実行範囲も自動で決めてくれるのがautoFillToNeighborメソッドです。先ほどは参照元範囲に対して実行範囲を引数にメソッドを実行していましたが、autoFillToNeighborメソッドは近接する列が入力されている行まで自動実行されます。

  let sheet = SpreadsheetApp.getActiveSheet();
  let sourceRange = sheet.getRange("B1:B2");

  sourceRange.autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

autoFillToNeighborメソッドの挙動

近接する列のパターンによってどのような挙動をするか参考までに確認しておきます。

基準となる列は右側でもOK

基準にできる列が右にも左にもあれば左側の列が優先される

入力されているセルがあればそのセルの直前までオートフィルが適応される

関数のオートフィル

関数で記述したセルを参照元としてオートフィルを実行すると、参照と元の列番号と行番号を連続値として挿入してくれます。

  let sheet = SpreadsheetApp.getActiveSheet();
  
  let sourceRange = sheet.getRange('C2');
  sourceRange.setFormula('=B2/A2');
  sourceRange.autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
参照元を固定する「$」マーク

スプレッドシートに使い慣れた方にはおなじみかと思いますが、列番号／行番号の接頭に「$」マークを付けることで参照元を固定できます。
例えば、ある固定目標に対する各月の実績値から目標対比を算出する場合、計算式の分母はすべてのセルで「A2」となります。この時、計算式内で「$A$2」と記述すれば、その部分は連続値とならずに常に「A2」を参照することになります。

  let sheet = SpreadsheetApp.getActiveSheet();
  
  let sourceRange = sheet.getRange('C5');
  sourceRange.setFormula('=B5/$A$2');
  sourceRange.autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

関数オートフィルの別の記述方法

スプレッドシートにおいて、関数は複製するだけで列番号と番号を連続した数値に置き換えてくれます。したがって厳密にはオートフィルではないものの、getLastRowメソッドとtoCopiyメソッドを使って同じような記述が可能です。

例えば上述の例でも、下記のコードを実行すると全く同じ結果となります。

  let sheet = SpreadsheetApp.getActiveSheet();
  
  let sourceRange = sheet.getRange('C2');  
  sourceRange.setFormula('=B2/A2');

  let lastRow = sheet.getLastRow();
  let copyRange = sheet.getRange(`C3:C${lastRow}`)
  sourceRange.copyTo(copyRange);

まとめ

GASにおけるオートフィルの適応方法をご紹介しました。