#36 レシートの合計金額を記録する

2022年4月21日 20:24

いつも給油しているガソリンスタンドでは、レシートを半年の間に 6万円分集めると、賞品として「ギフト券 500円分」か「洗車券 1000円分」がもらえるサービスを提供しています。

単純に金額を加算するだけでなく、火曜日などのサービスデーに給油すると発行されるピンクレシートは、金額が 2倍で計算されるなどのルールがあり、単純に積算するわけではないので、ちょっと合計が面倒なんです。

となると、表計算ソフトで計算したくなりますが、給油したレシートを見ながらパソコンの前で入力するのも面倒なので、何とかスムーズに計算できないものか考えてみました。
他の記事とは趣が異なりますが、Google のサービスと GAS を利用することで、作業を効率化するアイディアにつながればいいなと思い、まとめてみます。

※4/21 に初版を公開しましたが、4/24 に日付入力にも対応した版に更新しています。

（１）入力は Google フォーム

給油が終わったら、Google フォームからレシートを見ながら、スマートフォンから簡単に給油金額を登録できる流れとしました。

入力してもらう項目は、必須項目として「給油金額」、任意項目として「種別」「給油日」の 3つとしました。

半年のレシートで既定の金額に達するかを計算しなければならないので、「給油日」も必須項目なように思えますが、

スマートフォンから Google フォームを利用した場合、日付を入力する操作は面倒。また、別の記事にしたように、Google フォームで「今日」という日付を初期値として設定するのは難しい。
スマートフォンから給油後に登録することを考えると、自動的に設定される「タイムスタンプ」から「今日」という日付を取得できる。
→ 変更しなければならない場合には、後の工程で変更できるようにすればいい。

という感じで、「給油日」は入力されていなければ、今日の給油であると判断して「タイムスタンプ」から日付を指定するようにしました

「給油金額」は「回答の検証」によって不適切な入力を防ぎ、「種別」は「ラジオボタン」にすることで簡単に選択できるようにしました。

（２）集計は Google スプレッドシート

Google フォームで登録されたデータは、Google スプレッドシートに下図のように保存されます。 ※手持ちのレシートを見ながら入力したものなので、同じ日に連続して入力されていたり、キリのいい数字になっていたりします。

Google フォームで入力されたデータをそのまま利用してもいいのかもしれませんが、追加した別のシート「集計」に転記して集計することにしました。

トリガー関数を設定する

後述するような 40行あまりのプログラムを作成しました。このプログラムを、スプレッドシートの「拡張機能」→「App Script」に貼り付けます。

プログラム中の addRefueling 関数を、「フォーム送信時」のトリガーに設定し、フォームでデータが登録されるとプログラムで処理するようにします。

実行結果

フォーム送信が行われ、トリガー関数に設定された addRefueling 関数が実行されると、フォームからの入力内容をもとにして、列 A ～ F を設定しています。

列 B「給油金額」
給油した金額を設定します。
列 C「x2」
2倍であれば 2、そうでなければ 1 を設定する。
列 D「経過日」
GAS のプログラム中で、”=” からはじまる数式（の文字列）を生成して、セルに設定すると、スプレッドシートでは数式として計算される。
列 E「有効」
「給油日」と「今日の日付 today()」の差から、給油日から何日経過したかを求め、180日以上が経過している場合には 0、180日未満であれば 1 を設定する。
列 F「積算額」
列 B × 列 C × 列 E によって計算した金額が設定される。

セル H1 には、列 F に計算された積算額の合計を SUM 関数で求めて表示しています。前述のように設定されていれば、有効（給油から 6ヶ月以内）なレシートの金額は SUM 関数によって計算されます。

このシート「集計」の内容は、Google フォームとは連携していないので、

入力し忘れていたレシートの情報を登録するなどして、列 A「給油日」の日付を後から修正する。
間違って系列ではないガソリンスタンドのデータを入力した場合に、積算対象ではないデータ（行）を削除する。
列 A の変更を行った後は、範囲を選択して「給油日」をキーにして並び替えを行う。　※通常の登録時には、プログラム中で並び替えを行っています。

といった変更を行って構いません。

自身にメールを送信

処理が終わったら、以下のようなメールを自分に送信しています。
ガソリンスタンドを出る前に、既定の金額に達していることがわかれば、賞品をその場でもらって帰れます。

