More than 3 years have passed since last update.

GASで家計簿を作成する　その１

GoogleAppsScript

Posted at 2019-09-29

1. 背景

『年収２００万円からの貯金生活宣言』(横山光昭著)を読んだところ、「消費・浪費・投資」に分けた家計簿を付けなさいと書かれていました。
面白そうだし、そこそこ普及した手法らしいので、これで家計簿を書いてみようと思いました。
自分のやり方として、外ではスマホ、家ではPCで管理したい。
しかし、いい感じのサービスがない。

良い機会なので、これをWeb系勉強の題材にして作ってみよう。

ちなみに私は、授業などで単語を聞きかじった程度で、ちゃんとしたプロダクトを作るのは初めてです。

2．完成イメージ

PCとスマホからアクセスできる
家計簿データを貯める
月の結果を表示
月ごとの推移を表示
見たいデータ
- 予算と使用額
- 消費・浪費・投資のパーセンテージ
- 費用ごとの合計

家賃やサブスクなど、毎月固定の金額を自動計上したい。とか、トータルで予算を超えたのか否か計算したい。など考えたらキリがないので、まずは上記の内容の実装を目指します。

最初に、【月の結果表示】のイメージ図を描きました。
これを目指していきます。

3. 開発環境

開発環境として、Google Apps Script(GAS)を選択しました。

Googleが提供している、スプレッドシートやGoogleドライブなどの操作も行える、JavaScriptベースの開発環境です。

できること、使い方は、主に【保存版】初心者向け実務で使えるGoogle Apps Script完全マニュアルを読んで勉強しました。

GASを選んだ理由は、

無料である
Webページを公開できる
Webページのアクセス権をGoogleアカウントで管理できる
スプレッドシートをデータベース替わりに使える

から。

作って自分で使いたいので、セキュリティを考えなくて良いことと、
初心者なので、フロント側の勉強だけで使える。
という点が大きかったです。

他に似たサービスがあるのかは、調べてないので分かりません。

4．開発

家計簿データ

日付
金額
分類（消費、浪費、投資）
種類（食費、外食費、生活費、固定費、娯楽費、特別費）
名称（メモ）

上記のデータを貯めていきます。

入力フォームの作成

最初に、データをスプレッドシートに追加する入力フォームを作成します。

家計簿データは、スプレッドシート1枚で管理します。
月ごとにシートを変えることも考えましたが、スプレッドシートに関する処理が一番重たいとあったので、1枚で管理することを選びました。
なんとなく、データベースっぽさもありますし。

ということで、Googleフォームに項目を追加して、スプレッドシートに連携させるだけで完成。
楽ちん。

データを入力していった結果、スプレッドシートはこんな感じになります。
一番下の行は、手編集をためした名残です。

出力データの計算

最初に、GASを使えるようにします。
Googleドライブから、新規>その他>アプリの追加　と進み、Google Apps Scripntを検索して、接続します。
すると、「その他」の中にGoogle Apps Scriptが表示されます。
これをクリックすると、GASのプロジェクトが作成されます。

準備ができました。
それではGASで、

スプレッドシートのデータを取得する
取得したデータから、各合計を計算する
Webページで計算した結果を表示する

を実装していきます。

GASで、スプレッドシートのデータを得る

GASとスプレッドシートを連携させる方法は2つ
1．スプレッドシートからGASを作成する
2．GASを作成して、あとでアクセスする

1．は、スプレッドシートの連携が楽なのですが、何枚か使いたいので、2．で行きました。
(使える機能に違いがあるらしいけど、特に困っていない)

手順は、
　スプレッドシートのIDを指定し、スプレッドシート全体のオブジェクトを得る
→スプレッドシート全体のオブジェクトから、シート名を指定し、シートのオブジェクトを得る
→シート全体の値を、2次元配列として得る

コードは以下のようになります。
1行目は、スプレッドシートのIDをスクリプトのプロパティに追加しておいたので、それを得る処理を行っています。

初めて実行するとき、GASがスプレッドシートにアクセスする許可を求められるので、承認をしてください。
かなり危険な感じの警告ページから飛びます。
詳しくは【初心者向けGAS】スクリプト実行時の「承認」でびっくりしないためにから

function getData() {
  var EXPENSES_SS_ID = PropertiesService.getScriptProperties().getProperty('TR_EXPENSES_SS_ID');
  var exp_sheet = SpreadsheetApp.openById(EXPENSES_SS_ID).getSheetByName('expenses');
  var exp_data = exp_sheet.getDataRange().getValues();

  return exp_data;
}

