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

サーチコンソールのデータをスプレッドシートに自動転送したいなと思い、仕組みを構築したので備忘録です。
スプレッドシートには、サーチコンソールと連携できる無料で使えるアドオンがあり、そちらを使ってもサーチコンソールのデータを引っ張ってこれます。
ただ、データの取得は毎回手動で実行する必要があり、若干面倒です。
BigQueryとGASを使うことで、スプレッドシートに自動的にサーチコンソールのデータを取得することができます。
さらに、BigQueryにはサーチコンソールのダッシュボードでは確認できない詳細なデータが入っているので、その点もメリットかと思います。
サーチコンソールのデータをBigQueryに一括エクスポートする
- BigQueryでプロジェクトを作成する
- サーチコンソールとBigQueryを連携する
- 24時間ほど待って、テーブルの作成を確認する
の手順が必要です。
別記事でやり方をまとめていますので、こちらを参考にしてください。
BigQueryのデータをスプレッドシートに取得する
appscript.jsonの更新
スプレッドシートの「拡張機能」→「App Script」より、GoogleAppScriptの編集画面を開きます。
今回はプロジェクト名を「BigQueryデータ取得」としています。
(※初期状態では「無題のプロジェクト」をとなっているので、わかりやすい名前に変更してください。)

サイドバーの「プロジェクトの設定」より、
「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など末尾に拡張子がつきます。)

最初に入っているコード()を削除し、
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);
});
}

デーブル名の取得と設定
BigQueryのダッシュボードを、プロジェクトIDをコピーします。
プロジェクトIDが表示されるので、コピーします。
通常、プロジェクト名+数字になっています。
コードのこちらの部分に貼り付けます。

たとえば、プロジェクトIDが「bigquery-123456」だった場合は
function updateSpreadsheetFromBigQuery() {
const projectId = 'bigquery-123456';
const datasetId = 'searchconsole';
const tableId = 'searchdata_url_impression';
となります。
この見出しの作業目的は、GASで使用するテーブルIDを取得するためです。
目的によっては状況によってデータベースIDなどが違う場合がありますが、以下の手順でテーブル詳細を表示すれば、GASからアクセス可能なデーブルIDを特定できます。

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

欲しいデータや条件は、必要に応じて調整します。
(ChatGPTにやりたいことを伝えると、調整してくれます)
出力用のシートを作成する
一度スプレッドシートに戻り、新たにシートを1つ作成します。
右クリックして、シート名を「GSC」に変更してください。

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

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

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

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

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

「スプレッドシート名(安全ではないページ)に移動」
をクリックします。
「すべて選択」にチェックを入れます。

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

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

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

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

- 「実行する関数を選択」にて「updateSpreadsheetFromBigQuery」を選択
- 「イベントのソースを選択」にて「時間手動型」を選択
- 「時間ベースのトリガーのタイプを選択」にて「日付ベースのタイマー」を選択
- 「時刻を選択」にて「午前0時〜1時」を選択
以上で設定は完了です。
トリガーの設定は、BigQueryのアクセス権限を持ったGoogleアカウントから設定して下さい。
トリガーを設定したアカウントからプロジェクトを実行する仕組みなので、権限を持たないアカウントがトリガー設定すると、途中で実行がとまってしまうためです。