Google Apps Script で家計簿：各月データ→各月・各項目マトリックス

今回は前回の続きで、Google Apps Scriptで自動的に各月の支出を項目ごとに合計し、まとめたいと思います。

下記が、投稿データ等のスプレッドシートです。

リンクでGoogle Spreadsheetも公開しているので確認してください。

上の図でこれまでの内容をおさらいすると、

1. フォームで投稿されたデータが格納されているのが、「フォームの回答」シートで、これまでの全ての投稿が入っています。グレーの背景になっている物は、既に前回のプログラムで各月のシートにコピー済みの内容です。
2. 投稿データから各月に分けてコピーされたシートが「MM/YYYY」の名前のシートです。たとえば、「12/2015」のシートには2015年12月の項目が全てコピーされています。

今日は、各月シート（シート名「12/2015」など）の内容を各月の項目別合計をまとめて、一つのマトリックスにするプログラムを紹介したいと思います。
プログラムの大まかな流れは、下記の通り。

1. 月・項目マトリックスシートを選択（シートがなければ、新規に作成）。
2. 書き込む月のデータを初期化。
3. 小項目ごとに支出を合計。
4. 合計値を該当する月・項目欄へコピー。
5. 全ての小項目に対して、３・４を実施。

以下、ソースコードです。

// グローバル定数
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年の各月・各項目ごとに、支出が合計され、表にまとめられるようになります。

 

これでかなり家計簿として、見やすくなってきたと思います。次回は、これらのプログラムを定期的に実施する方法を紹介したいと思います。

Google Apps Script で家計簿：投稿データ→各月データ変換

Google各種ツールを駆使した家計簿から、今回はGoogleフォームで投稿された各支出のデータをGoogle Apps Scriptで自動的に各月の支出へまとめたいと思います。

下記が、投稿データになります。
ご覧の通り、Google Formで簡単家計簿入力で紹介したフォームで入力したデータが表にまとまっているのが分かります。
リンクでGoogle Spreadsheetも公開しているので確認してください。

このデータをGoogle Apps Scriptで、月ごとにどの項目へどれだけ使ったかを自動的にまとめるようなプログラムを作ろうと思います。

Google Apps ScriptはJava ScriptベースのGoogleの機能を扱うAPIを取り入れたスクリプトです。

まずは、スプレッドシート上からメニューのツール→スクリプトエディタで、スクリプトのエディタを開きます。すると右のようなエディターが開きます。
　

ここにプログラムを書いて、ウェブ上で自動的に実行することができるのです。

今回は、入力フォームのデータから月・項目別にまとめるシートへ変換するプログラムを書いてみます。
以下、ソースコードです。

// グローバル定数
var FORM_RES_SHEET_NAME = "フォームの回答";
var DATE_I = 1; //日付データ（いつ？）のインデックス（B列）

// フォームの回答集計シートから同じ月のデータを月別シートへコピーする
function separateRes(){
  // フォームの回答集計シートを選択する
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName(FORM_RES_SHEET_NAME);
  SpreadsheetApp.setActiveSheet(sh);
  
  //全データの配列を取得
  var rg0 = sh.getDataRange();
  //最終列番号を取得→回答数-1
  var rows = rg0.getLastRow();
  //最終行番号を取得→フォームで回答する項目数
  var columns = rg0.getLastColumn();
  //回答データの配列を取得（最初の行はフォームの項目名になっているため省く）
  var rg = sh.getRange(2, 1, rows-1, columns)
  var values = rg.getValues();
  
  for (i=0; i<rows-1; i++){
    // I行が月別シートへ既にコピーされているか否かをコラムの色で判断
    // I行はまだ月別シートへコピーされていない場合
    if (rg.getCell(i+1, 1).getBackgroundColor()!="#999999"){
      // 該当列の項目を月別シートへコピーし、コピー後にコラムの色を変更する
      copyResSheet(i, values[i][DATE_I].getFullYear(), values[i][DATE_I].getMonth(), sh);
    }
  }

  Browser.msgBox("finish!");
}

