スプレッドシートのquery関数を使って他のシートのデータを呼び出したときに、一番上の行に２行分のデータが入ってしまうというおかしな状況が発生することがあります。
このエラーの原因と対処法をまとめています。
エラーの詳細
query関数を使って次のようなクエリを作成。
=query(indirect('ダウンロード'!$D$15), 'ダウンロード'!$D$13)↓ 同じ
=query('商品マスタ'!$2:$15000, "select A,B,C,D,E,F,H,I,J,K,L,M,N,O,P,Q,R")selectの部分は選択したセルの値に応じて動的に変化するよう設定しています。
呼び出し元のデータは以下のようにキーが0001, 0002, 0003と並んでいます。
queryで引っ張り出した結果は以下のように、一番上の行に２行分のデータが入って呼び出されています。非常に困った状況です。
ちなみに、同じデータ範囲の中から抽出するカラムの数を減らすと正常に動作します。（数式は同じにも関わらず）
原因
このエラーの原因はquery関数の仕様で、見出し行の指定がない場合に、見出しを勝手に判断して呼び出すためです。（ドキュメントに記載してあります）
何気なく、query(データ範囲, クエリ) のように引数２つで使っていますが、これはプログラム側で見出しを自動で判定してくれていたわけです。
データ量が多くなった場合、プログラム側で見出しの特定が難しいことが原因となっているようです。
また、呼び出し先ではタイトル行はREGEXREPLACEを使って置換した別の個所を呼び出し、タイトル行を除いた２行目以下のデータ範囲を今回エラーが発生したqueryで抽出していたことも要因だと思います。
データ範囲のみに置いてあるデータは各列ごとに同様の規則性になっているためプログラム側でも見出し行の判断が難しいのでしょう。
- queryに見出し行の指定がない
- 類似した大量のデータを呼び出している
対処法
対処法はとても簡単です。query関数の第三引数で見出しとなる行を指定します。たったこれだけです。
query(データ, クエリ, 見出しとなる行)3行目は整数で指定します。※省略や「-1」とした場合はプログラムによる推定になります。
例えばタイトル行となるのが１行目のみの場合は「1」と記述します。
=query('商品マスタ'!$2:$15000, "select A,B,C", 1)
すると、想定したとおりに呼び出すことができました。
▼公式サンプル
タイトル行を複数にする
ちなみに、第三引数の見出しの数を3や4といった複数行分指定すると、指定した行のデータが１行目に入った状態で呼び出されます。
見出し行に3を指定したので、3行分がqueryで呼び出した１行目に入ります。
