Google Formの回答状況をスプレッドシート関数で一覧向けに整理する

本記事では、Google Spreadsheetに関数を書き込むことでGoogle Formの回答状況をノータイムで一覧できるようにする方法を紹介します。
本記事で想定しているのは、次のような状況で用いる方法です。

1. あらかじめ回答をお願いするメンバーが決まっている^[1]
2. 未回答の方にはリマインド・再度の回答依頼を行う
3. 回答受付開始後に時間を使いたくない。しかし事前準備の時間なら取れる^[2]。
4. 同一の回答者が複数回の回答送信をしても、必要なのは最新の回答のみである。または、1つの回答の中で整合性チェックを行いたい。

以下で紹介する内容で出来上がるものは次の性質をもちます：

• 最新の回答内容を自動的に一覧表へ反映します。^[3]
• 数式を使って回答・未回答を表示します（おまけ）
• Google Apps Scriptは使用しません。Googleスプレッドシートの関数のみで完結します。
• 当方の使用環境では50人から100人程度で利用可能でした。数が増えた場合に対応可能かどうかは（お手数ですが）ダミーデータ等を用意して各自で検証をお願いいたします。

Googleスプレッドシートに回答者を識別するためのデータを、その次にGoogle Formを用意する

• 回答を依頼する相手の一覧を用意し、各個人を判別するために使える材料用データを回答者の数だけ用意します^[4]。設問１つに収める必要はなく、複数の設問を組み合わせることで回答者を判別できるようにしても大丈夫です。
• Google Formの回答内容に、回答者が誰であるか判別できるような設問を用意します^[5]。このとき、Google Formの設問と選択肢を確定しておき、いつでも回答の受付開始が出来る状態にしておくと後が楽です。

さしあたり、話がしやすいので以下のようなセル配置をしたことにして話を進めます。

シート「回答状況一覧」

	A	B	C	D	E	F
1	設問１の回答	設問２の回答	回答用URL	回答行の算出用セル	設問３の回答	設問４の回答
2	回答者１の判別用データ_1	回答者１の判別用データ_2	(空欄)	(空欄)	(空欄)	(空欄)
3	回答者２の判別用データ_1	回答者２の判別用データ_2	(空欄)	(空欄)	(空欄)	(空欄)

後でコピペ・貼り付け先のシートでセルを切り貼りしやすいようにシートを作ったり、列を非表示にしたり列のグループ化をしてその部分をたたむなり、別途印刷用のシートを用意するなど　お好みの工夫をしてください。

Googleスプレッドシートに数式を用意

フォームの回答が書き込まれていくシート

使うGoogleスプレッドシート関数

• arrayformula・・・他の式と組み合わせて使う。
• row・・・行番号を計算する。

シート「フォームの回答」

	A	B	C	D	E
1	設問１	設問２	設問３	設問４	(空行)
2

あとで回答内容を引用する際の目印に使いたいので、Google Formの回答が記載される列から離れた位置の1行目に=Arrayformula(row(任意の列))を記入します。困った場合は=arrayformula(row(A:A))としておけば大丈夫です。任意の設問の回答列の左側に数式列を入れないようにしてください。

フォームの回答を回答者ごとに一覧するためのシート

シート「回答状況一覧」

	A	B	C	D	E	F
1	設問１の回答	設問２の回答	回答用URL	回答行の算出用セル	設問３の回答	設問４の回答
2	回答者１の判別用データ_1	回答者１の判別用データ_2	(空欄)	(空欄)	(空欄)	(空欄)
3	回答者２の判別用データ_1	回答者２の判別用データ_2	(空欄)	(空欄)	(空欄)	(空欄)

ある回答者による最新の回答行を見つける

使うGoogleスプレッドシート関数

• maxifs・・・最新の回答内容を一覧表に反映したい場合はこの関数を使う。

各回答者の最新の回答は何行目か？（今回の例でいえばD列の内容）を計算で求めます（あとでIndex関数で目的の行から値を引用するのに使います）。
ひとまず、回答者１の最新の回答の行を求めます（ほかの回答者については、あとで式を他の行にコピペします）。

