新生活と共にGoogleのツールを活用して家計簿作ってみた

寒い冬が過ぎ、2021年は早めに春がやってきました。春は変化の季節。学校が始まり転勤や転職なども増えてきます。そんな変化のタイミングでお財布管理も変わる事があるのではないでしょうか？
人生においてお金が一番大事とは言いませんが、お金の悩みがなければ出来る事はたくさんあります。
私は、2020年の秋に結婚し夫と生計を共にするようになりました。お金で喧嘩をすることだけは避けたい…と考え、その際に家計簿を自作し使用しています。
僭越ながら、どのような家計簿かご紹介させていただきます！

実現したいこと

私の家庭の場合、クレジットカードを使用する機会が多いです。メインは楽天カードですが、食材などは西友で購入する事があるため割引のあるセゾンカードを使用しています。また、現金でしか使用できないお店に行く事もありますが、その場で割り勘できるほど細かい小銭を持っていない場合もありますね。
支払い方法の種類がいくつかある事で割り勘が難しくなっています。都度、計算して精算するのも大変なので、クレジットカード会社より引き落としがかかるタイミングで、支払い金額を変えて精算すればよいのではないかと考えました。
どういう経緯で後述のツールを使用すれば実現できると思いついたのかは忘れました。たぶん散歩している時だと思います。それでは実際に家計簿作成をしていきましょう。

※PCから作成する事をおすすめします。作成後はスマートフォンからでも使用可能です。
※クレジットカードの締日や引き落とし日は2021年4月現在のものとなります。

必要なツール

• Google Chrome
• Googleアカウント
• Googleドライブ
• Googleフォーム
• Googleスプレッドシート
• Google Apps Script

Google大好きな人みたいですね〜。はい、Googleは大好きです。

あるとよいもの

• 共通で使用できるクレジットカード
  クレジットカードにもよると思いますが、楽天カードとセゾンカードは「家族カード」を作れます。なお審査によっては作れない場合もありますのでご了承下さい。

Googleフォーム

家計簿といえば日々の購入物を入力しますよね。Googleスプレッドシートに入力していく事も考えましたが、スマートフォンから入力するには画面が小さくてやりづらいです。その点、Googleフォーム（以降フォーム）でしたらスマートフォンの画面サイズに対応してくれるため使いやすいと考えました。

フォームの作成手順は下記のサイトを参考にしています。
参考：https://blog.hubspot.jp/google-forms

家計簿作成にあたり必要な情報を、質問項目に入力させるようにしています。
我が家で設定している質問は以下になります。

• 支払者
• 支払日
• 支払方法
• 金額
• カテゴリー
• 品目

品目ではないものは全て必須にしております。また、金額の入力は数字のみの入力ができるように回答の制御をしました。こんな感じです。

※マロとヒロシは頭痛ーるのキャラクターです。

支払方法には下記の4つをプルダウンで設定しています。

• 楽天カード
• セゾンカード
• 現金
• その他

その他は共有のクレジットカードではなく個人のクレジットカードを使用した時などに使います。

カテゴリーには下記を設定しています。

• 食費
• 外食/おやつ
• 遊び/趣味/旅行
• 日用品
• 交際費
• 家具家電
• 通信費
• 光熱費
• 水道代
• 駐車場代
• その他

生活に適したカテゴリーがあると思うので、探してみてくださいね！

このフォームを利用する人に共有して完成です。
出費があった際にご利用ください！

Googleスプレッドシート

フォームで入力された内容を整理してくれるGoogleスプレッドシート（以降スプレッドシート）を作成します。
実は入力された内容をスプレッドシートで保存できるんですよ。
下記サイトの「回答の保存先を選択する」を試してみてください。
https://support.google.com/docs/answer/2917686?hl=ja#zippy=

保存されたスプレッドシートが下記になります。
フォームで入力した内容はこのスプレッドシートに保存されていきます。
このスプレッドシートを活用していきます。

下記のサイトを参考にしたり、他に不明な関数などあればググりましょう。基本的にはエクセルと同じ機能が使えます。エクセルを使用した事がある方ですと馴染みやすいかもしれません。
参考：https://udemy.benesse.co.jp/business/efficiency/how-to-spreadsheet.html

そして、出来たものがこちらです！

細かく見ていきましょう。

まず、A列10行と11行には対象となる期間を設定します。
楽天カードは1日~月末締め翌月払いのため、2/1~2/28のように設定しています。

次にC列2行の条件式を見てみましょう。

=SUMIFS('フォームの回答'!E2:E100407,'フォームの回答'!D2:D100407,$A$2,'フォームの回答'!C2:C100407,">=" & A9,'フォームの回答'!C2:C100407,"<=" & A10)

SUMIFSという関数を使用しました。いくつかの条件を満たした場合のみ、加算されるという関数です。
ここでは2/1~2/28の楽天カードで支払いをした金額を加算して表示しています。

