QUERY関数で複数のIMPORTRANGEで読み込んだデータを動的に結合したい

Question

前提・実現したいこと

https://docs.google.com/spreadsheets/d/1GenADhlZfYTcGaEixjo5ruwGfvCaXw8DapjfIJUI3NU/edit?usp=sharing

上記のようなスプレッドシートを作成しており、シート「やっていること」で行っているように、

• 元データは別々のシートに保存されている
• そのシートから取得したデータを結合し、QUERY関数用いてフィルタ

ということを実施しています。現在は「やっていること」のA1セルにあるように、

=query(
{
 importrange("https://docs.google.com/spreadsheets/d/1GenADhlZfYTcGaEixjo5ruwGfvCaXw8DapjfIJUI3NU/edit#gid=810087653", "読売ジャイアンツ!A:H"); 
 importrange("https://docs.google.com/spreadsheets/d/1GenADhlZfYTcGaEixjo5ruwGfvCaXw8DapjfIJUI3NU/edit#gid=810087653", "横浜DeNAベイスターズ!A:H")}, 
"where Col6 = '右'", true)

とすることで、複数のシートからのデータをうまく結合できています。

ただし、今後複数のシート（この例でいえば別のチームのデータ）を追加した際に、上記のようにベタで書くのではなく動的にQUERYの引数に {IMPORTRANGE(...); IMPORTRANGE(...); IMPORTRANGE(...);} のように渡せるように、例えばチーム一覧とそのデータが記載されたURLを「チーム一覧」に書くようにして、動的にデータを結合したいのです。

実際にはスプレッドシートのURLのみがばらばらで、範囲セルの名称は固定でもOKです。

発生している問題・エラーメッセージ

インポートの内部エラーです。 と出て詳細は得られません

該当のソースコード

（省略）

試したこと

シート「試したがうまくいかない」のように

ARRAYFORMULA 式を用いようとしましたが、配列式というわけではないので実現できませんでした。

何かヒントでも良いので教えていただけると助かります。

補足情報（FW/ツールのバージョンなど）

GoogleAppsScriptを書けば実現はできそうですが、できるだけ標準関数の組み合わせで実現したいです。

Answer 1

これは残念ながら 標準関数では出来ません。

いい感じに「チーム一覧」の行分だけ、importrangeを繰り返してくれるような処理をしてくれるとありがたいのですが、文字列としては作れても関数として動いてくれません。

ひたすら式の中で

=Query({IMPORTRANGE('チーム一覧'!B2,'チーム一覧'!C2);IFERROR(IMPORTRANGE('チーム一覧'!B3,'チーム一覧'!C3),{"","","","","","","",""});IFERROR(IMPORTRANGE(... 

みたいにエラーだったらダミーの空配列を結合させる処理を繰り返す式を書いておけば動きはしますが、全然スマートじゃないのでお勧めしません。

自分なら

・データの書き方を変える
球団名をA列に書いたマスターデータのシートに全ての追加、変更を記載し 、球団毎のシートはマスターからQuery+importrangeで抽出するようにする。 → こうすると今回の抽出データにもチーム名が記載される。

・全部GASで処理する
チーム一覧と条件に基づいてgetValuesで取得したデータをfilterで絞りこんでから結合して書き出し

・Quryの参照先の部分だけGASで更新する
GAS実行すると Query式の参照範囲の部分だけ replaceでチーム一覧のデータ分だけimportrangeを繰り返す記述に書き換える。

あたりで対応するかなと。

Comments

[tetsunosuke]

ありがとうございます。
データの持ち方を考えてみます。

ちなみにあくまでもサンプルなので少なそうですが、百ファイル程度ありそうでして、GASだとタイムアウトしそうです。あと、別スプレッドシートのURLを拾うためのアクセス権の付与が必要になり厳しそうだったのです。

まあ上記はimportrangeでもハマるリスクは結構あるんですが...

他の方の回答があるかもしれないのでいったんベストアンサーは保留させてください。

・Queryの参照先の部分だけGASで更新する

ここがちょっと具体的なイメージができなかったのでヒントとして考えてみます

[tetsunosuke]

解決はできなかったのですが、ある程度無理なことがわかったので考え直す方向で進めます

[sawa]

コメントありがとうございます。importrangeは日によって読み込みが遅かったり、更新が不安定なこともあるので、百ファイルとなるとやはりお勧めできません。その量を結合してしまうとエラーになった時もどこで発生してるか見つけるのが困難そうですし。。
エリアや区分で10ファイルずつくらいを一度別々のシートでquery+importrangeで中間集計し、その中間集計したシートをさらに集計みたいな数珠繋ぎも考えられますが、こちらも更新が怪しかったりします。ご参考までに。