Googleフォーム、Googleスプレッドシート、LINE BOTで管理するサーバーレス家計簿です

Icons make by Freepik from www.flaticon.com

動作確認用に家計簿BOTを公開しているのでもしよかったら友達登録してみて動作を確認してみてください

• 公開用Googleスプレッドシート
• 公開用Googleフォーム

Googleフォームは家計簿にデータを登録するために使用します
入力したデータはGoogleスプレッドシートへと格納されていきます

Googleスプレッドシートではいくつかのシートがあります

フォームの回答 シートではGoogleフォームから入力されたデータを格納します

家計簿補正 シートは フォームの回答 シートのデータを補正するためのシートです
日の入力データを元にどの月に入力されたかを判断しています
日付補正列が追加されていてそこで判断をしています

今月 シートは 家計簿補正 シートを元に今月の支出を科目ごとまとめたシートになります

先月 シートは 家計簿補正 シートを元に先月の支出を科目ごとまとめたシートになります
今月 シートとの違いは 今月料金予測 、先月費差異 の項目がない、グラフが無いになります。ほんとんど今月 シートと同じです

月集計 シートは 家計簿補正 シートを元に月ごとの集計結果を算出しています

設定 シートはLINE BOTで入力テンプレートを取得する際に使用するシートとなります
詳しくはLINE BOTの方で説明します

LINE BOT画面の下部のメニューを押すことによりいろいろな機能を使用することができます
表示される内容についてはGoogleスプレッドシートの 今月 シートの内容を通知するようになっています

上の段の左の 科目毎の結果 をクリックすると科目選択のボタンメニュー上に表示されます
科目のボタンをクリックするとその科目の今月の結果を確認することができます

画像では食費の結果を確認している所です

グラフの設定をしている場合はリンクが表示されクリックすると下記のように表示されます

上の段の真ん中の 今月の支出状況 をクリックすると今月の使用状況が表示されます（ただし固定費項目は除く）

上の段の右の 家計簿へ をクリックすると 今月 シートへのリンクが表示されます

下の段の左の 入力 をクリックすると入力用のテンプレートが表示されます
このテンプレートの一覧はGoogleスプレッドシートの 設定 シートに設定されている 入力テンプレート から表示されます

入力テンプレート例

入力テンプレート名をクリックするとGoogleフォームへのURLが表示されます

テンプレート名の 昼食 を選択した場合は入力者が どどんき 、日が 2019-11-23 、科目が 食費 、備考が 昼食 で入力された状態のGoogleフォームへのURLが表示されます

下の段の真ん中の 固定費入力 をクリックすると固定費入力用のテンプレートが表示されます
このテンプレートの一覧はGoogleスプレッドシートの 設定 シートに設定されている 固定費入力テンプレート から表示されます

入力テンプレート例

入力テンプレート名をクリックするとGoogleフォームへのURLが表示されます 基本的に 入力 メニューの機能と一緒で対象が固定費になっているだけです

下の段の右の ヘルプ をクリックするとこのLINE BOTの使い方が表示されます
メニューを使わず話しかける時はヘルプに書かれている通りにするとLINE BOTが答えてくれます

Googleスプレッドシートのトリガー機能を使用し特定の時間にメッセージを通知させます

特定の時間に今月の支出状況を通知させます（ 今月の支出状況 メニューを実行した時と同じものが通知されます）

月末に今月の最終結果（月集計シートの内容）を通知させます

画像のような感じでGoogleフォームを作成します

夫婦などで管理する場合に入力者を設定するとよいでしょう
独り身の自分は自分の名前だけ設定しています

科目は下記のようなリストで作ると良いでしょう
特に決まりは無いので好きに作ってもらってよいです
※変更する場合は一部プログラムを修正する必要があります

• 通常科目
  • 食費
  • 日用品
  • 医療費
  • 交際費
  • 洋服代
  • 娯楽費
  • 雑費
• 固定費
  • 住居費
  • 借金返済
  • 通信費
  • 保険料
  • 水道光熱費
  • 交通費
• 収入科目
  • 給与
  • 雑収入

Googleスプレッドシート内に作成するシートは下記の通りです

1. フォームの回答
2. 家計簿補正
3. 今月
4. 先月
5. 月集計
6. 設定