// フォームの回答集計シートのある行を月別シートへコピーし、コピーし終わった項目の色をグレーに変更する。
function copyResSheet(index, year, month, sh){
  // Spreadsheetを選択し、シート名を設定（月/年）
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = (month+1) + "/" + year;

  // 月別シートを選択。コピーしたい月別シートがSpreadsheet内に無い場合は作成し、選択する。
  if(!ss.getSheetByName(sheetName))
    var shMonth = ss.insertSheet(sheetName);
  else
    var shMonth = ss.getSheetByName(sheetName);  
  SpreadsheetApp.setActiveSheet(shMonth);

  // 月別シートの配列を取得
  var rg = shMonth.getDataRange();
  // 月別シートの行数を取得
  var rows = rg.getLastRow();

  // フォームの回答集計シートのコピーする範囲を取得
  // getRangeでの行列指定と配列インデックスの違いに注意
  rg = sh.getDataRange();
  var columns = sh.getLastColumn();
  rg = sh.getRange(index+2, 1, 1, columns);

  // 初めてのコピーの場合。最初の行からへコピー
  if(shMonth.getRange(1,1).getValue()==0){
    rg.copyTo(shMonth.getRange(1, 1, 1, columns));
    // コピー元のコラムをグレーへ設定
    rg.setBackground("#999999");
  }
  // 月別シートへ追加でコピーする場合。
  else{
    rg.copyTo(shMonth.getRange(rows+1, 1, 1, columns));    
    // コピー元のコラムをグレーへ設定
    rg.setBackground("#999999");
  }
}

この関数(separateRes)を実行すると、「フォームの回答」シートから各月のシートに全ての支出がコピーされます。たとえば、2015年12月の支出は全て「12/2015」というシートにコピーされることになります。一度コピーしたものは、「フォームの回答」シートで背景がグレーになり、次回からはコピーされないようにプログラムされています。
この関数をスプレッドシート上から実行できるようにコマンドをツールバーに追加しました。







下記がソースコードです。スプレッドシートが開いたときに実行されるプログラム（onOpen())にて、"Command"というツールバーメニューが追加され、"フォーム回答→月別シートへコピー"を選択すると、separateRes関数が実行されるようになります。

// Spreadsheetが開いたときに実行するプログラム。メニューリストにプログラムを実行する項目を追加
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // 新しいメニューに追加する項目名の配列
  var menuEntries = [{name: "フォーム回答→月別シートへコピー", functionName: "separateRes"}];
  ss.addMenu("Command", menuEntries);
}

さて、もちろんこれではまだ月ごとに分けただけで、各項目の支出がどれくらいか分かりません。
それはまた別のプログラムで実施することにして、次回紹介したいと思います。

外資系企業の種類

外資系企業といっても、様々な種類があると思う。

これまで働いてきた企業や、面接させてもらったところ、友人・知人などの話から、2種類に大きく分けられると思う。

1. 日本企業を買収してない外資系
   100％出資の子会社である日本支社が東京にあり、大阪・名古屋など多くても日本では2，3拠点のみ構えているような企業。
   このような外資系企業は、給料も高めで、有休の100％消化義務、仕事よりも家族優先が当たり前、フレックスタイムや在宅勤務など働く環境として、日本企業より良い反面、社内のコミュニケーションでは英語必須、少数精鋭で幅広い範囲の仕事を熟さないといけない、本社を介さないとできないものも多いなど、全般的に高いスキルが要求される。
   いわゆる外資系とはこのスタイルだと思う。
   
2. 日本企業を買収した外資系
   外資系企業が日本へ進出する際に、日本企業を買収して、日本支社となった外資系。製造業であれば、工場や研究所もあり、従業員も多い。もともと日本企業であるため、ドメスティックな制度や文化が残っている可能性が高い。
   また、仕事上、英語も必須ではない人も多く、たまにお偉いさんから英語でメールが来たから外資系なんだなと感じる、などと言うくらいドメスティックなところも多い。

自分は、広い範囲で責任を持ちながら、あれもこれも手を出しつつ経験していきたいし、最初の就職では英語ができないと仕事にならない職場を探していたので、前者しか選択しになかった。転職活動をしたときも、両社の雰囲気は全く違っていて、自分はやはり前者の方が性に合っていると実感した。外資系の中でも、この違いは文化の違いを大きく表すので、参考になる。

でも、後者の方が海外勤務の可能性は高いのではないかと思う。前者の外資系企業は、日本語で自社の商品なりサービスを売り込んでくれるから、つまり「日本語でお客さんとつないでくれるから」雇っている訳であって、わざわざ日本人を本社に迎える必要はないのだ。そんなことであったら、日本でお客さん探してこい、となる。

しかし、後者の場合、日本企業を買収したメリットが少なくとも外資系企業にはあって、そのノウハウを本社側と共有したり、共同開発をしたりなど、海外で勤務する可能性は高いと思う。その点、日本の大企業もチャンスは多いのだろうけど。