【GAS活用システム③-3】GASデジタル伝票で注文受付・販売管理をする～補足編～

Hoshi-nok（ほしのっく）

2024年2月18日 10:46

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

GAS活用システムの第三弾として、「GASデジタル伝票での注文受付・販売管理」について紹介しています。

前回の【GAS活用システム③-2】の設定編に引き続き、今回は以下のような内容を補足編として説明していきたいと思います。

品目や価格を変更する時

前回は、【品目集計表】シートの品目と単価から、GASでフォームや【注文明細】シートを自動生成する方法をご紹介しました。

GASを実行した後は、【品目集計表】シート、フォーム、【注文明細】シートの計3箇所で、品目と単価は完全に一致している状態です。

この品目と単価の完全一致の状態から、品目を追加したり、単価を変えたりする場合の手順について説明していきます。

まず、【品目集計表】シートを開きましょう。

ここに、例えば、から揚げの単価を300円に変更して、新しい品目として、ポップコーン 250円を追加したい場合は、

こんな風に行を追加して、品目や単価を変更します。売上数や売上金額はあとでGASが設定してくれるのでこのままで大丈夫です。

さて、この状態では、もう一つの【注文明細】シートともフォームとも同期がとれていないので、createUpdateForm関数を実行して、現在の品目と単価から【注文明細】シートとフォームを再作成します。

スプレッドシートのメニューバーから、拡張機能 > Apps Script を選択して、スクリプトエディタを起動します。

「フォーム作成更新.gs」を選択して、「CreateUpdateForm」が表示されているのを確認したら、「実行」をクリックします。

以下のような実行ログが表示されます。

スプレッドシートに戻ってみましょう。まず【品目集計表】シートの売上数や売上金額が0に初期化されています。

これは、新しい【注文明細】シートを集計しているからです。【注文明細】シートをみると、から揚げが300円に変更されていて、ポップコーン 250円が追加されています。注文データがなくなったので、【品目集計表】シートで売上数や売上金額が0になったわけですね。

これまでの注文データは、注文明細bk_で始まるシートに残っています。から揚げが変更前の250円で、ポップコーンはありませんね。

注文明細bk_の後ろに、バックアップした時間をつけてシート名にしています。販売途中で、単価を引き下げたり、品切れが発生した場合などに、それまでの販売履歴は残しつつ、新しい品目と単価で同じように販売を続けていけます。

注文明細bkシートの古い販売履歴は、単なるテストデータで不要という場合はシートの削除をしてください。

もちろんフォームも新しい品目と価格で再作成されています。

フォームは、フォーム上の質問項目を一旦すべての削除して、再度、新しい品目と価格をもとに質問項目を再作成しています。フォームのファイルそのものは削除していないので、設定したデザインはそのまま残ります。

また、フォームにアクセスするためのURLも変更されないので、一日の途中で価格が変更されたりしても、同じURLにアクセスすれば、価格変更後の最新の状態で注文受付が継続できます。

フォームの一番はじめの質問項目「番号札」について

今回、フォームの一番上の質問項目は、番号:01～番号:20の選択肢をもつ、「番号札」にしています。

注文を受けるにあたり、なんらかの方法で購入者を識別する必要があると思いますが、今回は、番号札を使用することを想定しました。

番号を増やしたり減らしたりしたい時

とりあえず、番号を20までにしていますが、例えば番号:30まで作成したい場合は、createUpdateForm関数の以下の部分の20を

  let OneTo20 = createNumList(20, "番号:"); //"番号:01"～"番号:20"のリストを生成

...
  //はじめに番号札アイテムを作成（番号:01～番号:20の選択肢をもつプルダウンで）
  form.addListItem().setTitle("番号札").setChoiceValues(OneTo20);

次のように30に変更してください。

  let OneTo30 = createNumList(30, "番号:"); //"番号:01"～"番号:30"のリストを生成

...
  //はじめに番号札アイテムを作成（番号:01～番号:30の選択肢をもつプルダウンで）
  form.addListItem().setTitle("番号札").setChoiceValues(OneTo30);

これでスクリプトを保存して、再度、createUpdateForm関数を実行すれば番号:30までになります。

番号札でなくテーブル番号に変更したい時

