Google Sheets：SERPチェッカーの実装

https://gyazo.com/462d143b5213fd4616193931dfe1a472

概要

要件

SEOに活用するために、ある検索キーワードで検索したときの順位を簡単に知りたい・残したい

あるキーワードでWeb検索した時の検索結果（SERP、順位リスト）を Google Sheet に記録していく

1クリックで検索結果を取得し、セルに入力するようにする

Web上には数々のSERPチェッカーがあるが、（GASの勉強も兼ねて）安価で手軽に実装できる手段で実現してみる

Google Sheets を使うメリット

デザインや共有が容易

操作方法など、使う人にとって新しい知識がほぼ必要ないので、導入が容易

他のAPIを使ったGASの実装に転用できる

GASがJSなので、Node.js や React で実装された自社サービスにロジック部分を組み込むことが容易

実際の動作

https://gyazo.com/30f2d87d9f07e5a26348bc600660a500

チェックボックスをON（TRUE）にすると、下記の情報が自動で入力される

検索結果

検索時のURL

デバイス（スマホ、PC）

仕様

使うサービス

無料で100回/月まで検索APIを呼び出せる

セルの構成

A行「見出し」の他は、「URL」と「タイトル」の2行で構成される

table:セルの構成

URL行 タイトル行

A B C

1 （空欄） （空欄）

2 施策 （空欄） （空欄）

3 参考URL （空欄） （空欄）

4 キーワード （空欄） ${入力：キーワード}

5 SERP取得 （空欄） チェックボックス

6 検索ワード （空欄） ${結果：実際の検索ワード} ※ 確認用

7 デバイス （空欄） ${結果：検索のデバイス}

8 検索URL （Link） ${結果：検索のURL} ※ 確認用

9 1 ${結果：1位のURL} ${結果：1位のタイトル}

10 2 ${結果：2位のURL} ${結果：2位のタイトル}

11 ... ...

実装・設定

A. SerpAPI から API キーの取得

2. 右上の「Register」からアカウントの作成し、ログイン

3. 「Your Private API Key」から、API Key を取得する

B. Google Sheets の設定

1. Spreadsheet の作成

2. 上記の「セルの構成」を参考に、A行に見出しを入れる

https://gyazo.com/143b23dc3078e2cb1e2d3a61559597be

3. C5セルにチェックボックスを入れる

https://gyazo.com/c4257f35a60858b7be1b54035ac2a483

4. チェックボックスのひとつ上のセルに、キーワード（例：GPT）を入力する

https://gyazo.com/b506d58d447bf50063c0272e7ebd1ff9

C. GAS の実装

1. Apps Script を開く

https://gyazo.com/70394d7337c285a7009b9a6a35c9675e

2. 下記のコードをエディタ部分に貼り付け

code:SERP.js

OPERAITON_ROW_NUM = 5;

SERP_1_ROW_NUM = 6;

API_KEY="YOUR SerpAPI KEY" // SerpApi: Google Search API から取得したキーを設定

RESULT_NUM=100

//function onEdit(e) {

function atEdit(e) {

if (!isTargetCol(e)) return;

const sheet = SpreadsheetApp.getActiveSheet();

const row = e.range.getRow();

const col = e.range.getColumn();

// シート名に「スマホ」を含んでいたらモバイル用検索にする

const device = sheet.getName().includes("スマホ") ? "mobile" : "desktop"

// 検索結果を入力

{

// 操作セルの1つ上のセルをキーワードとする

keyword = sheet.getRange(row-1, col).getValue();

cellvs = [

]

cellvs = cellvs.concat(fetchSERP(keyword, device))

range.setValues(cellvs);

}

}

function fetchSERP(keyword, device) {

res = fetchJSON(url);

ors = res.organic_results

cellvs = []

ors.forEach( or => {

cellvs.push([

or.link,

or.title + " " + or.link // ドメイン名をベースとした条件付き書式を使いたいために、後ろにURLをつける

])

})

return cellvs;

}

/**

* アクションするセルかどうか

*/

function isTargetCol(e) {

if (e==null) return false;

// 値が削除されたときはvalueが undefになるので無視

if (!e.range.getValue()) return false;

// 行が違うとき

if (e.range.getRow() != OPERAITON_ROW_NUM) return false;

if(!e.value) return false;

return true;

}

/////////////////////////////////////////////////////

// Util

function fetchJSON(url) {

const options = {

'method': 'get',

'contentType': 'application/json',

'muteHttpExceptions': true

};

const response = UrlFetchApp.fetch(url, options);

const data = JSON.parse(response.getContentText());

return data;

}

3. 左にある「トリガー」をクリックし、トリガーの設定ページへ

https://gyazo.com/1437d36754c59aa2e50392626e43d9e7

4. トリガーを追加する

右下の「+ トリガーを追加」をクリックし、下記のように設定して保存

https://gyazo.com/9dc3c0e2b03c4dfd75444436d35c1dd9

table:トリガーを追加

項目 値 備考

実行する関数を選択 atEdit

イベントの種類を選択 編集時 「変更時」を選ばないように注意

エラー通知設定 （お好みで変更）

5. 保存を押すとパーミッションを求めるウィンドウが出るので、アカウントを選択し、許可する

https://gyazo.com/2f9dc36f6596d600c31fc8e6cf693ba4

自己責任でお願いします

完了すると、Google からセキュリティ通知のメールが届く

D. 動作確認

1. シートに戻り、チェックボックスをクリックすると、下に検索結果が入る

https://gyazo.com/af5987427113afc4ac33fc0e2d7cbe6f

2. お好みでデザインをカスタマイズしたり、横に並べてランク履歴を残せるようにしたり

https://gyazo.com/462d143b5213fd4616193931dfe1a472

罫線を書き、セル幅を整える

表示の固定

表示 > 固定 > 行8まで

表示 > 固定 > 列Aまで

表示形式 > ラッピング > 切り詰める

条件付き書式で、気になるURLにセル色

シートに「スマホ」を入れてモバイル用に

トラブルシューティング

上記に従い実装・設定したが、チェックボックスをクリックしても何も起きない

実行ログを参照する

Apps Script > 実行数 からログを参照できる

https://gyazo.com/66ea70cea74d8047c30a025761b66e92

原因

参考

参考

GAS