スプレッドシートで作る 簡易版キーワードサジェスト調査ツール

ラッコキーワード的なキーワードサジェストツールをスプレッドシートで作成してみた、という記事です。


キーワードサジェストツールを表計算シート上で使いたい、スプレッドシートで使うGoogle Apps Script(GAS)の学習例を見たい方のニーズにお応えできる記事となっています。(対象狭そうですが)

この記事でお応え出来るニッチニーズ
  • ブログ記事のネタ選びで使える無料のキーワードサジェストツールを探している
  • サイトの画面上でキーワード調査をするより、何となくスプレッドシート上で調査したい
  • キーワードの候補をスプレッドシートやExcelで管理しているので、結果をそのままコピペしたい
  • ラッコキーワードとか何となく使いたくない
  • ついでにGoogle Apps Script(GASの勉強もしてみたい)

上記のニッチニーズに対し本当にタイトルどおり、

キーワードサジェストツールをスプレッドシートで作ってみた

という記事ですので、ご興味のある方はお読みください。

また作る過程に興味はないので完成品だけ見たいという方は、スプレッドシートを共有公開していますので、よろしければ御覧ください。(事情により急に非公開にする可能性もあります)

Google Docs
検索サジェスト 簡易検索 サジェスト簡易検索 2022/10/24 サルート サジェスト結果,サジェスト結果の掛け合わせ サルート,サルート,サルート ブラ,サルート 意味,サルートン,サルートとは,サルート...
目次

スプレッドシートでキーワードサジェストを調べるための設計

スプレッドシートで実現しようとしたことは、以下の手順です。

STEP
検索したいキーワードを入力

A1のセルに「ブログ」と入力

STEP
入力したキーワードのサジェスト内容を表示する

A6セル以下に「ブログ」のキーワードサジェストを表示させる(2語の複合キーワード)

STEP
サジェスト内容を元キーワードとして、そのサジェスト内容を表示する

B6から横方向に順番にキーワードサジェストのサジェストを表示させる (3語の複合キーワード)

STEP
最初に入力したキーワードとひらがな1文字の掛け合わせのサジェスト内容を表示する

B18セルで選択した50音の「行」にもとづき、最初のキーワード + ひらがな1文字を入力した際のサジェストを表示させる(サジェスト以外の2語の複合キーワード)

上記のステップを実際に表現したシートが以下の通りです。

スプレッドシート版のキーワードサジェスト

実現方法[1] IMPORTXML関数を使う

スプレッドシート上で、キーワードサジェストを取得するための最もシンプルな方法は、スプレッドシートに組み込まれているIMPORTXML関数を使用するやり方です。

IMPORTXML関数は、指定したURLから取得したい情報の位置を指定し、その部分の情報をスプレッドシートに表示させることが出来る関数です。

関数の使い方は、

=IMPORTXML([URL] , [取得したい情報の場所])

となり、とてもシンプルな構造ではあるのですが、2番目の[取得したい情報の場所] の書き方が特殊なものとなります。

[取得したい情報の場所]は、「XPathクエリ」というルールで記載します。「XPathクエリ」については、かなり複雑なのでそれだけで1記事書けてしますので、今回は他サイトの詳しい記事に説明はおまかせします。

Qiita
クローラ作成に必須!XPATHの記法まとめ - Qiita 最近クローラーを作成する機会が多く、その時にXPathが改めて便利だと思ったので XPathについてまとめてみました! XPathを学ぶ方の役に立てれば幸いです。 初級編 XPa...

というわけで、ショートカットしてキーワードサジェストの取得出来る関数の書き方は以下の通りになります。