Googleフォームの回答結果を書き出すGoogleスプレッドシートを作成します
Googleフォーム作成画面の回答内のGoogleスプレッドシートのアイコンをクリックすることで作成することができます

作成すると自動的にGoogleスプレッドシートが表示されます
Sheet名を フォームの回答 に変更しておいてください

この段階でGoogleスプレッドシートも自動で作成されます

新しくシートを作成しシート名は 家計簿補正 にしてください
そのシートには下記のように値をセットしておいてください

下記のようになっていればOKです

下記のファイルをスクリプトエディタにて追加してください
_Config.gs ファイルは Config.gs にリネームしてください

作成したGoogleフォームを使って回答してみます
回答結果がフォームの回答シートに追加されていることを確認しましょう

家計簿補正シートに回答結果を反映させるため
追加機能メニューの フォームの回答データをコピー をクリックしてください

承認が必要 というウィンドウが出た時は

• 続行 ボタンをクリック
• 対象のアカウントを選択
• このアプリは確認されていません画面で 詳細 をクリック 一番下のリンク をクリック
• 許可 ボタンをクリック

無事、回答データがコピーされました

これで家計簿ファイルを開くたび、LINE BOTを実行するたびに家計簿補正シートが更新されるようになります
なぜかAndroidでGoogleスプレッドシートを開くとonOpen関数が実行されないようです:thinking:

新しくシートを作成しシート名は 今月 にしてください
作成するシートの中身は下記画像のような感じです

下記のように値をセットし上記画像のようになればOKです

A列には日付けを求める関数をセットします
月によっては31日までないものもあるのでその対応で日付けを取得できなかったら空になるようになっています

=IF(DAY(DATE($S$2,$S$3,ROW()-1))=ROW()-1,DATE($S$2,$S$3,ROW()-1),"")

B列には日付けから曜日を求める関数をセットします
例としては下記のような値です

=TEXT(A2,"dddd")

C〜I列、K〜P列には家計簿補正シートから日付けごと、科目ごとの集計を計算する関数をセットします
例としては下記のような値です

=IF(`$A2`="","",SUMIFS('家計簿補正'`!$E$2`:`$E$30992`,'家計簿補正'`!$C$2:$C$30992`,`$A2`,'家計簿補正'`!$D$2:$D$30992`,`C$1`))

J列には通常科目の合計値を求める関数をセットします
例としては下記のような値です

=IF($A2="","",SUM(C2:I2))

Q列には固定費科目の合計値を求める関数をセットします 例としては下記のような値です

=IF($A2="","",SUM(K2:P2))

R列には通常科目の合計と固定費科目の合計値をセットしてください
例としては下記のような値です

=J2+Q2

合計行には１ヶ月の合計値を求める関数をセットしてください
例としては下記のような値です

=SUM(C1:C32)

１日平均行には１日に使用している平均値を求める関数をセットしてください
$U$4 には月初から現在までの日数が求められています
例としては下記のような値です

=SUM(C2:C32)/$U$4 

１週間平均行には１週間で使用している平均値を求める関数をセットしてください
実際には １日平均 x 7 をしているだけです 例としては下記のような値です

=C34 * 7

今月料金予測行には今月の合計値の予測値を求める関数をセットしてください
実際には １日平均 x 月数 をしているだけです 例としては下記のような値です

=C34*DAY(EOMONTH(TODAY(),0))

先月費差異行には先月の合計値と今月料金予測の値を差し引きし先月に比べて今月がどうかを求める関数をセットしてください
この状態だと 先月 シートはまだ作成していないので正しい値はでません
例としては下記のような値です

=C36-'先月'!C33

下記のような表と同じ値になっていればOKです

下記のような感じで科目ごとのグラフを追加します
全部設定する必要はなく、自分が欲しいものだけ追加すると良いでしょう

グラフを追加したらURLの公開設定を行います
グラフの右上をクリックして グラフを公開 を押しリンクで設定します

今月 シートをコピーしシート名を 先月 にしてください
コピーし終わったら下記作業を行ってください

• 先月費差異 行の削除（必要ないので削除）
• グラフの削除（必要ないので削除）
• U5の 差 の値に -1 をセットしてください

これで 先月 シートの作成は完了です

新しくシートを作成しシート名は 月集計 にしてください
作成するシートの中身は下記画像のような感じです

