Google ドキュメントを活用する （家計簿編3）

前回でほぼ完成した家計簿ですが、月ごとに自動的にデータを分けておきたいですよね。今回は月ごとにデータをまとめる関数を実装したいと思います。家計簿編はこれで完結です。簡単に実装できると思ったんですが、意外にはまりました。

実装したい内容は、フォームから入力されたデータを保存するシートをコピーして名前を変える、コピー元のシートの古いデータを消去、先月の残高を繰越金として入力する、だけなのですが、いざ繰越金を入力しようとすると、セルに値を書くことは出来るのですが、フォームから入力されたデータとして認識してくれません。つまり、フォームからデータを入力すると折角書いた繰越金が上書きされて消えてしまいます。フォームを管理するスプレッドシートを開くと、メニューバーのフォームの隣りに入力されたデータ数がカッコに囲まれて表示されています。フォームからデータを入力するとインクリメントされますが、普通にスプレッドシートにデータを setValue() で書き込んでもここは変化しません。
データ数をコントロールする API が分かればよいのでしょうが、そんなのは見つかるはずも無いだろうということで、フォームにスクリプトから入力する方法を模索してそれを実装しました。

使った API は UrlFetchApp.fetch(url, optAdvancedArgs) です。

引数の url と optAdvancedArgs はフォームのソースコードから見つけます。HTML でフォームを作ったことがある方なら分かると思いますが、
< form action="~~~" >
で示される URL です。ここで注意すべき点は、url に含まれる formResponse?formkey= の部分で、formResponse?hl=ja&formkey= なっている場合があります。hl=ja&は消しましょう。理由はよくわかりませんが、これを消さないとうまく動作しません。
また optAdvancedArgs を使った繰越金（収入欄）の指定は、< input type="text" name="entry.1.single" でも分かるように、entry.x.single で示されるネームへの引数指定になります。その他の変数も併せて、適宜個々の環境に合わせて設定して下さい。

では前回のスクリプトの代わりに以下の完成版のスクリプトをコピペして置き換えて下さい。解説は特に書きませんので、コメントを参考に理解して下さい。

view plainprint?

