Go to Qiita Advent Calendar 2024 Top
LoginSignup
7
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@forest516

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

Posted at

1. 背景

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

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

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

2．完成イメージ

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

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

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

家計簿_月解析.jpg

3. 開発環境

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

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

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

GASを選んだ理由は、

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

から。

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

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

4．開発

家計簿データ

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

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

入力フォームの作成

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

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

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

入力フォーム.PNG

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

データ一覧.PNG

出力データの計算

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

準備ができました。
それでは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>

表示結果はこうなった。

Screenshot_20190922_104122_com.android.chrome.jpg

今までの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;
}

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

7
9
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?