1つのプロジェクト内の、1つのGASファイルの中に、関数はいくら追加しても良いので、続けて書いていきます。

GASで、各合計値を計算する

データは2次元配列に格納されたので、計算にGASの機能は使用せず、JavaScriptで処理を書いていきます。

今のままだと、全年月のデータを取得しているので、計算したい年月のみにフィルタします。

function selectData(data, selectYear, selectMonth) {
  var selectData = [];
  selectData.push(data[0]);

  for (var i=1; i<data.length; i++){
    var year = data[i][1].getFullYear();
    var month = data[i][1].getMonth() + 1;
    if( year == selectYear && month == selectMonth ) {
      selectData.push(data[i]);
    }
  }

  return selectData;
}

計算したい年月データの2次元配列が得られたので、それを引数として、各要素ごとの合計を計算する関数を書きます。
分類の項目にある文字列比較をして、配列に加算していきます。

分類ごとの合計の計算

function getCategory(data) {
  var category_data = [0, 0, 0];
  var category_name = ['消費', '浪費', '投資'];

  for (var i=1; i<data.length; i++){
    if(data[i][4] == category_name[0]){
      category_data[0] += data[i][2];
    }
    else if(data[i][4] == category_name[1]){
      category_data[1] += data[i][2];
    }
    else if(data[i][4] == category_name[2]){
      category_data[2] += data[i][2];
    }
  }

  return category_data;
}

種類ごとの合計の計算

function getItem(data) {
  var item_data = [0, 0, 0, 0, 0, 0];
  var item_name = ['食費', '外食費', '生活費', '固定費', '娯楽費', '特別費'];

  for(var i=1; i<data.length; i++){
    if(data[i][5] == item_name[0]){
      item_data[0] += data[i][2];
    }
    else if(data[i][5] == item_name[1]){
      item_data[1] += data[i][2];
    }
    else if(data[i][5] == item_name[2]){
      item_data[2] += data[i][2];
    }
    else if(data[i][5] == item_name[3]){
      item_data[3] += data[i][2];
    }
    else if(data[i][5] == item_name[4]){
      item_data[4] += data[i][2];
    }
    else if(data[i][5] == item_name[5]){
      item_data[5] += data[i][2];
    }
  }

  return item_data;
}

使用額の計算は、計算回数を減らそうと思い、分類ごとの合計が入った配列を引数としました。
data配列が1次元なのは、そのためです。

合計の計算

function getSum(data) {
  var sum = 0;

  for( var i=0; i<data.length; i++){
    sum += data[i];
  }

  return sum;
}

今月あといくら使えるのかも知りたいので、予算データも取ってきます。

別スプレッドシートを作成し、予算を記入。
同じ手順でデータを取得します。
予算処理に関してまだ手を出せないので、指定したセルからデータを取ってくるだけで終了。

function getBudget() {
  var BUDGET_SS_ID = PropertiesService.getScriptProperties().getProperty('BUDGET_SS_ID');
  var bud_sheet = SpreadsheetApp.openById(BUDGET_SS_ID).getSheetByName('budgets');
  var bud_data = bud_sheet.getDataRange().getValues();

  return bud_data[1][1];
}

これで、

予算
使用額
分類ごとの合計
種類ごとの合計

を得られました。

Webページの作成

公開->Webアプリケーションとして導入でURLがもらえ、公開できます。
公開範囲を自分だけに設定しておけば、PCからでも、スマホからでも自分のみアクセス可能な環境の出来上がりです。
判別はGoogleアカウントで行っているみたいです。

最初に実行する時、確認画面が出てくるので、承認してください。

このURLにGETリクエストしたとき、GASはdoGet関数を実行します。
もしPOSTリクエストすると、doPost関数を実行します。

doGet関数内で、HTMLファイルからオブジェクトを作成し、returnで返す。すると、クライアントはページが見える。ということみたいです。

最初に、ただのHTMLファイルをプロジェクト内に作成します。
ファイル->新規作成->HTMLファイルで、ファイル名をindexと入力すると、左側の欄にindex.htmlが追加されました。
タグは同じように使えるみたいですが、一部使えないものもあるみたいです。
代わりに、GASの方で追加できる関数が用意されています。

HTMLファイルの中身より先に、公開する方法について説明します。

HTMLファイルを、GASで公開できる形に変換する方法は2つあります。