下記のような表と同じ値になっていればOKです

新しくシートを作成しシート名は 設定 にしてください
画像のような形で設定シートを作成していきます

下記のような感じの表を作成します

家計簿回答用のGoogleフォーム画面にて下記ブックマークレットを実行し入力項目のIDを取得し設定シートに反映させます

javascript: (() => {
  const inputNameList = ['入力者', '日', '科目', '金額', '備考'];
  const ids = new Set();
  Array.prototype.forEach.call(document.querySelectorAll('input'), function (elem) {
  　if ( elem.name.indexOf('entry') != -1){
      if (elem.name.indexOf('_') != -1) {
          let place = elem.name.indexOf('_');
          ids.add(elem.name.substring(0, place));
      } else {
        ids.add(elem.name);
      }
    }
  });
  const idsArray = Array.from(ids);
  let message = '';
  inputNameList.forEach((name, index) => {
      message += name + '：' + idsArray[index] + '\n';
  });
  alert(message);
})();

実行結果

先程、作成した表に表示されているIDをセットしてください
下記のような画像の感じで設定してください

下記のような感じの表を作成します

下記のような画像の感じで設定してください

下記のような感じの表を作成します

下記のような画像の感じで設定してください

日付の欄で今日の日付けを設定したい時は下記関数を使用して設定してください

=nowDate()

今月の特定日を設定したい時は下記関数を使用して設定してください
下記の例だと今月の25日が設定されます

=nowDateForSpecifiedDay(25)

スクリプトエディタからウェブアプリケーションとして公開します

Current web app URL はLINE BOTと連携で使用するURL（ LINE BOTに設定するWebhook URL ）なのでコピーしておいてください

LINEのエンジニアの方が書かれている記事を参考にMessaging APIの作成を行って下さい

• LINEのBot開発 超入門（前編） ゼロから応答ができるまで - Qiita

LINEのアクセストークンが必要 なのでメモしておいて下さい
BOTの作成は必要ないので作成しなくて大丈夫です。BOT 本体はGoogleスプレッドシートになります

Webhook URL に Googleスプレッドシートをウェブアプリケーションとして公開した時の Current web app URL のURLを設定し 検証 ボタンをクリックして成功することを確認しておいてください

Googleスプレッドシートのスクリプトエディタから Config.gs を開き設定情報を追加します
下記コード内の LineAccessToken , LinePushNotificationDestination , HouseholdAccountBookUrl , FormUrl , グラフのURL の設定行います

// LineAccessToken                 ：LINE developersのメッセージ送受信設定に記載のアクセストークン
// LineReplyUrl                    ：LINE Messaging APIのURL（LINEからの応答用）
// LinePushUrl                     ：LINE Messaging APIのURL（LINEに対してPOSTする用）
// LinePushNotificationDestination : LineのUserID
// HouseholdAccountBookUrl         ：家計簿のURL
// FormUrl                         ：家計簿入力用のURL
var _Config = {
  LineAccessToken                 : 'Lineのアクセストークン',
  LineReplyUrl                    : 'https://api.line.me/v2/bot/message/reply',
  LinePushUrl                     : 'https://api.line.me/v2/bot/message/push',
  LinePushNotificationDestination : 'LineのUserID',
  HouseholdAccountBookUrl         : 'Googleスプレッドシートの今月の支出シートURL',
  FormUrl                         : 'GoogleフォームのURL'
};

// グラフの存在する項目のURLリスト
// Base64で取得する方法もあるがめんどいので今回は固定値で対応する
// https://developers.google.com/apps-script/reference/charts
var _ChartList = {
  食費  : '食費グラフURL',
  日用品: '日用品のURL',
  交際費: '交際費のURL',
  洋服代: '洋服代のURL',
  娯楽費: '娯楽費のURL',
  交通費: '交通費のURL',
  合計  : '合計のURL'
}

LineAccessToken にはチャンネルアクセストークンを設定してください

LinePushNotificationDestination にはユーザーIDをセットしてください

HouseholdAccountBookUrl には 今月 シートを開いてそのURLをセットしてください

FormUrl には 家計簿回答用のGoogleフォームを開いてそのURLをセットしてください

グラフのURL にはグラフを公開を押した時に表示されるURLをセットしてください
グラフが必要のない人はセットしなくてもよいです

再度、ウェブアプリケーションとして公開してください

