無料でスプレッドシート×GASでサジェスト・PAA・関連キーワードを自動収集する方法

公開日: 更新日: 投稿者: ブロガー夫婦：土志田（森）ゆりか：デザイナー
目次

いつもいつもキーワード探し、正直疲れていませんか？
今日紹介する“検索ネタ集めロボ”は、スプレッドシート×GASで、1語入れてボタンを押すだけ。サジェスト／PAA（他の人はこちらも質問）／関連キーワードを自動収集→重複除去→似たもの同士で箱分けまで一気にやってくれます。だから、見出しのタネが数分で揃うし、検索者の“生の疑問”にまっすぐ答えられるんです。

  • うれしいこと：見出し迷子が消える／網羅性が上がる／無料枠から始められる
  • できること：候補語の一括取得／重複整理／仮クラスタ化（＝H2/H3の下準備）

この記事でできるようになること

  • 「サジェスト」「PAA（他の人はこちらも質問）」「関連キーワード」を自動で集める
  • かぶってる言葉をまとめて、見出しのタネにする

用意したもの（全部無料）

  • Googleスプレッドシート（GoogleアカウントがあればOK）
  • SerpAPI（検索結果を取ってくるサービス）※電話番号認証あり
  • SerpAPI無料プラン：月 250回 まで（成功した検索のみカウント。キャッシュ結果は無料）。

※SerpAPI無料プランは月250回。成功した検索だけカウント／キャッシュは無料

ざっくり仕組み

  1. 種キーワード（例：味噌汁）をシートに入れる
  2. ボタンを押す
  3. ロボがGoogleを見に行って、
    • サジェスト（検索窓の候補）
    • PAA（=related_questions 検索結果の「質問」）
    • 関連キーワード（=related_searches ページ下のやつ）
      を集める → かぶりを消す → 似たもの同士でまとめる
  4. できあがった一覧（Clusters）を見て、見出し（H2/H3）を組む

手順（やったこと）

1) シートを作る

  • 新しいスプレッドシートを開く
  • シート名を6つ用意：Input / Suggest / PAA / Related / Merge / Clusters
  • Input のA列に、調べたい言葉を入れる（例：味噌汁）

2) ボタン（プログラム）を入れる

  • 上メニュー 拡張機能 → Apps Script を開く
  • 用意したスクリプトを貼り付けて保存（※コードは下記）
/** =========================================================
 *  キーワード収集BOT v3（安定版）
 *   - サジェスト：engine=google_autocomplete
 *   - PAA/関連：engine=google（1回で両方取得）
 *   - URLSearchParams不使用（GAS互換）
 *   - 日本語向けクラスタ改善
 *  ========================================================= */

// ▼必要なら直書き（推奨はメニュー「APIキーを設定」）
var SERPAPI_KEY_HARDCODED = ''; // 例: 'xxxxxxxxxxxxxxxxxxxxxxxx'

// ▼基本設定
var HL = 'ja';           // 言語
var GL = 'jp';           // 地域
var ENGINE_SEARCH = 'google';
var ENGINE_AUTOCOMPLETE = 'google_autocomplete';
var SLEEP_MS = 800;      // 無料枠向けレート調整
var SHEETS = ['Input','Suggest','PAA','Related','Merge','Clusters'];

/* ========== メニュー ========== */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('📊 Keyword BOT')
    .addItem('① 収集を実行', 'runKeywordResearch')
    .addItem('APIキーを設定', 'promptSetApiKey')
    .addItem('出力シート初期化', 'resetOutputSheets')
    .addToUi();
}

/* ========== APIキー設定 ========== */
function promptSetApiKey() {
  var ui = SpreadsheetApp.getUi();
  var res = ui.prompt('SerpAPIのAPIキーを入力してください', '', ui.ButtonSet.OK_CANCEL);
  if (res.getSelectedButton() === ui.Button.OK) {
    PropertiesService.getScriptProperties().setProperty('SERPAPI_KEY', res.getResponseText().trim());
    ui.alert('APIキーを保存しました！');
  }
}

