More than 3 years have passed since last update.

LINE BotAdvent Calendar 2019

@dodonki1223(どどんき)

無料で作成するサーバーレスな自分だけの最強家計簿

Last updated at 2019-12-09Posted at 2019-12-09

よくある家計簿ソフトってなにか足りないんだよな……
そうだ！！自分で好きなように作ってしまえばいいんだ！！

これはGoogleフォーム、Googleスプレッドシート、LINE BOTで管理するサーバーレス家計簿です
お金は一切かかりません……やったね！！

基本的にLINE BOTだけで全ての機能を使用することができるのでLINEさえ使えれば何もいりません

Icons make by Freepik from www.flaticon.com

概要

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

ソースコードはここに公開してあります

https://github.com/dodonki1223/household-account-book

Googleフォーム

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

Googleスプレッドシート

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

フォームの回答

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

家計簿補正

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

今月

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

先月

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

月集計

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

設定

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

LINE BOT

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

科目毎の結果

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

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

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

今月の支出状況

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

家計簿へ

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

入力

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

入力テンプレート例

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

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

固定費入力

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

入力テンプレート例

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

ヘルプ

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

通知機能

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

今月の支出状況

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

今月の最終結果

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

line_bot_income_and_expenditure_for_this_month

環境構築

結構な量なので興味のある人は環境構築してみてください！

Googleフォームを作成する

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

入力者のリストを設定

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

科目のリストを設定

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

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

Googleスプレッドシートを作成する

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

フォームの回答
家計簿補正
今月
先月
月集計
設定

フォームの回答シートを作成

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

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

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

家計簿補正シートを作成

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

	A	B	C	D	E	F	G
1	='フォームの回答'!$A1	='フォームの回答'!$B1	='フォームの回答'!$C1	='フォームの回答'!$D1	='フォームの回答'!$E1	='フォームの回答'!$F1	=IF(ROW()=1,"日付補正",IF(C1="","",DATE(YEAR(C1),MONTH(C1),1)))

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

家計簿のスクリプトをGoogleスプレッドシートに反映させる

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

ファイル名	説明
Bot.gs	LINEにメッセージを送る機能
Chart.gs	グラフ機能を提供する
Message.gs	メッセージを作成する機能
MonthlyAggregationSheet.gs	月集計シートからデータを取得する機能
Open.gs	最新状態を家計簿補正シートに反映させる機能
SettingSheet.gs	LINE BOT用の設定シート
Sheet.gs	シート全体に共通する機能
ThisMonthSheet.gs	今月シートからデータを取得する機能
Tool.gs	家計簿で使用する汎用的なメソッドをまとめたもの
_Config.gs	家計簿の設定ファイル

GoogleフォームとGoogleスプレッドシートが連携されているか確認する

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

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

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

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

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

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

今月シートを作成

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

T、U列に値をセット

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

	T	U
1	設定
2	年	=YEAR(EDATE(TODAY(), U5))
3	月	=MONTH(EDATE(TODAY(), U5))
4	日	=IF(U3=MONTH(TODAY()),DAY(TODAY()),DAY(DATE(U2,U3+1,0)))
5	差	0

A列（日付列）に値をセット

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

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

B列（曜日列）に値をセット

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

=TEXT(A2,"dddd")

C〜I列（通常科目列）、K〜P列（固定費科目列）に値をセット

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

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

J列（通常科目の合計列）に値をセット

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

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

Q列（固定費科目の合計列）に値をセット

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

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

R列（総合計列）に値をセット

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です

	A	B	C	……	J	K	……	Q	R