回答者ごとに最新の回答がシート「フォームの回答」の何行目にあるか？を求める数式は以下の通りです。

=maxifs('フォームの回答'!行番号を記入した列, 
        'フォームの回答'!回答者判別用データ１の列, 回答者１の判別用データ_1を記入したセル,
        'フォームの回答'!回答者判別用データ２の列, 回答者１の判別用データ_2を記入したセル
	)
// 見やすさのため改行した

列名の前にそれぞれ$を入れておくとコピペが楽です（記入例：$A:$A, $B2:$Bなど）
「（回答された）回答者の判別のためのデータ列」と「row関数で参照している列」が必ず同じ行から始まり、同じ行で終わっているようにしてください。
各回答者の判別用データを指定する場所(第3引数、第5引数など)では、セル参照(A1、B5など)を使うとセル内容の書き換えに強くなるのでお勧めです。

回答欄の内容の整合性チェックをしたい場合

もし回答欄の内容がそれぞれの回答内で整合しているかチェックし、整合していた回答のみを表示対象にしたい場合は、Arrayformula関数とif関数などを用いて

// 設問3は0以上　かつ　設問４は「はい」　かつ　設問５は「XL」ではない。
=arrayformula(
  if(('フォームの回答'!設問３の回答列 > 0) 
       *('フォームの回答'!設問４の回答列 = "はい"),
       *('フォームの回答'!設問５の回答列 <> "XL"),
     "OK", 
     "エラー")
 )
 //見やすさのため改行した

// 設問3は0以上である、
// 設問４が「はい」である、
// 設問５が「XL」ではない、
// ・・・の条件のうち、少なくとも１つ以上を満たしている。
=arrayformula(
  if(('フォームの回答'!設問３の回答列 > 0)
      +('フォームの回答'!設問４の回答列 = "はい")
      +('フォームの回答'!設問５の回答列 <> "XL"), 
     "OK", 
     "エラー")
 )
 //見やすさのため改行した

・・・という風に書くことができます。これをMaxifs関数の引数に含めるなり、この値を参照して「回答内容の表示用の列を設け、表示したい内容はそちらで指定する」など、お好きにカスタムしてください。

=arrayformula(if(
  'フォームの回答'!整合性を確認した列="OK", 
  'フォームの回答'!整合性を確認した列, 
  "エラー"
 ))
 //見やすさのため改行した

目的の列から、目的の行の回答内容を引用する

使うGoogleスプレッドシート関数

• index・・・回答内容を引用する。
  　indexで馴染みがなければlookupなどでも代替できます。

目的の行番号を(今回の例ではD列で)求めたので、E列やF列に=index('フォームの回答'!目的の列, 目的の回答者の行番号の入ったセル)と記入すると、目的の列・指定した行番号のセル内容が算出されます。
目的の列を指定する時には列全体(記入例：'フォームの回答'!D:D)を指定しておきます。^[6]

もし未回答の場合の見た目にこだわりたい場合は、
=iferror(index('フォームの回答'!目的の列, 目的の回答者の行番号の入ったセル), "")や
=if(not(isnumber(目的の回答者の行番号の入ったセル)), "", index('フォームの回答'!目的の列, 目的の回答者の行番号の入ったセル))で回避できます。
回答がもう来ているか？だけを見てわかる・フィルタ出来るようにしたい場合は=arrayformula(if(isnumber(目的の回答者の行番号の入る列), "回答済", "未回答"))という式が入った列を設けるなどして調整してください。

ここまでで回答者１の最新の回答を数式で算出することができますので、あとは式を他の行にもコピペしましょう。スプレッドシートで数式を入力する作業はこれで完了です。

回答依頼を送る

各回答者には自分用の回答先URLを送付し、そこから回答していただく手筈になります。よって、送付するURLを準備します。

Google Formで事前記入されたURLを生成する

Google Form右上から事前入力したURLを取得の作業をします。
各質問に対して「この回答はどの質問に対する質問か」が区別できるように回答を行ってから事前入力したURLを生成します。回答者を区別するための質問にはかならず回答してください（それ以外は空欄でも構いません）。
出来たURLはクリップボードにコピーします。お好みのテキストエディタ^[7]にコピーした内容を貼り付けてください。このURLから質問IDと回答の組を読み解いて、各回答者用の回答付URLを生成していきます。