/* ========== メイン ========== */
function runKeywordResearch() {
  var ss = SpreadsheetApp.getActive();

  ensureSheets(ss);
  var input   = ss.getSheetByName('Input');
  var suggest = ss.getSheetByName('Suggest');
  var paa     = ss.getSheetByName('PAA');
  var related = ss.getSheetByName('Related');

  // 出力系ヘッダを整える（内容は保持しない＝毎回クリア）
  resetWithHeader(suggest, ['seed','type','keyword','source']);
  resetWithHeader(paa,     ['seed','question','source']);
  resetWithHeader(related, ['seed','keyword','source']);

  var seeds = getSeeds(input);
  if (seeds.length === 0) throw new Error('InputシートA列に種キーワードを入れてね');

  for (var i=0; i<seeds.length; i++) {
    var seed = seeds[i];
    try {
      // サジェスト（専用エンジン）
      var s = fetchSuggest(seed);
      for (var a=0; a<s.length; a++) {
        suggest.appendRow([seed,'suggest',s[a],'SerpAPI Autocomplete']);
      }

      // PAA/関連（1回のsearchで両方）
      var sr = fetchSearchData(seed); // { paa:[], related:[] }
      for (var b=0; b<sr.paa.length; b++) {
        paa.appendRow([seed, sr.paa[b], 'SerpAPI Search']);
      }
      for (var c=0; c<sr.related.length; c++) {
        related.appendRow([seed, sr.related[c], 'SerpAPI Search']);
      }

    } catch (e) {
      logError(ss, seed, e);
    }
    Utilities.sleep(SLEEP_MS + i * 100);
  }

  buildMergeSheet(ss);
  buildClusters(ss);

  SpreadsheetApp.getActiveSpreadsheet()
    .toast('収集と整形が完了！', 'チーとんBOT');
}

/* ========== SerpAPI呼び出し ========== */
function serpApi(params) {
  var base = 'https://serpapi.com/search.json';
  var keyProp = PropertiesService.getScriptProperties().getProperty('SERPAPI_KEY');
  var apiKey = (keyProp && keyProp.trim()) || SERPAPI_KEY_HARDCODED;
  if (!apiKey) throw new Error('APIキー未設定：メニュー「APIキーを設定」or コード先頭に直書き');

  var q = toQuery(mergeObjects(params, { api_key: apiKey, hl: HL, gl: GL }));
  var lastErr = null;

  for (var i=0; i<3; i++) {
    var res = UrlFetchApp.fetch(base + '?' + q, { muteHttpExceptions: true });
    var code = res.getResponseCode();
    if (code === 200) {
      return JSON.parse(res.getContentText());
    }
    // 429/5xx はリトライ
    if (code === 429 || code >= 500) {
      lastErr = 'SerpAPI ' + code + ': ' + res.getContentText();
      Utilities.sleep(900 * (i + 1));
      continue;
    }
    throw new Error('SerpAPI ' + code + ': ' + res.getContentText());
  }
  throw new Error(lastErr || 'SerpAPIリトライ上限');
}

/* ========== 収集 ========== */
// サジェスト専用（Autocomplete）
function fetchSuggest(seed) {
  var data = serpApi({ engine: ENGINE_AUTOCOMPLETE, q: seed, client: 'chrome' });
  var out = [];
  if (data && data.suggestions) {
    for (var i=0; i<data.suggestions.length; i++) {
      var s = data.suggestions[i];
      var val = s.value || s.term || s.suggestion;
      if (val) out.push(String(val));
    }
  }
  return uniq(out);
}

