【GAS活用術⑨】フォームからスプレッドシートに送信されたデータに数式(計算式)を設定する
Google Apps Script (GAS)を、もっと身近に、日々の暮らしに。
前回の【GAS活用術⑧】では書式設定の話をしましたが、今回は「フォームからスプレッドシートに送信されたデータに数式(計算式)を設定する」方法について、王道バージョンと裏技バージョンの2パターンをご紹介していきたいと思います。
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で指定します。今回は隣り合った2つのセルなので、一度に指定できます。
数式が複雑でも、書式が設定されていても、同じコードでがっつりコピーができるのが、裏技バージョンの利点と言えるかもしれません。
トリガーの設定と動作確認
王道バージョンと裏技バージョンのどちらでもお好みの方のGASを登録して保存したら、トリガーの設定をして、動作確認をしてみましょう。
トリガーの設定
定義したsubmitForm関数を、フォーム送信のタイミングで実行するトリガーを設定する手順については、【GAS活用術⑧】のトリガーの設定を参照してください。
スクリプトを承認する手順もまったく同じです。
動作確認
フォームからテストデータを入力・送信して、スプレッドシートを開いてみると、
ちゃんと数式が設定され、計算も行われたことが確認できると思います。
結果は王道バージョンと裏技バージョン、どちらも一緒です。
今回は、【GAS活用術⑨】として、フォームからスプレッドシートに送信されたデータに数式(計算式)を設定する方法についてご紹介しました。
ご興味があれば、ぜひお試しいただければと思います。今回、使用した「Tシャツ申込書」は以下の記事でフォームの作成手順から紹介しています。
また、一つ前の【GAS活用術⑧】では、フォームからスプレッドシートに送信されたデータに書式設定をする方法について話をしていますので、あわせてお読みいただけると幸いです。
この記事が気に入ったらサポートをしてみませんか?