次にC列4行の条件式を見てみましょう。

=SUMIFS('フォームの回答'!E2:E100421,'フォームの回答'!D2:D100421,$A$4,'フォームの回答'!B2:B100421,$B$4,'フォームの回答'!C2:C100421,">=" & A10,'フォームの回答'!C2:C100421,"<=" & A11)

ここではC列2行と3行の条件式に、さらに支払者ごとで金額が分けられるようにしました。

それではこの情報を元に入金額を決めていきます！

E列2行の条件式を見ていきましょう。

=sum(($C$2+$C$3)/2+(sumif(B4:B7,$D$3,C4:C7)/2-sumif(B4:B7,$D$2,C4:C7)/2))

まず楽天カードとセゾンカードは共通カードなので、加算して2で割ればOKです。

そして、ヒロシの現金とその他の負担額を足して2で割り、そこからマロの現金とその他の負担額も足して2で割ったものを引きます。そして、先ほどの楽天カードとセゾンカードを加算して2で割ったものに足します！

そうするとあら不思議！マロが今月、クレジットカードの引き落とし時に入金が必要な金額が算出されるのです。

ヒロシも同様のことを行います。

次にA列14行を見ていきましょう。

=WORKDAY(datevalue(EDATE(MID(A10,1,7) & "/26",1))+1,-1,'祝日シート'!A2:A51)

実はこの時、「祝日シート」を利用しています。
下記のサイトよりCSVでダウンロードができました。
https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html

このCSVをエクセルなどで開き、今回のスプレッドシートに新しいシートを追加して貼り付けます。

楽天カードは締日の翌月27日払いです。

前日には振り込んで欲しいので、土日祝がなければ26日で日付を表示します。しかし、土日祝が被った場合には金曜日の日付を表示させるようにしています。
例えば、26,27日が土日だった場合、25日と表示がされます。

最後にカテゴリーごとの金額を確認していきましょう！

I列2行を見ていきましょう。

=SUMIFS('フォームの回答'!$E$2:$E$100421,'フォームの回答'!$F$2:$F$100421,H2,'フォームの回答'!$C$2:$C$100421,">=" & $A$10,'フォームの回答'!$C$2:$C$100421,"<=" & $A$11)

ここでは2/1~2/28の支払いのカテゴリーが食費の金額を加算して表示しています。

J列2行の上限目標は各自決めてください。

そして食費の金額から上限目標を引いたものがK列2行です。マイナスになった場合は背景色が赤色となる条件付き書式を設定しています。

これで基本となる家計簿は完成です！
次は応用編です！

Google Apps Script

さてここまで完成しましたが、スプレッドシートはスマホから見るには見づらいし、そもそも開くのが面倒くさいということに気づきました。
使用状況を知り、今月使い過ぎているのかどうか？という事をリアルタイムに追うことが出来ていなかったのです。

結局それでは意味がない！と思い、今回Google Apps Script（下記GAS）を導入し、メールで通知されるようにしてみました！

プログラミング経験のない方ですと難しい部分もあるかと思います。しかし検索すれば割と使い方は出てきます。私も今回初めてGASを使いましたが、すんなりと親しむ事ができました。プログラミング未経験の方が初めて触ってみるという分には使いやすいかと思います。

下記のような記事を参考にしました。
https://anagrams.jp/blog/google-apps-script/
https://qiita.com/matsuhandy/items/bbfa357db4e3e1e5c92a

最終的に下記記事をそれはとても大いに参考にしました。
https://qiita.com/mtsnrtkhr/items/cdb0c56f640d8c59a23f

そして出来上がったソースがこちらです。

function mail() {
  var today = new Date();
  today.setMonth(today.getMonth() +1);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(today.getFullYear() + "/" + today.getMonth());
  today.setMonth(today.getMonth()- 1);
  const range = sheet.getRange('H1:K13');
  var htmlTable = makeTable(sheet,range);
  sendMail(htmlTable, today);
}

function makeTable(sheet,range){
  var rangeHeight = range.getHeight();
  var rangeWidth = range.getWidth();
  var rangeValue = range.getValues();
  var rangeFontSize = range.getFontSizes();
  var rangeFontColor = range.getFontColors();
  var rangeFontStyle = range.getFontStyles();
  var rangeBackgroundColor = range.getBackgrounds();
  var rangeHAlign = range.getHorizontalAlignments();
  var rangeVAlign = range.getVerticalAlignments();

  var rangeFirstCol = range.getColumn();  //get first colmn of range in sheet
  var rangeColWidth = [];  //Array to hold the width of first row or range

  var htmlTable = "<table>";
  for(i = 0; i < rangeHeight; i++){
    htmlTable = htmlTable + "<tr>";
    for(j = 0; j < rangeWidth; j++){
      if(i == 0){
        rangeColWidth.push(sheet.getColumnWidth(rangeFirstCol+j));
      }
      htmlTable = htmlTable + "<td style = 'overflow-wrap:break-word; width:"+rangeColWidth[j]+"px; text-align:"+rangeHAlign[i][j]+"; vertical-align:"+rangeVAlign[i][j]+"; font-size:"+rangeFontSize[i][j]+"; color:"+rangeFontColor[i][j]+"; font-style:"+rangeFontStyle[i][j]+"; background-color:"+rangeBackgroundColor[i][j]+";'>"+rangeValue[i][j]+"</td>";
    }
    htmlTable = htmlTable + "</tr>";
  }
  htmlTable = htmlTable + "</table>";

  return htmlTable;
}

