【GAS活用システム②-2】チケットの枚数管理をする・応用設定編

Hoshi-nok（ほしのっく）

2024年1月13日 20:51

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

前回、【GAS活用システム②-1】では、【GAS活用システム】の第二弾の「チケットの枚数管理」の基礎設定編をご紹介しました。今回は、応用設定編です。

【チケット明細】シートの合計金額を表示させる

それでは、【GAS活用システム②-1】の続きとなりますが、まず、【チケット明細】シートの合計金額を計算するように設定していきます。

GASのコードとしては既に登録済みで、sumbitForm関数でこれを行います。理屈は後にして、先にこの関数をフォーム送信時に実行されるように、トリガーを設定してみましょう。

フォーム送信時のトリガーを設定する

スクリプトエディタを表示し、画面左端のメニューバーで「トリガー」を選択します。

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

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

保存ボタンをクリックしてフォーム送信時のトリガーを保存します。

２度目の動作確認（合計金額が表示されるか）

トリガーの設定が終わったら、これでもうフォームからデータが送信される度に合計金額が自動計算されて、表示されるはずです。さっそく、二度目の動作確認として、再びフォームからテストデータを入力します。

スプレッドシートの【チケット明細】シートを確認すると、、

合計金額が表示されていて、E3のセルにSUMPRODUCT関数を使った数式が設定されていますね！

コード解説（合計金額を表示する数式を設定する）

実は今回、初めてGASで数式を設定してみました。

【GAS活用システム②-1】で既に登録済みのsubmitForm(e)関数の中で、合計金額を表示するように数式を設定しているのは、以下の部分です。

function submitForm(e) {
  //スプレッドシードの一番左のシートを取得
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; 

  //合計金額を計算する数式をセットするセルを取得する
  const row = e.range.getRow();  //シートに書き込まれたデータの行番号
  let totalPriceCell = sheet.getRange(row, 5);  //合計金額は5列目

  //対象のセルに合計金額を計算する数式をR1C1形式で入力
  totalPriceCell.setFormulaR1C1("SUMPRODUCT(チケット単価,R[0]C[-2]:R[0]C[-1])")
                .setNumberFormat("[$¥-411]#,##0"); //通貨書式を指定

このsubmitForm(e)は、フォーム送信時に実行されることを前提にした関数です。以前【GAS活用術⑦-2】でも使用しましたが、( )の中にeはフォームの回答を受け取る引数になります。

submitForm(e)関数がフォーム送信時に実行されるようにトリガーを設定したのが、上記の手順となります。

また、【GAS失敗談】で説明しましたが、フォームからスプレッドシートに書き込まれたデータの行番号は「e.range.getRow()」で取得できるので、その行のE列（5列目）に合計金額を計算する数式を設定します。

単価×枚数なので、【GAS活用システム①-3】で説明したSUMPRODUCT関数を使用しています。

setFormulaR1C1メソッドで数式を設定し、SUMPRODUCT関数の引数として、単価の範囲は名前付き範囲の「チケット単価」、枚数の範囲はR1C1方式で指定しています。

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

フォームから送信されたデータは、スプレッドシートで登録されている最終行の次の行に挿入されるので、固定した数式を設定することはできません。

はじめはどのように数式を設定したらいいのかわかりませんでしたが、R1C1方式で指定するsetFormulaR1C1メソッドで解決しました。

（オプション）フォームから送信される毎にLINE通知する

今回は、必要性は低いかもしれませんが、フォームから送信される毎にLINE通知したい場合は設定してみてください。

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

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

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

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

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

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

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

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

【GAS活用システム②-1】のGASのコードでは、あらかじめ42行目の先頭に // をつけてコメントアウトし、sendLine関数を実行しないようにしています。実際にLINE通知したい場合は、先頭の // を消すだけで大丈夫です。

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

自分自身に送る場合は不要ですが、グループ宛に送る場合は、【GAS活用術①-6】のLINEグループにLINE Notifyを招待するの手順が必要になります。こちらも難しい手順ではありませんので、ぱぱっとやってしまいましょう。

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

３度目の動作確認（LINE通知が来るか）

再びフォームからテストデータを入力します。動作確認としては、３度目になりますね。

LINE通知も設定したトークン宛に送られてきているか確認しましょう。

上記のように、フォームで入力していない質問（上記の例では「備考」が未入力）は、通知文をコンパクトにするため、表示されないようにしています。

コード解説（フォーム送信毎にLINE通知する）

【GAS活用システム②-1】で既に登録済みのsubmitForm(e)関数の中で、LINE通知しているのは、以下の部分です。

  //フォームでの回答内容をe.valuesで一時配列で取得して分割代入で個別の変数に代入
  let [timeStamp, name, adult, student, description] = e.values

  const msg = '\n' + name + 'さんのチケット販売情報'
            + (adult && ('\n大人：' + adult + '枚'))
            + (student && ('\n学生：' + student + '枚'))
            + (description && ("\n備考：" + description))
            + '\n合計金額:'+totalPriceCell.getValue()+'円';
  
  const token = "★トークンをここに設定★"; //トークンを設定
  sendLine(msg, token);  //sendLine関数でmsgをtoken宛にLINE通知
}

