GASでスプレッドシートに関数を埋め込む

Uニヴェルス

2023年12月14日 22:56

[懺悔]　春の卒業LTでは面倒すぎて説明を諦めた内容をここに書き残し、スッキリして年を越したいと思います^^*

スプレッドシート関数は便利ですが、定期的に多数のシートに適用したい場合にはシートのコピーだけでも面倒ですよね。

各シートで少しずつ規則的に関数を変えたい場合も手修正では手間がかかります。

そこでGASでシートに自動で関数を埋め込んで集約的に管理しています。

そうすると、GASのGSファイルさえ見れば、どのセルにどのような関数を埋め込んだのか、その意図はなんだったのかも一目で分かりますし、修正や複製もラクなので、助かっています。

GASでスプレッドシートに関数を埋め込むメリット

可読性
再現性
メンテナンス性

GASでスプレッドシートに関数を埋め込む方法（setFormulaメソッド）

GASでスプレッドシートに関数を埋め込む時は、まず関数を埋め込みたい範囲（Range）を取得し、それに対してsetFormulaメソッドを使用します。

setFormulaメソッドの( )の中には、その範囲に埋め込みたい関数を　"（ダブルクォーテーション）で囲んで入力します。

たとえば、あるスプレッドシートの最初のシートのB5セルに以下の関数を埋め込みたいとします。

=SUM(B3:B4)

その場合、次のスクリプト例のように書けばOKです。（上記のGoogle Apps Script公式サイトより引用）

function test(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

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

「 cell.setFormula("=SUM(B3:B4)");」というのが関数を埋め込んでいる部分です。

ただ、これだけだと何の役に立つのか、という感じですよね。

ですが、たとえば以下のような生徒クラブ一覧シートがあり、毎月、クラブの数や参加者が変動するとして、毎月のクラブごとの出欠簿を自動作成して担当者に振り分け送信したい、というような場合、GASでクラブごとの参加者出欠簿を自動生成して関数を埋め込むと、毎月ボタン一つで何十という参加者出欠簿を自動作成して送信したりできます。

GASの配列を使うことによって、例えば上記の「生徒クラブ一覧」シートに記載されているクラブ名に合わせてシートが自動生成され、同時に各クラブに固有の情報が取得できます。

GASでスプレッドシートに複雑めの関数を埋め込んだ例

例１

埋め込む関数式が単純なら、先ほどのスクリプト例のように関数を「"」（ダブルクォーテーションマーク）で囲んでsetFormulaメソッドの引数に渡すだけで完了です。

ですが埋め込みたい関数自体に「"」（ダブルクォーテーションマーク）が含まれる場合（QUERY関数など）や、関数式の中にGASの変数を埋め込んで、ループごとに関数式の内容を少しずつ変えたい場合は、「" 」の使い方やエスケープの仕方に注意が必要なので、備忘を兼ねてメモしようと思います。

まずは、以下のような関数式を埋め込みたい場合。

=QUERY('クラブ'!A1:C100,"select A where C = '英語クラブ')

（シチュエーションを説明すると長くなるので、当面、こんな式を埋め込むにはどうするか、というところだけご覧ください。）

ただし、Cの条件として入れるクラブ名には、GASでマスタシートから取得済みの配列の要素を入れたいとします。

（つまりGASで事前にgetRange()などでその時存在するクラブ名の一覧を取得してあるので、現状、clubs = ['英語クラブ','物語クラブ','実験クラブ','パズルクラブ','お絵描きクラブ']といった配列が存在し、clubs.forEach((club, index) =>{ 　ループ処理　　})でclubsの要素を１つずつループ処理している、その{ ループ処理 }内でこの関数式の埋め込みを行うという設定です。

やっぱり説明すると長くなるので、当面、こんな式を埋め込むにはどうするか、というところだけご覧くださいw）

ループの度に作成する新規シートのB1セルに上記の関数式を埋め込むGASのスクリプト(一部）は以下のようになります。

newSheet.getRange("B1")
        .setFormula("=QUERY('クラブ'!A1:C100,\"select A where C = '" + club + "'\")");

ダブルクォーテーション、バックスラッシュ、シングルクォーテーションが組み合わさって、一見なんじゃこりゃ？という状態になっていますね。

ですがsetFormulaの()の中を分解してみると、規則にのっとっているのが分かります。

①まず、以下の図の赤字は、setFormulaメソッドによって関数式を埋め込む時、その関数式の前後につけることになっている「"」（ダブルクォーテーションマーク）です。

②次に、以下の図の緑は、QUERY関数のselect句の前後につけることになっている「"」（ダブルクォーテーションマーク）です。

ただし、setFormulaメソッドの()内では、最初の「"」から次の「"」までを引数（つまり、埋め込むべき関数）と認識するようなので、select句の前後の「"」をsetFormulaメソッドの引数の終わりの「"」だとPCが認識しないように、「"」の直前にバックスラッシュ「\」を置いてエスケープする必要があります。

③最後に、以下の図の水色部分について。

setFormulaの引数となる埋め込む関数内にGASの変数(特に文字列）を埋め込む場合

setFormulaメソッドの引数内にGASの変数を埋め込む場合（ここではclubが変数。getRangeでシートから配列として取得したclubsの要素です。変数の前でいったん""で囲まれた文字列部分を終わらせ、"文字列" + 変数 + "文字列" という構成になっています。

PCが変数を変数として認識できるようにしてやる、GASの通常の書き方で説明できる気がします。

ここでは更に、clubを文字列としてQUERY関数に渡したいので、その外側にシングルクォーテーションマーク「'」を付けています。

例２

newSheet.getRange("A2")
        .setFormula("=QUERY(" + sheetName + "!A2:Y300,\"select A,B,C,D where " + alphabetArr[index] + " = TRUE\")");

（またシチュエーションの説明が長くなりそうですが、こちらは「生徒クラブ一覧」シートに基づいて、クラブの数だけループしながらシートを生成し、そのシートに各クラブの参加者(チェックボックスがTRUEの生徒）を抽出掲載していく場面で使っているスクリプトです。）

①sheetNameも決め打ちではなく変数にして、変更に柔軟に対応できるようにしています。

②ここではalphabetArrは、自作関数で作成した[A,B,C,D,…]という配列です。これを使うことで、"select A,B,C,D where E = TRUE" というような決め打ちではなく、新しいクラブのループに入るごとに埋め込まれるQUERY関数のselect句が、"select A,B,C,D where F = TRUE"や"select A,B,C,D where G = TRUE"へと変化していくようになっています。(クラブ名をシートから取得し、それに合ったシートを自動生成し、それに合った列を自動参照して参加者出欠簿を掲載する）

前提条件が複雑なので、例にあげたコードが分かりづらくてすみません。QUERY関数ではなくてimportrangeと表示・非表示を使って参加者出欠簿を作った方がいいかもしれませんが、setFormulaメソッドの引数においてエスケープをどうするか、また変数を埋め込む場合はどうするか、の備忘としてメモしてみました。

この記事が気に入ったらサポートをしてみませんか？