// https://docs.google.com/forms/d/e/(各Google Formの回答用ID)/viewform?usp=pp_url&entry.なんかの数字=英数字記号の並ぶ文字列&entry.なんかの数字=英数字記号の並ぶ文字列
//　↑というURLは以下のように分解できる。
// フォームURL：　https://docs.google.com/forms/d/e/ほげほげ/viewform
// Googleが何かに使っているらしいキーワード: ?usp=pp_url
// 質問と回答のペア：　&entry.質問ID1=回答1のエンコード済文字列
// 質問と回答のペア：　&entry.質問ID2=回答2のエンコード済文字列

手っ取り早く探すには、質問ID近辺にある?を探してみるか、&を検索してみると区切りが見やすいと思います。

// エンコード済み文字列の内容を見ながら質問を思い出す場合は、
// ブラウザの開発者ツールを開いてコンソールを開く。
// (Google ChromeであればWindowsで「Control + Shift + J」, 
//  Google CHromeでMacなら「Command + Option + J」を押す。
// Microsoft Edgeなら「Ctrl + Shift + I」を押すと呼び出せる)

//　次のように入力してエンターを押すと中身が読める。
decodeURI("英数字記号の並ぶ文字列（回答1のエンコード済文字列）")

// または以下でもよい。
decodeURIComponent("英数字記号の並ぶ文字列（回答1のエンコード済文字列）")

参考:
decodeURI() - JavaScript | MDN
decodeURIComponent() - JavaScript | MDN

これを切り分けしてスプレッドシートのencodeURL関数とhyperlink関数などを使うと、次のような関数でリンクを貼ることができます。

// 1セルでいいなら
=concatenate(
    "https://docs.google.com/forms/d/e/（何か英数字の並び）/viewform?usp=pp_url", 
    "&entry.質問ID1=", encodeURL(回答1の内容が入ったセル), 
    "&entry.質問ID2=", encodeURL(回答2の内容が入ったセル)
  )
// *見やすさのため改行した

上の式をコピペしてもよいし、下の式を使ってもOKです。

// Arrayformulaを用いて、項目名の列に数式を入力する。
=Arrayformula(
      "https://docs.google.com/forms/d/e/ほげほげ/viewform?usp=pp_url" 
      & "&entry.質問ID1=" & encodeURL(回答1の内容が入った範囲)
      & "&entry.質問ID2=" & encodeURL(回答2の内容が入った範囲)
)
// *見やすさのため改行した

// 範囲についてはA2:Aのように範囲末尾を指定すると列の増減を自動で補完してくれる。
// Arrayformula関数を使うとConcatenate関数は動かないので、&演算子で文字列をつなぐ。
// なおConcat関数であればArrayformula関数内でも動く。

出来たURLは、あとで回答を依頼する時に回答者まで送付します。ハイパーリンクにしたい場合は、hyperlink関数を使ってもいいかもしれません。^[8]
とりあえずは上記の式を使い（今回の例ではC列に各回答者向けの回答用URLを用意し）ます。

シート「回答状況一覧」

	A	B	C	D	E	F
1	設問１の回答	設問２の回答	回答用URL	回答行の算出用セル	設問３の回答	設問４の回答
2	回答者１の判別用データ_1	回答者１の判別用データ_2	回答者１の回答用URL	(数式)	(数式)	(数式)
3	回答者２の判別用データ_1	回答者２の判別用データ_2	回答者２の回答用URL	(数式)	(数式)	(数式)

これで、各回答者に送る回答先URLを用意できました。
自分用のURLから回答をお願いします、という風にして、URLはメール本文へコピペ、Excelシートに貼り付けて添付など、お好みの方法で共有していただければと思います。

回答を待つ

送付後、見ているタイミングで回答が来ると計算結果が反映されると楽しい気持ちになります。
「今の回答状況は？」と聞かれたら、一覧表の画面を見ながらサクッと報告したり、一覧表をそのままエクスポート・スクリーンショット・コピー＆ペーストなどして送ったりできます。
あるいは最初からGoogleスプレッドシートの一覧表のURLを共有しておくなりすると「あの件、どうなった？」が無くなります。