以前紹介した、【GAS活用術④-1】と【GAS活用術④-2】では、今回と同じようにGoogleフォームから送信されたタイミングでLINE通知していましたが、フォーム側にスクリプトを登録していました。

今回はなぜスプレッドシート側のスクリプトにしたかというと、LINE通知で合計金額を表示するために必要なチケットの単価の情報がスプレッドシート側にあるからです。

スプレッドシート側のスクリプトで、フォームから送信された回答の値を取得するには、またフォームのスクリプトとはお作法が異なります。

スプレッドシート側のGASでは、e.valuesでフォームから送信された回答の値が配列で取得できます。

今回の例では、スプレッドシートの一番初めのA列に設定されるタイムスタンプの値はe.values[0]で取得できます。e.values[1]が名前の値、e.values[2]が大人チケット枚数の値、、となります。

そのままではLINEの通知文を作りづらいので、e.valuesを分割代入なる方法で、個別の変数に代入しました。

あとはメッセージを生成して、sendLine関数でLINE通知するという点は、フォームのスクリプトと同様になります。

メッセージ生成では、フォームで未回答の質問はLINE通知に表示しないように、論理積演算子（&&）を使っています。詳細は、【GAS活用術④-2】の「論理積演算子（&&）にもチャレンジ」をご参照ください。

（オプション）その日の終わりに当日売れた枚数と総合計枚数をLINE通知する

ここまで必要はないのかも、、と思いながら、こんなこともできますよ、と紹介程度に載せておきます。

notifyCount関数の登録と実行

まず、以下のnotifyCount関数を、スプレッドシートのGASに追加してください。