1	日付	曜日	食費	……	合計	住居費	……	固定費合計	総合計
2	=IF(DAY(DATE(`$S$2`,`$S$3`,ROW()-1))=ROW()-1,DATE(`$S$2`,`$S$3`,ROW()-1),"")	=TEXT(A2,"dddd")	=IF(`$A2`="","",SUMIFS('家計簿補正'`!$E$2`:`$E$30992`,'家計簿補正'`!$C$2:$C$30992`,`$A2`,'家計簿補正'`!$D$2:$D$30992`,`C$1`))	……	=IF(`$A2`="","",SUM(`C2:I2`))	=IF(`$A2`="","",SUMIFS('家計簿補正'`!$E$2`:`$E$30992`,'家計簿補正'`!$C$2:$C$30992`,`$A2`,'家計簿補正'`!$D$2:$D$30992`,`K$1`))	……	=IF(`$A2`="","",SUM(`K2:P2`))	=J2+Q2
3	=IF(DAY(DATE(`$S$2`,`$S$3`,ROW()-1))=ROW()-1,DATE(`$S$2`,`$S$3`,ROW()-1),"")	=TEXT(A3,"dddd")	=IF(`$A3`="","",SUMIFS('家計簿補正'`!$E$2`:`$E$30992`,'家計簿補正'`!$C$2:$C$30992`,`$A3`,'家計簿補正'`!$D$2:$D$30992`,`C$1`))	……	=IF(`$A3`="","",SUM(`C3:I3`))	=IF(`$A3`="","",SUMIFS('家計簿補正'`!$E$2`:`$E$30992`,'家計簿補正'`!$C$2:$C$30992`,`$A3`,'家計簿補正'`!$D$2:$D$30992`,`K$1`))	……	=IF(`$A3`="","",SUM(`K3:P3`))	=J3+Q3
4	=IF(DAY(DATE(`$S$2`,`$S$3`,ROW()-1))=ROW()-1,DATE(`$S$2`,`$S$3`,ROW()-1),"")	=TEXT(A4,"dddd")	=IF(`$A4`="","",SUMIFS('家計簿補正'`!$E$2`:`$E$30992`,'家計簿補正'`!$C$2:$C$30992`,`$A4`,'家計簿補正'`!$D$2:$D$30992`,`C$1`))	……	=IF(`$A4`="","",SUM(`C4:I4`))	=IF(`$A4`="","",SUMIFS('家計簿補正'`!$E$2`:`$E$30992`,'家計簿補正'`!$C$2:$C$30992`,`$A4`,'家計簿補正'`!$D$2:$D$30992`,`K$1`))	……	=IF(`$A4`="","",SUM(`K4:P4`))	=J4+Q4
……	……	……	……	……	……	……	……	……	……
33		合計	=SUM(C1:C32)	……	=SUM(J1:J32)	=SUM(K1:K32)	……	=SUM(Q1:Q32)	=SUM(R1:R32)
34		１日平均	=SUM(C2:C32)/$U$4	……	=SUM(J2:J32)/$U$4	=SUM(K2:K32)/$U$4	……	=SUM(Q2:Q32)/$U$4	=SUM(R2:R32)/$U$4
35		１週間平均	=C34 * 7	……	=J34 * 7	=K34 * 7	……	=Q34 * 7	=R34 * 7
36		今月料金予測	=C34 * DAY(EOMONTH(TODAY(),0))	……	=J34 * DAY(EOMONTH(TODAY(),0))	=K34 * DAY(EOMONTH(TODAY(),0))	……	=Q34 * DAY(EOMONTH(TODAY(),0))	=R34 * DAY(EOMONTH(TODAY(),0))
37		先月費差異	=C36-'先月'!C33	……	=J36-'先月'!J33	=K36-'先月'!K33	……	=Q36-'先月'!Q33	=R36-'先月'!R33

グラフを追加する

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

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

先月シートを作成

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

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

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

月集計シートを作成

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

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

	A	B	C	D	F	……	S	……