"番号札"という項目名ですが、学園祭などでカフェをするのでテーブル番号にしたい、という場合は、"番号札"を"テーブル番号"に変えればそれでOKです。

番号札もテーブル番号も不要の時

また、たとえばフリーマーケットのような場所での販売なので、番号札やテーブル番号も不要という場合でも、フォーム上で、一つ目の品目の前に、質問項目をひとつ設定しておく必要があります。

フォームの一番はじめに、購入者を識別するための質問項目がひとつ設定されている前提で、すべてのスクリプトが記述されているためです。

番号札やテーブル番号を使用しない場合は、たとえば"購入者"といった項目名で、プルダウンではなく、記述式の質問項目にしてみましょう。

  //はじめに番号札アイテムを作成（番号:01～番号:20の選択肢をもつプルダウンで）
  form.addListItem().setTitle("番号札").setChoiceValues(OneTo20);

  //はじめに購入者アイテムを作成（記述式で）
  form.addTextItem().setTitle("購入者");

こんな感じのフォームになります。

友人・知人が買いに来てくれた場合はお名前を、通りすがりの方が購入してくれた場合は「親子」「学生」などざっくりした購入層をあらわす言葉を入力しておいてもいいですね。

もちろん、必須項目ではないので、何も入力しないでも大丈夫です。

とにかく、「一つ目の品目の前に、なんでもいいので質問項目をひとつだけ設定しておく」ことが、正しくGASを動作させるために必要となります。

【注文明細】シートの「調整金額」列の使い方

【注文明細】シートの「合計金額」の左隣にある、「調整金額」の列について簡単に説明します。

注文受付後のお会計の際に、「おまけしちゃうね」とか、「お釣りはいらないよ」なんてことが起きた時、そのままだと手元の現金とデータ上の売上金額の合計に差異が出てきてしまいますよね。

このようにおまけした金額や寄付された金額があった場合、その金額を「調整金額」として入力することができます。

たとえば、1850円の合計金額に対して「2000円でお釣りは結構よ」と言われた場合、また、「1050円だけど50円おまけしちゃう」場合は、以下のように入力します。

この調整金額は、【品目集計表】シートでも合計が算出されます。【品目集計表】シートでは、商品の売上合計と、調整金額合計を含めた売上総合計を集計しています。

なお、調整金額は、注文受付時ではなく、合計金額が計算された後の、会計のタイミングで入力すると思われるため、フォームからではなく、スプレッドシートに直接入力することにしました。

フォームのリンクを取得する

さて、ここまで設定が終わったら、実際に注文データを入力するためのフォームのリンクを取得しましょう。フォームの編集画面の送信ボタンをクリックします。

フォームを送信画面で、送信方法で真ん中のリンクアイコンをクリックします。URLを短縮したい場合は、チェックボックスをクリックして表示されるリンクをコピーします。

このリンクを、実際に注文データを入力する人(達)にお知らせして、注文データがちゃんとスプレッドシートに挿入されるか、合計金額が計算されるか、LINE通知が行えるか、等々を確認しましょう。

また、この時点で、LINEの送り先をLINEグループに変更する場合は、【GAS活用システム③-2】のトークンの取得、スクリプトにトークンを貼り付けを参考にトークンの変更を行ってください。

フォームから注文データを複数人でほぼ同時に送信しても、ちゃんと動くはずなので、その辺りも本番前にテストして確認してくださいね。

項目の並びの方向の違いはOFFSET関数とMATCH関数で解決

最後に、今回使用した若干複雑な関数について補足していきます。

今回、たとえば【品目集計表】シートのC4セルには、やきそばの売上数を集計するためにこんな長い計算式が設定されています。
=SUM(OFFSET('注文明細'!$C$3:$C,0,MATCH(A4,'注文明細'!$C$1:$1,0)-1))

心が折れそうな長さですが、なぜこんな長い計算式が必要だったのか、そこから説明していきたいと思います。

まず、この【品目集計表】シートC4セルで集計したいのは、【注文明細】シートのC列のやきそばの売上数です。

なので、やきそばのC4セルだけ考えれば、もっとずっとシンプルに、注文明細シートのC3セルからC列全体の合計する、以下の計算式でOKなのです。
=SUM('注文明細'!C3:C)

ですが、この計算式を次の品目のたこ焼き（注文明細シートのD3セルからD列全体）の売上数を合計して表示したい、C5セルに向かってコピーすると、

