【GAS活用システム④-2】GoogleフォームのTシャツ申込書テンプレートを改造する・GAS設定編

Hoshi-nok（ほしのっく）

2024年3月10日 23:01

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

前回より、GAS活用システムの第四弾として、学校やサークルなどでオリジナルTシャツを作ったときの「Tシャツ申込書」を扱っています。

前回の【GAS活用システム④-1】では、Googleフォームのテンプレート「Tシャツ申込書」を変更して、複数カラー（種類）・複数サイズ・複数枚数を申し込めるようにしました。

少し変えれば、スポーツのユニフォームやシューズなどの一括購入前のもう仕込みにも使用できますね。

今回は、その続編として、Google Apps Script (GAS)を設定して、申し込み枚数・申し込み合計金額を自動計算したり、申し込みがあったらLINEで管理者に通知したり、とさらに便利に実用的にしていきます。

GASで申込枚数と合計金額を自動計算

そもそもなぜGASが必要か

まず、前回、フォームにリンクしたスプレッドシートを開きます。

フォームの「Tシャツ申込書」と同じフォルダに、変更していなければ「Tシャツ申込書（回答）」の名前でスプレッドシートが作成されていると思います。

前回、フォームから送信したデータが表示されます。

ここに合計枚数・合計金額を計算するためには、まず「その他のご意見またはコメント」の列の左にでも、列を２列追加して

申込合計枚数、合計金額など、お好みで見出し名を設定して

申込合計枚数には、関数SUMを使って、同じ行の、C列のセルから、左隣の列（下の例はL列）のセルまでの範囲指定をして
=SUM(C2:L2)

合計金額には、合計枚数×単価（ここでは、すべてのサイズで一律2000円とします）を計算するために、左隣の申込合計金額のセル（下の例ではM2セル）に2000をかける数式、
=M2*2000

を設定していけばいいわけですね。あとは、合計金額に通貨の書式設定をすれば完璧ですね。

あとは、この２つのセルの数式と書式を下のセルに向かってコピーしておけば、、、

お、いい感じ。問題なさそうに思えます。
が、この状態で、フォームから申込みデータを送信みるとどうなるでしょうか。

前回の短縮したURLからスマホでアクセスしてももちろんいいですが、今、表示しているスプレッドシートのメニューバーから、
　ツール＞フォームを管理＞実際のフォームを開く
を選択してもフォームが開きます。

フォームからテストデータとして、適当なサイズ・枚数を選択して送信してみると、、、

あららららら。申込合計枚数と合計金額が表示されていませんね。。
そうなのです。フォームから送信されたデータは、新規の行として挿入されるので、残念ながら計算式や書式は設定していても反映されません。

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

GASの登録

改めて今回、GASで登録したい処理は、以下の内容です。

申込合計枚数の列（例ではM列）に、同じ行の、C列から合計枚数の列の左隣の列までの値を合計（SUM）する計算式を設定する
合計金額の列（例ではN列）に、単価*同じ行の左隣の列の値の計算式を設定し、通貨の書式設定をする

これに、LINE通知するためのスクリプトも一緒に登録することにします。

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

無題のプロジェクトが生成され、Apps Scriptのスクリプトエディタが表示されました。ファイル：コード.gsに、「function myFunction」が表示されています。

