【GAS活用術⑨】フォームからスプレッドシートに送信されたデータに数式（計算式）を設定する

Hoshi-nok（ほしのっく）

2024年4月7日 14:52

Google Apps Script (GAS)を、もっと身近に、日々の暮らしに。

前回の【GAS活用術⑧】では書式設定の話をしましたが、今回は「フォームからスプレッドシートに送信されたデータに数式（計算式）を設定する」方法について、王道バージョンと裏技バージョンの２パターンをご紹介していきたいと思います。

GASで数式を設定するのはどんな時か

改めて、どんな時に数式を設定したいか、という話から始めましょうか。

例えば、【GAS活用システム④】で扱った「Tシャツ申込書」では、フォームから、Tシャツのカラー・サイズ・枚数を選択して送信すると、スプレッドシートにそのデータが蓄積されていきました。

申込みデータ毎に、申込合計枚数と合計金額を計算したい、、そんな場合に、手動で列を追加し、その列に対して数式を設定します。

もし、数式をあらかじめ設定しておいても、フォームから送信された行に数式は設定されません。

前回の【GAS活用術⑧】でふれましたが、フォームから送信されたデータは「新規の行」として挿入されるためです。

フォームから送信されたデータに数式を設定したい場合は、フォームからデータが送信されたタイミング（トリガー＝フォーム送信時）で、数式を設定する、Google Apps Script（GAS）を実行する必要があります。

今回は、【GAS活用システム④】の「Tシャツ申込書」を例に、申込合計枚数と合計金額に、以下のような数式をGASで設定してみます。

申込合計枚数の列（例ではM列）に、同じ行の、C列から一つ左隣の列までの値を合計（SUM）する数式を設定する
合計金額の列（例ではN列）に、同じ行の一つ左隣の列のセルの値×2000(単価)の数式を設定する（ついでに通貨の書式設定も）

王道の数式の設定方法

それでは、実際にGASを登録してみましょう。まずは王道バージョンです。

GASを登録（王道バージョン）

スプレッドシートのメニューバーから、拡張機能 > Apps Script を選択します。

無題のプロジェクトが生成され、Apps Scriptのスクリプトエディタが表示されたら、ファイル：コード.gsの「function myFunction ( ) { }」のところに、上書きする形で下記のコードをコピぺします。

function submitForm(e) {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //一番左のシートを取得
  const row = e.range.getRow();   //シートに書き込まれたデータの行番号
  const sumCol = 13;              //（★要変更★）数式を設定したい列番号を指定
  
  //書き込まれた行の、数式を設定したい列（申込合計枚数）のセルに数式を設定
  sh.getRange(row,sumCol).setFormulaR1C1("SUM(RC3:RC[-1])");

  //書き込まれた行の、一つとなりの列（合計金額）のセルに数式と通貨の書式を設定
  sh.getRange(row,sumCol+1).setFormulaR1C1("RC[-1]*2000").setNumberFormat("[$¥-411]#,##0"); 
}

実際にGASを実行する場合は、4行目の数式を設定したい列番号を必要に応じて変更してください（例ではM列＝13番目の列なので13としています）。

「無題のプロジェクト」をクリックして、プロジェクト名を変更し、ツールバーの保存ボタンでプロジェクトを保存します。

王道ならsetFormulaR1C1メソッドで数式を設定

setFormulaR1C1メソッドで数式を設定しています。私も少し前に使い始めたわりには、今や旧知の仲といえるくらい最近よく使ってます。

R1C1方式は、基準となるセルの、相対的な位置を行(RowのR)方向の数値と、列(ColumnのC)方向の数値で表す形式です。

例えば、N8のセル（N列8行目のセル）を基準にすると、M8のセルのことは、同じ行なのでR[0]、列は一つ左（列番号が一つ減る）のでC[-1]、つまり、R[0]C[-1]と指定できます。

ここまでは【GAS活用システム②-2】チケットの枚数管理をする・応用設定編でも説明したのですが、後から知ったことが以下の二つ。

同じ行ならR[0]と指定するが、[0]は省略可能（RだけでOK）
一つ左の列を指定するならC[-1]だが、[ ]をつけずにC3と、Cの後に数値を指定すると3列目、つまりC列となる（相対的な位置だけでなく、絶対的な位置も指定が可能）

