【GAS】スプレッドシートの数式機能まとめ【サンプルソース付】

GAS開発者向けにスプレッドシートの数式機能をすべてまとめました。

たくさんある便利な関数を数式で簡単に設定できるようになると、やれることが無限大に拡がります。

特にスプレッドシートはExcelにはない超強力な関数があるので、積極的に使うべきです。

A1形式やR1C1形式で設定できます。最初はややこしいかもしれませんが、絶対参照や相対参照の理解にも結び付くはずです。

用途に応じて使い分け、自分のやりやすいやり方を見つけてもらえればと思います。

数式に関するメソッドをすべて載せてます。少しでも参考になれば幸いです。

Google Apps Script スプレッドシート基礎リファレンス
セル範囲クラス[Range]の数式設定
セル範囲リストクラス[RangeList]の数式設定
1. 数式設定A1形式[setFormula(式)]
2. 数式設定R1C1形式[setFormulaR1C1(式)]
セル範囲クラス[Range]の数式取得
最後に

Google Apps Script スプレッドシート基礎リファレンス

当ページは、GAS公式リファレンスを元にスプレッドシートの機能毎にまとめた基礎的な内容となっております。

若干、文言がおかしい可能性がありますが、あらかじめご了承ください。

GAS基本メソッドまとめ一覧はコチラ↓↓↓です。スプレッドシートの基本的な機能をまとめてますので、ぜひご活用ください。

【GAS】スプレッドシート基本メソッドまとめ一覧【初心者入門】

GAS開発者向けにスプレッドシートの基本的なメソッドを一覧にまとめました。各機能ごとのクラス、メソッドごとのサンプルソース付ページへリンクする一覧です。初心者にとって、スプレッドシートの基本的な操作・機能単位のメソッドを知...

まずは実際に動かしてみることをオススメします。マクロの登録方法は以下を参照いただければと思います。

【GAS】Googleスプレッドシートでマクロの使い方入門【動画付】

Googleの「Google Apps Script」通称GAS。GoogleマップやGメールなど、いろんなGoogle関連のサービスと自由に連携できたり、独自のWebアプリを開発することもできる、いろんな可能性を秘めたワクワクの...

GASの本はまだまだ少ないですね。基礎レベルであれば、以下の参考書が初心者にはベストの本です。

基本的な仕様やよく使われる機能の設定方法がまとまっています。

リンク

※JavaScriptの知識が既にある程度ある人にとっては冗長かもしれません。

セル範囲クラス[Range]の数式設定

セル範囲の数式設定メソッドです。複数セルへの設定もあります。

数式設定A1形式[setFormula(式)]

setFormula(formula)
この範囲の式を更新します。与えられた式はA1表記でなければなりません。

function setFormulaSample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var cell = sheet.getRange("B5");
  cell.setFormula("=SUM(B3:B4)");
}

Parameters
名前	タイプ	説明
formula	String	セルに設定する数式を表す文字列。

Return
Range - 連鎖用のセル範囲。

数式設定R1C1形式[setFormulaR1C1(式)]

setFormulaR1C1(formula)
この範囲の式を更新します。与えられた式はR1C1表記でなければなりません。

function setFormulaR1C1Sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var cell = sheet.getRange("B5");
  // これは、式をB5より上の3行の合計になるように設定します
  cell.setFormulaR1C1("=SUM(R[-3]C[0]:R[-1]C[0])");
}

Parameters
名前	タイプ	説明
formula	String	文字列式です。

Return
Range - 連鎖用のセル範囲。

