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

2021 10/11

2021.10.11 2022.01.16

URL Copied!

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

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

この記事でお応え出来るニッチニーズ

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

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

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

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

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

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

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

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記事書けてしますので、今回は他サイトの詳しい記事に説明はおまかせします。

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

=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関数を使えばよさそうです。

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

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

IMPORTXML関数の課題

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

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

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

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

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

STEP

指定したURLからデータを取得

STEP

取得したデータをパース(分解)

STEP

欲しいデータ(サジェスト)を縦に並べて表示

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

あわせて読みたい

Apps Scriptの事始め – とりあえず動かすこんにちは。Roidです。この記事では、Google Apps Script(以下、略してApps Scriptと呼びます。)について書かせていただきます。このブログがQUERY関数とはじめとした…

というわけで、早速コードです。
メインの処理は「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;
}

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

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

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

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

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であった場合のみ、キーワードサジェストの取得プログラムが稼働するように指定しています。

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