こんにちは！ atama plusでエンジニアをしているnaoshiです。
この記事では、Googleスプレッドシートのデータコネクタ機能を使ってBigQueryを叩く際、パラメータ機能で配列（IN 句）を使おうとしてハマったポイントと、その回避策 を紹介します。
同じ課題に遭遇した方の参考になれば幸いです。
はじめに
弊社では、プロダクトのデータをBigQueryに集約しています。
分析にはBIツールも導入していますが、簡易的なデータの可視化や、ビジネスサイドのメンバーが手軽にデータを扱いたい場面では、スプレッドシートを活用するケースもあります。
その際に活用しているのが、スプレッドシートの「データコネクタ」機能です。これを使うと、シートから直接BigQueryのデータを参照できます。
中でも便利なのが「パラメータ」です。 クエリ内の変数をスプレッドシートのセルに紐付けておくことで、SQLに詳しくない非エンジニアの方でもセルの値を書き換えるだけで抽出条件を変更できるようになります。
基本のおさらい：単一値のパラメータ
例として、以下のようなユーザテーブルがあるとします。
| user_id | name | birth_date |
|---|---|---|
| 1001 | アタマ太郎 | 1990-01-15 |
| 1002 | アタマ花子 | 1985-03-22 |
| ... | ... | ... |
単一のIDで絞り込む場合は、次のようにパラメータを使えます。
SELECT user_id, name, birth_date
FROM users
WHERE user_id = @USER_ID
スプレッドシート側で @USER_ID をセル（例: A2）と紐付ければ、A2に 1002 と入力するだけで該当ユーザを取得できます。
本題：パラメータで配列（IN句）を扱いたい
ここからがこの記事のメインです。
「複数のユーザ ID を IN 句で絞り込みたい」
というケースを考えます。
スプレッドシートのA列に対象IDを並べて、これを動的にクエリへ渡したい状況です。
実現したいクエリは以下のとおりです。
SELECT user_id, name, birth_date
FROM users
WHERE user_id IN (1002, 1004) -- ここを動的にしたい
❌ 最初に試した方法（失敗）
まず、TEXTJOIN を使って 1002,1004 のようなカンマ区切り文字列を作り、それをパラメータに渡してみました。
SELECT user_id, name, birth_date
FROM users
WHERE user_id IN (@USER_IDS)
しかし、データは取得できません。
なぜうまくいかないのか？
データコネクタのパラメータは 単一の文字列 としてBigQueryに渡されるようです。
そのためBigQueryは次のように解釈してしまいます。
WHERE user_id IN ('1002,1004')
つまり「1002,1004 というuser_idを持つ行」を探しに行ってしまうため、ヒットしません。
✅ 解決策：カンマ区切り文字列 & SPLIT + UNNEST
スプレッドシート側ではカンマ区切り文字列を渡し、クエリ側で配列に変換する アプローチを取るとうまくいきます。
Step 1: スプレッドシートでカンマ区切り文字列を作る
=TEXTJOIN(",", TRUE, A2:A)
これで 1002,1004 のような文字列を生成できます。
Step 2: クエリ側で SPLIT + UNNEST を使って配列化する
IDカラムが STRING型の場合
もし user_id が文字列型（STRING）であれば、話はシンプルです。
SELECT user_id, name, birth_date
FROM users
WHERE user_id IN UNNEST(SPLIT(@USER_IDS, ','))
-
SPLIT(@USER_IDS, ','): 文字列"1002,1004"を配列["1002", "1004"]に変換します。 -
UNNEST(...): 配列を行として展開し、IN句で使えるようにします。
IDカラムが INT64型の場合
user_id が数値型（INT64）の場合は、型を合わせるためにキャストが必要です。
SELECT user_id, name, birth_date
FROM users
WHERE user_id IN (
SELECT SAFE_CAST(id AS INT64)
FROM UNNEST(SPLIT(@USER_IDS, ',')) AS id
)
SPLIT の結果は文字列なので、SAFE_CAST で数値に変換します。
おまけ：TEXTJOIN 関数のススメ
今回の肝はスプレッドシート側の TEXTJOIN 関数です。
=TEXTJOIN(",", TRUE, A2:A)
JOIN 関数ではなく TEXTJOIN 関数を使うメリットは、「空のセルを無視できる（第2引数: TRUE）」 点です。
A2:A のように列全体を範囲指定しておけば、スプレッドシート側でユーザIDを追記するだけで、自動的にクエリの検索対象へ含まれるようになります。運用が非常に楽になるのでオススメです。
まとめ
BigQuery × スプレッドシート（データコネクタ）で配列パラメータを扱うには：
- ❌ NG: スプレッドシート側で
TEXTJOINを使い1001,1002を作り、それをそのまま使う - ✅ OK: スプレッドシート側で
TEXTJOINを使い1001,1002を作り、クエリ側でSPLIT+UNNESTする
この方法を使えば、スプレッドシートにIDを追記するだけで、柔軟に絞り込み条件を変更できます。
ぜひ試してみてください！
Discussion