IMPORTRANGE関数の"インポート範囲の内部エラー"

Question

前提・実現したいこと

GGSのIMPORTRANGE関数を用いて、１つのシートに同一ファイル内の別シート（複数）からデータを引っ張ってこようとしています。

発生している問題

最初は特に問題なかったのですが、暫く作業を続けていると#REFのエラーメッセージが約10分毎にでてくるようになり、一旦そうなるとデータを参照できなくなってしまいます。

###エラーメッセージ

前述の#REFのエラーメッセージをクリックすると"IMPORT RANGE internal error"とのみ表示され、それ以上のことは分かりせん。

該当のソースコード

試したこと

*元シートの複製シートを別に作成し、新たなシートに再度データを引っ張ってくる。
*一旦スプレッドシートを閉じて、再度開いてみる。こうすると一時的に参照できるようになるが、すぐに同じERRORのメッセージでてくる。

Comments

[hiroshi0240]

入力しているIMPORTRANGE関数が複雑すぎる、参照が多すぎる等何らかの原因でサーバー側の計算にキャパを超える負荷がかかっているのではないかと推察します。具体的なIMPORTRANGE関数の中身を公開することはできますか？

[Utats]

ご回答ありがとうございます。中身を公開することはできませんが、おそらく仰る通りサーバーに負担がかかり過ぎていることががだと思います。Import range関数を使用するセルを減らすことによって症状が改善されました。

Answer 1

情報不足な所も数点あるのですが、いくらか考えられることがあるので書いておきます。

エラーが数分後に発生する問題

これは、シートを開いたときは、シート内のデータを読み取るため、
最初問題ない様に表示されているだけかもしれません。

ですが、実際には1つのシートしか取れていなかったり、
結構前の情報のままだったりするかもしれません。

なぜ数分後なのかは、シートを開いた後に各関数の処理が動いたとき、（最新の情報を持ってくる時）
問題が発生して、数分後のエラーとなっている可能性があります。

キャパオーバーの可能性

IMPORTRANGEは対象シートの最終行の情報をまでを空白含めて出力します。

例えばです
IMPORTRANGE("*********","'シート'!A1:F5")
という書き方の場合、30セルしかとりませんが

IMPORTRANGE("*********","'シート'!A:F")
の場合、仮にほかは空白でも"F500"のセルにデータが含まれると、
"A1:F500"の範囲の情報が出力されるため、
複数のシートからとってくる場合、
セルのキャパオーバーになる可能性はあるとおもいます。

ただし、対象シートが仮に1000行あったとしても、20行ぐらいしかデータがない場合は、
その分少なく出力されます。

また、これは憶測でもうしわけありませんが、そうして出力するセルが多いと、
セルの計算が、GASでいうところの6分の壁問題に遭遇して、内部エラーとなる可能性があります。

GASではそういう問題がありますが、まさかスプレッドシートのデフォルト関数にまで
影響が及ぶという情報は持っていませんので実際のところは不明です。

複数のシートの統合の仕方の問題

これは私の統合の仕方だとエラー文が違うので別問題かもしれませんが、複数のシートを1つのセルで統合しようとする場合、全く同じセル範囲をとらないとエラーとなります。

例えば
={IMPORTRANGE("","'シート3'!A1:F5");
IMPORTRANGE("","'シート3'!H1:L5")}

と、縦つなぎに統合する場合、
AFは６列に対してHLは5列のため、

ARRAY_LITERAL の配列リテラルで、1 つ以上の行の値が見つかりませんでした。

という問題があります。

逆に
={IMPORTRANGE("","'シート3'!A1:F5"),
IMPORTRANGE("","'シート3'!H1:L4")}

と、横つなぎに統合する場合、
今度は列数はあって違ってても大丈夫ですが、1～5行と　1～4行を統合しようとすると

関数 ARRAY_ROW のパラメータ 2 に一致しない行サイズがあります（5 となるべきところが 1 になっています）。

という問題がでてきます。

つなぎ方次第ですが、情報の入り方次第では列と行に差が生まれて、エラーとなる場合があるとおもいます。

代替案

例えば、その対象シートに新たに統合用のシートを作成し、
対象シートを1回だけIMPORTRANGEする手があります。

ファイルA（送りたい方）の統合用シートに
=index('データ元'!A:A)
を打ち込めば、簡単に別のシート情報を参照できますので、
まずはそれを利用して送りたい方の情報を1つにまとめて、

ファイルB（受け取りたい方）の情報受け入れ用シートに
=IMPORTRANGE("************","'統合用シート'!A:F")
で情報を1回のみ受け取りをし、

ファイルBの実際に使うシートに
=index('受取ったシート'!A:A)
という様に展開していく手があります。

この手が使えないとなると、もうあとはGASで起動時トリガーか、時間指定トリガーをつかって無理やりとってくる方法しかないかもしれません。

---

以上いろいろ可能性の話をさせていただきました。
どれか1つでもヒントになれば幸いです。