はじめに
こんにちは、京セラコミュニケーションシステム 西田（@kccs_hiromi-nishida）です。
前回の記事が思わぬ反響をいただいてびっくりしています！
記事を読んでくださった皆様、ありがとうございます。
Googleフォームはアンケートなどで利用されたことがある方、多いのではないでしょうか。
今回はGoogleフォームのマルチアンサー項目のデータをグラフ化するとき、関数でどうにかする方法もありますが、今回はBigQueryを経由する方法もあるよ！という視点から記事を書いてみます。
この記事の対象者
- Googleフォームを利用している方
- Googleフォームのマルチアンサー項目データのグラフ化に困っている方
構成
構成を簡単に図示しました。
Googleフォームのデータは設定によりスプレッドシートに保存されますが、このスプレッドシートのデータをまずはBigQueryに外部テーブルとして連携します。
その後、コネクテッドシートでいい感じにデータを取ってこよう！というイメージです。
作成するアンケートフォームと問題点
今回はこのように複数選択が可能な設問をいれたアンケートフォームを作成しました。
Google Forms
このアンケートの回答をスプレッドシートで確認すると、このように1つのセルにカンマ区切りで複数の回答が格納されていることがわかります。
Google Sheets
こういうデータをたとえば選択肢別にカウントしグラフ化するのってちょっと面倒です。
「サスペンス,ホラー」と「コメディ,アニメ,ホラー,アクション,ヒューマンドラマ」はそれぞれ別のデータになってしまうので、このままだとホラー映画が好きな人だけをカウントすることはできません。
スプレッドシートの関数を使ってデータを分割して集計する方法もありますが、関数以外にも一度BigQueryにデータを連携し、データを整形して取得するという方法もあると思ったので、今回はその方法を紹介したいと思います。
BigQueryに連携してみよう
では、さっそくGoogleフォームの結果が保存されているスプレッドシートをBigQueryの外部連携テーブルとして作成します。
フォームの回答をスプレッドシートにリンクさせる
まず、作成したGoogleフォームを開き、回答を選択してください。
Google Forms
スプレッドシートにリンクを選択すると以下のポップアップが表示されますので、作成を選択します。これでフォームの回答がスプレッドシートにリンクされます。
Google Forms
BigQueryの外部連携テーブルを作成する
Google Cloudの利用開始がまだの方は、以下より利用登録を行うことができます。
［参照］公式サイト・ドキュメント
無料トライアルと無料枠
上記サイトの「無料で開始」ボタンから登録が可能
Google Cloud の無料プログラム
Google Cloudの無料プログラムの詳細を確認できます
BigQueryを開き、データセットを選択してテーブルを作成を選択します。
Google Cloud - BigQuery
以下の画像を参考に選択・入力し、テーブル作成を選択します。
Google Cloud - BigQuery
- ドライブのURI
先ほどフォームの回答をリンクしたスプレッドシートのURLを貼り付け
ただし、edit以下は削除しておく（以下のURLだとedit#gid=11111111部分は削除）
https://docs.google.com/spreadsheets/d/xxxxxx/edit#gid=11111111
- シート範囲
回答の範囲をシート名!左上のセル:右下のセル形式で指定
Google Sheets
-
テーブル名
任意のテーブル名を入力 -
スキーマ
今回はスプレッドシートの3項目を定義（genre,method,name）
無事、BigQueryに連携できました。
Google Cloud - BigQuery
コネクテッドシートで可視化
使用画像について
特別な記載のない限り、画像はGoogle Sheets画面をキャプチャしたものとなります
では、コネクテッドシートからBigQueryに接続して可視化を行っていきます。
スプレッドシートを開き、データ→データコネクタ→BigQueryに接続と選択します。
プロジェクトを選択します。
（先ほどスプレッドシートを連携したBigQueryを含むGCPプロジェクトを選択）
カスタムクエリを作成を選択します。
表示されたSQLエディターに以下のSQLを貼り付けます。
WITH
base AS (
SELECT
name,
SPLIT(genre) AS genre_ary,
SPLIT(method) AS method_ary
FROM
`プロジェクト名.データセット名.テーブル名` )
/*映画を見る方法を取得する場合は、genreをmethodに変更してください*/
SELECT
name,
REPLACE(genre, ' ', '') as genre
FROM
base,
UNNEST(genre_ary) AS genre
GROUP BY name,genre
1つめのSQLでgenreという項目の値を配列に変換しています。
SPLIT関数の区切り文字はデフォルトでカンマなので、SPLIT(genre)とすればカンマ区切りの文字列を配列化してくれます。
実行後のデータはこんな感じです。カンマ区切りだったものが、配列化されました。
Google Cloud - BigQuery
次に配列をテーブルの行に変換し、フラット化します。
その時に使う関数がUNNEST関数です。
※参照：UNNEST関数
エディター画面の右下の接続を選択し、実行結果を確認します。
このようにスプレッドシートに新たなシートが自動で追加され、SQLの実行結果が表示されています。
先ほどの配列だったデータが、それぞれ行として出力されました。
ここまでくればもうグラフ化は簡単！
ジャンル別にそれぞれの回答者数をグラフ化してみます。
先ほどの画面に、グラフというボタンがあるのでそれを選択してください。
挿入先は新しいシートを選択し、作成を選択します。
新しいシートにグラフが追加されるので、グラフの設定は以下の画像のようにしてください。
あとはグラフのタイトルや縦横軸のタイトルを設定すれば、このようにマルチアンサー項目でも回答者数別にグラフを作成することができました。
おわりに
いかがでしたか？
一度アンケート結果をBigQueryに連携し、SQLを用いてデータの整形を行ってグラフ化するという流れを紹介しました。
アンケートでマルチアンサー項目は使うことが結構あるかと思うので、この記事が皆さんの一助になれば嬉しいです！私もさっそくアンケートの集計をこれでやりたいと思います。
おしらせ
弊社X（旧：Twitter）では、Qiita投稿に関する情報や各種セミナー情報をお届けしております。情報収集や学びの場を求める皆さん！ぜひフォローしていただき、最新情報を手に入れてください