// SearchからPAA/関連を一発取得
function fetchSearchData(seed) {
  var data = serpApi({ engine: ENGINE_SEARCH, q: seed });
  var paa = [];
  var rel = [];

  if (data && data.related_questions) {
    for (var i=0; i<data.related_questions.length; i++) {
      var rq = data.related_questions[i];
      if (rq && rq.question) paa.push(String(rq.question));
    }
  }
  if (data && data.related_searches) {
    for (var j=0; j<data.related_searches.length; j++) {
      var rs = data.related_searches[j];
      if (rs && rs.query) rel.push(String(rs.query));
    }
  }
  return { paa: uniq(paa), related: uniq(rel) };
}

/* ========== 整形（Merge / Clusters） ========== */
function buildMergeSheet(ss) {
  var merge = needSheet(ss, 'Merge');
  resetWithHeader(merge, ['keyword','from','seed']);

  var tables = [
    { sheet:'Suggest', keyCol:3, fromCol:2, seedCol:1 },
    { sheet:'PAA',     keyCol:2, fromCol:null, seedCol:1 },
    { sheet:'Related', keyCol:2, fromCol:null, seedCol:1 }
  ];

  var seen = {};
  for (var t=0; t<tables.length; t++) {
    var cfg = tables[t];
    var sh = ss.getSheetByName(cfg.sheet);
    var rows = sh.getDataRange().getValues(); rows.shift(); // ヘッダ行除去
    for (var i=0; i<rows.length; i++) {
      var r = rows[i];
      var kw = normalize(String(r[cfg.keyCol - 1] || ''));
      if (!kw) continue;
      var from = cfg.fromCol ? String(r[cfg.fromCol - 1]) : cfg.sheet;
      var seed = String(r[cfg.seedCol - 1] || '');
      if (!seen[kw.toLowerCase()]) {
        merge.appendRow([kw, from, seed]);
        seen[kw.toLowerCase()] = true;
      }
    }
  }
}

function buildClusters(ss) {
  var merge = ss.getSheetByName('Merge');
  var clusters = needSheet(ss, 'Clusters');
  resetWithHeader(clusters, ['cluster','keyword']);

  var vals = merge.getDataRange().getValues(); vals.shift();
  var map = {};
  for (var i=0; i<vals.length; i++) {
    var kw = String(vals[i][0] || '').trim();
    if (!kw) continue;
    // 日本語は空白が少ないため、先頭3文字でバケツ分け（英数は空白前トークン）
    var head = /[一-龠ぁ-ゔァ-ヴー々〆〤]/.test(kw.charAt(0)) ? kw.slice(0,3) : kw.split(/\s|　/)[0];
    if (!map[head]) map[head] = [];
    map[head].push(kw);
  }
  for (var h in map) {
    for (var j=0; j<map[h].length; j++) {
      clusters.appendRow([h, map[h][j]]);
    }
  }
}

/* ========== 出力初期化 & ログ ========== */
function resetOutputSheets() {
  var ss = SpreadsheetApp.getActive();
  ensureSheets(ss);
  resetWithHeader(ss.getSheetByName('Suggest'),  ['seed','type','keyword','source']);
  resetWithHeader(ss.getSheetByName('PAA'),      ['seed','question','source']);
  resetWithHeader(ss.getSheetByName('Related'),  ['seed','keyword','source']);
  resetWithHeader(ss.getSheetByName('Merge'),    ['keyword','from','seed']);
  resetWithHeader(ss.getSheetByName('Clusters'), ['cluster','keyword']);
  SpreadsheetApp.getActiveSpreadsheet().toast('出力シートを初期化しました', 'チーとんBOT');
}
function logError(ss, seed, err) {
  var sh = needSheet(ss, 'Log');
  if (sh.getLastRow() === 0) {
    sh.appendRow(['time','seed','message']);
  }
  sh.appendRow([new Date(), seed, String(err)]);
}

