コネクテッドシートのカスタムクエリでテーブル名等をパラメータ設定したくなったら

こちらは株式会社ガラパゴス（有志） Advent Calendar 2025の7日目の記事です。

たまには過去に自分が執筆した記事のアクセス数でも調べてみようと思い確認したところ、最もアクセス数が多かったのはコネクテッドシート機能を用いてスプレッドシートにBigQueryのデータを表示する、というものでした。

https://zenn.dev/galapagos/articles/7f75aebe80ae58

2年前の記事ですが、今でも自分の記事の中で最もアクセス数が多く、皆さまがそれだけスプレッドシートやコネクテッドシートのことを愛している証左かと思います。

というわけでアクセス数を稼ぐためにも今回はコネクテッドシートのカスタムクエリを題材とします。

カスタムクエリでデータセット内のテーブルを眺める

コネクテッドシートはとても便利なやつで、BigQueryのデータを簡単にスプレッドシートに表示できます（詳細な手順は上述の記事を参照）。

コネクテッドシートの設定時に接続先のデータセットの一覧が表示されるので、この中のデータしか取得できないと思われがちです。しかしカスタムクエリでは、例えば以下のようなクエリも実行できます。

SELECT
  table_name,
  STRING_AGG(column_name) AS columns
FROM `<project_id>.<dataset>.INFORMATION_SCHEMA.COLUMNS`
GROUP BY table_name

こちらは特定のデータセット内にあるテーブルと、そのテーブルが持つカラムを一覧で取得するクエリです。こういうクエリにももちろん更新スケジュールを設定できるので、自動でテーブルの変更やカラムの追加等を追従でき、簡単なテーブル設計書の代わりになるかもしれません。

またカスタムクエリではパラメータを設定できます。
例えばカラム名の部分一致の条件をスプレッドシート内の特定のセルに入れておき、WHERE句でパラメータを含むように絞り込むことができます。わざわざカスタムクエリのSQLを見に行かなくても絞り込み条件を修正できるのは便利ですね。

SELECT
  table_name,
  column_name
FROM `<project_id>.<dataset>.INFORMATION_SCHEMA.COLUMNS`
WHERE column_name LIKE CONCAT('%', @COLUMN, '%')

カスタムクエリではFROM句のテーブル名等をパラメータ指定できない

しかし同じクエリで、データセット名をセルから参照することはできません。

以下のようにFROM句にパラメータを用いることはできないのです。

SELECT
  table_name,
  STRING_AGG(column_name) AS columns
FROM `@[e-mail]_SCHEMA.COLUMNS`
GROUP BY table_name

個人的には当初この挙動が気になって、カスタムクエリめ…！と思うことも多かったのですが、自分も含めて皆が大好きなカスタムクエリを憎むわけにもいかず、行き場のない憤りに苦しんでいました。しかしそれから月日が流れ自分も心身ともに丸くなると、カスタムクエリのありのままをようやく受け入れることができました。ではどうすべきでしょうか？
そう、何でもやってくれるスーパーマンこと、あいつの出番です。

GASでやればいいじゃん

かつてGASという文字を見るだけで頭や内臓がチクチクしたこともありましたが、今や生成AIのおかげもあって、GAS実装への抵抗がかなり減りました。

例えばこんなコードをGASで書いてみます。

function getAllTables() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("シート1");
  const project = sheet.getRange("C2").getValue();
  const dataset = sheet.getRange("C3").getValue();

  const query = `
    SELECT
      table_name,
      STRING_AGG(column_name) AS columns
    FROM \`${project}.${dataset}.INFORMATION_SCHEMA.COLUMNS\`
    GROUP BY table_name
  `;

  const request = {
    query: query,
    useLegacySql: false
  };

  const response = BigQuery.Jobs.query(request, project);
  const rows = response.rows || [];
  const values = rows.map(r => r.f.map(col => col.v));
  sheet.getRange(6, 2, values.length, values[0].length).setValues(values);

  return values;
}

なお実行にはBigQueryの権限が必要となるので、appscript.jsonの方も修正しておきます。

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "BigQuery",
      "serviceId": "bigquery",
      "version": "v2"
    }]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/bigquery",
    "https://www.googleapis.com/auth/spreadsheets"
  ]
}

こちらを実行すれば、セルで指定したデータセット内のテーブルをいとも簡単に取得することができました。

これがGASの力です。もちろんGASならトリガー設定で定期実行も可能ですし、前後に別の処理をかませることもできます（あまりにも重いデータは取れないけど）。

また他にもテーブルの一覧からデータを取得するのも簡単です。ちょうど先ほど取得したテーブルとカラムの一覧データがあるので、各テーブルのデータを少しずつ見てみましょう。こんなコードをGASで書けばOKです。

function getLimitedData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("シート1");
  const project = sheet.getRange("C2").getValue();
  const dataset = sheet.getRange("C3").getValue();

  const resultSheetName = "Result";
  let resultSheet = ss.getSheetByName(resultSheetName);
  if (!resultSheet) {
    resultSheet = ss.insertSheet(resultSheetName);
  } else {
    resultSheet.clear();
  }

  // 先ほど出力した table/columns を取得
  const values = sheet.getRange(6, 2, sheet.getLastRow() - 1, 2).getValues();
  let currentRow = 1

  values.forEach(([table, columns]) => {
    if (!table || !columns) return;
    const headerRow = ["データセット", "テーブル名"].concat(columns.split(","))
    const query = `
      SELECT
        '${dataset}' AS dataset,
        '${table}' AS table,
        ${columns}
      FROM \`${project}.${dataset}.${table}\`
      LIMIT 5
    `;

    const request = { query, useLegacySql: false };
    const response = BigQuery.Jobs.query(request, project);

    if (!response.rows) return;
    const rows = response.rows.map(row => row.f.map(col => col.v));

    resultSheet.getRange(currentRow, 1, 1, headerRow.length).setValues([headerRow]);
    currentRow++;
    if (rows.length > 0) {
      resultSheet.getRange(currentRow, 1, rows.length, rows[0].length).setValues(rows);
      currentRow += rows.length;
    }
  });
}

こちらを実行すると、こんな感じで、テーブルのカラム数が異なるデータも平気で取得してくれます。

上記2つの処理を統合すれば、データセット内の全テーブルのデータを一括でのぞき見できます。これはカスタムクエリではなし得なかった業ですね。
（注）上記のGASは全テーブルに対してSELECT *をしているようなものなので、クエリ料金にはご注意ください。実行前にクエリ対象のテーブルとカラムを確認し、不要なものを削除してから行うのをおすすめします。またパーティションフィルタを必須に設定しているテーブルがあるとエラーになります

まとめ

どうしてもカスタムクエリへの愛を捨てきれず、この境地に到達するまで2年もかかってしまいましたが、今ならはっきりと言えます。

カスタムクエリのFROM句にセルのデータを使いたいのなら、遠慮なくGASを使おう！