=SUM('注文明細'!C4:C)
になってしまいます。本当はたこ焼きの集計であれば
=SUM('注文明細'!D3:D)
にする必要があるのですが。。。

【品目集計表】シートの品目が行方向（縦方向）に並んでいるのに対して、【注文明細】シートの品目は列方向（横方向）に並んでいるため、普通のSUM関数をそのままコピーするとこのようなことになってしまうわけです。

むー、どうしたものか。。。【注文明細】シートはフォームと連携しているのでどうしても品目は列方向（横方向）になるし、でも、【品目集計表】シートではやっぱり品目を行方向（縦方向）に並べたいし。。

といろいろ調べた結果、OFFSET関数を使って、基準となる範囲（ここではやきそばの合計範囲「'注文明細'!C3:C」）を1つずつ列方向にずらせばできそう！、ということがわかりました。

【OFFSET関数】
基準となる開始セル参照から指定した行数と列数だけシフトした位置にあるセル範囲の参照を返す
　OFFSET(基準, 行数, 列数)

例）=SUM(OFFSET('注文明細'!$C$3:$C,0,1)　　　
OFFSET関数で、基準となる範囲「'注文明細'!$C$3:$C」を行を0、列を1、ずらしてから、SUM関数で合計

そして、何列ずらすかは、やきそばを基準にして、たこ焼きなら1列、から揚げなら2列、、と、品目の相対的な位置となるので、指定した範囲の中で相対的な位置を返してくれる、MATCH関数を使用することにしました。

【MATCH関数】
指定した値と一致する範囲内のアイテムの相対的な位置を返す
　MATCH ( 検索値 , 検索範囲 , 検索の種類 )

　例）MATCH(A5, ‘注文明細’!$C$1:$1,0)
　　A5の値（やきそば）を、
　　品目が並ぶ範囲「‘注文明細’!$C$1:$1」内を、
　　0：完全一致で検索して、
　　相対的な位置（何番目か）を返す

MATCH関数は、範囲の中で一番始めにみつかったら1を返すので、やきそばなら1、たこ焼きなら2、、、が返ってきます。ずらす数としては、やきそばなら0、たこ焼きなら1、、、としたいので、MATCH関数の戻り値から-1しました。

このOFFSET関数とMATCH関数の合わせ技が、【品目集計表】シートのA4セルに設定されている以下の計算式になるわけです。

=SUM(OFFSET('注文明細'!$C$3:$C,0,MATCH(A4,'注文明細'!$C$1:$1,0)-1))

最終的に、品目や単価が変更された時に実行するcreateUpdateForm関数の中で、【品目集計表】シートの品目の売上数の範囲（C4セルから品目の数だけ行方向に移動したセルまで）に対して、どーんとsetFormulaでこの計算式を指定しました。

  //品目集計表シートの集計するための関数を設定
  sh.getRange(4,3,menu.length)
    .setFormula("SUM(OFFSET("+sh0Name+"!$C$3:$C,0,MATCH(A4,"+sh0Name+"!$C$1:$1,0)-1))");

GASで複数セルにどーんと同じ計算式を設定しても、相対参照のA4セルだけが行方向（下方向）にコピーされ、結果、いい感じに計算式が設定されました。

以上が、悩ましかった売上数の自動集計の方法です。

説明が長くなりましたが、後で自分でもわからなくなりそうだったので、備忘録をかねて細かく解説しました。

少々難解ですが、これで集計対象項目の並びの方向の違いを解消して、売上数を自動集計する計算式をGASで設定することができました。

めでたし、めでたし。

さて、GAS活用システム第三弾のGASデジタル伝票の補足編は、この辺で終わりにしようと思います。

GASデジタル伝票では、フォームの自動生成・自動更新、2つのシートとフォームの計３箇所での品目と単価の完全同期、などなど、かなり欲張ったせいで、【GAS活用システム③-2】のスクリプトは胃もたれしそうなほど、超絶長くなってしまいました。ちょっと反省。。。

スクリプトの長さにめげず、「GASデジタル伝票」、機会があればぜひご活用ください。

今回のスクリプト、長いだけあって、いろんなネタがちりばめられていますので、今後、GAS活用術として少しずつ切り分けて別の記事にしていきたいと思います。

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