リライト記事をAI検索で検証したい。事前準備－記事マスタをBigQueryに連携する手順

顧客管理表（人が編集する）

  ↓ GASで毎朝自動転記

BigQuery連携用スプシ（必要列だけ整形）

  ↓ 外部テーブルとしてBigQuery参照

BigQuery（GA4・GSCとJOINして分析）

第1ステップ：顧客管理表 → BigQuery連携用スプシの自動転記

1-1. 顧客管理表の構造をClaudeに伝える

まずClaudeに「この管理表のどの列を、どういう名前で連携用スプシに転記したい」を伝える。今回の★様のケースだと、こんな対応にした。

F列: URL                  → url

O列: 公開日               → published_at

Q列: リライト1回目公開日   → rewritten_at_1

S列: リライト2回目公開日   → rewritten_at_2

U列: リライト3回目公開日   → rewritten_at_3

V列: アンケート反映        → survey_applied（"反映"の文字列があればtrue）

ポイントは2つ。

• リライトの公開日列（Q/S/U）の隣にあるドキュメントURL列（P/R/T）は除外する
• アンケート反映列は文字列なので、boolean判定（”反映”ならtrue）にしてBigQuery側で扱いやすくする

1-2. BigQuery連携用の新しいスプシを用意

新規スプシを1つ作る。今回は「【★様】記事マスタ_BigQuery連携用」という名前で作成。 シート名は「記事マスタ_BigQuery連携用」に変更しておく（GASでこの名前を指定するため）。

1行目にヘッダーだけ手で入れる。

url | published_at | rewritten_at_1 | rewritten_at_2 | rewritten_at_3 | survey_applied

1-3. 新しいスプシにGASを作成して連携させる

連携用スプシ側の「拡張機能 → Apps Script」を開いて、以下のコードを貼り付ける。 重要なポイントは、このGASは連携用スプシ側に置く（顧客管理表側ではなく）。

/コード

const SOURCE_SPREADSHEET_ID = '顧客管理表のスプシID';

const SOURCE_SHEET_NAME = '記事管理表';

const TARGET_SHEET_NAME = '記事マスタ_BigQuery連携用';

function syncArticleMaster() {

  const sourceSS = SpreadsheetApp.openById(SOURCE_SPREADSHEET_ID);

  const sourceSheet = sourceSS.getSheetByName(SOURCE_SHEET_NAME);

  const lastRow = sourceSheet.getLastRow();

  // F列〜V列を表示文字列のまま取得（getDisplayValuesがポイント）

  const data = sourceSheet.getRange(2, 6, lastRow - 1, 17).getDisplayValues();

  const output = data

    .filter(row => row[0])  // URLが空の行は除外

    .map(row => [

      row[0],                // url (F列 = index 0)

      formatDate(row[9]),    // published_at (O列 = index 9)

      formatDate(row[11]),   // rewritten_at_1 (Q列 = index 11)

      formatDate(row[13]),   // rewritten_at_2 (S列 = index 13)

      formatDate(row[15]),   // rewritten_at_3 (U列 = index 15)

      row[16] === '反映'      // survey_applied (V列 = index 16)

    ]);

  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(TARGET_SHEET_NAME);

  // 既存データクリア（書式も含めて）

  if (targetSheet.getLastRow() > 1) {

    targetSheet.getRange(2, 1, targetSheet.getLastRow() - 1, 6).clear();

  }

  // 新データ書き込み（テキスト形式を強制）

  if (output.length > 0) {

    const range = targetSheet.getRange(2, 1, output.length, 6);

    range.setNumberFormat('@');

    range.setValues(output);

  }

  Logger.log(`同期完了: ${output.length}件`);

}

function formatDate(value) {

  if (!value) return '';

  const str = String(value);

  // "2022年10月28日" 形式

  let match = str.match(/(\d{4})年(\d{1,2})月(\d{1,2})日/);

  if (match) {

    return `${match[1]}-${match[2].padStart(2, '0')}-${match[3].padStart(2, '0')}`;

  }

  // "2022/10/28" 形式

  match = str.match(/(\d{4})\/(\d{1,2})\/(\d{1,2})/);

  if (match) {

    return `${match[1]}-${match[2].padStart(2, '0')}-${match[3].padStart(2, '0')}`;

  }

  // "2022-10-28" 形式

  match = str.match(/^(\d{4})-(\d{1,2})-(\d{1,2})/);

  if (match) {

    return `${match[1]}-${match[2].padStart(2, '0')}-${match[3].padStart(2, '0')}`;

  }

  // "Fri Oct 28 2022 09:00:00 GMT+0900..." 形式（Date.toString()）も汎用パース

  const d = new Date(str);

  if (!isNaN(d.getTime())) {

    return Utilities.formatDate(d, 'Asia/Tokyo', 'yyyy-MM-dd');

  }

  return str;

}

