はじめに

お久しぶりです。Qiitaでは一年ぶりくらいです。私は新年度になって社会人になりました。

さて、社会人になると収入を得るわけですが、キャッシュレスでの支払いがメインの方々(&私)は知らず知らずのうちに使いすぎたという経験があるのではないでしょうか。今回はこれを管理する家計簿を自作しようという話です。

余談：マネー管理アプリを使わない理由

大手アプリの無料版とかは何かしらの制限で使えなかったりします。
特に、利用している金融機関が多い人は取引金融の制限や数制限で完全にそのアプリで完結できなかったりします。
サブスクで全機能(過去ログ制限とかエクスポート制限とか)使えたりしますが、お金の使いすぎを減らすのにサブスクするのは本末転倒では？とは思ったりします。
というか、プログラマーなら自作しようぜ！

最終目標

まず、Googleフォームで必要な情報を入力して送信します。

次に、Googleスプレッドシートで情報を加工します。(内容が具体的過ぎたので画像は載せませんでした。)

最後に、画像のようにいい感じにグラフが表示できていればOKです。
この記事では全資産 最近の履歴 グラフ 表 を扱います。

Googleフォーム

まず、取引の入力はGoogleフォームで行います。Googleフォームなので出来ることは限られていますが、とりあえず必要な情報を毎回入力して送信する形をとります。(日付のみ別)
あとはお金を利用した履歴を残す習慣も付けた方がいいかなと思いこういう形になりました。

トップ

まず、Googleフォームにアクセスした際に表示される項目です。ここでは日付 金額 名称 分類に分けています。
日付に関しては説明にもある通り、なにも入力しなければ送信時の日付を送信するようにしています。ただし、これはGoogleフォーム側ではなく受け取り側(Googleスプレッドシート)の設定になるので、とりあえずここだけ必須ではないようにしています。
また、分類での選択によって移動するセクションを分岐しています。

支出

支出のセクションです。ここでは支払方法(支出)と種類(支出)で分けています。支払方法(支出)には実際は利用するクレジットカード等が項目として存在しています。

収入

収入のセクションです。ここでは預入方法(収入)と種類(収入)で分けています。預入方法(収入)には実際は利用する金融機関等が項目として存在しています。

以上でGoogleフォーム作成は終了です。ここで一度、フォームで何かを送信します。するとドライブの同ディレクトリにGoogleスプレッドシートが生成されているはずです。次はそれを編集していきます。

Googleスプレッドシート

同じような設定の場合、こういった列になると思います。このシートの行末尾に追加されていく形になります。

日付の自動入力

値を加工する前にまず、日付項目を自動で入力する処理を作成していきます。GoogleスプレッドシートのScriptにアクセスするには拡張機能→Apps Scriptを選択します。ファイル名はなんでもいいです。私はcode.gsにしました。そして以下のコードを保存します。

function onFormSubmit(e) {
  const sheet = SpreadsheetApp.getActiveSheet();
  
  if (e.values[1].length == 0) {
    sheet.getRange(sheet.getLastRow(), 2).setValue(e.values[0].substr(0, 10));
  }
}

これだけでは、まだ呼び出してくれないので画面左のトリガーを選択します。

+ トリガーを追加を選択し、画像の通りに設定すれば、送信時にこの関数が呼び出されるようになります。

これで日付の自動入力の対応は終了です。つぎに表示する項目の式を作成していきます。

Googleスプレッドシートの対応

Googleスプレッドシートに戻り、シートを追加していきます。ウェブサイトに表示する用に値をまとめるdashboardを作成します。

B5;全資産

=SUMIFS(finances!$C:$C, finances!$H:$H, C$8)-SUMIFS(finances!$C:$C, finances!$F:$F, "現金")

C16;今月の支払

=SUMIFS(finances!$C:$C, finances!$B:$B, ">="&DATE(YEAR($D$22), MONTH($D$22), 1), finances!$B:$B, "<="&EOMONTH($D$22, 0), finances!$F:$F, "現金")

D22;今月

=YEAR(TODAY())&"/"&MONTH(TODAY())

E22~;先月以前

=YEAR(EDATE(D$22, -1))&"/"&MONTH(EDATE(D$22, -1))
=YEAR(EDATE(D$22, -2))&"/"&MONTH(EDATE(D$22, -2))
...

C23;収入の累計

=SUMIFS(finances!$C:$C, finances!$E:$E, "収入")

C24;支出の累計

=SUMIFS(finances!$C:$C, finances!$E:$E, "支出")

D23~;今月以前の収入

=SUMIFS(finances!$C:$C, finances!$B:$B, ">="&DATE(YEAR(D$22), MONTH(D$22), 1), finances!$B:$B, "<="&EOMONTH(D$22, 0), finances!$E:$E, "収入")
=SUMIFS(finances!$C:$C, finances!$B:$B, ">="&DATE(YEAR(E$22), MONTH(E$22), 1), finances!$B:$B, "<="&EOMONTH(E$22, 0), finances!$E:$E, "収入")
...