1		月収入	月支出	月固定費支出	食費	……	給与	……
2	2019/11/01	=SUM(`$S2:$T2`)	=SUM(`$F2:$L2`)	=SUM(`$M2:$R2`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A2`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A2`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
3	2019/12/01	=SUM(`$S3:$T3`)	=SUM(`$F3:$L3`)	=SUM(`$M3:$R3`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A3`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A3`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
4	年合計	=SUM(B2:B3)	=SUM(C2:C3)	=SUM(D2:D3)	=SUM(F2:F3)	……	=SUM(S2:S3)	……
5	2020/01/01	=SUM(`$S5:$T5`)	=SUM(`$F5:$L5`)	=SUM(`$M5:$R5`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A5`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A5`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
6	2020/02/01	=SUM(`$S6:$T6`)	=SUM(`$F6:$L6`)	=SUM(`$M6:$R6`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A6`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A6`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
7	2020/03/01	=SUM(`$S7:$T7`)	=SUM(`$F7:$L7`)	=SUM(`$M7:$R7`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A7`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A7`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
8	2020/04/01	=SUM(`$S8:$T8`)	=SUM(`$F8:$L8`)	=SUM(`$M8:$R8`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A8`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A8`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
9	2020/05/01	=SUM(`$S9:$T9`)	=SUM(`$F9:$L9`)	=SUM(`$M9:$R9`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A9`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A9`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
10	2020/06/01	=SUM(`$S10:$T10`)	=SUM(`$F10:$L10`)	=SUM(`$M10:$R10`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A10`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A10`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
11	2020/07/01	=SUM(`$S11:$T11`)	=SUM(`$F11:$L11`)	=SUM(`$M11:$R11`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A11`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A11`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
12	2020/08/01	=SUM(`$S12:$T12`)	=SUM(`$F12:$L12`)	=SUM(`$M12:$R12`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A12`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A12`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
13	2020/09/01	=SUM(`$S13:$T13`)	=SUM(`$F13:$L13`)	=SUM(`$M13:$R13`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A13`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A13`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
14	2020/10/01	=SUM(`$S14:$T14`)	=SUM(`$F14:$L14`)	=SUM(`$M14:$R14`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A14`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A14`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
15	2020/11/01	=SUM(`$S15:$T15`)	=SUM(`$F15:$L15`)	=SUM(`$M15:$R15`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A15`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A15`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
16	2020/12/01	=SUM(`$S16:$T16`)	=SUM(`$F16:$L16`)	=SUM(`$M16:$R16`)	=-1*SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A16`,'家計簿補正'`!$D$2:$D$30992`,`F$1`)	……	=SUMIFS('家計簿補正'`!$E$2:$E$30992`,'家計簿補正'`!$G$2:$G$30992`,`$A16`,'家計簿補正'`!$D$2:$D$30992`,`S$1`)	……
17	年合計	=SUM(B5:B16)	=SUM(C5:C16)	=SUM(D5:D16)	=SUM(F5:F16)	……	=SUM(S5:S16)	……

設定シートを作成

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

フォーム項目IDの設定を行う

下記のような感じの表を作成します
詳しくは解説の Googleフォームの項目にデフォルト値をセットする をみてください

	A	B	C
1	フォーム項目ID設定	項目	項目ID
2		日	entry.xxxxxxxxx
3		科目	entry.xxxxxxxxx
4		金額	entry.xxxxxxxxx
5		備考	entry.xxxxxxxxx

家計簿回答用の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をセットしてください
下記のような画像の感じで設定してください

通常科目の入力テンプレートの設定を行う

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

	E	F	G	H	I	J	K	L
1	入力テンプレート	ID	テンプレート名	入力者	日	科目	金額	備考
2		1
3		2
4		3
5		4
6		5
7		6
8		7
9		8
10		9
11		10
12		11
13		12
14		13

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

固定費科目の入力テンプレートの設定を行う

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

	N	O	P	Q	R	S	T	U
1	固定費入力テンプレート	ID	テンプレート名	入力者	日	科目	金額	備考
2		1
3		2
4		3
5		4
6		5
7		6
8		7
9		8
10		9
11		10
12		11
13		12
14		13

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

日付けの指定について

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

=nowDate()

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

=nowDateForSpecifiedDay(25)

Googleスプレッドシートをウェブアプリケーションとして公開する

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

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

LINE BOTの作成

新規チャンネルの作成を行う

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

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

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

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

Googleスプレッドシートに設定情報を追加する

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

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

LinePushNotificationDestination

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

HouseholdAccountBookUrl

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

FormUrl

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

グラフのURL

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

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

LINE BOTの動作確認

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

LINE BOTにメニューを追加する

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

リッチメニューを使う

リッチメニューを登録する

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にリッチメニューを紐付ける

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

自動通知設定

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

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

今月の支出状況を通知させる

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

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

今月の最終結果を通知させる

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

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

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

解説

今回、このアプリの作成で学びのあったことをいくつか紹介します

Googleフォームの回答データが正しく参照できないぞ

元々はGoogleフォームの回答データをそのまま別シートで参照するような仕組みにしていたのですが、回答されるたびにうまく参照できない現象がありました
どうもGoogleフォームからのデータ挿入は「行の挿入」なので参照がなくなるそうです

googleフォームの回答を別シートで参照する場合にバグのような挙動があります - G Suite 管理者ヘルプ

フォームの回答 シートから 家計簿補正 シートへはcopyForm メソッドを使用してコピーするようにしました
詳しくは下記コードを確認してみてください

Open.gs#L18

Googleフォームの項目にデフォルト値をセットする

GoogleフォームのURLにクエリパラメータを追加することでデフォルト値をセットすることができます。セットする形式は下記のような感じです

entry.xxxxxxxxx=セットしたい値

entry.xxxxxxxxx は入力項目に割り当てられているIDみたいなもので画像（金額項目です）を見てもらうとわかると思うんですが name属性 に設定されています

金額項目に500円をセットした状態でGoogleフォームを表示したい時は

https://docs.google.com/forms/d/e/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/viewform?usp=pp_url&entry.1017631428=500

とすると500が入力された状態で表示されます

ただし日付項目は年、月、日ごとにそれぞれ分かれてIDが割り振られています

entry.xxxxxxxxxx_year
entry.xxxxxxxxxx_month
entry.xxxxxxxxxx_day

entry.xxxxxxxxxx の部分は同じ値になっています
日付にデフォルト値を設定したい時は

https://docs.google.com/forms/d/e/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/viewform?usp=pp_url&entry.xxxxxxxxxx=2019-12-05

とすると2019年12月5日が入力された状態で表示されます

LINE BOTのクイックリプライ機能

クイックリプライを使う

クイックリプライ感動しました！！
この家計簿アプリでもめちゃくちゃ使ってやりましたよ。科目毎の結果 、 入力 、 固定費入力 の機能ではすごくお世話になりました
１つのアクションで複数のアクションを割り当てられるので便利ですね

こんな感じで書けばクイックリプライをLINE BOTで確認することができます
LINEのユーザーID 、 LINEのアクセストークン を適宜変更してください

/**
 * LINE BOTにPostする
 * @return {JSON} Postが成功した情報をJSON形式で返す
 */
function linePost() {
  // クイックリプライメッセージの作成
  var quickReplyItems = buildQuickReplyItemsForArray(['食費', '日用品', '交際費']);
  var messages = buildQuickReplyMessages('科目を選んでね😍', quickReplyItems);

  // LineにPostする
  UrlFetchApp.fetch('https://api.line.me/v2/bot/message/push',createPushRequest(messages));
  
  // 成功のステータスを返す
  return ContentService.createTextOutput(JSON.stringify({'content': 'post ok'})).setMimeType(ContentService.MimeType.JSON);
}

/**
 * クイックリプライメッセージのitemオブジェクト作成（配列用）
 * @param {Array} [values] - 配列の値
 * @return {Array} クイックリプライメッセージのitem
 */
function buildQuickReplyItemsForArray(values) {
  return values.map(function(value){
    return {
      'type': 'action',
      'action': {
        'type': 'message',
        'label': value,
        'text': value
      }
    };
  });
}

/**
 * LineにPostするクイックリプライメッセージ情報
 * @param {String} [message] - メッセージ
 * @param {Array} [quickReply] - クイックリプライ用のメッセージ
 * @return {Array} クイックリプライメッセージの情報
 */
function buildQuickReplyMessages(message, quickReply) {
  return [{
      'type': 'text',
      'text': message,
      'quickReply': {
        'items': quickReply
      }
  }]
}

/**
 * プッシュ用のリクエスト情報（JSON）を作成する
 * @param {Array} [Array] - message情報
 * @return {Object} リクエスト情報（JSON）
 */
function createPushRequest(message) {
  return {
    'headers': header(),
    'method': 'post',
    'payload': JSON.stringify({
      'to': 'LINEのユーザーID',
      'messages': message
    }),
  }
}

/**
 * リクエストのヘッダーを作成
 * @return {Object} リクエスト情報のヘッダー
 */
function header() {
  return {
      'Content-Type': 'application/json; charset=UTF-8',
      'Authorization': 'Bearer ' + 'LINEのアクセストークン',
      }
}

LINE BOTのリッチメニュー

リッチメニューを使う

今まで作成したLINE BOTは入力した文字列に対してアクションを起こすものだけでしたがリッチメニューを使うと操作性が全然違いますね
フリープランだとapi経由でしか登録ができないので少しとっつきにくいですが慣れてしまうとめちゃくちゃ便利です！

ユーザーごとリッチメニューを変える必要がなければ登録する手順は以下の３つのみでターミナルでcurlコマンドを叩くだけで完了します

リッチメニューの作成
作成したリッチメニューに画像を紐付ける
デフォルトのリッチメニューを紐付ける

月末のみトリガーを実行したい

トリガーで月末のみ実行したいと思っても設定画面に月末のみ実行できる設定がない！？
トリガー自体は毎日実行で関数側で月末かどうか判断し月末のみ処理を実行する必要がありました

月末日や最終金曜日にGoogle Apps Scriptを自動で実行するテクニック - タダケンのEnjoy Tech

こちらの記事を参考にして月末かどうかを判断する関数を作成しました
今日日付に＋１した日が１ならば月末って判断方法です……なるほど🤔

/**
 * 月末か？
 * @return {Boolean} 
 */
function isEndOfMonth() {
  var date = new Date();
  date.setDate(date.getDate() +1);

  // 現在日付＋１の日付が１日の場合は月末と判断する
  return date.getDate() === 1;
}

下記関数はトリガーで日付ベースで毎日実行する設定にすれば月末のみ実行されます

/**
 * 今月の最終結果をLINE BOTにPostする
 * 月末以外の時は何もしない
 * @return {JSON} Postが成功した情報をJSON形式で返す
 */
function doIncomeAndExpenditureForThisMonthPost() {
  if (!isEndOfMonth()) return;
  
  var messages = buildMessages(incomeAndExpenditureForThisMonthMessage());
  return linePost(messages);    
}

Chromeの拡張機能を使用してGoogleスプレッドシートの開発をしやすくする

Googleスプレッドシートのスクリプトエディタでプログラムを書く時に拡張機能を入れると開発しやすくなります

AppsScript Color

AppsScript Color

スクリプトエディタの色をいい感じに見やすくしてくれる拡張機能です

Google Apps Script GitHub アシスタント

Google Apps Script GitHub アシスタント

スクリプトエディタからGitHubにPull、Pushすることができる拡張機能です
いちいちローカルでPullやPushしなくて済むのですごく使えます！

Stackdriver Logging

これは個人のメモなんですが、 Stackdriver Logging というものを知らなかったのですが調べるといろいろと便利そうだなぁと！

トリガー時やWebアプリケーションのログ確認はこちらの方法でしかできないのでエラーを検出したい時やデバッグ時に今後の制作物ではぜひ使おうかと思います！

最後に

Googleスプレッドシートはカスタマイズしやすいので自分の好きなようにカスタマイズすることができます……最高だぜ！
このアプリをひな形に自分の使いやすい最強の家計簿アプリを作ってみるのはどうでしょうか？

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up