保存して、関数選択で syncArticleMaster を選んで実行。初回は権限承認が必要。 ログに「同期完了: ○○件」と出れば成功。

このコードでハマったポイント

ここは時間取られた部分なので共有しておく。

最初に書いたコードでは、日付セルが「Fri Oct 28 2022 09:00:00 GMT+0900 (Japan Standard Time)」みたいなJavaScript Dateオブジェクトの文字列形式でスプシに書き込まれてしまった。 原因は2段階あって、

1. getValues() だと顧客管理表側のセル値がDate型で返ってくるが、instanceof Date チェックが効かないケースがある
2. 顧客管理表側のセル表示形式自体が、JST時刻のDate.toString()形式になっていた

対策は、getDisplayValues() で「シート上の表示文字列」をそのまま取ってきて、formatDate 関数で複数の形式（年月日／スラッシュ／ハイフン／英語）に対応する正規表現と、最後のフォールバックで new Date() で汎用パースする、という多段構えにした。

書き込み側も setNumberFormat(‘@’) でテキスト形式を強制しないと、Google Sheetsが「2022-10-28」を勝手に日付型として解釈し直してしまう。これも罠。

第2ステップ：連携用スプシ → BigQuery自動連携

2-1. BigQueryに外部テーブルを登録

GoogleスプレッドシートはBigQueryから「外部テーブル」として参照できる。これを使えば、スプシを更新するだけでBigQueryに反映される（テーブルへのインポート不要）。

BigQueryのクエリエディタで以下を実行する。

CREATE OR REPLACE EXTERNAL TABLE `プロジェクトID.データセット.article_master` (

  url STRING,

  published_at STRING,

  rewritten_at_1 STRING,

  rewritten_at_2 STRING,

  rewritten_at_3 STRING,

  survey_applied STRING

)

OPTIONS (

  format = 'GOOGLE_SHEETS',

  uris = ['連携用スプシのURL'],

  sheet_range = '記事マスタ_BigQuery連携用',

  skip_leading_rows = 1

);

実行したら確認用SELECT。

SELECT * FROM `プロジェクトID.データセット.article_master` LIMIT 10;

URLと日付とboolean文字列が並べばOK。

2-2. GASのトリガー設定（毎朝自動実行）

最後にGASを毎朝自動実行する設定をする。これで顧客管理表が更新されたら翌日にはBigQuery側にも反映される。

1. Apps Scriptエディタを開く
2. 左サイドバーの時計マーク「トリガー」をクリック
3. 右下の「＋ トリガーを追加」をクリック
4. 設定を入れる
   • 実行する関数: syncArticleMaster
   • 実行するデプロイ: Head
   • イベントのソース: 時間主導型
   • 時間ベースのトリガーのタイプ: 日付ベースのタイマー
   • 時刻: 午前6時〜7時（任意の早朝の時間帯）
5. 「保存」をクリック

トリガー一覧に syncArticleMaster の行が追加されていればOK。

完成した連携の動き

次のステップ

ここまでで「記事マスタ」がBigQuery上で扱える状態になった。 次にやるのは、これをGA4とJOINして「AI経由流入があったランディングページ」を抽出するSQLを書くこと。 そこから検索プロンプトを仮説化して、AI検索トラッキングに繋ぐ流れを作る。これは別記事で。

補足：なぜ間にスプシを挟むのか

直接BigQueryにテーブルを作って、GASからAPIで書き込む方法もある。 ただ、間にスプシを挟むメリットは、

• 編集者が万が一目視で確認したいときにすぐ見られる
• BigQueryの外部テーブルを使えばインポートが不要で、ストレージコストもかからない
• スプシの権限管理だけで済むのでシンプル

エンジニアじゃない人がデータ確認できる設計の方が長期的に運用が回る。 逆にいうと、SaaS化したり集計を高頻度で回したいならBigQueryに直接書き込みでもいい。今回のような月1〜週1の運用なら、スプシ経由で十分。