D24~;今月以前の支出

=SUMIFS(finances!$C:$C, finances!$B:$B, ">="&DATE(YEAR(D$22), MONTH(D$22), 1), finances!$B:$B, "<="&EOMONTH(D$22, 0), finances!$E:$E, $B24)
=SUMIFS(finances!$C:$C, finances!$B:$B, ">="&DATE(YEAR(E$22), MONTH(E$22), 1), finances!$B:$B, "<="&EOMONTH(E$22, 0), finances!$E:$E, $B24)
...

セルまで指定した理由は、あとでGAS側で直接指定するためです。

一旦これで、Googleスプレッドシートの対応は終了です。

Goolgle Apps Script

新しくGoogle Apps Scriptを作成します。右クリック→その他→Google Apps Scriptにあります。

gs

const TRANSACTION = {
  TIMESTAMP:    0, // タイムスタンプ
  DATE:         1, // 日付
  AMOUNT:       2, // 金額
  NAME:         3, // 名称
  CATEGORY:     4, // 分類
  PAYMENT_TYPE: 5, // 支払方法(支出)
  EXPENSE_TYPE: 6, // 種類(支出)
  DEPOSIT_TYPE: 7, // 預金方法(収入)
  INCOME_TYPE:  8, // 種類(収入)
};

const EXPENSE_TYPE = {
  FOOD:          0, // 食費
  DAILY:         1, // 日用品
  ENTERTAINMENT: 2, // 娯楽費
  TRAFFIC:       3, // 交通費
  MISCELLANEOUS: 4, // 雑費
  SPECIAL:       5, // 特別費
  FIXED:         6, // 固定費
};

function doGet() {
  /* HTML Output */

  var htmlOutput = HtmlService.createTemplateFromFile('index');
  var [financesData, dashboardData, settingsData] = getFinancialData();

  /* Constants */

  const transpose        = (a)            => a[0].map((_, c) => a.map((r) => r[c]));
  const getFormattedDate = (date, format) => Utilities.formatDate(date, Session.getScriptTimeZone(), format);
  const getLast          = (data, start)  => data[data.length - start];

  const TODAY               = new Date();
  const FORMATTED_MONTH     = [...Array(6)].map((_, i) => getFormattedDate(new Date(TODAY.getFullYear(), TODAY.getMonth() - i), 'yy/MM'));
  const RECENT_TRANSACTIONS = [...Array(5)].map((_, i) => getLast(financesData, i + 1));

  /* Data */

  htmlOutput.totalBalance = getCellValue(dashboardData, 'B5');

  var financesChartData = [['', '収入', '支出', '収支']];
  FORMATTED_MONTH.forEach((month, i) => {
    var income  = parseInt(getCellValue(dashboardData, String.fromCharCode('D'.charCodeAt(0) + i) + '23'));
    var expense = parseInt(getCellValue(dashboardData, String.fromCharCode('D'.charCodeAt(0) + i) + '24'));
    financesChartData.push([
      month,
      income,
      -expense,
      income - expense,
    ]);
  });
  htmlOutput.financesChartData = JSON.stringify(financesChartData);

  var totalIncome  = parseInt(getCellValue(dashboardData, 'C23'));
  var totalExpense = parseInt(getCellValue(dashboardData, 'C24'));
  financesChartData.push(
    ['累計', totalIncome, -totalExpense, totalIncome - totalExpense]
  );
  htmlOutput.financesTableData = JSON.stringify(transpose(financesChartData));

  var recentTransactionsData = [['日付', '金額', '名称', '分類', '方法', '種類']];
  RECENT_TRANSACTIONS.forEach((transaction, i) => {
    recentTransactionsData.push([
      getFormattedDate(new Date(transaction[TRANSACTION.DATE]), 'yy/MM/dd'),
      transaction[TRANSACTION.AMOUNT],
      transaction[TRANSACTION.NAME],
      transaction[TRANSACTION.CATEGORY],
      transaction[TRANSACTION.PAYMENT_TYPE] || transaction[TRANSACTION.DEPOSIT_TYPE],
      transaction[TRANSACTION.EXPENSE_TYPE] || transaction[TRANSACTION.INCOME_TYPE],
    ]);
  });
  htmlOutput.recentTransactionsData = JSON.stringify(recentTransactionsData);

  /* Set Properties */

  var html = htmlOutput.evaluate();
  html.setTitle('家計簿ダッシュボード');
  return html;
}

/**
 * Retrieves data from the spreadsheet.
 * @param {Array<Array>} data - An array of data from the spreadsheet.
 * @param {string} cell - The cell to retrieve the value from.
 * @returns {string} - The value of the cell.
 */
function getCellValue(data, cell) {
  const MATCH = cell.match(/([A-Z]+)(\d+)/);
  const COLUMN = MATCH[1].split('').reduce((acc, cur) => acc * 26 + cur.charCodeAt(0) - 64, 0) - 1;
  const ROW = parseInt(MATCH[2]) - 1;
  return data[ROW][COLUMN];
}