function doGet(){
  return HtmlService.createHtmlOutputFromFile('index');
}

function doGet(){
  return HtmlService.createTemplateFromFile('index').evaluate();
}

どちらもHTMLファイル名を指定し、クリエイトした結果を返します。
しかし、JavaScriptをHTML上で動かす場合、下の書き方にしなければダメらしいです。

下の書き方で、タイトルも追加したものがこれ。

function doGet() {
  var htmlOutput = HtmlService.createTemplateFromFile('index');
  var html = htmlOutput.evaluate();
  html.setTitle('家計簿 月の解析');

  return html;
}

HTML内で、GASの関数を実行する方法も、2つある。

<? ?> と <?= ?>

違いはイコール(=)があるか、ないかだけ。
イコールがない場合<? ?>、実行した結果は表示されない
イコールがある場合<?= ?>、実行した結果が表示される

HTML内で変数を宣言もでき、その値を別の行で呼び出すこともできた。

hoge.html

<?var hoge = getHoge()?>
HTML<br>
<?=hoge?>

こんな風に書いて、もしhogeが2なら、

HTML
2

と表示される。

ということで、最初に全部のget関数を<? ?>で呼び出し、変数に結果を格納。
欲しいところで <?= ?>で呼び出せばWebページは完成する。

JSON(ぽいやつ)の作成

調べている間に、JSONというものを知った。
WebAPIなどで良く使われている形式で、言語を気にせずデータをやり取りすることが可能という。
今後、GAS以外のシステムを使いたくなる時を考えると、これは良さそう。
なにより標準に倣え。ということでGASとHTML間のデータのやり取りは、JSON形式で行うことに決定。

最初はJSONを返すGASプロジェクトと、計算結果を表示するGASプロジェクトを2つ作成し、連携させようと思ったが、簡単にはいかなかった。
ひとまずリリースだけしたい！となったので、同プロジェクト内にあるget関数の返り値を、JSONにすることでごまかしました。

最初にJSONの設計

{
  "year":YYYY,
  "month":MM,
  "budget":予算額,
  "sum":使用額,
  "category": {
    "sho":消費額,
    "ro":浪費額,
    "to":投資額
  },
  "item": {
    "food":食費,
    "eatingOut":外食費,
    "living":生活費,
    "fixes":固定費,
    "leisur":娯楽費,
    "special":特別費
  }
}

GASに、引数で指定した年月の計算結果をJSONで返す関数を追加する。

function getJson(year, month) {
  var json = '';
  var data = selectData(getData(), year, month);
  var budget = getBudget();
  var category = getCategory(data);
  var item = getItem(data);
  var sum = getSum(category);

  json += '{'
  json += '"year":'   + year   + ',';
  json += '"month":'  + month  + ',';
  json += '"budget":' + budget + ',';
  json += '"sum":'    + sum    + ',';
  json += '"category":{'
  json +=   '"sho":' + category[0] + ',';
  json +=   '"ro":'  + category[1] + ',';
  json +=   '"to":'  + category[2];
  json += '},';
  json += '"item":{'
  json +=   '"food":'      + item[0] + ',';
  json +=   '"eatingOut":' + item[1] + ',';
  json +=   '"living":'    + item[2] + ',';
  json +=   '"fixes":'     + item[3] + ',';
  json +=   '"leisur":'    + item[4] + ',';
  json +=   '"special":'   + item[5];
  json += '}';
  json += '}';

  return json;
}

GASにはJSONを作成する関数があるみたいだが、今回は文字列で送ったものが、HTML側はJSONとして認識した。

続けてHTMLファイルを作成する。
最初に、HTMLにアクセスした年月の家計簿データを、JSONで得る。
JSON文字列をパースすると、クラス変数みたいに使えた。
平文で、順番に結果を表示させる。

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?var today = new Date();?>
    <?var year = today.getFullYear();?>
    <?var month = today.getMonth() + 1;?>
    <?var jsonData = getJson(year, month);?>
    <?var json = JSON.parse(jsonData);?>
  </head>
  <body>
    <p>
    <?=json.year?>年<?=json.month?>月
    </p>
    予算　：<?=json.budget?><br>
    使用額：<?=json.sum?><br>
    <h3>
    分類比率
    </h3>
      <li>消:<?=Math.round(json.category.sho/json.sum*100)?>%</li>
      <li>浪:<?=Math.round(json.category.ro/json.sum*100)?>%</li>
      <li>投:<?=Math.round(json.category.to/json.sum*100)?>%</li>
    <h3>
    種別
    </h3>
      <li>食:<?=json.item.food?></li>
      <li>外食:<?=json.item.eatingOut?></li>
      <li>生活:<?=json.item.living?></li>
      <li>固定:<?=json.item.fixes?></li>
      <li>娯楽:<?=json.item.leisur?></li>
      <li>特別:<?=json.item.special?></li>
  </body>