function notifyCount(){
  //指定のシート上のデータがある範囲の表示値を二次元配列ですべて取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheets()[0];     //一番左のシートを取得
  const table = sh.getDataRange().getDisplayValues().slice(1);   //ヘッダー行を除く

  //今日の日付を取得 
  const date = new Date();    //現在時刻を取得
  //date.setDate(date.getDate() -1);  //昨日の日付に変更するなら-1（ 2日前なら-2に変更）
  const today = Utilities.formatDate(date, 'JST', 'yyyy/MM/dd');  //日付書式を設定
  
  //「A列の値の0文字目から10文字分(yyyy/MM/dd)が今日の日付と同じもの」を条件にフィルターをかける
  const regs = table.filter(row => row[0].slice(0,10) == today);

  //今日売れた大人枚数と学生枚数を計算
  let adultSum = 0;
  let studentSum = 0;

  for(let i = 0; i < regs.length; i++){   
    adultSum += Number(regs[i][2]);
    studentSum += Number(regs[i][3]);
  }

  //msgCountに今日の販売枚数と現時点の総販売枚数をメッセージとしてセット
  const msgCount = "\n本日、" + today + "の販売枚数"
                 + "\n 大人：" + adultSum + "枚、学生：" + studentSum + "枚"
                 + "\n現時点の総販売枚数"
                 + "\n 大人：" + ss.getRangeByName('大人合計枚数').getValue() + "枚、" 
                 + "学生：" + ss.getRangeByName('学生合計枚数').getValue() + "枚";

  console.log(msgCount); 

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

スクリプトをペーストする場所は、sendLine関数の後、56行目からでよいでしょう。

プロジェクトを保存しておきます。その後、実行する関数として「notifyCount」を選択し、実行ボタンをクリックします。

実行ログとして、以下のように販売枚数が表示されます。

枚数のカウントが正しくされているか、スプレッドシートのデータと比較して確認してみましょう。

昨日の販売枚数の表示したい場合は、64行目の先頭の // のコメントアウトの記号をとります。

保存後、再度、「notifyCount」関数を実行すると、

昨日の日付と販売枚数が表示されます。今日の販売枚数をLINE通知したい場合は、64行目の先頭に再度、// をつけてコメントアウトしてください。

ログでの確認が終わったら、実際にLINE通知をしてみましょう。手順は、上記でLINE通知した時と同じです。
88行目に、送り先のトークンを設定し、89行目の先頭の // をとるだけです。

保存後、さらにもう一度、「notifyCount」関数を実行し、

上記のようなLINE通知が来ていれば確認完了です。

時間主導型のトリガーを設定する

最後に、notifyCount関数を定期的に実行するトリガーを設定します。スクリプトエディタを表示し、画面左端のメニューバーで「トリガー」を選択します。

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

トリガーを追加の画面で、「実行する関数の選択」でnotifyCount、「イベントのソースを選択」で時間主導型を選択します。

毎日実行させる場合は、「時間ベースのトリガーのタイプを選択」で日付ベースのタイマーを選択し、時刻を選択します。
毎週決まった曜日に実行させる場合は、「時間ベースのトリガーのタイプを選択」で週ベースのタイマーを選択し、曜日、時刻を選択します。

例えば、毎週土曜日の午後9時台にLINE通知をしたい場合は、以下のような設定になります。

保存ボタンをクリックして時間主導型のトリガーを保存します。

コード解説（当日売れた枚数と総合計枚数をLINE通知する）

シート上にデータが存在する範囲(Range)をgetDataRangeでガツッと取得して、そのまま二次元配列として読み込み、「タイムスタンプ列（A列）に今日の日付が設定されているデータ」だけをfilterメソッドでギュッと絞り込むところは、【GAS活用術⑤-1】前日にGoogleフォームから登録された件数をLINEで通知と同じ方法となります。

その後、今日販売分のみに絞り込まれた配列regsに対して、2列目の値を合計して大人枚数、3列目の値を合計して学生枚数をそれぞれ計算しています。以下の部分です。

  //今日売れた大人枚数と学生枚数を計算
  let adultSum = 0;
  let studentSum = 0;

  for(let i = 0; i < regs.length; i++){   
    adultSum += Number(regs[i][2]);
    studentSum += Number(regs[i][3]);
  }

また、総合計枚数は、【チケット集計】シートで既に合計枚数が計算されているので、設定しておいた名前付き範囲の値を読むようにしています。

  //msgCountに今日の販売枚数と現時点の総販売枚数をメッセージとしてセット
  const msgCount = "\n本日、" + today + "の販売枚数"
                 + "\n 大人：" + adultSum + "枚、学生：" + studentSum + "枚"
                 + "\n現時点の総販売枚数"
                 + "\n 大人：" + ss.getRangeByName('大人合計枚数').getValue() + "枚、" 
                 + "学生：" + ss.getRangeByName('学生合計枚数').getValue() + "枚";

以上がすべての手順と説明になります。

あまり機会は多くないかもしれませんが、チケットの枚数管理をすることがあれば、ぜひ使ってみてください。

次回は、今回のチケットの枚数管理での技術を活用して、他の用途のGAS活用システムを紹介していきたいと思います。

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