こんにちは、おかちゃんせんせいです！
再びIMPORTRANGE関数で予期せぬエラーに遭遇したため、その要因と解決方法について備忘もかねて整理します。
【解決】Googleスプレッドシートで「配列結果は自動的に展開されませんでした」エラーを回避する方法
目次
どんな時に発生するエラー？
ネット上で「結果が大きすぎます」と表示される原因や対策はたくさんヒットします。
共通している原因として挙げられているのが、
参照するデータ数が非常に多い場合
です。
しかし、厄介なのが具体的にどのくらいのデータ（セル）数を参照するとエラーになるかは公開されていないため、線引きについてはまだ判明されていないようです。
（補足）
5000行・50万セルをIMPORTRANGEで読み込むと正常に動作しなくなると書いている記事もあります。
検証１：参照データ範囲を同じにする
ただ、私が検証した限りでは、
単純に参照したデータ数だけに依存していないこと
は確かです。
＜参照元セル範囲＞
スプレッドシートA：18万行×12列=216万セル
スプレッドシートB：18万行×12列=216万セル
※どちらも関数なし
※AとBの参照データはまったく同じデータ
＜参照先＞
スプレッドシートC：スプレッドシートAを参照⇒エラー
スプレッドシートD：スプレッドシートBを参照⇒正常に表示
つまり、参照元のデータ数が同じであってもエラーになる場合と、エラーにならない場合があるということ。
ということは、参照したデータ数以外が要因の可能性が濃厚です。
では、厳密にスプレッドシートＡとＢが同じかというと、そうではありません。
スプレッドシートＡの方には、別シートにも同じデータがバックアップとして入力されています。
一方でスプレッドシートＢには１つのシートしか使っていません。
そこで疑ったのは、スプレッドシートＡのシート全体はスプレッドシートの上限数である1,000万セルの半分近く使用していること。
それによって、スプレッドシートＡを参照しようとするとIMPORTRANGE関数が正常に動作しなくなるのではないか、と。
そうだとしたら、Ａよりはシート全体で表示されているセル数の少ないＢを参照するとエラーにならなかった理由が説明できます。
検証２：参照する列を変えてみる
あと、なぜ1,000万セルの半分にも満たないデータを参照してエラーになってしまった件ですが、
検証した結果では
表示形式が日付のものが他の表示形式のものと比べてデータが大きく扱われる
ということ。
たとえば、日付以外のものを複数参照してもエラーにならないのに、日付（時間）の列を一列だけIMPORTRANGE関数で参照しようとしたらエラーになりました。
最初は文字数かと考えましたが、文字列の方が文字数が多いケースがあるので、文字数ではないと思われます。
＜参照元セル＞
A〜C列：表示形式が「日付」以外
F列：表示形式が「日付」
＜エラーにならないケース＞
=QUERY({
IMPORTRANGE(スプレッドシートID,”all!A:C”)
},”select *”)
＜エラーになるケース＞
=QUERY({
IMPORTRANGE(スプレッドシートID,”all!F:F”)
},”select *”)
検証３：エラーを回避する方法
さらに検証した結果、日付のある列をQUERY関数を用いて縦に分割して参照したところ、エラーなく表示されました。
＜参照元セル＞
F列：表示形式が「日付」
＜エラーにならないケース＞
=QUERY({
IMPORTRANGE(スプレッドシートID,”all!F1:F10000″);
IMPORTRANGE(スプレッドシートID,”all!F10001:F20000″)
},”select *”)
＜エラーになるケース＞
=QUERY({
IMPORTRANGE(スプレッドシートID,”all!F:F”)
},”select *”)
ということは、以上の検証結果からやはり日付データがエラーの要因の一つになっていたということになります。
→分割したことで正常に動作したということは、エラーになるデータ（セル）があったわけではない。
日付データの方がデータ量が大きく認識されているかもしれません。
もちろん何も考えずに、複数列ある場合には列分割してQUERY関数で横に結合したり、今回のように行分解してQUERY関数で縦に結合したりするのも一つの手かもしれません。
しかし、もしそれでもエラーがなかなか回避できない場合は、日付列があるかどうか確認が必要のようです。
まさか日付であることが問題だと気付かず、確認に３時間以上使ってしまいました。
日付列をまったく参照していなくてもエラーになってしまう場合には、もっと細かく分割するか、データの構成を考え直した方がエラーと格闘する時間を最小限に留めることができるかもしれません。
・単純に参照するデータ数が非常に多い場合
・参照先のGoogleスプレッドシートの使用セル数
・参照列に「日付」データがある場合
まだまだGoogleスプレッドシートを理解しきれていないことが多いので、これからも何か躓くことがあって、検証して、解決したことができましたらシェアしますね。
最後まで読んでいただき、ありがとうございました！
↓ 今日も１日ポチッと ↓
↑ いつもボタンクリ応援・口コミ応援ありがとうございます ↑
