自作関数を使ったシートの値をスクリプトで取得する場合の注意点
Googleスプレッドシートにて、いわゆる「ユーザ定義関数」を使った式を使ってる場合、標準装備の関数と異なり、計算結果が出るまで凄く遅かったりします。その為、これらのユーザ定義関数を使ってるシートの値を、Google Apps Scriptで取得する場合、まだ計算途中のシートデータを取得してしまい、スクリプト内で使用する場合や、また、HTML Serviceを使ったウェブアプリケーションで使う場合に、エラー表示されてしまい、具合が悪いケースがあります。
しかも、この場合、プログラム的には別にエラーではないので、エラートラップにも掛からない上に、二回目以降だと普通に取得出来たりするので、デバッグが非常にしにくく、トラブル解決する時に非常に面倒な自体に嵌まる事があります。今回はこの小ネタを解消してみたいと思います。
今回使用するメソッド類
問題のコードと解消方法
問題のあるコード
Google Apps Scriptでは非常によく利用するスプレッドシートのデータを取得するコードは以下の通り。今回のコードはHTML Serviceで表示させたHTML側からgoogle.script.run.withSuccessHandlerで呼び出し、スプレッドシートのデータの塊を返すコードです。
|
1 2 3 4 5 6 |
//テーブルデータの生成 function tabledata(){ var sheet = SpreadsheetApp.openById(spreadid); var ss = sheet.getSheetByName("データ").getRange("A2:AC").getValues(); return JSON.stringify(ss); } |
spreadidにはスプレッドシートのIDが入り、それを元にデータという名前のシートのA2:ACの範囲を取得して返します。しかしこのシートには、独自に作成した関数が使われており、計算結果が出るまで時間が掛かるのですが、このルーチンがアクセスした時に計算途中のまま取得してしまう事があります（特に１回目。もう一度実行した２回目以降の場合、無事に取得できちゃったりします）。
この時のスプレッドシート上の表示状態は以下のような感じ。Loading…のままになってるわけです。どんなにシンプルなユーザ定義関数でも計算結果が標準関数と違い、凄く遅いので、複雑な関数を作って使ったりした場合や、それらの関数を大量に数式として使ってる場合、こんな表示状態が続いてしまいます（正直、これはなんとかしてほしい現象の１つなのですが）。
但し、標準関数でもFilter関数やQuery関数のような計算時間が掛かるものであっても、これらの場合は普通にデータは取得可能なので、今回のブログネタのようなテクニックは不要です（その分、表示や他のルーチンでそれらのデータを使った表示や計算結果が出るまでが遅いですが）。
解消法
このケースの解消法は単純と言えば単純です。以下のようなwhile文を使ったコードに書き直して、取得出来るまでループを回す方法です。Loading…の場合には、Google Apps Scriptで取得してしまうと、#NAME?の状態で取得されてしまうので、これを条件式に利用します。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
//テーブルデータの生成 function tabledata(){ var sheet = SpreadsheetApp.openById(spreadid); var ss = sheet.getSheetByName("データ").getRange("A2:AC").getValues(); //関数が計算中の時、正しく値が取得出来るまでサイド値を取得する while (ss[0][19] == "#NAME?"){ ss = sheet.getSheetByName("データ").getRange("A2:AC").getValues(); } return JSON.stringify(ss); } |
while文の中で、今回はデータの１行目の20列目にその関数を使用してるので、ここが「#NAME?」となり、他のスクリプトのルーチンやHTML側でデータを受け取って、w2uiのようなGUIライブラリで表示させた場合に、エラーとなってしまうわけです（プログラムのエラーではないので、if文などで判定させていても、通過してしまったりします）。
そこで、#NAME?ならば、while文を回し続けて、再度データを取得させてまた判定をし、#NAME?でなければ、return文まで進めるという仕組みです。およそ２回目ではデータが取得可能なので、この方法で対応が出来ますが、大量にユーザ定義関数を使ってる場合には、スプレッドシートの最終行あたりの値を取得してループさせればベストだと思います。
今回使った問題のユーザ定義関数
ちなみに、今回使用した独自作成したユーザ定義関数は、以下のような仕組みの関数です。
- Googleカレンダーの日本の祝日を取得して、スプレッドシートに日付と休日名を取得しておく。
- 対象の日付の時刻が15:00以降の場合には、翌日の日付にするworktime関数を作成（ユーザ定義関数）
- 更に標準関数であるWORKDAY.INTL関数を使用して、土曜日や日曜日、1.の祝日に該当する場合、次の日付にする関数の入れ子にする
- 使用例は、=WORKDAY.INTL(worktime(B4)–1,1,1,‘祝日リスト’!$B$2:$B) こんな感じ。
いわゆるフォームで申請されてきたデータのタイムスタンプを見て、受付する日が土日祝を除いた営業日以外の場合、翌日以降に日付を繰り延べるという関数です。コレ以外にも単純に、値をユーザ定義関数内でswitch文で判定して返すなんていうユーザ定義関数でも起きます。Googleスプレッドシートではなるべく、ユーザ定義関数を使わずに、標準関数のみを使用するか？値をシートに格納する場合には、計算式をsetFormulaで格納するのではなく、計算結果を格納するような努力をしたほうが良いでしょう。