プログラムの中では、これまでの給油がそれぞれ何日経過したものなのか、有効な給油金額の積算、といった処理は行っていません。それらの判定や処理は、Google スプレッドシートの関数が行っています。
GAS のプログラムでは、それらの関数をセルに設定し、SUM 関数によって合計金額を積算しているセルから値を取得しているだけです。

更新：2022/05/12
上記のメール中に、集計を行っている Google スプレッドシートへの URL を記載するようにしました。受信したメールの本文から、詳細な状況をすぐに確認できます。

プログラムの説明

作成したプログラムは、以下のようなコメントを含めて 45行の簡単なものです。

1行目

/** @OnlyCurrentDoc */        // 扱うのはこのドキュメントだけ

1行目については、以下の記事でも紹介した、このプログラム内で扱うデータの範囲を宣言するものです。このドキュメントしか扱わないので、このように宣言してみました。

2 ～ 3行目

"use strict";                 // 変数の宣言を強要
const totallingName = "集計";

2行目は、変数の宣言を強要するものです。
3行目は、入力されたデータを出力するためのシートの名前を定義しています。

5 ～ 8行目

/*****************************************************************************
 * 給油金額の登録
 */
function addRefueling(e) {

トリガー関数となる addRefueling のコメントと宣言です。
トリガー関数の宣言については、以下 URL で説明されているように、それぞれのトリガーに応じた引数を受け取って処理する関数を用意します。

9 ～ 10行目

  // フォームで登録された内容はシート「集計」に追加する
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(totallingName);

フォームで入力されたデータを書き出すシートは、4行目で定義された名前のシートとしています。

11行目

  let date = e.values[0].substr(1, 10);    // 日付情報は、日時情報を削除

トリガー関数の引数 e から、入力されたデータの 0 番目「タイムスタンプ」を参照し、先頭の 10文字分を切り取って、時間の部分を削除した文字列を作成しています。

12行目

  let row = sheet.getLastRow() + 1;         // 0 からではなく 1 からはじまる数字にするため +1

変数 row は、セルに設定する数式で使用する行番号とするために +1 した値にしています。　※getLastRow() の結果は 0 からはじまる数字が戻されるため。

13行目

  let magnification = (e.values[2] == "２倍") ? 2 : 1;    // 倍率

入力されたデータの 2 番目「種別」を参照し、「２倍」が設定されていたら 2、そうでなければ 1 を、変数 magnification に設定してます。ここで判定する文字列は、フォームで選択肢として設定した文字列となるので注意してください。

この ? と : を用いた記述は、三項演算子と呼ばれるもので、条件が真（True）であれば : の前の値、偽（False）であれば後ろの値が設定されるものです。
同じことは if でも記述できますが、あまり見かけない記述方法なので所見の方も多いかもしれません。今回は、サンプル的な意味であえて三項演算子を使って記述してみました。

15 ～ 17行目

  if (e.values[3] != "") {                  // 給油日が指定されていれば、指定された日の給油として扱う
    date = e.values[3];
  }

任意の入力項目である「給油日」が設定されていれば、その日付を「給油日」として使用します。Google フォーム側で入力チェックが行われていることから、ここでは何のチェックも行っていません。

19 ～ 27行目

  let aData = [
    date,                                   // A: 給油日
    e.values[1],                            // B: 給油金額
    magnification,                          // C: 通常→1、２倍→2
    "=today()-A" + row,                     // D: 給油日から何日経過したかの数式
    "=if(today()-A" + row + "<180,1,0)",    // E: 180日以上経過していたら 0、そうでなければ 1
    "=B" + row + "*C" + row + "*E" + row    // F: 加算する金額を算出する数式
  ];
  sheet.appendRow(aData);                    // 最後行の下に行を追加

19 ～ 26行目で、シートに追加する行のデータを配列 aData として宣言します。順番に、列 A ～ F のデータを設定しています。
列 D ～ F については、14行目で設定した変数 row を用いて、数式を文字列として設定しています。
27行目では、設定した配列 aData をシートの最終行に追加します。

この部分が、今回のプログラムで肝となる部分だと思います。プログラム中で生成した文字列をセルに設定すると、その文字列が数式として動作してくれるのです。
そして、その設定されたセルの計算結果が、瞬時に他のセルにも反映されるのです。今回の場合、列 A ～ C には固定の値が設定されますが、列 D ～ F はそれらの値から数式によって算出されるようになっています。後述する 28行目の処理では、この追加した行の内容も反映された値がセル H1 から得られます。

29 ～ 31行目

  // 現時点の合計金額を取得する
  let iTotal = sheet.getRange("H1").getValue();
  Logger.log("合計:" + iTotal);

30行目では、列 F の合計を計算しているセル H1 の値を取得しています。
セル H1 には SUM 関数によって列 F の合計を求める数式が設定されているので、このプログラムでは何もしていません。

前述したように 27行目で追加した行の内容が、瞬時に反映され、30行目の処理を行うときには合計金額が更新されています。

31行目は、チェックのために読み込んだ H1 の値を、ログに記録しています。この行そのものは、動作に影響のない処理です。

33 ～ 35行目

  // シートの内容をソートする     ※マクロ機能で保存したものを流用
  sheet.getRange('A:F').activate();
  sheet.getActiveRange().offset(1, 0, sheet.getActiveRange().getNumRows() - 1).sort({ column: 1, ascending: true });

この部分は、「拡張機能」→「マクロ」→「マクロを記録」で列 A ～ F の範囲をソートする操作を記録し、その記録されたマクロから流用しています。
この処理が追加されていることで、過去の日付の入力が行われても、シートの内容は日付順に並んでいるようになります。

37 ～ 44行目

  // 現在までの合計金額をメールで連絡する
  GmailApp.sendEmail(
    Session.getActiveUser().getUserLoginId(),
    "給油金額合計 : " + iTotal + "円",
    `${e.values[1]}円 × ${magnification} を加算して、合計金額は ${iTotal}円です。\n\n https://docs.google.com/spreadsheets/d/${SpreadsheetApp.getActiveSpreadsheet().getId()}/edit#gid=${sheet.getSheetId()}`,
    {
      name: "プログラムによる返信"
    });

この部分では、プログラムの処理した結果をメールで送信しています。
GmailApp.sendEmail の引数は、順に

宛先 → プログラムを実行しているアカウント
件名
本文
オプション → 自分宛のメールとなるので、表示名を変更しています

といった感じで設定しています。
本文には、Google スプレッドシートを編集するための URL を記載しておき、詳細を確認できるようにしてあります。

45行目

8行目の { に対応する、関数の終わりの } です。

サンプルファイルの提供

ここまで説明したようなフォーム＋スプレッドシート＋GAS を、実行するためには、次のように環境を構築してください。

Google フォームのファイルは、サンプルファイルとして提供しにくいので、ご自身で上記の例を参考に作成してください。
Google スプレッドシートと GAS のプログラムは、以下の URL にアクセスすることで、自身の Google ドライブにコピーできます。
https://docs.google.com/spreadsheets/d/1U_HYhKxGf6QCCJCMn4MiAevj61ObNeIR_JdVW6YxZps/copy
自作された Google フォームの回答を、「既存のスプレッドシートを選択」によって、上記 URL でコピーしたスプレッドシートに出力するようにリンクさせます。
スプレッドシート内に保存されている GAS のプログラムで、前述のようにトリガー関数を設定します。

はじめに使用する場合は、

手元にあるレシートについて、フォームから入力を行います。
シート「集計」に登録されたデータについて、列 A の日付を正しい日付に修正します。

とすれば準備が完了です。あとは、給油する度にフォームからデータを追加するだけです。

既定の金額に達して商品と交換した場合には、交換に使用したデータをシート「集計」から削除（行の削除）します。

最後に

最後に、お決まりのフレーズなどを書いておきます。

一応の動作確認は行っているものの、不慮のトラブルによって損害等が生じても、責任はとれませんので予めご了承ください。
コメントを含めても 40行あまりのスクリプトであり、実行に際して目的外の場所への書き出しや収集などは行っていません。
特別なエラー処理は行っていないので、意図しないケースでエラーが発生してしまうかもしれません。どうにもならない場合には、ご連絡ください。

わたし自身にしてみると、このような「スクリプトを作ること」が目的になっているような感じですが、このスクリプトが何かの役に立てば幸いです。

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