【GAS活用システム①-3】立替と会費の精算を自動化する・補足編

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

前々回から「立替と会費の精算システム」を紹介しています。

前々回の【GAS活用システム①-1】では使用方法を先に、また、前回の【GAS活用システム①-2】では設定方法をご紹介しました。

今回は、フォームを自動生成したスクリプトや、使用したスプレッドシートの関数について補足説明をしていきたいと思います。

SpreadsheetAppクラスのflushメソッド

前回の【GAS活用システム①-2】では、GASのcreateForm関数を実行することで、フォームを作成やスプレッドシートとの連携をしました。スプレッドシートとの連携は、createForm関数の以下のコードで実行しています。

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  ...(略)...

  const ssId = ss.getId(); // スプレッドシートIDを取得
  ...(略)...
 
  //フォームの回答の保存先を集計表シートのスプレッドシートに設定
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ssId);

これらのコードで何を実行しているか、あらためて、手動でフォームを作成した場合と比較してみましょう。

手動で画面からフォーム作成後、スプレッドシートを連携させるためには、フォームの「回答」のリンクから、「スプレッドシートにリンク」をクリックします。

送信先として、既存のスプレッドシートを選択し、

集計表シートを含む「立替と会費の精算テンプレート」をコピーしたファイルを選択すると、「フォームの回答１」という名前のシートが一番左側にできます。

これらの操作をGASで実行しているのが、setDestinationになります。

さて、createForm関数では、フォームと連携しているこのシート「フォームの回答1」の名前を「立替明細」に変更しています。createForm関数の一番最後の以下の部分です。

  //フォームと連携しているシート名を立替明細に変更
  SpreadsheetApp.flush();
  let sh = ss.getSheets()[0]  //一番左のシート名
  sh.setName("立替明細")   //シート名を変更

実は、このシート名の変更がなかなかうまくいきませんでした。どううまくいかなかったというと、変更したいシート「フォームの回答１」ではなく、既存のシート「集計表」の名前が「立替明細」に変わってしまったのです。

どうやらフォームと連携している「フォームの回答１」シートが作成される前に、一番左のシートの名前を変更する処理が実行されてしまっているようでした。

いろいろ試行錯誤（スプレッドシートの連携させるコードからシート名を変更するコードまで間に他の処理を入れたり、sleepメソッドで実行時間を遅延させてみたり）しましたが、最終的に「SpreadsheetApp.flush();」と追加することでうまくいきました。

GoogleのApps Scriptのリファレンスには、SpreadsheetAppクラスのflushメソッドの説明として、「スプレッドシートの保留中の変更をすべて適用します。」と記載されていますが、要は、スプレッドシートを最新の状態にしてくれるようです。

flushメソッドのおかげで、ちゃんとシートが追加された最新の状態となり、新しく作成されたシートを「立替明細」の名前に変更できました。

スプレッドシートのSUMIFS関数

立替明細シートが作成された後に、名前付き範囲を３つ定義しました。

立替明細シートのB列（'立替明細'!B:B）を「立替えた人」、C列（'立替明細'!C:C）を「分類」、D列（'立替明細'!D:D）を「立替金額」という名前付き範囲です。

その後、集計表シートのB5のセルに
  =SUMIFS(立替金額,立替えた人,$A5,分類,B$4)
の計算式を設定しました。

B5セルで集計したいものは、立替明細シートのデータのうち、立替えた人が佐藤さんの、分類が食材費の、立替金額の合計値です。

このように複数条件を指定して合計値を算出したい場合は、SUMIFS関数を使用します。

また、$A5、B$4の「$」はコピーした時に、A列目(人の名前)と4行目(分類名)は動かさずに固定したいためにつけています。

試しに、$を付けない状態で、B5の計算式をC5にコピーしてみます。B5のセルの右下の青い丸を左クリックでつかんでC5までドラッグします。

C5の計算式をみると、「=SUMIFS(立替金額,立替えた人,B5,分類,C4)」と計算式の参照セルも一つずつ列がずれてしまっています。

コピーしても、A列目(人の名前)と4行目(分類名)は固定したいので、$A5、B$4と$を固定したい列または行の前に$をつけます。
$をつけて、コピーすると、今度は期待通りに計算式が表示されました。

$については、絶対参照というキーワードで調べるといろいろ出てくると思います。

スプレッドシートのSUMPRODUCT関数

「立替と会費の精算テンプレート」には、あらかじめSUMPRODUCT関数を使用して、会費合計を計算する計算式を設定してあります。

上記のように大人と子供の会費が異なる場合、「=大人会費*大人参加人数+子供会費*子供参加人数」で会費合計が求められますよね。この式をコンパクトに記述できるのが、SUMPRODUCT関数です。

この会費のように、単価×数量の合計を計算する時に、SUMPRODUCT関数は便利です。引数として、「単価の範囲」と「数量の範囲」を指定することができます。

SUMPRODUCT関数なら会費単価が10パターンあっても、単価の範囲を指定できるので無駄に計算式が長くなることはありません。

単価の範囲はコピーしてもずれることがないように、$H$2:$I$2と$でがっちり固定しています。

---

以上が、【GAS活用システム①】立替と会費の精算を自動化する、の補足編でした。

今回は短めでしたが、この辺で。