Googleフォームのマルチアンサー項目をグラフ化しよう

# はじめに

こんにちは、京セラコミュニケーションシステム　西田（@kccs_hiromi-nishida）です。

前回の記事が思わぬ反響をいただいてびっくりしています！
記事を読んでくださった皆様、ありがとうございます。

Googleフォームはアンケートなどで利用されたことがある方、多いのではないでしょうか。
今回はGoogleフォームのマルチアンサー項目のデータをグラフ化するとき、関数でどうにかする方法もありますが、今回はBigQueryを経由する方法もあるよ！という視点から記事を書いてみます。


## この記事の対象者

* Googleフォームを利用している方
* Googleフォームのマルチアンサー項目データのグラフ化に困っている方

## 構成

構成を簡単に図示しました。

![img01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/f194d7a1-b457-219b-2b38-4e947bf400b4.png)

Googleフォームのデータは設定によりスプレッドシートに保存されますが、このスプレッドシートのデータをまずはBigQueryに外部テーブルとして連携します。
その後、コネクテッドシートでいい感じにデータを取ってこよう！というイメージです。

## 作成するアンケートフォームと問題点

今回はこのように複数選択が可能な設問をいれたアンケートフォームを作成しました。

<img width="300" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/c1da122c-1f59-290c-e40c-1e551ad3d176.png">

<font color="DarkGray">_Google Forms_</font>

このアンケートの回答をスプレッドシートで確認すると、このように1つのセルにカンマ区切りで複数の回答が格納されていることがわかります。

<img width="600" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/83070014-3450-8a00-5435-377b882364e0.png">

<font color="DarkGray">_Google Sheets_</font>

こういうデータをたとえば選択肢別にカウントしグラフ化するのってちょっと面倒です。

「サスペンス,ホラー」と「コメディ,アニメ,ホラー,アクション,ヒューマンドラマ」はそれぞれ別のデータになってしまうので、このままだとホラー映画が好きな人だけをカウントすることはできません。

スプレッドシートの関数を使ってデータを分割して集計する方法もありますが、関数以外にも一度BigQueryにデータを連携し、データを整形して取得するという方法もあると思ったので、今回はその方法を紹介したいと思います。

## BigQueryに連携してみよう

では、さっそくGoogleフォームの結果が保存されているスプレッドシートをBigQueryの外部連携テーブルとして作成します。

### フォームの回答をスプレッドシートにリンクさせる

まず、作成したGoogleフォームを開き、`回答`を選択してください。

<img width="400" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/d2684d72-0a65-f528-9b25-a4c54021e039.png">

<font color="DarkGray">_Google Forms_</font>

`スプレッドシートにリンク`を選択すると以下のポップアップが表示されますので、`作成`を選択します。これでフォームの回答がスプレッドシートにリンクされます。

<img width="350" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/316b0385-e0ff-8dc7-fcf5-4a4e08cc4a83.png">

<font color="DarkGray">_Google Forms_</font>

### BigQueryの外部連携テーブルを作成する

:::note info
Google Cloudの利用開始がまだの方は、以下より利用登録を行うことができます。

