GAS:入力された日付の曜日をスプレッドシート側で自動計算

Google Apps Scriptは便利なのですが、時にはプログラム上での計算処理ではなく、スプレッドシート側の関数で処理したいこともあると思います。

可視化性やメンテナンス性を高めるためにこういう柔軟性は必要だと思います。

よくある例としてはGoogleFormで日付を入力させた場合です。

入力者に出張開始日と出張終了日をYYYY/MM/DD形式で入力させることは難しくないでしょう、でも曜日や出張日数を自動計算できないシステムなんてちょっと機能的にも信頼されないと思います。

スプレッドシート側では以下の様な形で収納したいとしましょう。

sheet

フォームからはK列とM列にデータが入ります。L列,M列,O列はそれぞれ


L2=vlookup(weekday(K2),曜日,2,false)

M2=vlookup(weekday(M2),曜日,2,false)

O2=M2K2+1

といった式を入れておきたいわけです。

なおここでは「曜日」という範囲を別のシートに以下の様なデータで定義しています。EXCELと同じような名前定義が使えるのは便利ですね。

【「曜日」で定義されたデータ】

1 日
2 月
3 火
4 水
5 木
6 金
7 土

さて、スクリプトです。

入力されたデータを自動処理するあたりで文字列として「=vlookup(weekday(K2),曜日,2,false)」をセットしてみるのですが、どうもセルの参照である「K2」が正しく働いてくれません。
色々やってみた挙句、以下のように相対参照(いわゆるR1C1形式)で表記すれば自動的に正しいセルのアドレスを取得してくれるようです。これはこれで便利ですね!コードに落とした場合は以下のとおりです。

[javascript]
//曜日と出張日数を自動計算
d = sh.getRange(row, 1, 1, rg.getLastColumn()).getValues(); //指定行のデータを取得
if (d[0][11]=="") { //左から数えて12個め,つまりL列が空白だったら以下のコードを自動で入れます
rg.getCell(rowline, 11+1).setValue("=vlookup(weekday(R[0]C[-1]),曜日,2,false)");
rg.getCell(rowline, 11+3).setValue("=vlookup(weekday(R[0]C[-1]),曜日,2,false)");
rg.getCell(rowline, 11+4).setValue("=R[0]C[-2]-R[0]C[-4]+1");
}
[/javascript]

Google Apps Scriptはクラウド側で演算してくれるので、利用者が演算コストを意識する必要はないのですが、スクリプト側のタイムアウトを防ぐためにもスプレッドシート側で演算できるものはできるかぎりスプレッドシート側で処理させたほうが良いと思います。
スクリプト側にハードコーディングしてしまうと、スプレッドシート側でいろいろやりたいエンドユーザ(EXCELスキルがある人とか……)を突き放すことにもなります。

実際にはEXCELでいろいろやりたいユーザさんが味方になってくれてこそのGAS利用だと思いますので

このような設計手法は柔軟性があって良いと思います!