太陽がまぶしかったから

C'etait a cause du soleil.

Googleスプレッドシートで急上昇キーワードと関連語の一覧を表示する

f:id:bulldra:20170831083930j:plain

Googleスプレッドシートでトレンドを知る

 「いま話題になっていること」を探すために検索エンジンの急上昇キーワードを取得して、関連語も表示するスプレッドシートを作成した。

 Googleスプレッドシートではセルの値をパラメータにして外部サービスと連携可能。Webに公開されているRSSやAPIをピタゴラスイッチのように組み合わせて自分なりのダッシュボードをDIYすることができる。

トレンドキーワードを取得

f:id:bulldra:20170831084245j:plain

 トレンドキーワードの取得にはGoogle提供のRSSが使えていたが、サービスが終了したため、Yahoo!提供のRSSで代用する。Googleスプレッドシートのセルに以下の計算式を入力して急上昇キーワードの一覧を表示。

=TRANSPOSE(IMPORTFEED("https://searchranking.yahoo.co.jp/rss/burst_ranking-rss.xml","items title",false,10))

 やっていることは以下の通り。

  • IMPORTFEEDで急上昇キーワードのRSSを10件取得
  • RSSから item title の一覧を取得
  • TRANSPOSEで縦に展開されるキーワードを横に転置

 下のセルに取得RSSのURLを変えて複数の観点から急上昇ワードを取得する。

トレンドキーワードの関連語を取得する

f:id:bulldra:20170831084333j:plain

 メニュー→データ→データの入力規則から、検索キーワードセルの入力規則を設定。「セルにプルダウンリストを表示」にチェックをいれることで取得した急上昇キーワードをプルダウンリストの選択肢にできる。プルダウンリストで選択したキーワードの関連語取得にはGoogleのサジェストキーワード取得APIを利用。

=IFERROR(IF($B$5<>"", IMPORTXML("http://www.google.com/complete/search?hl=en&output=toolbar&q=" & $B$5,"//CompleteSuggestion[position( )>=2]/suggestion/@data"), ""),"")

 やっていることは以下の通り。

  • 検索キーワードのセルが入力されていたら計算式を実行
  • IMPORTXMLで http://www.google.com/complete/search?hl=en&output=toolbar&q=検索キーワード の結果を取得
  • 1つ目の結果はキーワードそのものになるため除外
  • suggestion タグの data アトリビュートを取得

関連語の関連語を行列形式取得する

 取得した関連語一覧を参照して関連語を横に展開するセルも作成。

=IFERROR(IF($B8<>"", TRANSPOSE(IMPORTXML("http://www.google.com/complete/search?hl=en&output=toolbar&q=" & $B8,"//CompleteSuggestion[position( )>=2]/suggestion/@data")), ""),"")

 関連キーワード一覧にして掘り下げることで、ブログのネタにしたり、知らなかったトレンドを知ることできる。なお、Yahoo!やGoogleのAPIを何度も何度も更新していると提供が停止する可能性があるため、節度をもって利用してくだしあ。