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

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

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

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

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

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

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

![家計簿_月解析.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/501846/91cd54bb-7f91-03e9-4ac2-339ef290ea21.jpeg)




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

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

できること、使い方は、主に[【保存版】初心者向け実務で使えるGoogle Apps Script完全マニュアル](https://tonari-it.com/google-apps-script-manual/)を読んで勉強しました。

GASを選んだ理由は、

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

から。

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

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


# 4．開発

## 家計簿データ

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

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

## 入力フォームの作成 

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

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

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

![入力フォーム.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/501846/b4ef98eb-5eac-2981-efaa-c04a35b59efc.png)

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

![データ一覧.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/501846/7a9f1d99-342e-6869-3ea9-173098a28602.png)


## 出力データの計算

 最初に、GASを使えるようにします。
Googleドライブから、新規>その他>アプリの追加　と進み、Google Apps Scripntを検索して、接続します。
すると、「その他」の中にGoogle Apps Scriptが表示されます。
これをクリックすると、GASのプロジェクトが作成されます。
![アプリを追加.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/501846/950c899b-8623-3f2b-989e-b10a6415eae9.png)

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

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

を実装していきます。


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

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

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

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

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

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

```JavaScript
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で処理を書いていきます。

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

```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次元配列が得られたので、それを引数として、各要素ごとの合計を計算する関数を書きます。
分類の項目にある文字列比較をして、配列に加算していきます。

```JavaScript:分類ごとの合計の計算
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;
}
```

```JavaScript:種類ごとの合計の計算
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次元なのは、そのためです。

```JavaScript:合計の計算
function getSum(data) {
  var sum = 0;
  
  for( var i=0; i<data.length; i++){
    sum += data[i];
  }
  
  return sum;
}
```

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

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

```JavaScript
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つあります。**

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

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

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

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

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

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

`<?  ?>` と `<?=  ?>`

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

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

```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の設計

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

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

```JavaScript
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文字列をパースすると、クラス変数みたいに使えた。
平文で、順番に結果を表示させる。

```HTML: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/501846/68177fd9-f1a5-4e71-60e3-873e558c6294.jpeg)



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

```JavaScript:コード.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;
}

```

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