二つ目の[ ]をつけずにC3と、さくっと絶対的な位置で指定できるのは結構便利です。例えば、上記の例ではM列が基準となるセルで、C列なら、、、-10戻るから、、えっと、C[-10]かな？、、というように、基準となるセルと対象のセルが離れているとわかりづらいのです。

一方、一つ上の行ならR[-1]、一つ左隣の列ならC[-1]、と近い位置なら相対的な位置の方がわかりやすいです。

このように、相対的な位置と絶対的な位置をうまいこと組み合わせて、setFormulaR1C1メソッドで数式を指定することができます。

ちなみに、話が前後しましたが、基準となるセルはgetRangeで指定します。

【GAS失敗談】で説明したように、フォームからスプレッドシートに書き込まれたデータの行番号は「e.range.getRow()」で取得できますから、getRange(e.range.getRow(), 14)とすることで、フォームからスプレッドシートに書き込まれた行の14列目（N列）のセル、となります。

裏技の数式の設定方法

え、、R1C1方式？、、もっと楽な方法は、、？、と思われた方には、【GAS活用術⑧】の書式設定と同様、裏技をご用意しています。

2行目の数式と書式をがっつりコピーするcopyToメソッドです。

GASはちょこっとシンプルに（裏技バージョン）

copyToメソッドで2行目の数式と書式をがっつりコピーする裏技バージョンで、さきほどのsubmitForm関数を書き換えてみます。

function submitForm(e) {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //一番左のシートを取得
  const row = e.range.getRow();   //シートに書き込まれたデータの行番号
  const sumCol = 13;              //（★要変更★）数式を設定したい列番号を指定
  
  //2行目の2セル分の数式と書式をフォームから送信された行にがっつりコピー
  sh.getRange(2,sumCol,1,2).copyTo(sh.getRange(row,sumCol,1,2));
}

ちょこっとシンプルになりましたね。今回数式を設定するセルが隣あった2セルだったので一つの文にまとまりました。

copyToメソッドを使うと数式だけでなく書式設定もコピーされるので、その分、シンプルになります。今回の例では、合計金額の列に通貨の書式を設定しておけばそれもコピーされます。

事前に2行目の数式設定＋copyToメソッドでがっつりコピー

もちろん2行目の、対象のセルにはあらかじめ数式を設定しておくことが前提です。

この例では、M2のセルには、=SUM(C2:L2)

N2のセルには、=M2*2000

と数式を設定しておきます。スプレッドシートでの数式設定は簡単ですね。

このように、2行目にあらかじめ数式を設定しておき、copyToメソッドで、常に2行目から、フォームから挿入された行に対して数式をコピーする訳です。

copyToメソッドでは、コピー元の範囲もコピー先の範囲もgetRangeで指定します。今回は隣り合った２つのセルなので、一度に指定できます。

数式が複雑でも、書式が設定されていても、同じコードでがっつりコピーができるのが、裏技バージョンの利点と言えるかもしれません。

トリガーの設定と動作確認

王道バージョンと裏技バージョンのどちらでもお好みの方のGASを登録して保存したら、トリガーの設定をして、動作確認をしてみましょう。

トリガーの設定

定義したsubmitForm関数を、フォーム送信のタイミングで実行するトリガーを設定する手順については、【GAS活用術⑧】のトリガーの設定を参照してください。

スクリプトを承認する手順もまったく同じです。

動作確認

フォームからテストデータを入力・送信して、スプレッドシートを開いてみると、

ちゃんと数式が設定され、計算も行われたことが確認できると思います。
結果は王道バージョンと裏技バージョン、どちらも一緒です。

今回は、【GAS活用術⑨】として、フォームからスプレッドシートに送信されたデータに数式（計算式）を設定する方法についてご紹介しました。

ご興味があれば、ぜひお試しいただければと思います。今回、使用した「Tシャツ申込書」は以下の記事でフォームの作成手順から紹介しています。

また、一つ前の【GAS活用術⑧】では、フォームからスプレッドシートに送信されたデータに書式設定をする方法について話をしていますので、あわせてお読みいただけると幸いです。

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