importrange関数には利用制限が存在しており、GASやプラグインとリソースを共有している

表題の通り、どうやらしているみたいです。しばらくインターネットの海を泳ぎましたがだれもこの主張をしていなくて困ったので書いときます。

Googleスプレッドシートの関数には利用制限のあるものがあります。最も顕著にわかりやすいのはimportxml関数です。
これは簡易的にスクレイピングを行うことができる関数です。

URLを渡すとWEBページのHTMLをfetchしてきてくれて、パースして利用したりできます。

たとえばSEOメディア運用者であれば、競合サイトの全記事のタイトルやH要素を取ってくるのに使えます。理論上は。

実際には使い物になりません。なぜなら利用制限がキツくて、何十本何百本とスクレイピングしていると、すぐに読み込み不可能になるからです。

似たような制限がimportrangeにも存在しています。

スプレッドシートを巨大なSaaSのように便利に使用していると、ときおりimportrangeを利用することがあるでしょう。

会社の数値管理とかをスプシでしていると、しばしばあると思います。

こちらもレート制限が存在しており、制限に達すると読み込みが完了しなくなります。

このとき厄介なのは、特にそれらしいエラーが出ないことです。ただただ読み込みが完了しないという現象として表出します。

僕のところでは外部データの取得にSupermetricsというプラグインを利用しています。importrangeが動かないタイミングでこちらも動かなくなりました。

さらに、importrangeが動かないのであれば大本のDBからGASによって直接シートへインポートしようとしたのですが、それも動きません。

どれも他のスプレッドシートで試すと、普通に動きます。

以上のことからわかるのは、

・importrange、GAS、API利用のプラグインは同じ利用制限を共有している。
・制限はスプレッドシートごとに存在している

ということです。

GASやプラグインからシートへ書き込みを行う場合はライブラリを利用しているはずで、ライブラリはAPIを利用しています。

APIに利用制限があることは普通のことなので、そこは特に違和感ありませんね。
そしてこれらと同じタイミングで制限がかかるということで、どうやらimportrangeもAPIを利用しているらしいと考えることができますね。

GASを利用すると、スプシ内で利用できるカスタム関数を作成できます。（すっごい実行遅いので、あんまり使いませんが）

この場合はもちろんライブラリを通してAPIを叩いているので、API制限の枠内で使用することになると思います。

僕が思うに、importrangeやimportxmlも、いわば公式が用意してくれた、実行の早いカスタム関数なのではないでしょうか。

そう考えるとGASと同タイミングで利用制限に引っかかるのも納得です。

ちなみにGASの方の処理では、外部DBからのデータの取得自体は成功していて、そのあとシートに書き込むところでタイムアウト落ちしていました。

書き込み制限にひっかかった場合はタイムアウトエラーしかでません。

なぜこのような仕様になっているかというと、おそらくですが、importrangeのような関数は、時間が経過して利用制限が外れたあとに自動で再度実行され整合性がたもたれるので、google的にはこれはエラーではないとみなしているのではないでしょうか。

一時的に利用制限に引っかかることが前提の設計なのです。

GAS視点だとそのような仕様だと困りますが、GASはあくまでオプショナルな存在なので、GASにとっては分かりづらい仕様になっているのもまあ仕方ないのかもしれませんね。

なんにせよ、一時的にでも利用制限に引っかかる状態はよくありません。日常的に利用制限の閾値付近をうろうろしてしまう構造になっているということなので。

importrangeやGAS、プラグインの利用量を減らして、利用制限に抵触しない範囲に収まるようにリファクタしたいですね。

なお、レート制限に達しているシートを複製して、レート制限をリセットしようとしても、うまくいきません。残念ながら。

これは

・複製時にレート制限を引き継いでしまっている
または
・シート複製時に一旦すべての関数が実行される仕様になっており、すべてのimportrangeが一度に実行され、一瞬でレート制限に達してしまう

の2つのどちらかかなと思っています。
おそらく後者です。

スプレッドシートはあくまで関数の文字列を元データとしているっぽい挙動を時折見せるため、複製時には文字列のみがコピーされ、その後全ての関数を実行して表示を作成しているのではと考えています。

以上です。
こんな記事を最後まで読むなんて、あなたはきっと重要なシートを保守しているんでしょう。お役に立てたら嬉しいです。

シート保守頑張ってください。
きっとうまくいきますよ。

https://twitter.com/1000tea