［参照］公式サイト・ドキュメント
  [無料トライアルと無料枠](https://cloud.google.com/free)</br>
　上記サイトの「無料で開始」ボタンから登録が可能
  [Google Cloud の無料プログラム](https://cloud.google.com/free/docs/free-cloud-features)
　Google Cloudの無料プログラムの詳細を確認できます
:::

`BigQuery`を開き、データセットを選択して`テーブルを作成`を選択します。

<img width="500" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/f67ad763-7102-5502-c3c8-3129506e749a.png">

<font color="DarkGray">_Google Cloud - BigQuery_</font>

以下の画像を参考に選択・入力し、`テーブル作成`を選択します。

<img width="500" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/5dfd58ef-fac2-3d7c-2a99-98c7e8511483.png">
<img width="500" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/e8dec33d-915c-7f3c-6989-5113df10e859.png">

<font color="DarkGray">_Google Cloud - BigQuery_</font>


* ドライブのURI
先ほどフォームの回答をリンクしたスプレッドシートのURLを貼り付け
ただし、edit以下は削除しておく（以下のURLだと`edit#gid=11111111`部分は削除）
```
https://docs.google.com/spreadsheets/d/xxxxxx/edit#gid=11111111
```
* シート範囲
　回答の範囲を`シート名!左上のセル:右下のセル`形式で指定
 　<img width="500" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/aa942009-cc8a-1502-8a17-5adfd0a7e597.png">

<font color="DarkGray">_Google Sheets_</font>

* テーブル名
任意のテーブル名を入力

* スキーマ
今回はスプレッドシートの3項目を定義（genre,method,name）

無事、BigQueryに連携できました。

<img width="500" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/29bd6afe-c282-7bd6-451f-3cebf7de22fd.png">

<font color="DarkGray">_Google Cloud - BigQuery_</font>

## コネクテッドシートで可視化

:::note warn
使用画像について
特別な記載のない限り、画像はGoogle Sheets画面をキャプチャしたものとなります
:::

では、コネクテッドシートからBigQueryに接続して可視化を行っていきます。
スプレッドシートを開き、`データ`→`データコネクタ`→`BigQueryに接続`と選択します。
<img width="300" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/2c7ed5e4-cb88-2ade-2975-70a7ba64b9c2.png">

プロジェクトを選択します。
（先ほどスプレッドシートを連携したBigQueryを含むGCPプロジェクトを選択）
<img width="300" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/f3c874eb-477c-85ec-25c2-e8f87556dd86.png">

`カスタムクエリを作成`を選択します。
<img width="300" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/0bb1567e-bf59-46bb-8f32-9ea1b01908b5.png">

表示されたSQLエディターに以下の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)`とすればカンマ区切りの文字列を配列化してくれます。
実行後のデータはこんな感じです。カンマ区切りだったものが、配列化されました。

<img width="500" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/02a48067-1039-0500-ab74-97f262e55dac.png">

<font color="DarkGray">_Google Cloud - BigQuery_</font>

次に配列をテーブルの行に変換し、フラット化します。
その時に使う関数が`UNNEST`関数です。
※参照：[UNNEST関数](https://cloud.google.com/bigquery/docs/arrays?hl=ja#flattening_arrays "UNNEST")

エディター画面の右下の`接続`を選択し、実行結果を確認します。
<img width="400" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/384078c9-5495-764b-8215-3f67dada9eb9.png">

このようにスプレッドシートに新たなシートが自動で追加され、SQLの実行結果が表示されています。
先ほどの配列だったデータが、それぞれ行として出力されました。

<img width="400" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/41534e71-b21c-8da7-7b6d-15fbeecef1d6.png">

ここまでくればもうグラフ化は簡単！
ジャンル別にそれぞれの回答者数をグラフ化してみます。

先ほどの画面に、`グラフ`というボタンがあるのでそれを選択してください。
挿入先は`新しいシート`を選択し、`作成`を選択します。
<img width="200" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/fb5718db-fe9f-b987-7c8d-04fe623c570f.png">

新しいシートにグラフが追加されるので、グラフの設定は以下の画像のようにしてください。
<img width="200" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/2862cee3-1b15-851a-f4f6-fefda3b660ca.png">

あとはグラフのタイトルや縦横軸のタイトルを設定すれば、このようにマルチアンサー項目でも回答者数別にグラフを作成することができました。
<img width="400" alt="image" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2752159/1bc3a6fc-6bef-ce50-e730-f0b9fdb9baab.png">


## おわりに

いかがでしたか？
一度アンケート結果をBigQueryに連携し、SQLを用いてデータの整形を行ってグラフ化するという流れを紹介しました。
アンケートでマルチアンサー項目は使うことが結構あるかと思うので、この記事が皆さんの一助になれば嬉しいです！私もさっそくアンケートの集計をこれでやりたいと思います。

# おしらせ

弊社X（旧：Twitter）では、Qiita投稿に関する情報や各種セミナー情報をお届けしております。情報収集や学びの場を求める皆さん！ぜひフォローしていただき、最新情報を手に入れてください:grinning:

https://x.com/kccs_digital