=IMPORTXML(“http://www.google.com/complete/search?hl=en&output=toolbar&q=[キーワード]”, “//suggestion/@data”)

実際にスプレッドシートに組み込む際は、他のセルで指定した値を[キーワード]として代入して、サジェストを取得してくるようにするのが現実的です。

=IMPORTXML(“http://www.google.com/complete/search?hl=en&output=toolbar&q=”&A1,”//suggestion/@data”)

この例ですと、A1に入力したキーワードのサジェスト内容が、A4セル以下に自動で表示されるようになります。

では最初にみた完成イメージをIMPORTXML関数で実現するには、

IMPORTXML関数の必要数
  • 最初のキーワードのサジェストを取得する : 1個
  • 10個のサジェストのサジェストを取得する:10個
  • 指定した50音行のサジェストを取得する:5個

で合計16個のIMPORTXML関数を使えばよさそうです。

上記の色のついたセルの下に、1つ1つIMPORTXML関数を入れていく

というわけで、これで解決!

、、、となれば良かったのですが、このIMPORTXML関数には課題があり、IMPORTXML関数だと上手く稼働しない事がわかりました。

IMPORTXML関数の課題
  • シート内での実行順番が指定出来ず、シートを開くと同時に処理が開始される(最初に記載したSTEP2とSTEP3がその順番どおりに実行される保証はない)。
  • 一度処理が滞ると、永遠に「Loading…」になって表示されない。(スプレッドシート自体をリロードすると再開され、処理が完遂する場合もある)

雑な言い方をすると、全く使い物にならん、という感じでしたので、別のやり方を検討しました。

実現方法[2] Google Apps Script(GAS)で処理する

関数処理が途中で滞るのであれば、関数は使わずにプログラムで処理すればいいじゃん、という代替案です。

IMPORTXML関数でやっていることは、

STEP
指定したURLからデータを取得
STEP
取得したデータをパース(分解)
STEP
欲しいデータ(サジェスト)を縦に並べて表示

という大変シンプルな処理だけですので、Google Apps Scriptであれば問題なく、実現出来る処理です。
※Apps Scriptと何?という方は、以下の記事もご参照ください。

というわけで、早速コードです。
メインの処理は「keywordSuggestions」で行っていますが、データ取得の部分を「getSuggestion」という関数にしています。

const basicURL = "http://www.google.com/complete/search?hl=en&output=toolbar&q=";

function keywordSuggestions() {
  //入力したキーワードを取得する
  let SS = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = SS.getSheetByName("サジェスト簡易検索");  
  let keyword = sheet.getRange(2,1).getValue();

  //キーワード出現回数をカウントするための連想配列
  let keywordCounter={};

  //最初のキーワードのサジェストを取得
  let suggestionList =[];  
  let eList = getSuggestion(keyword);
  suggestionList.push(eList);
  
  //出力
  sheet.getRange(5,1,10,11).clearContent();
  let oColumn = 0;
  suggestionList.forEach((sList) => {
    let outputRange = sheet.getRange(5,1+oColumn,sList.length,1).setValues(changeFormatt(sList));
    oColumn +=1;
  })
  
  //50音かけ合わせ
  let hiraganas = sheet.getRange(19,2,1,5).getValues();
  suggestionList =[];
  hiraganas[0].forEach((h) =>{
    suggestionList.push(getSuggestion(keyword + " " + h));
  })
  
  //50音掛け合わせの出力
  oColumn = 0;
  sheet.getRange(21,2,10,5).clearContent();
  suggestionList.forEach((sList) => {
    let outputRange = sheet.getRange(21,2+oColumn,sList.length,1);
    outputRange.setValues(changeFormatt(sList));
    oColumn +=1;
  })
}

//取得からパースまで
function getSuggestion(kw){
  let requestURL = basicURL + kw;
  let suggestXML = UrlFetchApp.fetch(requestURL).getContentText();
  let suggestions = XmlService.parse(suggestXML).getRootElement().getChildren("CompleteSuggestion");
  let list =[];
  suggestions.forEach(e => list.push(e.getChild("suggestion").getAttribute("data").getValue()));
  return list;
}

//縦横入れ替え用(.setValuesの仕様に合わせる)
function changeFormatt(list){
  let oList =[];
  list.forEach(l => oList.push([l]));
  return oList;
}

お恥ずかしながら、ひとまず動く、というコードなので、もしお使いになる場合はリファクタリングの上、お使いください。よく分からない、、、けど使いたいという方は、以下のスプレッドシートを公開しておくのでお使いください。

Google Docs
検索サジェスト 簡易検索 サジェスト簡易検索 2022/10/24 サルート サジェスト結果,サジェスト結果の掛け合わせ サルート,サルート,サルート ブラ,サルート 意味,サルートン,サルートとは,サルート...

便利に使うためのトリガー設定

より便利使うため、A2セルのキーワード、もしくはB18セルの50音行指定が変更されたら、サジェスト取得の処理が自動で開始されるという、トリガーの設定を行います。

GIFアニメなっているので、画像を拡大すると動きます

トリガーの設定を行う前に、トリガー時に実行されるコードを追加します。

function doByTrigger(){
  let mySheet = SpreadsheetApp.getActiveSheet();
  let myCell = mySheet.getActiveCell();

  if((myCell.getColumn()==1 && myCell.getRow()==2) || (myCell.getColumn()==2 && myCell.getRow()==18)){
    keywordSuggestions();
  }
}

このコードでは、変更されたセルがA2もしくはB18であった場合のみ、キーワードサジェストの取得プログラムが稼働するように指定しています。

次に実際のトリガーの設定です。
トリガーの設定が、トップメニューの「ツール」> 「スクリプトエディタ」から行います。

スクリプトエディタの開き方

スクリプトエディタの左端の「目覚まし時計」のアイコンをクリックし、右下の「トリガー追加」のボタンを押します。

押したら、以下のように設定を行い、最後に「保存」を押せば完了です。

トリガーの設定内容

これで、指定セルを編集したことをトリガーに、サジェストの取得処理が開始されるようになります。

まとめ

最初にも書いた通り、今回は、

スプレッドシートでキーワードサジェストの調査を出来るようにしたい

という極めてニッチなニーズにお応えする記事でした。(ブログ的な収益化要素ゼロ、、、w)

やり方は色々書きましたが、よければ以下のスプレッドシートは公開しておきますのでお使いください。シートの設定たコードを変える場合は、ご自分のアカウントにコピペしていただいても構いませんが、変更したことによって動作しなくなってもサポートは致しかねますので、予めご了承ください。

Google Docs
検索サジェスト 簡易検索 サジェスト簡易検索 2022/10/24 サルート サジェスト結果,サジェスト結果の掛け合わせ サルート,サルート,サルート ブラ,サルート 意味,サルートン,サルートとは,サルート...

使ってみての感想などあれば、問い合わせ欄か、Twitter(@QueryRoid)までメッセージいただけると大変励みになります。

スプレッドシート、Google Apps Scriptの記事まとめ

そもそもプログラミングに挑戦してみたい!という同世代(40代)の方は、こちらも御覧ください。

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
目次
閉じる