サーチコンソールのデータをBigQueryを使ってスプレッドシートに転送する方法

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

function myFunction() {
  
}

function updateSpreadsheetFromBigQuery() {
  const projectId = 'project-id';
  const datasetId = 'searchconsole';
  const tableId = 'searchdata_url_impression';

// フルテーブル名を変数で組み立てる
const tableName = `${projectId}.${datasetId}.${tableId}`;

    const query = `
    SELECT
      data_date,
      url,
      query,
      country,
      device,
      search_type,
      clicks,
      impressions,
      sum_position
    FROM
      \`${tableName}\`
    WHERE
      query IS NOT NULL
      AND query != ''
      AND country = 'jpn'
      AND search_type != 'IMAGE'
    ORDER BY
      data_date DESC
    LIMIT 1000
  `;

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

  // クエリを投げる
  const queryResults = BigQuery.Jobs.query(request, projectId);
  // Logger.log(JSON.stringify(queryResults));
  const jobId = queryResults.jobReference.jobId;

  
  // ★ジョブが完了するまで待機する処理を追加
  let sleepTimeMs = 500; // 最初500ms待つ
  let maxRetries = 10;   // 10回リトライ（最大5秒ぐらい待つ）

  let jobStatus;
  for (let i = 0; i < maxRetries; i++) {
    Utilities.sleep(sleepTimeMs);
    const jobInfo = BigQuery.Jobs.get(projectId, jobId, {location: 'asia-northeast1'});

    if (jobInfo.status.state === 'DONE') {
      jobStatus = jobInfo.status;
      break;
    }
    sleepTimeMs *= 2; // リトライごとに少し待つ時間を増やす
  }

  if (!jobStatus || jobStatus.errorResult) {
    throw new Error('BigQuery job failed or timed out.');
  }
  
  // ジョブが完了しているので、クエリ結果を取得
  const results = BigQuery.Jobs.getQueryResults(projectId, jobId, {location: 'asia-northeast1'});

  
  // スプレッドシートに書き出す
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('GSC');
  sheet.clear(); // 前回結果を消す

  const headers = results.schema.fields.map(field => field.name);
  sheet.appendRow(headers);

  results.rows.forEach(row => {
    const values = row.f.map(field => field.v);
    sheet.appendRow(values);
  });
}

function updateSpreadsheetFromBigQuery() {
  const projectId = 'bigquery-123456';
  const datasetId = 'searchconsole';
  const tableId = 'searchdata_url_impression';