/**
 * Retrieves data from the finances spreadsheet.
 * @returns {Array<Array>} - An array of data from the finances spreadsheet.
 */
function getFinancialData() {
  var FINANCES_SSID = PropertiesService.getScriptProperties().getProperty('FinancesSsid');
  var FINANCES_SS   = SpreadsheetApp.openById(FINANCES_SSID).getSheetByName('finances');
  var DASHBOARD_SS  = SpreadsheetApp.openById(FINANCES_SSID).getSheetByName('dashboard');
  var SETTINGS_SS   = SpreadsheetApp.openById(FINANCES_SSID).getSheetByName('settings');
  return [
    FINANCES_SS.getDataRange().getValues(),
    DASHBOARD_SS.getDataRange().getValues(),
    SETTINGS_SS.getDataRange().getValues()
  ]
}

html側に値を送る方法として、htmlのインスタンス.名前(今回の場合はhtmlOutput.名前)となります。

html

次にhtmlを作成します。ファイル名はindex.htmlとしています。

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="https://www.gstatic.com/charts/loader.js"></script>
    <script>
      // finances chart
      (function() {
        const FINANCES_CHART_DATA = JSON.parse('<?=financesChartData?>');
        google.charts.load('current', {'packages':['corechart'], 'language': 'ja'});
        google.charts.setOnLoadCallback(drawChart);
        function drawChart() {
          var data = google.visualization.arrayToDataTable(FINANCES_CHART_DATA);
          var options = {
            title: '収支',
            vAxis: {title: '金額', format: 'currency'},
            seriesType: 'bars',
            series: {
              2: {type: 'line'}
            },
            isStacked: true,
          };
          var chart = new google.visualization.ComboChart(document.getElementById('financesChart'));
          chart.draw(data, options);
        }
      })();
      // finances table
      (function() {
        const FINANCES_TABLE_DATA = JSON.parse('<?=financesTableData?>');
        google.charts.load('current', {'packages':['table']});
        google.charts.setOnLoadCallback(drawTable);
        function drawTable() {
          var data = new google.visualization.arrayToDataTable(FINANCES_TABLE_DATA)
          var options = {
            allowHtml: true,
            width: '100%',
            height: '100%',
          };
          var table = new google.visualization.Table(document.getElementById('financesTable'));
          table.draw(data, options);
        }
      })();
      // recent transactions
      (function() {
        const RECENT_TRANSACTIONS_DATA = JSON.parse('<?=recentTransactionsData?>');
        google.charts.load('current', {'packages':['table']});
        google.charts.setOnLoadCallback(drawTable);
        function drawTable() {
          var data = new google.visualization.arrayToDataTable(RECENT_TRANSACTIONS_DATA)
          var options = {
            allowHtml: true,
            width: '100%',
            height: '100%',
          };
          var table = new google.visualization.Table(document.getElementById('recentTransactions'));
          table.draw(data, options);
        }
      })();
    </script>
    <style>
      .TotalBalance {
        display: flex;
        align-items: center;
      }
    </style>
  </head>
  <body>
    <h1>家計簿ダッシュボード</h1>
    <hr>
    <div class="TotalBalance">
      <h2>全資産</h2>
      <h3><?=totalBalance?></h3>
    </div>
    <hr>
    <h2>最新の履歴</h2>
    <div id="recentTransactions" style="width: 900px"></div>
    <hr>
    <div id="financesChart" style="width: 900px; height: 500px"></div>
    <div id="financesTable" style="width: 950px"></div>
  </body>
</html>

GASでは普通にjsやcssも使えます。
code.gsで渡した値は<?=名前?>で受け取れます。

property.gs

GASでは、ソースコードに含めたくない変数(IDとか)をProperty(環境変数みたいなもの)に保存できる機能があります。今回は、GoogleスプレッドシートのIDをこれを利用して保存してみます。これは別のgsファイルを作成して保存してください。

function setFinancesSsid() {
  PropertiesService.getScriptProperties().setProperty('FinancesSsid', 'ID');
}

IDには実際のGoogleスプレッドシートのIDを指定してください。IDはGoogleスプレッドシートのURLに含まれています。これを上部でsetFinancesSsidを選択し、▶実行を選択すると保存されます。

デプロイ

今回は外部に公開するわけではないので、デプロイせずデプロイをテストでアクセスできるようにします。

右上のデプロイ▼→デプロイをテストを選択します。

その中に表示されているURLにアクセスして、正しく動作しているか確認できるようになります。

以上で終わりです。お疲れ様でした。

おわりに

これを作り始めてからもう2年ほどが経ち、溜まってきた履歴から散財の傾向などもわかるようになってきました。まだ表示部分が簡素なのでまだまだ改善の余地はありますが、そのベースとして役に立てたら幸いです。