数式配列設定A1形式[setFormula(式's)]

setFormulas(formulas)
式の2次元配列を設定します（この範囲のサイズと一致している必要があります）。与えられた式はA1表記でなければなりません。このメソッドは、行、次に列でインデックス付けされた式の2次元配列を取ります。配列の次元は範囲の次元に対応していなければなりません。

function setFormulasSample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // これにより、式は合計の行になり、そのすぐ下に平均の行が続きます。
  // 2次元配列のサイズは範囲のサイズと一致する必要があります。
  var formulas = [
    ["=SUM(B2:B4)", "=SUM(C2:C4)", "=SUM(D2:D4)"],
    ["=AVERAGE(B2:B4)", "=AVERAGE(C2:C4)", "=AVERAGE(D2:D4)"]
  ];

  var cell = sheet.getRange("B5:D6");
  cell.setFormulas(formulas);
}

Parameters
名前	タイプ	説明
formulas	String[][]	式の2次元文字列配列です。

Return
Range - 連鎖用のセル範囲。

数式配列設定R1C1形式[setFormula(式's)]

setFormulasR1C1(formulas)
式の2次元配列を設定します（この範囲のサイズと一致している必要があります）。与えられた式はR1C1表記でなければなりません。

function setFormulasR1C1Sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // これにより、一連の合計に続いて一連の平均に対応する式が作成されます。
  var sumOfRowsAbove = "=SUM(R[-3]C[0]:R[-1]C[0])";
  var averageOfRowsAbove = "=AVERAGE(R[-4]C[0]:R[-2]C[0])";

  // 2次元配列のサイズは範囲のサイズと一致する必要があります。
  var formulas = [
    [sumOfRowsAbove, sumOfRowsAbove, sumOfRowsAbove],
    [averageOfRowsAbove, averageOfRowsAbove, averageOfRowsAbove]
  ];

  var cell = sheet.getRange("B5:D6");
  // これは、式をB5より上の3行の合計になるように設定します。
  cell.setFormulasR1C1(formulas);
}

Parameters
名前	タイプ	説明
formulas	String[][]	R1C1形式の式の2次元配列。

Return
Range - 連鎖用のセル範囲。

セル範囲リストクラス[RangeList]の数式設定

セル範囲リストの数式設定メソッドです。

数式設定A1形式[setFormula(式)]

setFormula(formula)
範囲リスト内の各範囲の式を更新します。与えられた式はA1表記でなければなりません。

function setFormulaSample() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangeList = sheet.getRangeList(['A11', 'C11']);
  rangeList.setFormula('=SUM(B1:B10)');
}

Parameters
名前	タイプ	説明
formula	String	設定する式を表す文字列。

Return
RangeList - 連鎖用のセル範囲リスト。

数式設定R1C1形式[setFormulaR1C1(式)]

setFormulaR1C1(formula)
範囲リスト内の各範囲の式を更新します。与えられた式はR1C1表記でなければなりません。

function setFormulaR1C1Sample() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangeList = sheet.getRangeList(['A11', 'C11']);
  // これは、式をB5より上の3行の合計になるように設定します
  rangeList.setFormulaR1C1('=SUM(R[-3]C[0]:R[-1]C[0])');
}

Parameters
名前	タイプ	説明
formula	String	文字列式です。

Return
RangeList - 連鎖用のセル範囲リスト。

セル範囲クラス[Range]の数式取得

セル範囲の数式取得メソッドです。

数式取得A1形式[getFormula()]

getFormula()
範囲の左上のセルの数式（A1表記）を返します。セルが空の場合、または数式が含まれていない場合は、空の文字列を返します。

function getFormulaSample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // これはあなたがB2:B4を合計する
  // B5の関数を持っていると仮定します
  var range = sheet.getRange("B5");

  // 計算値と計算式を記録します
  Logger.log("Calculated value: %s Formula: %s",
  range.getValue(),
  range.getFormula());
}

Return
String - セルの数式です。

数式取得R1C1形式[getFormulaR1C1()]

getFormulaR1C1()
Returns the formula (R1C1 notation) for a given cell, or null if none.

function getFormulaR1C1Sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var range = sheet.getRange("B5");
  var formula = range.getFormulaR1C1();
  Logger.log(formula);
}

Return
String - R1C1表記の式です。

数式配列取得A1形式[getFormulas()]

getFormulas()
範囲内のセルの数式（A1表記）を返します。 2D配列のエントリは、数式がないセルの空の文字列です。

function getFormulasSample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var range = sheet.getRange("B5:C6");
  var formulas = range.getFormulas();
  for (var i in formulas) {
    for (var j in formulas[i]) {
      Logger.log(formulas[i][j]);
    }
  }
}

Return
String[][] - 文字列形式の数式の2次元配列です。

数式配列取得R1C1形式[getFormulasR1C1()]

getFormulasR1C1()
範囲内のセルの数式（R1C1表記）を返します。式のないセルの場合、2D配列のエントリはnullです。

function getFormulasR1C1Sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var range = sheet.getRange("B5:C6");
  var formulas = range.getFormulasR1C1();
  for (var i in formulas) {
    for (var j in formulas[i]) {
      Logger.log(formulas[i][j]);
    }
  }
}

Return
String[][] - R1C1表記の式の2次元配列です。