今回は前回の続きで、Google Apps Scriptで自動的に各月の支出を項目ごとに合計し、まとめたいと思います。
下記が、投稿データ等のスプレッドシートです。
上の図でこれまでの内容をおさらいすると、
下記が、投稿データ等のスプレッドシートです。
リンクでGoogle Spreadsheetも公開しているので確認してください。
上の図でこれまでの内容をおさらいすると、
- フォームで投稿されたデータが格納されているのが、「フォームの回答」シートで、これまでの全ての投稿が入っています。グレーの背景になっている物は、既に前回のプログラムで各月のシートにコピー済みの内容です。
- 投稿データから各月に分けてコピーされたシートが「MM/YYYY」の名前のシートです。たとえば、「12/2015」のシートには2015年12月の項目が全てコピーされています。
今日は、各月シート（シート名「12/2015」など）の内容を各月の項目別合計をまとめて、一つのマトリックスにするプログラムを紹介したいと思います。
プログラムの大まかな流れは、下記の通り。
- 月・項目マトリックスシートを選択（シートがなければ、新規に作成）。
- 書き込む月のデータを初期化。
- 小項目ごとに支出を合計。
- 合計値を該当する月・項目欄へコピー。
- 全ての小項目に対して、３・４を実施。
以下、ソースコードです。
// グローバル定数
var FORM_RES_SHEET_NAME = "フォームの回答";
var DATE_I = 1; //日付データ（いつ？）のインデックス（B列）
var PRICE_I = 2; //値段（いくら？）のインデックス（C列）
var MAJOR_I = 3; //大項目（何を？）のインデックス（D列）
var NOTE_I = 4; //補足のインデックス（E列）
var FIRST_I = 5; //小項目開始のインデックス（F列）
var FOOD_I = 5; //食費の小項目のインデックス（F列）
var LIVING_I = 6; //生活費の小項目のインデックス（G列）
var TRANS_I = 7; //交通費の小項目のインデックス（H列）
var HOBBY_I = 8; //趣味の小項目のインデックス（I列）
var CLOTH_I = 9; //被服費の小項目のインデックス（J列）
var OTHER_I = 10; //その他の小項目のインデックス（K列）
var INVEST_I = 11;//貯蓄・投資の小項目のインデックス（L列）
var IM_TITLE = ["大項目","小項目","1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"]
var MINOR_I_MI = 1; //小項目のインデックス（B列）
var MAJOR_LIST = ["食費","生活費","交通費","趣味","被服費","その他","貯蓄・投資"]
var FOOD_LIST = ["食材", "外食（昼）","外食（夜）","カフェ"];
var LIVING_LIST = ["日用品","子供","家具","電化製品","家賃","電気代","水道代","ガス代","携帯","通信費","保険（掛け捨て）"];
var TRANS_LIST = ["電車代","バス代","タクシー代","高速代","車代","ガソリン代","自転車代","飛行機代"];
var HOBBY_LIST = ["音楽","スポーツ","本","カメラ","旅行","習い事"];
var CLOTH_LIST = ["アクセサリ","美容院","インナー","アウター","靴","バッグ"];
var OTHER_LIST = ["ローン","交際費","病院","その他"];
var INVEST_LIST = ["保険（貯蓄型）","外貨預金","国内株式","国内債券","国内不動産","海外株式","海外債券","海外不動産","バランスタイプ","クラウドファンド"];
// 月別シートから項目毎に集計し、月x項目のマトリックスでまとめる
// year->年、month→月、id→他のスプレッドシートから読む場合はそのid。同じスプレッドシートに月別シートがある場合は0とする。
function itemMonth(year, month, id) {
// 引数idの値によって、読み込む月別シートがあるスプレッドシートを定義。
if (id != 0)
var ss_r = SpreadsheetApp.openById(id);
else
var ss_r = SpreadsheetApp.getActiveSpreadsheet();
// 書き込む月・項目マトリックス・シートを定義。
var ss_w = SpreadsheetApp.getActiveSpreadsheet();
// 読み込む月別シートの定義
var sheetName = month + "/" + year;
var sh = ss_r.getSheetByName(sheetName);
// 月別シートの全データ取得
var rg2 = sh.getDataRange();
// 月別シートの行数取得→その月の支出として投稿された数
var rows = rg2.getLastRow();
// 月別シートの列数取得→フォームで回答する選択肢の数
var columns = rg2.getLastColumn();
// 書き込む月・項目マトリックス・シートの名前定義
var itemName = "Item" + year;
// 月・項目マトリックス・シートを選択。シートが無い場合は新しく作成する。
if(!ss_w.getSheetByName(itemName)){
var sh_w = ss_w.insertSheet(itemName);
makeItemMonth(sh_w);
}
else
var sh_w = ss_w.getSheetByName(itemName);
SpreadsheetApp.setActiveSheet(sh_w);
// 月別シートの全値を取得。
var values = rg2.getValues();
// 比較用変数と結果格納変数
var temp, result;
// 月・項目マトリックス・シートの書き込む月のデータを一旦消去する
clearItemMonth(sh_w, month);
// 同じ小項目の支出を合計していく。
for(l=FIRST_I; l<columns; l++){
// 小項目データの列で月別シートをソートし、全値を取得。
rg2 = rg2.sort(l+1);
values = rg2.getValues();
// 比較用変数に最初の小項目の内容を格納
temp = values[0][l];
// 支出結果格納変数を初期化
result = 0;
for (m=0; m<rows; m++) {
// 小項目の内容が同じ限り、支出のデータを足していく。
if(values[m][l]==temp){
result += values[m][PRICE_I];
}
// 小項目の内容が変わると、それまでの結果を月・項目別シートへ書き込み、各種変数を初期化する。
else{
// 結果の格納
writeItem(result, sh_w, temp, month);
// 次の小項目データを比較用変数に格納。
temp = values[m][l];
// 結果の初期値を設定。
result=values[m][PRICE_I];
}
}
// 最後あるいは一列しかないときの書き込み処理
if(values[rows-1][l]!='' && (m<rows || rows==1)){
// 結果の格納
writeItem(result, sh_w, temp, month);
}
}
}
// 指定された月の小項目の合計金額を月・項目マトリックスシートへ書き込む関数
// 書き込むべき行を小項目の名前で検索して、記入する。
function writeItem(result, sh_w, item, month){
var rg_w = sh_w.getDataRange();
// 行数を取得
var rows_w = rg_w.getLastRow();
// 月・項目マトリックスシートの全値を取得
var values_w = rg_w.getValues();
// タイトル行以下で、同じ小項目の行を探し、指定された月列に合計金額を書き込む。
for (k=1; k<rows_w; k++){
if(values_w[k][MINOR_I_MI]==item){
sh_w.getRange(k+1,month+2).setValue(result);
}
}
}
// 月・項目マトリックス・シートを新規作成する
function makeItemMonth(sh_w){
// 列数の定義：各小項目の数
var rows_w = FOOD_LIST.length + LIVING_LIST.length + TRANS_LIST.length + HOBBY_LIST.length + CLOTH_LIST.length + OTHER_LIST.length + INVEST_LIST.length;
// 全値を取得
var rg_w = sh_w.getDataRange();
var values_w = rg_w.getValues();
for (n=0; n<rows_w+1; n++){
// タイトル行の入力
if (n==0){
for (o=0; o<IM_TITLE.length; o++){
sh_w.getRange(n+1, o+1).setValue(IM_TITLE[o]);
}
}
// 食費項目名の入力
else if(n<FOOD_LIST.length+1){
sh_w.getRange(n+1,1).setValue(MAJOR_LIST[0]);
sh_w.getRange(n+1,2).setValue(FOOD_LIST[n-1]);
}
// 生活費項目名の入力
else if(n<FOOD_LIST.length+LIVING_LIST.length+1){
sh_w.getRange(n+1,1).setValue(MAJOR_LIST[1]);
sh_w.getRange(n+1,2).setValue(LIVING_LIST[n-FOOD_LIST.length-1]);
}
// 交通費項目名の入力
else if(n<FOOD_LIST.length+LIVING_LIST.length+TRANS_LIST.length+1){
sh_w.getRange(n+1,1).setValue(MAJOR_LIST[2]);
sh_w.getRange(n+1,2).setValue(TRANS_LIST[n-FOOD_LIST.length-LIVING_LIST.length-1]);
}
// 趣味項目名の入力
else if(n<FOOD_LIST.length+LIVING_LIST.length+TRANS_LIST.length+HOBBY_LIST.length+1){
sh_w.getRange(n+1,1).setValue(MAJOR_LIST[3]);
sh_w.getRange(n+1,2).setValue(HOBBY_LIST[n-FOOD_LIST.length-LIVING_LIST.length-TRANS_LIST.length-1]);
}
// 被服費項目名の入力
else if(n<FOOD_LIST.length+LIVING_LIST.length+TRANS_LIST.length+HOBBY_LIST.length+CLOTH_LIST.length+1){
sh_w.getRange(n+1,1).setValue(MAJOR_LIST[4]);
sh_w.getRange(n+1,2).setValue(CLOTH_LIST[n-FOOD_LIST.length-LIVING_LIST.length-TRANS_LIST.length-HOBBY_LIST.length-1]);
}
// その他項目名の入力
else if(n<FOOD_LIST.length+LIVING_LIST.length+TRANS_LIST.length+HOBBY_LIST.length+CLOTH_LIST.length+OTHER_LIST.length+1){
sh_w.getRange(n+1,1).setValue(MAJOR_LIST[5]);
sh_w.getRange(n+1,2).setValue(OTHER_LIST[n-FOOD_LIST.length-LIVING_LIST.length-TRANS_LIST.length-HOBBY_LIST.length-CLOTH_LIST.length-1]);
}
// 貯蓄・投資項目名の入力
else if(n<FOOD_LIST.length+LIVING_LIST.length+TRANS_LIST.length+HOBBY_LIST.length+CLOTH_LIST.length+OTHER_LIST.length+INVEST_LIST.length+1){
sh_w.getRange(n+1,1).setValue(MAJOR_LIST[6]);
sh_w.getRange(n+1,2).setValue(INVEST_LIST[n-FOOD_LIST.length-LIVING_LIST.length-TRANS_LIST.length-HOBBY_LIST.length-CLOTH_LIST.length-OTHER_LIST.length-1]);
}
}
}
// 月・項目マトリックス・シートの書き込む月のデータを一旦消去する
function clearItemMonth(sh_w, month){
var rg_w = sh_w.getDataRange();
// 行数を取得
var row_w = rg_w.getLastRow();
// 月・項目マトリックスシートの全値を取得
var values_w = rg_w.getValues();
// タイトル行より下で、指定された月の項目を0に設定。
for (n=1; n<row_w; n++){
sh_w.getRange(n+1, month+2).setValue(0);
}
}
このプログラム（itemMonth()）をツールバーのCommandに"月別シート→月・項目マトリックス"という名前で追加し、スプレッドシート上から実行できるようにします。
// Spreadsheetが開いたときに実行するプログラム。メニューリストにプログラムを実行する項目を追加
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// 新しいメニューに追加する項目名の配列
var menuEntries = [{name: "フォーム回答→月別シートへコピー", functionName: "separateRes"}];
menuEntries.push(null);
menuEntries.push({name: "月別シート→月・項目マトリックス", functionName: "doItemMonth"});
ss.addMenu("Command", menuEntries);
}
function doItemMonth()
{
// スプレッドシート内の全シートのIDを取得
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
// スプレッドシートの名前で、月別シートか判別する。
if (sheets.length >= 1) {
for(var i=0; i< sheets.length; i++) {
var tempStr = sheets[i].getName();
var tempIn = tempStr.indexOf('/');
// '/' が入っている場合は月別シートと判定し、itemMonth関数を呼び出す。
if(tempIn!=-1){
var month = parseInt(tempStr.slice(0,tempIn));
var year = parseInt(tempStr.slice(-4));
itemMonth(year, month, 0);
}
else{
}
}
}
}
実行した結果の例です。2015年の各月・各項目ごとに、支出が合計され、表にまとめられるようになります。
これでかなり家計簿として、見やすくなってきたと思います。次回は、これらのプログラムを定期的に実施する方法を紹介したいと思います。
