[Google Forms][Googleスプレッドシート][Form]google formでスプレッドシートとの連携でカスタマイズを行う方法

Google Forms
Googleスプレッドシートとの連携
必要なGoogleスプレッドシートの関数
IMPORTRANGEの使用法、最終行の取得
ARRAYFORMULAでの関数
サンクスページの編集
関連リンク

Google Forms

Google Forms: オンラインフォーム作成ツール | Google Workspace

Google Forms を使用して、さまざまな質問形式でオンラインフォームやアンケートを作成しましょう。どのデバイスでもリアルタイムで結果を分析できます。

ややマニアックな話題だけど。Google Formsは他のGoogleサービスとの連携もあり便利なのだが、カスタマイズでやや苦戦したので、備忘録として。

Googleスプレッドシートとの連携

アンケートフォームの作成自体は難しいことはない。肝となるのはスプレッドシートとの連携。今回のミッションとしては、アンケートフォームのデータ格納と、サンクスページでのレーダーチャートの表示。結果を後でメール送信するより、その場でパッと表示してあげたほうが良いだろうとのことで。アンケートフォームを作成するページの「回答」タブで結果を格納するスプレッドシートを指定できる。

このGoogleスプレッドシートに上からどんどん回答されたアンケート結果が格納されていく。今回は、Likertスケールで「全くあてはまらない」「ほとんどあてはまらない」「少しあてはまる」「ほぼあてはまる」「非常にあてはまる」と\(5\)段階でチェックボックスで選択する形にした。

これは日本語としてはわかりやすいのだが、Googleスプレッドシートに格納される情報としては、以下のように日本語になってしまう。

このままだとデータとして扱いにくく、数値に変化したいのだが、スプレッドシートにはどんどん新たな結果が増加していくので、数値で平均など計算する場合も更新していきたい。

必要なGoogleスプレッドシートの関数

はじめはIMPORTRANGE関数

IMPORTRANGE - Google ドキュメントエディタヘルプ

指定したスプレッドシートからセルの範囲を読み込みます。使用例 IMPORTRANGE("", "シート1!A1:C10") IMPORTRANGE(A2,"B2") 構文 IMPORTRANGE(スプレッドシートの URL, 範囲の文字列)

を利用しようとしたのだが、これが以外に使いにくい。以下のように、

=IMPORTRANGE("シートリンク", "フォームの回答 1!A2:A"& INDIRECT("フォームの回答 1!CA1"))

上一行だけ入力すれば指定セルまですべて延長して計算してくれる便利な関数だが、例えばこれを

SUBSTITUTE(IMPORTRANGE("指定スプレッドシートのURL", "シート1!A1:A10"), "全くあてはまらない", 1)

などとすると、指定セルまでの延長が効かなくなる。これが鬼門となり、色々試した結果、IMPORTRANGEではなくARRAFORMULAを利用することで解決できた。

ARRAYFORMULA - Google ドキュメントエディタヘルプ

配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。使用例 ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0))) ARRAYFORMULA(A1:C1+A2:C2) 構文 ARRAYFORMULA(配列数式) 配列数式 - 1 ...

ちなみに、シートのリンクは

https://docs.google.com/spreadsheets/d/

の後にある文字列のこと。

IMPORTRANGEの使用法、最終行の取得

ARRAYFORMULAの説明の前に、まずIMPORTRANGEについても述べる。これも使えることができればとても便利。Goggle Formでは次々とアンケート結果がスプレッドシートに格納されていくので、自動的に最終行を取得したい。この方法としては、「フォームの回答 1」のシート上で、結果の格納に干渉しない部分（第\(1\)行が良い）に、以下のように最終行を取得するセルを設定しておく。

コマンドは以下。

=MAX(IFERROR(MATCH(MAX(A:A)+1,A:A,1),0),IFERROR(MATCH("",A:A,-1),0))

このように最終行を取得できるようにしておいた上で、別シートを作成し、「ほぼあてはまる」などを数値変換する。

そして、一番上の行（第\(2\)行）に以下のコマンドを入力する。

=IMPORTRANGE("シートのリンク", "フォームの回答 1!B2:B"& INDIRECT("フォームの回答 1!CA1"))

こうすることで、一番上の行を入力するだけで、最新行まで自動的にfillしてくれる。

注意しなくてはいけないのは、この「変換シート」で第\(3\)行以降に何らかの値が入力されていると、「上書きができない」というエラーが出てしまうので、セルを空にしておく必要がある。

ARRAYFORMULAでの関数

上のようにIMPORTRANGEは便利なのであるが、SUBSTITUTEなどで置換を行ったり、特定の関数を用いるとオートフィルが働かない。そこでARRAYFORMULAを用いる。上記のような日本語のLikert尺度を数値化するコードはこちら。

=ARRAYFORMULA(IF('フォームの回答 1'!F2:F10003 = "全くあてはまらない" ,1, IF('フォームの回答 1'!F2:F10003 = "ほとんどあてはまらない", 2, IF('フォームの回答 1'!F2:F10003 = "少しあてはまる", 3, IF('フォームの回答 1'!F2:F10003 = "ほぼあてはまる", 4, IF('フォームの回答 1'!F2:F10003 = "非常にあてはまる", 5))))))

ここでは第\(2\)行から第\(10003\)行までを指定しているが、前述のように最新行までを指定することもできる。

このように数値化すれば、平均値を出したし解析を行いやすい。ただし、ARRAYFORMULAにも使えない関数がある。例えばIFの複数形IFSなどは指定できない。

サンクスページの編集

Google Formsのサンクスページ（アンケートやフォーム入力後の画面）はそっけない。その場でカスタマイズしたサンクスページを表示することも可能（Dev. Toolsで必要なform actionなどを取得）であるが、簡単に行うには「設定」→「プレゼンテーション」で回答後の表示文章にリンクを掲載してしまえば良い。ただし、html tagは使えない模様。このようにすることで、Google Formsの良さを保ったまま、遷移ページで自由に情報を提供することが可能。