Google Formsで収集したアンケートのデータをGoogle Spreadsheetでワイドデータからロングデータに変換する

Google Formsで得られたアンケート結果のデータを、集計のためにワイドデータからロングデータに変換する方法を紹介します。本稿では、Google Spreadsheetで使用可能な関数のみを用いて実現し、ほぼ処理時間なしでデータを変換できました。

#組織開発室

#Google Spreadsheet

#エンゲージメント

ハヂメ

2023.12.25

こんばんは。僕です。

はじめに

クラスメソッドでは、従業員エンゲージメントを測定するパルスサーベイを実施しており、サーベイ結果の分析レポートを各部署の組織改善責任者に展開しています。サーベイのアンケートは、Google Formsで作成しています。そして、アンケートの結果をLooker Studioで集計し、ダッシュボードを作成しています。Google Formsで得られるアンケート結果のデータは、ワイドデータ（いわゆる横持ちのデータ）となっています。様々な集計を行うためには、これがロングデータ（いわゆる縦持ちのデータ）となっていたほうが好都合です。

提供しているダッシュボードの改善について、データアナリティクス事業本部の社員による記事が公開されています。ぜひ、ご一読ください！

Google Spreadsheetに得られているワイドデータをロングデータに変換する方法としては、Google Apps Scriptを用いて変換するというものが考えられます。しかし、この方法を試してみると、特に回答者の人数が多い場合は処理に時間がかかり、正常終了したかどうかをログから把握しにくくなる懸念があります。

そこで今回は、Google Spreadsheetの関数を組み合わせて変換する方法を採用しました。

Google Spreadsheet上の関数でワイドデータをロングデータに変換する方法

ワイドデータというのは、カラム名にデータが含まれた状態になっている形式です。この横向きに並んだデータを、データ行として縦向きに並ぶよう転置する必要があります。

「ワイドデータ／ロングデータ」「横持ち／縦持ち」といったキーワードさえ心得ていれば、その変換方法に関する情報をウェブ上で見つけることができます（この記事の最後に参考文献として載せています）。画像の例のようなワイドデータを含むシート「シート1」があるとして、ロングデータに変換したものを別シート上に展開するには、以下のような関数を使います。

=ARRAYFORMULA(SPLIT(FLATTEN('シート1'!B2:B & "|" & 'シート1'!C1:L1 & "|" & 'シート1'!C2:L), "|"))

この関数を使うと、ロングデータの例として掲載している画像のA〜C列が出力されます。

この関数では、ARRAYFORMULAによって、以下のような流れの処理を実現しています。

ワイドデータの列見出しにある「設問」、データ行にある「メールアドレス」「回答（1〜5の数字）」をデリミタ（"|"）を挟む形で文字列結合する（&による処理）
結合された文字列を含む全てのセルをデータ行として展開する（FLATTENによる処理）
各行のセルに含まれる文字列をデリミタで分割し、カラムに展開する（SPLITによる処理）

関数を順に適用することで、処理の流れを追ってみます。

`&`による処理

=ARRAYFORMULA('シート1'!B2:B & "|" & 'シート1'!C1:L1 & "|" & 'シート1'!C2:L)

この関数を適用すると、以下の結果が得られます。

各セルの内容は以下のような形式になっています。

m0346001@example.com|設問1|4

ARRAYFORMULAと&の文字列結合でなぜこのようなことができるのかについては、別の記事にまとめてありますので参考にしてください。

`FLATTEN`による処理

=ARRAYFORMULA(FLATTEN('シート1'!B2:B & "|" & 'シート1'!C1:L1 & "|" & 'シート1'!C2:L))

FLATTENによって、全てのセルをデータ行として展開します。

`SPLIT`による処理

=ARRAYFORMULA(SPLIT(FLATTEN('シート1'!B2:B & "|" & 'シート1'!C1:L1 & "|" & 'シート1'!C2:L), "|"))

SPLITの処理を加えることで、初めに提示した関数と同じものになります。

文字列を結合するときに使用したデリミタで再度分割し、カラムごとに展開されるようにします。

タイムスタンプは、元のシートのデータからメールアドレスをキーにして引っ張ってくることができます。

D2のセルに以下のような関数を置くことで、タイムスタンプを各行に含められます。

=ARRAYFORMULA(IFERROR(TEXT(VLOOKUP(A2:A, QUERY('シート1'!A:B, "SELECT Col2, Col1"), 2, FALSE), "yyyy/MM/dd hh:mm:ss"), ""))

実際の運用について

初めからこのような変換用の関数を含んだシートを用意しておくことで、回答データのシートにGoogle Formsから回答が届くたび、ほぼ処理時間なしで変換後のデータを得ることができます。バッチ処理の必要がなく、データの運用が楽になるというメリットがあります。

また、実際の運用では、サーベイの結果を部署ごとに集計し、分析レポートを各部署に展開しています。部署ごとに集計するためには、回答データと回答者の部署情報を紐づける必要があります。回答者の部署情報とメールアドレスの紐づけられたデータをあらかじめ用意しておき、先ほど変換したデータと統合することで、回答データを部署ごとに集計できるようになります。

なお、分析レポートを各部署に展開する際、誰がどのような回答をしたのかについては知らされないようにしています。先ほどの変換結果にはメールアドレスが残りますが、部署情報と統合する際に別シートを用意し、そのシートにはメールアドレスの情報を含めないようにします。その上で、メールアドレスを含まないデータのシートをLooker Studioのデータソースとして使用することで、分析レポートを操作・閲覧する人には回答者個人の情報が開示されないようにしています。