「function myFunction ( ) { }」のところに、上書きする形で下記のコードをコピぺします。

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

  //書き込まれた行の合計金額のセルに計算式と通貨の書式を設定
  sh.getRange(row,sumCol+1).setFormulaR1C1("RC[-1]*"+unitPrice).setNumberFormat("[$¥-411]#,##0"); 

  const items = sh.getRange(1,2,1,sumCol-2).getValues().flat();
  let msg = "\n"+ e.namedValues[items[0]] +"さんから以下の申込を受け付けました";

  //申込みがあったサイズと枚数のみメッセージとして出力(種類・サイズの数だけ繰り返し)
  for (let i=1; i < e.values.length-2; i++) {
    let item = items[i];
    if (e.namedValues[item] != ""){     //枚数が指定されている時のみ
      msg += "\n　" + item + ": " + e.namedValues[item]+"枚";
    }
  }              
  //申込合計枚数と合計金額を出力
  msg += "\n　申込合計: " + sh.getRange(row,sumCol).getValue() + "枚 "
                         + sh.getRange(row,sumCol+1).getDisplayValue();

  //コメントが入力されている場合のみメッセージに追加
  if (e.values[e.values.length-1] != ""){      
    msg +=  "\nコメント: " + e.values[e.values.length-1];
  }
  console.log(msg);

  const token = "★トークンをここに設定★"; //トークンを設定
  //sendLine(msg, token);  //sendLine関数でmsgをtoken宛にLINE通知
}

function sendLine(msg,token){
  
  let options = {
    "method": "post",
    "Content-Type" : "application/x-www-form-urlencoded",
    "payload": "message=" + msg,
    "headers":{"Authorization" : "Bearer " + token}
  };
  UrlFetchApp.fetch("https://notify-api.line.me/api/notify",options);
}

上記のコードをコピペしたら、まず、変更していただきたいのは以下の二点です。

4行目：申込合計枚数の列番号を指定します（例ではM列＝13番目の列なので13としています）。必要に応じて、4行目の13を別の番号に変更してください。
5行目：Tシャツの単価です。サイズに関わらず、一律であることを前提にしています。ここでは、2000円としています。必要に応じて、2000を別の数値に変更してください。

「無題のプロジェクト」をクリックして、プロジェクト名を変更します。

ツールバーの保存ボタンでプロジェクトを保存します。

その後、デバッグと実行ログの間に、submitFormの関数が表示されていていることを確認後、実行ボタンをクリックします。

「承認が必要です」と言われます。

新規プロジェクトを実行する時に必要な「実行権限の承認」です。以下の手順で承認しましょう。
→ 「権限を確認」をクリック
→ Googleにログイン画面で、自分のアカウントを選択
→ 「詳細」、「[プロジェクト名]（安全ではないページ）に移動」を順番にクリック
→ 「許可」をクリックすることで、実行権限の承認完了

手順が不安な方は、【GAS活用①-3】のページで、画面ショットつきで説明しているので、そちらを参考にしてください。

実行権限の承認が完了すると、以下のような実行ログが出力されます。

エラー　TypeErrorと出てきて、どきっとしますが、このエラーが出てきて正解です。

今回のスクリプトの一行目に「function submitForm(e)」と、()の中にeが入っていますが、このeは、フォームの回答を受け取る引数を意味します。

submitForm関数は、フォームから送信された回答を受け取ることを前提として作成されているので、スクリプトエディタから実行しても上記エラーになってしまうのです。

エラーにならずに動作させるためには、フォーム送信時のトリガーでsubmitForm関数を実行する必要があります。

トリガーの設定

それでは、トリガーの設定をしてみましょう。スクリプトエディタを表示し、画面左端のメニューバーで「トリガー」を選択します。

「トリガーを追加」をクリックします。

トリガーを追加の画面で、「実行する関数の選択」でsubmitForm、「イベントのソースを選択」でスプレッドシートから、「イベントの種類を選択」でフォーム送信時を選択します。

保存をクリックすると、なぜかまた承認のため、もう一度アカウントの選択画面が表示されることがあります。しかも英語で。。

一時的なもの（もしくは私だけ？？）かもしれませんが、もし、英語でアカウントを選択する画面が表示表示されても、臆せずに再度、下記の手順で実行権限の承認を実施してください。

→ 自分のアカウントを選択
→ 「Advanced」、「Go to [プロジェクト名] (unsafe)」を順番にクリック
→ 「Allow」をクリックすることで、実行権限の承認完了

トリガーが設定されると、以下のような画面が表示されます。

計算式と書式設定の動作確認

もう一度、フォームからテストデータを入力して、以下のように申込合計金額と合計金額が表示されるか確認をしてみましょう。