LINEに作成したBOTを友だち登録して実際に動作するか試してみます
作成したLINE BOTに 食費 と入力して話しかけてみてください
下記のような感じでGoogleスプレッドシートの結果が返ってきたら成功です

いちいち文字を打つのがめんどくさいのでメニューを登録しボタンを押すだけで家計簿の機能が使えるようにします
公式サイトの下記ドキュメント参考に追加していきます

• リッチメニューを使う

curlコマンドを使用してリッチメニューをチャンネルに追加します
この作業はリッチメニューを作成するだけでLINE BOTに紐付いているわけではありません

curl -X POST https://api.line.me/v2/bot/richmenu \
    -H 'Authorization: Bearer {チャンネルアクセストークン}' \
    -H 'Content-Type: application/json' \
    -d \
    '{
        "size": {
            "width": 2500,
            "height": 1686
        },
        "selected": true,
        "name": "ProductionMenu",
        "chatBarText": "メニュー",
        "areas": [
            {
                "bounds": {
                    "x": 0,
                    "y": 0,
                    "width": 834,
                    "height": 843
                },
                "action": {
                    "type": "message",
                    "text": "科目結果"
                }
            },
            {
                "bounds": {
                    "x": 834,
                    "y": 0,
                    "width": 834,
                    "height": 843
                },
                "action": {
                    "type": "message",
                    "text": "今月の支出状況"
                }
            },
            {
                "bounds": {
                    "x": 1668,
                    "y": 0,
                    "width": 832,
                    "height": 843
                },
                "action": {
                    "type": "message",
                    "text": "家計簿"
                }
            },
            {
                "bounds": {
                    "x": 0,
                    "y": 843,
                    "width": 834,
                    "height": 843
                },
                "action": {
                    "type": "message",
                    "text": "入力"
                }
            },
            {
                "bounds": {
                    "x": 834,
                    "y": 843,
                    "width": 834,
                    "height": 843
                },
                "action": {
                    "type": "message",
                    "text": "固定費入力"
                }
            },
            {
                "bounds": {
                    "x": 1668,
                    "y": 843,
                    "width": 832,
                    "height": 843
                },
                "action": {
                    "type": "message",
                    "text": "ヘルプ"
                }
            }
        ]
    }'

実行に成功するとリッチメニューのIDが表示されるのでコピーしておきます

{"richMenuId":"richmenu-xxxxxxxxxxxxxxxxxxxxx"}

作成したリッチメニューに画像を紐付けを行います

リッチメニューに紐付ける画像をダウンロードします

git clone https://github.com/dodonki1223/household-account-book.git
cd household-account-book/menu/

画像の登録を行います

curl -X POST https://api.line.me/v2/bot/richmenu/リッチメニューのID/content \
    -H 'Authorization: Bearer {チャンネルアクセストークン}' \
    -H 'Content-Type: image/png' \
    -T menu.png

LINE BOTに作成したリッチメニューの紐付けを行います

curl -v -X POST https://api.line.me/v2/bot/user/all/richmenu/リッチメニューのID \
    -H "Authorization: Bearer {チャンネルアクセストークン}"

LINE BOTを確認しメニューが表示されていればOKです

LINE BOTにメニューが追加されていて、メニューを押して動作するようならLINE BOTの設定は完了です

Googleスプレッドシートのトリガーを使用して特定の時間に家計簿の結果を通知させることができます
編集メニューの 現在のプロジェクトのトリガー をクリックしてください

現在のプロジェクトのトリガー をクリックするとトリガー設定画面へ遷移します

メニューの 今月の支出状況 の機能を毎日、特定の時間に実行されるように設定します トリガーを追加 ボタンをクリックし下記の画像のような設定し保存してください

上記設定で毎日23時〜24時の間に今月の支出状況がLINE BOTに通知されるようになります

月集計 シートの今月の結果を月末に通知するよう設定します
トリガーを追加 ボタンをクリックし下記の画像のような設定し保存してください

上記設定で毎月の最終日に23時〜24時の間に今月の最終結果がLINE BOTに通知されるようになります
doIncomeAndExpenditureForThisMonthPost メソッド内で月末かどうか判断し月末の時だけLINE BOTにPostするような処理になっているため、毎日実行する設定にしています

実行されると下記のような通知になります