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

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

サーチコンソールのデータをスプレッドシートに自動転送したいなと思い、仕組みを構築したので備忘録です。
スプレッドシートには、サーチコンソールと連携できる無料で使えるアドオンがあり、そちらを使ってもサーチコンソールのデータを引っ張ってこれます。
ただ、データの取得は毎回手動で実行する必要があり、若干面倒です。

BigQueryとGASを使うことで、スプレッドシートに自動的にサーチコンソールのデータを取得することができます。
さらに、BigQueryにはサーチコンソールのダッシュボードでは確認できない詳細なデータが入っているので、その点もメリットかと思います。

サーチコンソールのデータをBigQueryに一括エクスポートする

  1. BigQueryでプロジェクトを作成する
  2. サーチコンソールとBigQueryを連携する
  3. 24時間ほど待って、テーブルの作成を確認する

の手順が必要です。

別記事でやり方をまとめていますので、こちらを参考にしてください。

BigQueryのデータをスプレッドシートに取得する

appscript.jsonの更新

スプレッドシートの「拡張機能」→「App Script」より、GoogleAppScriptの編集画面を開きます。

今回はプロジェクト名を「BigQueryデータ取得」としています。
(※初期状態では「無題のプロジェクト」をとなっているので、わかりやすい名前に変更してください。)

Screenshot

サイドバーの「プロジェクトの設定」より、
「appsscript.json」マニフェスト ファイルをエディタで表示する
にチェックを入れます。

エディタを開き、appsscript.jsonを選択します。

以下の内容を記載して、保存ボタンを押します。

{
  "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"
}

「BigQuery API サービス」の追加

サイドバーの「エディタ」→「サービス」→「+」をクリックします。

「BigQuery API」を選択し、「追加」をクリックします。
※既に追加済みの場合、「サービス」下にBigQueryの表示が出ます。

GASの作成

サイドバーの「エディタ」→ファイル横の「+」→「スクリプト」をクリックします。

「bigquery」など、わかりやすい名前をつけます。
(決定後、bigquery.gsなど末尾に拡張子がつきます。)

最初に入っているコード()を削除し、

javascript
function myFunction() {
  
}

以下のコードを貼り付けて、保存します。

javascript
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);
  });
}

デーブル名の取得と設定

BigQueryのダッシュボードを、プロジェクトIDをコピーします。

プロジェクトIDが表示されるので、コピーします。
通常、プロジェクト名+数字になっています。

コードのこちらの部分に貼り付けます。


たとえば、プロジェクトIDが「bigquery-123456」だった場合は

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

となります。

テーブルIDの取得方法

この見出しの作業目的は、GASで使用するテーブルIDを取得するためです。
目的によっては状況によってデータベースIDなどが違う場合がありますが、以下の手順でテーブル詳細を表示すれば、GASからアクセス可能なデーブルIDを特定できます。

クエリの設定

サンプルのコードでは、以下のようなクエリになっています。

欲しいデータや条件は、必要に応じて調整します。
(ChatGPTにやりたいことを伝えると、調整してくれます)

出力用のシートを作成する

一度スプレッドシートに戻り、新たにシートを1つ作成します。
右クリックして、シート名を「GSC」に変更してください。

こちらにサーチコンソールの出力データが書き込まれていきます。
※出力するシート名は、コードのこちらの部分から変更できます。

実行して、認証と出力テスト

GASを保存した後、「実行ボタン」から実行します。
こちらの画面が表示されるので、「権限を確認」をクリックします。

Googleアカウントを選択します。

オーナー権限がない場合など

この時、スプレッドシートのオーナー権限がない場合など、以下のようなページが表示されます。

「詳細」をクリックして、

「スプレッドシート名(安全ではないページ)に移動」
をクリックします。

「すべて選択」にチェックを入れます。

ページ下部の「続行」をクリックします。

スプレッドシートの「GSC」シートに、以下のようにデータが出力されていれば、成功です。

トリガーを設定して定期実行

左サイドバーの「トリガー」より、「トリガーを追加」をクリックします。

以下のような設定のトリガーを追加します。

  • 「実行する関数を選択」にて「updateSpreadsheetFromBigQuery」を選択
  • 「イベントのソースを選択」にて「時間手動型」を選択
  • 「時間ベースのトリガーのタイプを選択」にて「日付ベースのタイマー」を選択
  • 「時刻を選択」にて「午前0時〜1時」を選択

以上で設定は完了です。

トリガーの設定は、BigQueryのアクセス権限を持ったGoogleアカウントから設定して下さい。
トリガーを設定したアカウントからプロジェクトを実行する仕組みなので、権限を持たないアカウントがトリガー設定すると、途中で実行がとまってしまうためです。

あなたへのおすすめ