Googleフォームで集計した値をスプレッドシートで計算する

オンラインでアンケートや質問紙を収集するとき、集計した値を自動で計算してくれると便利ですよね。
オンラインでのアンケート収集ツールとして使い始めやすいものに、Googleフォームが挙げられます。
Googleフォームで収集した回答のデータは、自動でGoogleスプレッドシートにコピーされます。

私は最初は、フォームの作成時に自動生成されるスプレッドシート内で、フォームから転送されてくる値に演算を加えようとしました。
ところが、これがうまく行かない・・・・何故なのかは置いておいて、ここでは別の方法で、フォームから送られてくる値を勝手に計算してくれるようなシステムを作ることにします。

Googleフォームとスプレッドシートの作成

GoogleDrive上で、新たなフォームを作成します。


作成したフォームを開きます。
今回のフォームでは、「記述式」の回答記入欄を1つ作成し、数値を入力してもらうようにしました。
その後、「回答」タブに移動します。


右のアイコンから「スプレッドシートを作成」を押下、


スプレッドシートを適当な名前で作成します。


作成したスプレッドシート：デフォルト名「無題のフォーム（回答）」を開くと、質問項目「数値」の値は、B列の2行目以降にコピーされるようになっているようです。

新たにスプレッドシートを作成・既存のシートを参照する

GoogleDrive上で新たにスプレッドシートを作成します。


この新たに作成したスプレッドシートに「無題のフォーム（回答）」を連携させます。
ここでは、新しいほうのスプレッドシートの各セルで、関数importrange()で既存のスプレッドシート内のセルを参照します。


まず、既存のスプレッドシート「無題のフォーム（回答）」のリンクを取得します。右クリックでメニューから共有可能なリンクを取得します。


上図のようにこのスプレッドシートのリンクが見れますので、これをコピーしておきます。
スプレッドシートの識別子は、リンク「https://docs.google.com/spreadsheets/d/ *** /edit?usp=sharing」の「***」がそれに該当します。この識別子を関数の引数とします。


新しいスプレッドシートの先頭行セルに関数を入力します。
ここでは、arrayformula()関数内にimportrange()を入れ子にしています。
importrange()は、一番目の引数に参照したいスプレッドシート識別子を、第2引数に参照したいセルを指定します。
arrayformula()は、一つのセルに入力した数式だけで、以降の行にも同じ関数を適用することができます。
ここでは新しいスプレッドシートのセルA1にのみ数式を入力していますが、参照元のスプレッドシートの行にフォームから値がコピーされるごとに、新しいSシートの次の行にも値が反映されます。
上の図の例では元Sシートの行2〜10までの9つのセルの値までが反映されます。


数式を入力後、参照元Sシートへのアクセス許可を求められるので、許可します。


さて、元のフォームに回答を入力して試してみますと・・・・


新しいスプレッドシートの該当行に値が反映されました！
さらにフォームに値を入力していくと、ちゃんとそれらも次の行に反映されました。


新しいスプレッドシートの参照元数値に対して、その隣の列で演算をするよう式を入れてみます。
すると、今度はちゃんと、フォームの値を送信した直後に計算されるようになりました！！