GASからBigQuery のクエリを実行する

GASからBigQueryのクエリを実行して、スプレッドシートへのデータ書き出し機能をつくります。

やりたいこと

スプレッドシートにメニューを追加して、チームの誰でもワンクリックでBigQueryから最新のデータをから取得できる状態を目指します。BigQueryのWEB UIも便利ですが、広くチームで使用していく場合、メンバーが使い慣れたスプレッドシートでデータを確認できることは大事でしょう。ノー学習コスト！

「BigQuery API」を有効にする

スクリプトエディタから「BigQuery API」サービスを追加しましょう。サービスの隣の＋をクリックすると、サービス一覧が確認できます。

公式ドキュメントより解説

The BigQuery service allows you to use the Google BigQuery API in Apps Script. This API gives users the ability to manage their BigQuery projects, upload new data, and execute queries.
https://developers.google.com/apps-script/advanced/bigquery

BigQueryサービスを有効にすると、Google App ScriptからBigQuery APIが利用できるようになります。API経由で、BigQueryのプロジェクト管理、データの追加、クエリの実行ができます。

For detailed information on this service, see the reference documentation for the BigQuery API. Like all advanced services in Apps Script, the BigQuery service uses the same objects, methods, and parameters as the public API.
https://developers.google.com/apps-script/advanced/bigquery

BigQueryサービスで使用するオブジェクトやメソッド、パラメーターはpublic APIと同じなので、オプションなどは以下の公式ドキュメントを参照するとよいでしょう。

コード

//メニューを追加する
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('BigQuery');
  menu.addItem('シートを更新する', 'updateSheetFromBQ');
  menu.addToUi();
}

//クエリの結果を受け取り、シートを更新する
function updateSheetFromBQ() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  ss.toast('データを取得しています…', 'Status', 0);
  const query = 'SELECT * FROM `PROJECTID.DATASET.TABLE` LIMIT 100';
  const values = createBigQueryJob(query)

  ss.toast('シートを更新しています…', 'Status', 0);
  sheet.clear();
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  ss.toast('完了しました。', 'Status', 3);
}

//クエリを実行して、結果を二次元配列で返す
function createBigQueryJob(query) {
  const projectId = 'PROJECTID';
  console.log(query)

  let result = BigQuery.Jobs.query(
    {
      useLegacySql: false,
      query: query,
      timeoutMs: 20000,
      //location: 'asia-northeast1',
    },
    projectId
  );
  console.log(result.jobComplete)

  while (!result.jobComplete) {
    console.log('Waiting for job complete...');
    Utilities.sleep(1000)
    result = BigQuery.Jobs.getQueryResults(
      result.jobReference.projectId,
      result.jobReference.jobId,
      {
        location: result.jobReference.location
      }
    );
  }
  
  console.log(result)

  const rows = result.rows.map(row => {
    return row.f.map(cell => cell.v)
  })
  const headers = result.schema.fields.map(({ name }) => name);
  table = [headers, ...rows]
  return table
}

ポイント

標準のBigQueryテーブルだけでなく、スプレッドシートなどの外部データソースに対してクエリを投げたりしていると、実行結果が返ってくるまでにタイムアウトしてしまうので注意が必要です。
時間がかかりそうな処理をさせる場合は、タイムアウトまでの時間を長めに設定しておくか、jobs.getQueryResultsメソッドでクエリが完了するまで処理を継続させます。

Method: jobs.query

timeoutMs
Optional: Specifies the maximum amount of time, in milliseconds, that the client is willing to wait for the query to complete. By default, this limit is 10 seconds (10,000 milliseconds).
https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query

初期値は10000ミリ秒(=10秒)でタイムアウトします。timeoutMs: 20000にするなどして調整しましょう。

If the query is complete, the jobComplete field in the response is true. If the query has not yet completed, jobComplete is false.

クエリが完了したかどうは、result.jobCompleteから確認できます。完了していない場合は、result.jobCompleteはfalseを返します。

If jobComplete is false, you can continue to wait for the query to complete by calling the getQueryResults method until the jobComplete field in the getQueryResults response is true.

未完了のクエリに対しては、getQueryResultsメソッドを使って、レスポンスがTrueになるまで継続して待つことができます。

Method: jobs.getQueryResults

jobs.getQueryResultsメソッドでは、USやEUのmulti-regional location以外でジョブを実行する場合、必ずロケーションを指定しなければなりません。

The geographic location of the job. You must specify the location to run the job for the following scenarios:
If the location to run a job is not in the us or the eu multi-regional location
https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/getQueryResults

つまり、データセットのロケーションが、東京(asia-northeast1)の場合、ProjectIdとJobIdが合致していても、locationを指定しない限り、データが見つからないということになってしまうので注意が必要です。USの公式ドキュメントやStack Overflowなどから何気なくコードを持ってくると、こういうところでつまづきますね！

なおロケーションを指定しないと以下のようなエラーがでます。

GoogleJsonResponseException: API call to bigquery.jobs.getQueryResults failed with error: Not found: Job XXXXXXXXXXXXXXXXXXXX