</html>

表示結果はこうなった。

今までのGASをまとめたものがこちら。

コード.gs

function doGet() {
  var htmlOutput = HtmlService.createTemplateFromFile('index');

  var html = htmlOutput.evaluate();
  html.setTitle('家計簿 月の解析');

  return html;
}

function getJson(year, month) {
  var json = '';
  var data = selectData(getData(), year, month);
  var budget = getBudget();
  var category = getCategory(data);
  var item = getItem(data);
  var sum = getSum(category);

  json += '{'
  json += '"year":'   + year   + ',';
  json += '"month":'  + month  + ',';
  json += '"budget":' + budget + ',';
  json += '"sum":'    + sum    + ',';
  json += '"category":{'
  json +=   '"sho":' + category[0] + ',';
  json +=   '"ro":'  + category[1] + ',';
  json +=   '"to":'  + category[2];
  json += '},';
  json += '"item":{'
  json +=   '"food":'      + item[0] + ',';
  json +=   '"eatingOut":' + item[1] + ',';
  json +=   '"living":'    + item[2] + ',';
  json +=   '"fixes":'     + item[3] + ',';
  json +=   '"leisur":'    + item[4] + ',';
  json +=   '"special":'   + item[5];
  json += '}';
  json += '}';

  return json;
}


function getData() {
  var EXPENSES_SS_ID = PropertiesService.getScriptProperties().getProperty('TR_EXPENSES_SS_ID');
  var exp_sheet = SpreadsheetApp.openById(EXPENSES_SS_ID).getSheetByName('expenses');
  var exp_data = exp_sheet.getDataRange().getValues();

  return exp_data;
}

function selectData(data, selectYear, selectMonth) {
  var selectData = [];
  selectData.push(data[0]);
  Logger.log(data.length);
  for (var i=1; i<data.length; i++){
    var year = data[i][1].getFullYear();
    var month = data[i][1].getMonth() + 1;
    if( year == selectYear && month == selectMonth ) {
      selectData.push(data[i]);
    }
  }

  return selectData;
}

function getSum(data) {
  var sum = 0;

  for( var i=0; i<data.length; i++){
    sum += data[i];
  }

  return sum;
}

function getBudget() {
  var BUDGET_SS_ID = PropertiesService.getScriptProperties().getProperty('BUDGET_SS_ID');
  var bud_sheet = SpreadsheetApp.openById(BUDGET_SS_ID).getSheetByName('budgets');
  var bud_data = bud_sheet.getDataRange().getValues();

  return bud_data[1][1];
}

function getCategory(data) {
  var category_data = [0, 0, 0];
  var category_name = ['消費', '浪費', '投資'];

  for (var i=1; i<data.length; i++){
    if(data[i][4] == category_name[0]){
      category_data[0] += data[i][2];
    }
    else if(data[i][4] == category_name[1]){
      category_data[1] += data[i][2];
    }
    else if(data[i][4] == category_name[2]){
      category_data[2] += data[i][2];
    }
  }

  return category_data;
}

function getItem(data) {
  var item_data = [0, 0, 0, 0, 0, 0];
  var item_name = ['食費', '外食費', '生活費', '固定費', '娯楽費', '特別費'];

  for(var i=1; i<data.length; i++){
    if(data[i][5] == item_name[0]){
      item_data[0] += data[i][2];
    }
    else if(data[i][5] == item_name[1]){
      item_data[1] += data[i][2];
    }
    else if(data[i][5] == item_name[2]){
      item_data[2] += data[i][2];
    }
    else if(data[i][5] == item_name[3]){
      item_data[3] += data[i][2];
    }
    else if(data[i][5] == item_name[4]){
      item_data[4] += data[i][2];
    }
    else if(data[i][5] == item_name[5]){
      item_data[5] += data[i][2];
    }
  }

  return item_data;
}

ひとまずここまで。
次は棒グラフや円グラフを描けるようにしたい。

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

GASで家計簿を作成する その１