function sendMail(htmlTable, today){
  // Get the email address of the active user - that's you.
  var email = "maro@gmail.com,hiroshi@gmail.com";

  // Get the name of the document to use as an email subject line.
  var subject = Utilities.formatDate(today,"JST", "yyyy/MM/dd") + " 時点の家計簿をお知らせします。";

  var body = ""; //ignored when htmlBody can be used in mailer

  // document to use as an email body
  var htmlBody = "" + htmlTable + "";

  var aliases = GmailApp.getAliases();
  // Send yourself an email with a link to the document.
  GmailApp.sendEmail(email, subject, body, {htmlBody: htmlBody,'from': aliases[0], 'name': 'Payment Manager','cc': '','bcc': ''});
}

夜21:00~22:00の間で送られるように設定しました。
届いたメールがこちらです。

例えばこの状態ですと、日用品を買い込み過ぎている事がわかります。ただ外食はあまりしていないのでまだ今月余裕があるようですね。よっしゃ買い物しよう！！！！（貯金に回しなさい）

これは出来なかった！つまずいた話

家計簿自体も更に充実させたり、GASももっと応用する事ができるでしょう。事実これからもアップデートしていく予定です。
しかし現時点ではうまくいかなかった、というお話を実例として紹介します。もしかしたらうまく出来る方法はあるけれど、私が知識不足であるという可能性はありますが、つまずきポイントとして頭に入れておくと何かの役に立つかもしれません。

メール送信と併用してシート名を取得する

家計簿のシートを増やす場合にはシートをコピーして、A列10行と11行を修正する事が必要です。しかし、シートをコピーしてシート名に年と月を設定するだけでシートを増やすようにしたかったので、シート名を取得して活用できないかと考えました。
調べたところGASを使用して関数を作りスプレッドシート内に埋め込む事で、シート名を取得することができる事がわかったのです。
参考：https://gsuiteguide.jp/sheets/getsheetname/

最初はうまくいったのですが、メール送信のGASを組み込んだところ送られてくるカテゴリーごとの金額が全て0になってしまったのです。
恐らくですが、スプレッドシート内に関数を組み込んでいたため、メール送信のタイミングではこの関数が作動しなかったのでしょう。
メール送信のGAS内に組み込んでみたりもしましたがなかなかうまく行かず、諦めました。

メール送信の時間を指定する

現時点、メール送信のタイミングは1時間ごとの時間帯指定のみとなります。21時ぴったりに送るという事ができません。
そのため、GASにその設定を組み込んでみました。
参考：https://tonari-it.com/gas-trigger-set/

こちらも最初はうまくいきました。しかし1週間ほど経った頃から送られて来なくなってしまったのです。
しかもGAS内の実行ボタンで実行される場合はうまく行くのですが、時間指定したタイミングでは次のトリガーをうまく作る事が出来なかったのです。
突然だったためGASのアップデートとかも関係しているかと思ったのですが、結局ログも残ってくれず、原因がよく分からないまま諦めました。

入力漏れ

こればっかりはもうどうする事も出来ません。毎月5000円程度の誤差が出るので、支払前にクレジットカードの明細と照らし合わせ、夫に他に漏れがないか確認しています。その際には出力したCSVをスプレッドシートに貼り付けてCOUNTIFという関数を使っているため、約10分ほどで終わる作業となりました。
この点がどうしても煩わしい場合は家計簿を作ることをやめて口座やクレジットカードと連携できるアプリを使用するのが良いかと思うのですが…（このブログの意義がなくなる）

作ってみて

いろいろ調べたり、うまくいかない事があり大変ではありました。しかし私の場合、ロジックが分かっている安心感、自分好みの使いやすい形式、何よりも作る楽しさがあったので、結果的に作って良かったと感じています。

そしてこの家計簿作成に興味を持ち、GASを作ってみて楽しいと感じたそこのあなた！
ポッケではエンジニアもたくさんおり、日々新しい技術を取り入れて楽しく働いております。気になった方は下記のページよりご連絡お待ちしております！
株式会社ポッケ 採用情報