GoogleSpreadsheetsでGASを使うと「#ERROR!」(”Service Invoked Too Many Times”エラー)

シェアする

  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存

Googleスプレッドシートの大量セルでGASを一気に使うとエラーが発生する

Error Image

エラー
Service invoked too many times in a short time:exec qps. Try Utilities.sleep(1000) between calls.(行0) が発生しました。

一般的な対処法

大量に処理を行う場合、下記の方法を検討してください。

  1. 同じ内容のデータが連続してある場合はスキップさせるなどの工夫をする。
    例) 空白のセルの場合はユーザー関数を呼び出さない
    `=if( A1 = "", "", yourFunction( A1 ) )`
  2. 各行に計算式を入れるのではなく、1行目に複数行を一括処理するタイプの関数に変える
    例) 1行目のセルのみで複数行を処理する
    `=yourFunction( A1:A2000 )`

    //上記の計算式(1,2の両方で利用可能)
    function yourFunction( input ){
    var myResult = '';
    if ( input.map ) { //範囲指定型の入力か確認(A1:A2000のようなタイプ)
    return input.map( yourFunction ); //再帰的に入力値を処理
    } else {
    // ここに従来の処理
    return myResult;
    }
    }
  3. 利用可能な処理回数(クオーターの制限)を超えていないか確認する

私の場合は、空白セルが多かったので上記の1番が効果あったのですが、2番目も作成して試したところ、圧倒的に処理速度が早かったです。エラーメッセージの言うように`Utilities.sleep(1000)`をスクリプトの最初に入れてみても効果はなく、むしろ劇的に遅くなるだけだったので、そもそもユーザー関数を呼び出す頻度に問題があるという結論になったのでした。

所感

Googleスプレッドシートで、セルの入力値をWEBで使う際になるべく問題のないようにサニタイズする(データの内容を健全に使えるようにする)ユーザー関数をGAS(GoogleAppsScript)で作りました。

具体的には、「スペース以外は英数字、記号含む全てを全角に変換、連続した複数スペースを1つに置換、禁則文字列を伏字に置換」といった処理をする関数です。WebではDBを使う際の出力時に比較的よくやる処理だと思います。

最初はデータの利用先で、これらの置換処理をするという事だったらしく、相談を受けた時にWeb側の処理にコストをかけられないみたいだったので入力時に処理しちゃった方がいいと思うけどなぁ、とアドバイスしたのですが、案の定セルにHTMLタグを入れて利用する人(独自に装飾を始めた人)が出てきて、なんとかして欲しいという、懸念していた通りのアルアルな結果になりました。

相談してきたデザイナー兼SEの為に擁護すると、自分たちで出来ないというより装飾タグを使っていたのがマイ・ホームページを持っている重役らしく、「引っ張ってきたデータの仕様上できない」という形にしたいという大人の事情というやつみたいです。

ところが、作成した関数を利用して、別の列で入力一覧(2000行)を一気に処理したところ、エラー「#Error!」がところどころ発生して、内容を見ると「Service invoked too many times in a short time」と、短時間での大量の処理リクエストが原因と思われるエラーが出ました。ポツポツと不規則に発生しており、エラーのデータを一旦削除して入力し直すと、ちゃんと処理されるため、処理落ちっぽいイメージです。

Googleってそんなに処理できひんのかーい、と思ったのですが、まぁ無駄な(非効率的な)処理をかましてリソースを使って他のGoogle利用者に迷惑をかけているわけですから、スクリプトを改善して、「一気に小出しにリクエストするのではなく、一括でリクエストして結果を一気に流し込む」スタイル(上記の2番目の方法)で解決しましたとさ。

参考文献

関連記事

Googleスプレッドシートで文字列をコンマ区切りにする(TEXT関数)... Google Spreadsheetsで、数値をコンマ区切り(カンマ区切り)にしたい場合、通常は書式設定で設定するのですが、"3,000 hours"と言った文字列付き(単位付き)フォーマットで表示にしたい場合に困ります。 Excelの様に書式設定でカスタム指定できないし、Format関数があ...
ChromeでGoogleスプレッドシートのタブが表示されない... GoogleSpreadsheetsのタブがChromeで表示されない 特定のマシンのChromeで、Googleのスプレッドシートのタブが表示されず、入力も出来ない状態が発生しました。 結論から言うと、拡張機能が原因で一旦すべてをオフにすると回避できます。 Safari,IE,Firef...
GoogleSpreadsheetでMD5ハッシュを取得する時の落とし穴... 違う文字列を渡しても`DigestAlgorithm.MD5`のハッシュ値が同じ結果になる Googleスプレッドシートのセルの値をMD5化したい場合など、GoogleのAppsスクリプト(GAS)で`Utilities.DigestAlgorithm.MD5`メソッドを使って日本語文字列のMD5...
GASにおける`charAt`メソッド – Alternative of `charAt... `charAt`メソッドをGoogleAppsScriptで使う n進数の変換処理など、ある文字列(String)から再帰的に「x番目の文字を取り出す」必要がある場合、GoogleAppsScriptだと`charAt`メソッドが使えないので少し悩みました。 JavaScriptの場合 Jav...

スポンサーリンク
レクタングル(大)広告

シェアする

  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存
スポンサーリンク
レクタングル(大)広告