いい感じです。

GASでフォームから送信される毎にLINE通知する

続けて、フォームから申込みデータが送信される毎に、LINE通知する設定をします。

GASを2行だけ変更

実は、さきほどトリガーを設定したsubmitForm(e)関数の中に必要なコードは設定しているので、簡単にLINE通知できちゃいます。

修正する箇所は33～34行目の2行だけです。

まず、LINE通知の送り先のトークンを設定します。

トークンは、43文字程度の英数字となります。LINE Nofifyの公式サイトにご自分のLINEアカウントでログインして、通知を送りたいLINEグループ、または自分自身に通知するためのトークンを取得しましょう。

細かい手順は、【GAS活用術①-2】LINEグループへリマインド通知を自動送信・その２で画面ショットつきで書いてありますので、参考にしてください。

そして、トークンが取得できたら、33行目の" "の中、★～★までを、LINE通知の送り先のトークンで上書きしてください。

注：トークンは文字列になるので、両端を "(ダブルクォーテーション)で囲む必要があります。" "で囲まれた中（★～★までの間）を変更して、" "は消さないようにしてください。

次に、34行目の始めの // を消します。

上記のGASのコードでは、あらかじめ34行目の先頭に // をつけてコメントアウトして、sendLine関数を実行しないようにしています。実際にLINE通知したい場合は、先頭の // を消すだけです。

ツールバーの保存ボタンでプロジェクトを保存します。

なお、自分自身に送る場合は不要ですが、グループ宛に送る場合は、【GAS活用術①-6】のLINEグループにLINE Notifyを招待するの手順が必要になります。

これでフォームからデータが送信されたらLINE通知が来るはずです。確認してみましょう。

LINE通知が来るか動作確認

再びフォームからテストデータを入力します。

注文データがスプレッドシートに書き込まれ、

同じ内容で以下のようなLINE通知がくれば、完璧です。

数種類・複数サイズがあっても、申込みがあったものだけ通知されるようにしています。

残念ながら、LINE Notifyの制限により、申込みをした人に直接LINE通知を送ることはできませんが、確かに申込を受けましたよ、ということを伝えるために、このLINE通知を手動でコピーして送ってもいいと思います。

申込を受け付ける前と受け付けた後に

あとは、実際に申込を受け付ける前に、いろんなパターンでフォームからテストデータを送信してみてください。

テストが終わったら、一旦スプレッドシートからテストデータを削除しましょう。フォーム側にも回答が保持されているので、こちらもテストデータは一旦削除しておいた方がいいかもしれません（あまり見ていないなら気にしなくてもいいかもしれませんが）。

回答を削除したら、フォームのリンクをお知らせして申込の受付を開始しましょう。

受付期間が終わったら、フォームの回答タブで「回答を受付中」の状態から

「回答を受け付けていません」の状態に変更しておきましょう。

回答の受付を終了したフォームにアクセスすると、こんなメッセージが表示され、申込データを入力することができなくなります。

注文の受付が終わってから、スプレッドシートで各サイズ毎の数を集計すればいいわけです。

以上が、今回ご紹介しようとしたすべての手順です。

あらためまして、今回は事前に用意されていた「Tシャツ申込書」テンプレートを元に、以下のように実用的に改造しました。

複数サイズを複数枚数、申込み可能にする
カラーなど複数の種類がある場合にも対応する
申込みデータをスプレッドシートに送信するタイミングで、申込み枚数・申込み合計金額を自動計算する（単価はどのサイズでも同じとする）
管理者に申込み内容の詳細（名前、種類、サイズ、枚数、合計金額）をLINEで通知する

テンプレートを変更する手順は、【GAS活用システム④-1】をご参照ください。

機会があればぜひ使ってみてください。

また、下記の記事で「フォームからスプレッドシートに送信されたデータに数式を設定する」方法についてこの「Tシャツ申込書」のスクリプトを元に解説していますので、よろしければこちらもご覧ください。

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