1. //-------------------------------------------------------------------  
2. // 家計簿スクリプト for Google docs  
3. // Date: 2/4/'11  
4. // Author: Tatsuro  
5. //-------------------------------------------------------------------  
6.   
7. // 定数定義  
8. var UPDATE_DATE = 1;  // バックアップを実行したい日  
9. var BALANCE_COL = 2;  // 残高の列番号B  
10. var IN_COL      = 3;  // 収入の列番号C  
11. var EX_COL      = 4;  // 支出の列番号D  
12. var IN_COL_SUM  = 7;  // 収入合計の列番号G  
13. var EX_COL_SUM  = 8;  // 支出合計の列番号H  
14.   
15. // FORM の情報  
16. var FORM_URL    = 'http://spreadsheets.google.com/formResponse?formkey=dGg2dnoyN3pKaEJtb2FfMFF5R3JmNEE6MQ&ifq';  
17. var FORM_TAG_IN = 'entry.1.single=';  // 収入  
18. var FORM_TAG_C1 = '&entry.3.single='; // 適用  
19. var FORM_TAG_C2 = '&entry.4.single='; // 費目  
20.       
21. //-------------------------------------------------------------------  
22. // accessForm(value_form):  
23. // 新規シートのセットアップのために繰越金(value_form)をフォームを経由して書き込む  
24. //-------------------------------------------------------------------  
25. function accessForm(value_form) {  
26.   // Form に入力したい項目を設定する  
27.   var post_str =   
28.       FORM_TAG_IN+encodeURIComponent(value_form)+  
29.       FORM_TAG_C1+encodeURIComponent("先月")+  
30.       FORM_TAG_C2+encodeURIComponent("繰越");  
31.   var options = {  
32.     "method"  : "post",  
33.     "payload" : post_str  
34.   };  
35.   
36.   // Form にデータを POST する    
37.   UrlFetchApp.fetch(FORM_URL, options);  
38. }  
39.   
40. //-------------------------------------------------------------------  
41. // backupLatest():  
42. // 毎月一度の集計時に呼ばれることを前提とした関数  
43. // 先月分をシートごとコピー、シート名変更、フォームのデータが保存されるシートの初期化を行う  
44. //-------------------------------------------------------------------  
45. function backupLatest() {  
46.   // 今日の日付を取得する  
47.   var today = new Date();  
48.   
49.   // 日付がバックアップを実行したい日のみ実行  
50.   if (today.getDate() == UPDATE_DATE) {  
51.     // 左端のシートをアクティブにし、アクティブなシートの複製をつくる  
52.     var ss = SpreadsheetApp.getActiveSpreadsheet();  
53.     ss.setActiveSheet(ss.getSheets()[0]);  
54.     ss.duplicateActiveSheet();  
55.       
56.     // 複製されたシート（左から2番目）をアクティブにする  
57.     ss.setActiveSheet(ss.getSheets()[1]);  
58.       
59.     // 複製されたシートのセル A2 のデータを取り出す（日付）  
60.     var date_str = ss.getSheets()[1].getRange(2,1).getValue();  
61.     // 複製されたシートのセル B1 のデータを取り出す（残高）  
62.     var balance = ss.getSheets()[1].getRange(1,BALANCE_COL).getValue();  
63.       
64.     // 複製されたシートの日付のデータから Date オブジェクトを生成し、年、月を抽出する  
65.     // 文字列解析がめんどうなので(いや、正しい対処法か ww)  
66.     var date = new Date(date_str);  
67.     var year = date.getFullYear();  
68.     var month = date.getMonth() + 1;  
69.       
70.     // 複製されたシート名を年-月に変更する  
71.     // (既に存在するシート名ではエラーになるので注意)  
72.     ss.renameActiveSheet(year+'-'+month);  
73.   
74.     // フォーム入力用のシート(左端のシート)を初期化する  
75.     // 左端のシートのオブジェクト取得  
76.     var s_new = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];  
77.     // 左端のシートでデータが書かれている最大の行数を取得する  
78.     var maxRow = s_new.getLastRow();  
79.     // 先月のデータを消す  
80.     s_new.deleteRows(2, (maxRow-1));  
81.   
82.     // 繰り越し入力  
83.     accessForm(balance);  
84.   }  
85. }  
86.   
87. //-------------------------------------------------------------------  
88. // updateCalculation():  
89. // フォーム入力後に呼ばれることを前提とした関数  
90. // 残高の計算式を埋め込みアップデートする  
91. //-------------------------------------------------------------------  
92. function updateCalculation() {  
93.   // 左端のシート(今月の家計簿・フォームのデータ保存先)のオブジェクトを取得する  
94.   var s_latest = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];  
95.   
96.   // 左端のシートでデータが書かれている最大の行数を取得する  
97.   var maxRow = s_latest.getLastRow();  
98.   
99.   // 支出の合計を計算したいので、その範囲を A1 形式の文字列にして準備する  
100.   // 範囲は、支出列の入力したばかりの行と前回の支出合計欄  
101.   var sumRngOutC = s_latest.getRange(maxRow,  EX_COL    ).getA1Notation();  
102.   var sumRngOutL = s_latest.getRange(maxRow-1,EX_COL_SUM).getA1Notation();  
103.   
104.   // 支出の縦合計の計算式を保管したいセルに先程の A1 形式を使っ埋め込む  
105.   // 保管先のセルは入力したばかりの行の支出合計欄  
106.   // 最初の行の場合は前回の結果が無いので今回の値を参照するだけ  
107.   if (maxRow == 2) {  
108.     s_latest.getRange(maxRow, EX_COL_SUM).setFormula('='+sumRngOutC);  
109.   } else {  
110.     s_latest.getRange(maxRow, EX_COL_SUM).setFormula('=SUM('+sumRngOutC+'+'+sumRngOutL+')');  
111.   }  
112.     
113.   // 収入の欄も同様に処理する  
114.   var sumRngInC = s_latest.getRange(maxRow,  IN_COL    ).getA1Notation();  
115.   var sumRngInL = s_latest.getRange(maxRow-1,IN_COL_SUM).getA1Notation();  
116.   
117.   if (maxRow == 2) {  
118.     s_latest.getRange(maxRow, IN_COL_SUM).setFormula('='+sumRngInC);  
119.   } else {  
120.     s_latest.getRange(maxRow, IN_COL_SUM).setFormula('=SUM('+sumRngInC+'+'+sumRngInL+')');  
121.   }  
122.   
123.   // 残高の計算のため、その元となる最後に入力した行の収入合計、支出合計、  
124.   // 最後の行の残高の保管セルの位置を A1 形式の文字列で準備する  
125.   var sumOut    = s_latest.getRange(maxRow, EX_COL_SUM).getA1Notation();  
126.   var sumIn     = s_latest.getRange(maxRow, IN_COL_SUM ).getA1Notation();  
127.   var refResult = s_latest.getRange(maxRow, BALANCE_COL ).getA1Notation();  
128.   
129.   // 残高の計算式を埋め込む  
130.   s_latest.getRange(maxRow, BALANCE_COL).setFormula('='+sumIn+'-'+sumOut);  
131.   
132.   // 残高をフォームに表示する為、残高の計算結果の参照の式をセルにオーバーライド  
133.   s_latest.getRange(1, BALANCE_COL).setFormula('='+refResult);  
134. }  
135. ​  

