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);
}

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