📊

GASでBigQueryを叩く

2022/01/24に公開

GAS

BigQuery

tech

概要

GASからBigQueryを叩きたいことはありませんか？僕はあります

SQL文はGASコードに隠蔽したくない
- SpreadSheetに直接書きたい
出力結果をSpreadSheetに書き出して分析したい

をやります

試したこと

大筋

最初は以下の記事を参考にしていたが、

Not found: Dataset ******:`firestore_export was not found in location US

と

Cannot reference a standard SQL view in a legacy SQL query.

のエラーに引っかかった

解決

以下の記事に従って useLegacySql: false を追加した

実装

まずやる

左メニュー「サービス」から「BigQuery API」を有効にする
GASでBigQuery APIを設定する

諸々の設定は頑張る

SpreadSheet

こんな感じ
SpreadSheetの構成
A2 にGCPのプロジェクト名、 B2 にクエリ、C2 に書き出し先のsheet名

コード

bigQuery.gs

// https://tech.buysell-technologies.com/entry/2020/12/16/070000
function bigQuery() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const querySheet = spreadsheet.getSheetByName("query");
  const projectId =  querySheet.getRange(2, 1).getValue();
  const query =  querySheet.getRange(2, 2).getValue();
  const resultSheetName = querySheet.getRange(2, 3).getValue();
  const resultSheet = spreadsheet.getSheetByName(resultSheetName)

  console.log(query)
  const result = BigQuery.Jobs.query(
    {
      useLegacySql: false,
      query: query,
    },
    projectId
  );

  const rows = result.rows.map(row => {
    return row.f.map(cell => cell.v)
  })

  resultSheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
}

書き出し先のsheetに自動で全データが書き込まれる

概要

試したこと

大筋

解決

実装

まずやる

SpreadSheet

コード

Discussion