/* ========== ヘルパ ========== */
function ensureSheets(ss) {
  for (var i=0; i<SHEETS.length; i++) { needSheet(ss, SHEETS[i]); }
}
function needSheet(ss, name) {
  return ss.getSheetByName(name) || ss.insertSheet(name);
}
function resetWithHeader(sh, headers) {
  sh.clear();
  sh.getRange(1,1,1,headers.length).setValues([headers]);
  sh.setFrozenRows(1);
}
function getSeeds(sheet) {
  var last = sheet.getLastRow() || 1;
  var vals = sheet.getRange(1,1,last,1).getValues();
  var out = [];
  for (var i=0; i<vals.length; i++) {
    var v = String(vals[i][0] || '').trim();
    if (v && out.indexOf(v) === -1) out.push(v);
  }
  return out;
}
function uniq(arr) {
  var out = [], seen = {};
  for (var i=0; i<arr.length; i++) {
    var v = String(arr[i]).trim();
    if (!v) continue;
    var k = v.toLowerCase();
    if (!seen[k]) { out.push(v); seen[k] = true; }
  }
  return out;
}
function normalize(s) {
  // 最低限の正規化（前後空白/全角空白→半角空白）
  return String(s).replace(/\u3000/g,' ').trim();
}
function mergeObjects(a, b) {
  var o = {}, k;
  for (k in a) if (a.hasOwnProperty(k)) o[k] = a[k];
  for (k in b) if (b.hasOwnProperty(k)) o[k] = b[k];
  return o;
}
function toQuery(params) {
  var out = [], k, v;
  for (k in params) {
    v = params[k];
    if (v === undefined || v === null) continue;
    out.push(encodeURIComponent(k) + '=' + encodeURIComponent(String(v)));
  }
  return out.join('&');
}

3) SerpAPIの登録とAPIキー

  • 電話番号認証をする
    • 国を日本(+81)にして、先頭の0を抜く（例：090→90）
  • メールアドレス認証をする
  • SerpAPIにログイン → 無料プランを「購読する」を押す
  • 右上メニューからサインイン → Dashboard で API Key をコピー

4) APIキーをシートに設定

  • スプレッドシートに戻って
    📊 Keyword BOT →「APIキーを設定」 に貼り付け

5) 動かしてみる

  • Input に 味噌汁 を1つだけ入れる
  • 📊 Keyword BOT →「① 収集を実行」
  • しばらく待つと、Suggest / PAA / Related に言葉が入り、
    Merge に重複なしの一覧、Clusters に似たものグループができる

つまずきポイント＆解決

  • 「このアプリはGoogleで確認されていません」
    → 詳細を表示 → （安全ではないページ）に移動 → 自分のアカウントで許可
    （自分で作ったスクリプトなのでOK）
  • SerpAPIの電話番号でエラー
    → 国を Japan(+81) に変更 → 先頭の0を抜く（+81 90 … の形）
  • Dashboardが見つからない
    → まず 無料プランの「購読する」 を押して有効化 → 再読み込み
    → 右上のプロフィール → Dashboard

集まった言葉はどう使う？

  • PAA（質問）は、そのままQ&A見出しにすると強い
  • Clusters（似たものグループ）をH2、中の個別ワードをH3にすると設計が速い
  • 種キーワードは3〜5個ずつ回すと安定（無料枠の節約にも◎）

まとめ

  • 1語入れてボタン → 検索ネタが一気に集まる
  • 迷わず見出しが作れるようになった
  • 時間のかかる「下調べ」をロボに任せるのがコツ

上級者向けメモ（超ざっくり）

  • 使っているのは Google Apps Script（スプレッドシートを動かすプログラム）
  • 外のサービス（SerpAPI）にアクセスするので、APIキーを使う
  • 結果はJSONで返ってくる → 必要なところだけ抜いて、重複を消して整理

Chat GPTに「見出し化までやりたいから、完成H2/H3まで仕上げて」と言って、Clusters を貼れば、一気にH2/H3まで出してくれるので、あとは記事を書くだけになります！

To Page Top