Google Formの回答状況を調べるのに時間を使いたくない方はぜひご活用ください。もし、もっといい方法があったら（読みに行きますので）ぜひ記事に書いてください。

使用時の注意

• 本記事で紹介する方法では、使う関数の性質上フォームの回答が書き出されるシートの回答を「回答が古い順」以外でソートすると数式の結果が狂います。どうしてもソートしたいときはsort関数やquery関数で回答用シートの内容を一度ソートしたものに対してMaxifs関数を使ってください。
• 当方の使用環境では50人から100人程度で利用可能でした。数が増えた場合に対応可能かどうかは（お手数ですが）ダミーデータ等を用意して各自で検証をお願いいたします。

余談

Maxifs関数の登場で作成難易度が下がっていますね。公式ドキュメントを調べてよかった。
以前似た内容でGASを使いましたが、これならGASがなくても大丈夫そうな気がします。
似たようなことをしたい人のための記事が本記事の公開^[9]前に既に出ているかもしれませんが、その場合にはそちらが先出ということで張り合う気はありません。

本記事のツールを作った経緯

あるところに、IT活用とは少し距離のある会社がありました。
その会社では社内の各部署から報告を集めるため、社内メーリングリストで「これこれの事項について、メールで（報告を本文に書いて、あるいは添付のExcelファイルに記入の上返送という形で）報告してください」と業務連絡を流すことが通例でした。定期的に報告を行う必要があるため、担当部署では送られてきたメールを一つ一つ開封し、報告内容を読んでは記入、時にはファイルを開いてコピー・貼付、報告内容にモレがあれば確認のため連絡をとり、それらを一覧表にまとめて提出する業務がありました。
状況が更新されていくにつれて報告は増えていき、送られてきたメールは時に200を超え、作業ミスによる手戻りがあれば１時間単位で作業のし直しが必要になります。
そこで、担当者は考えました。「自動化してやる！」

という訳で、本記事の仕込みをした上で業務連絡メールの下書き（回答方法のインストラクション）を用意してファイル添付、依頼者の許可を得てそのメールを流しました。手作業の撲滅完了、無事に以前の運用はアナログ運用だった頃の昔話になりました（了）。

脚注

1. もし決まっていない場合であれば、表記揺れ対策をあらかじめした上でUnique関数を利用して回答者名簿を生成する等の方法も使えそうではあります。なお、思い付き程度で未検証です

2. 1-3までの条件のみであれば、以前にこちらの記事で方法が紹介されています。次の条件が不要であれば本記事を読む必要はありません

3. 各回答の内容がそれぞれ整合的かどうかの確認は、少し工夫すればある程度まで回答内容の表示に反映することができます。回答内容の遷移に対する整合性チェックは本記事ではカバーしません。

4. 本人も使用している番号（社員ID）などがあればそれを用いると楽です。人名、所属先など人が見てもわかるようなデータだと、作る側に優しく回答側にも「クリックするリンク先を誤ってないか確認できる」という意味で違和感がないです

5. 回答者が書き換えしてしまうと数式で最新の回答を反映できなくなるので、プルダウン・ラジオボタンなどの使用が安全です。もちろん一行テキスト欄を用いても動くには動きますが、回答者が内容を編集すると数式で回答を見つける段で見逃しが発生します（よってお勧めはしません）。Googleスプレッドシートで1人1行ずつになるように判別用のデータを書いて、それから当該範囲をコピーしてGoogle Formに貼り付けると選択肢が一気に増やせて手軽です

6. 先ほどの式で使ったrow関数の仕様上、一番上のセルが行番号1になります。そしてIndex関数は2番目の引数で1を指定すれば範囲内の一番上にある行、2なら2行目・・・という風に動くが故の指定です。

7. メモ帳でもWordでもGoogle Docsでも、文字が読めればなんでも構いません。読み解きに際して検索と改行が使えると楽です。

8. =hyperlink(concat(引数), "回答用リンク")または=arrayformula(hyperlink(式), "回答用リンク")のように使ってください

9. 初版公開は2023.04.21です