GoogleスプレットシートのQUERY関数及びINDIRECT関数を活用する際に、表示行数を動的に指定する方法についての説明です。
#値のある行数だけQUERY関数/INDIRECT関数でデータ表示する方法
今回の想定
今回は、例としてGoogleフォームでデータでイベント参加者を募るケースを例として挙げます。
サンプルデータと、各種結果はこちらにて公開しておりますので、必要に応じてコピペして使ってください。
Googleフォームで取得した画像URLから、【数式①】Web上で画像を取得するURLを作成する数式と、【数式②】その数式をもとにセル上に画像を表示する数式を作成し、下の行へコピーしたとします。（数式の詳細は、過去の記事をご覧ください。）
= CONCATENATE(“http://drive.google.com/uc?export=view&”,QUERY(SPLIT(E2,“?”),“select Col2”))
= IMAGE(F2)
このように数式をコピーすると、参照元の値がな`れて意図しない空値が表示されてしまうなど弊害が起こる可能性があります。**
なので、正しい値が入っている行（期待する行数）だけに絞ったデータを扱う、シートを新しく作ります。その際に、QUERY関数とINDIRECT関数を使うのですが、参照元で値が適切に入っている行数を取得して、必要なデータだけ表示するようにします。
なお、画像表示を行うIMAGE関数をQUERY関数で取得すると画像が表示されない仕様になっているようなので、今回の例ではINDIRECT関数も併用していますが、IMAGE関数の扱いがなければ**QUERY関数だけで事足りる**と思います。
#INDIRECT関数で必要行数を表示
ということで、まずはINDIRECT関数を用いた画像の取得です。A1セルに以下の数式を入力しています。
= {INDIRECT(“‘作業シート’!G1:G”&COUNTA(‘作業シート’!A:A))}
｛ ｝を用いてコピーする配列を作成しているのですが、参照元の範囲指定にINDIRECT関数を活用しています。範囲指定には数式を使って、以下の文字列を作成しています。
‘作業シート’!G1:G10
参照元のシートからIMAGE関数で画像表示している列（今回の例だとG列目）に関して、回答者数の数を指定して範囲を決めています。この回答者数の数は増減する値ですが、COUNTA関数を用いて、Googleフォームが送られると強制的に追加される（＝空値にはならない）カラム（タイムスタンプ）の数を求めてきています。
#QUERY関数で必要行数を表示
次はQUERY関数ですが、B1セルに以下を入力しています。
= Query(‘作業シート’!A1:F50,“SELECT B,C,D,A,F LIMIT “&COUNTA(‘作業シート’!A:A)–1)
考え方は、INDIRECT関数のときと同じです。QUERY関数の範囲指定（第一引数）に関しては、参照元シート（今回だと”作業シート”）のA1:F50を指定しています。これは、対象となる値がA~F列に格納されているためです。また、対象とする行数は50行にしていますが、50件以上のデータが入る見込みがある場合は適宜余分を見込んで設定しなおしてみてください。
第２引数では、関数を使って以下のクエリを作成しています。
SELECT B,C,D,A,F LIMIT 9
“B,C,D,A,F”の部分は、表示したい順に参照元ファイルの列を指定してください。全部取得したい場合は”*”にしておけばOKです。その後は、クエリで取得する行数を求めていますが、こちらもINDIRECT関数のときと同じく、COUNTA関数を用いて回答者の数を取得してきています。ただ今回は、テーブル名が入っている1行目が余分にカウントされてしまうので、COUNTAの値から1を引いています。
ということで、GoogleスプレットシートのQUERY関数 / INDIRECT関数で表示行数を動的に指定する方法を説明しました。
値の入らない数式など予め省いておくことで、スプレットシートの値を外部サービスから参照する際に予期せぬノイズが入らなかったりと何かと便利だと思うのでおすすめです。