//-------------------------------------------------------------------
// 家計簿スクリプト for Google docs
// Date: 2/4/'11
// Author: Tatsuro
//-------------------------------------------------------------------

// 定数定義
var UPDATE_DATE = 1;  // バックアップを実行したい日
var BALANCE_COL = 2;  // 残高の列番号B
var IN_COL      = 3;  // 収入の列番号C
var EX_COL      = 4;  // 支出の列番号D
var IN_COL_SUM  = 7;  // 収入合計の列番号G
var EX_COL_SUM  = 8;  // 支出合計の列番号H

// FORM の情報
var FORM_URL    = 'http://spreadsheets.google.com/formResponse?formkey=dGg2dnoyN3pKaEJtb2FfMFF5R3JmNEE6MQ&ifq';
var FORM_TAG_IN = 'entry.1.single=';  // 収入
var FORM_TAG_C1 = '&entry.3.single='; // 適用
var FORM_TAG_C2 = '&entry.4.single='; // 費目
    
//-------------------------------------------------------------------
// accessForm(value_form):
// 新規シートのセットアップのために繰越金(value_form)をフォームを経由して書き込む
//-------------------------------------------------------------------
function accessForm(value_form) {
  // Form に入力したい項目を設定する
  var post_str = 
      FORM_TAG_IN+encodeURIComponent(value_form)+
      FORM_TAG_C1+encodeURIComponent("先月")+
      FORM_TAG_C2+encodeURIComponent("繰越");
  var options = {
    "method"  : "post",
    "payload" : post_str
  };

  // Form にデータを POST する  
  UrlFetchApp.fetch(FORM_URL, options);
}

//-------------------------------------------------------------------
// backupLatest():
// 毎月一度の集計時に呼ばれることを前提とした関数
// 先月分をシートごとコピー、シート名変更、フォームのデータが保存されるシートの初期化を行う
//-------------------------------------------------------------------
function backupLatest() {
  // 今日の日付を取得する
  var today = new Date();

  // 日付がバックアップを実行したい日のみ実行
  if (today.getDate() == UPDATE_DATE) {
    // 左端のシートをアクティブにし、アクティブなシートの複製をつくる
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.setActiveSheet(ss.getSheets()[0]);
    ss.duplicateActiveSheet();
    
    // 複製されたシート（左から2番目）をアクティブにする
    ss.setActiveSheet(ss.getSheets()[1]);
    
    // 複製されたシートのセル A2 のデータを取り出す（日付）
    var date_str = ss.getSheets()[1].getRange(2,1).getValue();
    // 複製されたシートのセル B1 のデータを取り出す（残高）
    var balance = ss.getSheets()[1].getRange(1,BALANCE_COL).getValue();
    
    // 複製されたシートの日付のデータから Date オブジェクトを生成し、年、月を抽出する
    // 文字列解析がめんどうなので(いや、正しい対処法か ww)
    var date = new Date(date_str);
    var year = date.getFullYear();
    var month = date.getMonth() + 1;
    
    // 複製されたシート名を年-月に変更する
    // (既に存在するシート名ではエラーになるので注意)
    ss.renameActiveSheet(year+'-'+month);

    // フォーム入力用のシート(左端のシート)を初期化する
    // 左端のシートのオブジェクト取得
    var s_new = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    // 左端のシートでデータが書かれている最大の行数を取得する
    var maxRow = s_new.getLastRow();
    // 先月のデータを消す
    s_new.deleteRows(2, (maxRow-1));

    // 繰り越し入力
    accessForm(balance);
  }
}

