Google ドキュメントのフォームは後でスクリプトから表示内容を直接変えることが出来ないようなのですが、一つだけこれを変更する簡単な手段があります。スプレッドシートの一行目を編集を変えることです。では早速、前回作った家計簿の一行目にある残高と書かれているセルに実際の残高の結果があるセルの値を代入してみましょう。表示例ではセルB3に残高の結果があるので、セルB1に=B3と書き込みます。
セルB1に値が反映されたことを確認したら、フォームを開いてみましょう。ラジオボタンのタイトルに金額がちゃんと反映されていますね？本来なら、残高という文字をタイトルにして、その下に金額を表示させたいところですが、ちょっとイマイチですがやりたいことは出来ているので大目にみましょう（だれか他にいい方法あったら教えて下さい）。
次に数式の挿入の自動化です。折角ですから、これらのスプレッドシートの操作を自動化したいと思いませんか？後でPCなどですスプレッドシートを直接編集するのは間違う可能性があるだけでなく億劫です。そこで登場するのが、無償で利用出来る Google Apps Script。Java Script に Google のスプレッドシートを扱う API を加えたものです。Excel の VBA に似ていますので、VBA の経験か Java Script の経験があれば取っ付きやすいと思います。このスクリプトを駆使して、自動化にトライします。
まずは、スクリプトエディタを開きます。これはスプレッドシートを開いている状態でメニューのツール->スクリプト->スクリプトエディタで開くことが出来ます。
このようなエディターが開きます。
とりあえず、サンプルコードを以下に書いておきますので、コピペしてひな形の関数 myFunction() を上書きしてしまいましょう。
// 定数定義
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
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);
}
こんな風にコピペします。関数の先頭に列の定義を書いていますので、使う際はあなたの環境に合わせて数字を変更して下さい。また列を示す数値 1, 2, 3 ... は A, B, C 列を意味します。
続いて適当な名前でスクリプトを保存します。
ではこのスクリプトを試してみましょう。既にスプレッドシートにデータがあるならば、最後の行と残高の一行目のセルが書き変わることを確認します。スクリプトエディタのプレイボタンをクリックするか、メニューの実行をクリックして見て下さい。スクリプトが実行されます。エラーが出た場合は修正してから再度実行して下さい。
ここまでで半自動化ができましたので、次に完全自動化を目指します。完全自動化には何かのきっかけでスクリプトが自動的に実行できればよいわけです。Google Apps Script にはきっかけを指定する機能があります(Event と呼ばれています)。Event を指定するには、スクリプトエディタのトリガーから Current script's triggers... （現在のスクリプトのトリガー）を選択します。
続いて No triggers set up. Click here to add one now.（トリガーがセットされていません。ここをクリックしてトリガーを追加します）をクリックします。
次に実行する関数を選ぶリストと、Event を指定するリストが現れます。Event は From spreadsheet で、On form submit を選択します。これは、フォームからデータを送信する度に指定の関数を実行するという意味になります。
Event の指定が終了したら、Save ボタンを押して保存します。以上で用意した関数が自動実行されるお膳立てが出来ました。早速フォームを開いて、適当な値を入れて収入、支出を別々に送信して下さい。正しくデータが更新されることが確認されれば以上で終了です。これで少し使い勝手がよくなりました。今回は以上になります。
最後にスプレッドシートの編集の注意点ですが、1行目は消さないこと。2行目以降は、セルの中身ではなく行単位で削除しないと新しく入力されたデータが詰めて挿入されません。消したい行番号を選択して、右ボタンを押し行を削除を選んで実行しましょう。
次回は、月ごとにデータを保存する機能を実装しようと思います。
では。
0 件のコメント:
コメントを投稿