Google スプレッドシートに BigQuery のクエリ結果を転写したい（with GAS）

// 定数
// -- SQLクエリシート情報
const SQL_QUERY_SHEET_NAME = "SQLクエリ";
const TARGET_QUERY_RANGE = "A2:E1000";

// -- Slack監視用通知設定
const SLACK_CHANNEL_NOTIFIED = "#hoge_channel";
const SLACK_USERNAME = "BigQuery_データ連携シート";
const SLACK_ICON = "google_data_studio";
const SLACK_COMMENT = "死活監視用です。動いてるよー";
const SLACK_ERR_COMMENT = "エラーだよー！！";
const SLACK_WEBHOOK_URL = "https://hooks.slack.com/services/AAAAA/BBBBB/xxxxxxx";

// エントリーポイント
function main() {
  try {
    // 各種 基本設定
    const sqlSheet = getSheet(SQL_QUERY_SHEET_NAME); // クエリが記載されているシートを取得
    const rows = sqlSheet.getRange(TARGET_QUERY_RANGE).getValues();

    for (const rowIdx in rows) {
      const row = rows[rowIdx];

      const projectId = row[0]; // BigQueryのプロジェクトID
      const query = row[1]; // SQLクエリ
      const sheetName = row[2]; // 結果出力シート名
      const startRow = Number(row[3]); // BigQueryデータ転写 開始行
      const startColumn = Number(row[4]); // BigQueryデータ転写 開始列

      if (projectId === "") {
        break;
      }

      console.log(`running ${sheetName}...`);
      const dataRows = runBigQuery(projectId, query); // SQLクエリ実行
      const data = getDataFromQueryResult(dataRows); // クエリ結果の抽出

      const resultSheet = getSheet(sheetName); // 結果入力用シートの指定
      resultSheet
        .getRange(startRow, startColumn, data.length, data[0].length)
        .setValues(data);
    }
    // 動作確認用Slack通知
    notifySlack(SLACK_CHANNEL_NOTIFIED, SLACK_COMMENT);
  } catch (err) {
    console.log(err);
    // 動作確認用Slack通知
    notifySlack(SLACK_CHANNEL_NOTIFIED, SLACK_ERR_COMMENT);
  }
}

// ------------------------------------------------------------------------------------------

// 結果入力用シートの指定
function getSheet(sheetName) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(sheetName); // 結果を出力するシートを取得
  return sheet;
}

// SQLクエリ実行
function runBigQuery(projectId, query) {
  // Queryパラメータの設定
  const queryRequest = {
    query,
    useLegacySql: false
  };
  let queryResults = BigQuery.Jobs.query(queryRequest, projectId); // クエリ実行結果
  const jobId = queryResults.jobReference.jobId; // クエリ実行ジョブID

  // クエリ実行が完了したか定期的にチェック
  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // テーブルデータのページ分割に対応（次ページが存在する場合、再度クエリ実行し、続きのデータを取得）
  // URL: https://cloud.google.com/bigquery/docs/paging-results
  let dataRows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    dataRows = dataRows.concat(queryResults.rows); // データを統合
  }
  return dataRows;
}

// SQLクエリ結果の取得
function getDataFromQueryResult(rows, convertFn = v => v) {
  return rows.map((row, index) => {
    const cols = row.f;
    return [...cols.map(col => convertFn(col.v))];
  });
}

// Slack 通知
function notifySlack(channel, comment) {
  const payload = JSON.stringify({
    channel: channel,
    username: SLACK_USERNAME,
    icon_emoji: SLACK_ICON,
    text: comment
  });

  UrlFetchApp.fetch(SLACK_WEBHOOK_URL, {
    method: "post",
    contentType: "application/json",
    payload: payload
  });
}