//-------------------------------------------------------------------
// updateCalculation():
// フォーム入力後に呼ばれることを前提とした関数
// 残高の計算式を埋め込みアップデートする
//-------------------------------------------------------------------
function updateCalculation() {
  // 左端のシート(今月の家計簿・フォームのデータ保存先)のオブジェクトを取得する
  var s_latest = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

  // 左端のシートでデータが書かれている最大の行数を取得する
  var maxRow = s_latest.getLastRow();

  // 支出の合計を計算したいので、その範囲を A1 形式の文字列にして準備する
  // 範囲は、支出列の入力したばかりの行と前回の支出合計欄
  var sumRngOutC = s_latest.getRange(maxRow,  EX_COL    ).getA1Notation();
  var sumRngOutL = s_latest.getRange(maxRow-1,EX_COL_SUM).getA1Notation();

  // 支出の縦合計の計算式を保管したいセルに先程の A1 形式を使っ埋め込む
  // 保管先のセルは入力したばかりの行の支出合計欄
  // 最初の行の場合は前回の結果が無いので今回の値を参照するだけ
  if (maxRow == 2) {
    s_latest.getRange(maxRow, EX_COL_SUM).setFormula('='+sumRngOutC);
  } else {
    s_latest.getRange(maxRow, EX_COL_SUM).setFormula('=SUM('+sumRngOutC+'+'+sumRngOutL+')');
  }
  
  // 収入の欄も同様に処理する
  var sumRngInC = s_latest.getRange(maxRow,  IN_COL    ).getA1Notation();
  var sumRngInL = s_latest.getRange(maxRow-1,IN_COL_SUM).getA1Notation();

  if (maxRow == 2) {
    s_latest.getRange(maxRow, IN_COL_SUM).setFormula('='+sumRngInC);
  } else {
    s_latest.getRange(maxRow, IN_COL_SUM).setFormula('=SUM('+sumRngInC+'+'+sumRngInL+')');
  }

  // 残高の計算のため、その元となる最後に入力した行の収入合計、支出合計、
  // 最後の行の残高の保管セルの位置を A1 形式の文字列で準備する
  var sumOut    = s_latest.getRange(maxRow, EX_COL_SUM).getA1Notation();
  var sumIn     = s_latest.getRange(maxRow, IN_COL_SUM ).getA1Notation();
  var refResult = s_latest.getRange(maxRow, BALANCE_COL ).getA1Notation();

  // 残高の計算式を埋め込む
  s_latest.getRange(maxRow, BALANCE_COL).setFormula('='+sumIn+'-'+sumOut);

  // 残高をフォームに表示する為、残高の計算結果の参照の式をセルにオーバーライド
  s_latest.getRange(1, BALANCE_COL).setFormula('='+refResult);
}
​

backupLatest() を単独で実行すると期待通りの動作を行います。上手く実行出来ない場合は、定数 UPDATE_DATE が実行したい日の指定に合っているかとどうか確認して下さい。UPDATE_DATE はスクリプトを実行したい日です。例えば日付が1日に変わった直後にバックアップをとれば丁度一月区切りで都合が良いと思います。

ところで、月に一度これを実行するにはどうすればよいかというと、前回使用したトリガーで、そのきっかけをフォームでなく、時間(Time-driven)で指定することで実現可能です。Time-driven は Minutes, Hour, Day そして Week をきっかけに起動できますが、Day timer の時間指定 (例えば夜中の0:00-1:00 の間)で自動でスクリプトを起動し、UPDATE_DATE と日付が合致した場合に限り残りの作業を行うようにします。
Time-driven トリガーを設定する前に必ずスクリプトのプロパティでタイムゾーンを合わせましょう。デフォルトでは日本になっていないようなので、注意です。また、タイムゾーンを設定する前に Time-driven トリガーを設定すると異なるタイムゾーンの設定が残ってしまいますので、その場合は一旦設定したトリガーを削除して、タイムゾーンを設定後にトリガーも登録しなおして下さい。

プロパティのメニュー

タイムゾーン設定

トリガー設定

以上でザックリですが、オートバックアップ付きの家計簿の例の紹介を終わります。まだ実験レベルでの提供による時間で起動できるトリガーも使えるというのは衝撃的事実でした。Google Apps Script にて Cron もどきの機能が使えるということは夢が広がりますね。