はじめに
毎月全社員向けに実施している社内アンケートの運用を効率化するために実施している試みについて紹介します。
使用するもの
- GoogleForm：アンケートに利用します
- GoogleAppsAcript：定期的なアンケートの作成、結果の集計に使用します
- GoogleDataPortal：アンケート結果の表示に使用します
※ここで紹介するスクリプトはすべて clasp + typescript のローカル環境で作成されているのでご注意ください。
今回題材にするアンケート
ところどころ具体例があったほうが説明が楽なので以下のようなアンケートを例とします。
- 実施頻度・・・月に一回
- 内容・・・5段階数値の回答項目（全く当てはまらない～非常に当てはまる）が16件ある
- 集計方法・・・回答者毎にすべての項目の回答（1~5）の合計値を求める。求めた合計値の全回答者の平均を求める。
最終的には集計した値から以下のようなグラフを作成します。
ゴール
- 毎月末（25日）に来月分のアンケートが自動で作成される
- アンケート（フォーム）か誰かが回答を送信すると、集計用のシートに書き込まれる
- レポートは集計シートを参照しており、特に変更することなく収集済みの回答の次毎のデータを確認できる
ひな形から定期的にアンケートを作る
定期的にアンケートを行う場合は最初の作ったものをひな形として、実際にアンケートを行う際はひな形をコピーして使う運用になるかと思います（ひな形には回答を入力しない）。実施するたびにファイルのコピーなどを行う運用は煩雑なので自動化したいところです。
GASでgoogleFormを複製する
以下のようなスクリプトを実装します。
先に注意点を一つ、このフォームを複製するスクリプトをひな形のフォームのGASプロジェクトに実装しないことです。動作自体は問題ないのですが file.makeCopy() メソッドでフォームが複製された際に紐づいているGASプロジェクトも一緒に複製されてしまいます。結果として同名でIDが異なるプロジェクトが大量生産されてしまいます。
function makeForm() {
const templateFormFile = DriveApp.getFileById('{フォームのひな形のID}')
const currentFolder = templateFormFile.getParents().next()
const destinationFolderName = makeDestinationFolderName()
const destinationFolder = currentFolder.createFolder(destinationFolderName)
const name = templateFormFile.getName()
const formFile = templateFormFile.makeCopy(name, destinationFolder)
}
function makeDestinationFolderName() {
// 来月を YYYYMM 形式で返す
const date = new Date()
const yearStr = date.getFullYear()
const monthStr = (`0${2 + date.getMonth()}`).slice(-2)
return `${yearStr}${monthStr}`
}
makeForm()ではGoogleDrive上のひな形を置いてあるフォルダに、makeDestinationFolderName()で決定した名前のフォルダを作成し、その中にひな形のコピーを置いています。
ひな形のフォームはIDで指定する必要があります。フォームにこのスクリプトを実装できればFormApp.getActiveForm()が使えたのですが残念。ちなみに自分はこの手の設定をConfig用のSpreadsheetに書いておいてそこから値を取得するようにしています。
ひな形を置いてあるフォルダは file.getparents() で取得していますが、メソッド名からもわかる通り単一の Folder ではなく FolderIterator が返ってくることにご注意ください。これはGoogleDriveでは一つのファイルを複数のフォルダに配置できるためであると思います。今回はひな形は単一のフォルダに置かれていることを前提に、 next() しています。
file.makeCompy()メソッドは、作成する複製の名前や配置するフォルダを 指定する/しない によっていくつかオーバーロードがあります。今回は、スクリプトが実行された翌月の名前でフォルダを作成して元のファイルと同じ名前で複製を配置していますが、この辺りは運用の仕方によるかと思います。
トリガーで定期実行する
スクリプトをPushしたらひな形のフォームのスクリプトエディタを開き、トリガーの設定を行います。
月に一回であれば例えば以下の通りですね。
レポートを作成する
googleFormで収集した回答をグラフ化して表示するレポートを作成します。以下のような理由から GoogleDataPortal を使用します。
GoogleDataPortalのメリット
- 1回だけレポートを作成すればアンケート毎にグラフを作成する作業がなくなる
- 結果が一か所に集約されるので、毎回違う結果（のURLなど）を展開しなくて済む
なんかかっこいい
googleFormは回答をSpreadsheetに出力する機能がありますので、そのままspreadsheetでグラフを出力しても良いですが、例えば以下のグラフであれば毎月結果から回答者毎のスコアの合計を計算してグラフを作成する必要があり面倒です。
GoogleDataPortal は spreadsheet などのデータソースからグラフや表を含むレポートを作成できるのですが、レポート上にコントローラ（表示するデータを制御するUI）を配置することができます。なので、例のように毎月実施するアンケートであれば、とりあえず全てのデータを表示するグラフを作っておいて、コントローラで表示する月を選択するようにしておけば、毎月グラフを作成する必要がなくなります。
集計シートをデザインする
GoogleDataPortalを触ってみて思ったのですが非常にとっつきにくいです。当初のイメージではアンケートの回答結果をそのまま持つ以下のようなシートを作成しておいて、合計や平均といった集計はすべてGoogleDataPortal側に任せようと思ったのですがうまくできず挫折しました・・・・。
シートから読み取った値をもとにいろいろ計算したかったのですが 使用できる関数 ではうまく実現できず・・・ここはまだ勉強不足のところです。
とりあえずの対応策として、なるべく GoogleDataPortal 側でそのまま利用できる形でシートをデザインします。後述しますが、どうせこのシートにはアンケートの結果からGASを使ってデータを書き込むので、 GoogleDataPortal で頑張るよりGASで集計したり成形したりするほうが楽だと感じてしまいました。私のような GoogleDataPortal 初心者の方は自分が満足のいくレポートを作成するためにはどういうシートを用意すればよいか、先にダミーデータなどを使って決めたほうがよさそうです。
例えば以下のような集計済みのシートを作成しておけば楽できますね。
-
回答者別のスコアの合計のシート（棒グラフ用）
-
合計スコアの平均値のシート（折れ線グラフ用）
レポートにコントロールを配置する
spreadsheetからグラフを作成する基本的な流れは割愛して、コントロールを配置していきます。
コントローラの配置はDataPortalのメニューから簡単に行うことができます。今回は回答者毎のスコアの合計（棒グラフ）のデータを「調査年月日」で絞り込むために2つのコントローラを試してみました。
- プルダウンリスト
メニューから配置したら、右側のペインで以下の通り「期間のディメンジョン」と「コントロールフィールド」で参照先のシートのカラムである「調査年月日」を選択します。
これはこれで良いのですがデータがたまってくるとプルダウンの選択肢が増えて操作しずらくなってきます。
- スライダー
年と月を別々に選択できるスライダーがあればプルダウンより少ない手数で目的の月のデータを表示できそうです。参照先のシートに「年」と「月」の列を追加します。
そのうえでスライダーの「コントロールフィールド」を年、月にそれぞれ設定します。
これでアンケート結果を可視化するレポートができました。また、どのような集計シートを用意すればよいかも決まりました。
googleFormの回答を集計シートに出力する
集計シートのデザインが決まったらgoogleFormの結果を成形して集計シートに出力する処理を実装します。
集計の方法は大きく分けて2つあると思います。
googleFormに回答が送信されたら自動的にシートに出力する
フォームのトリガーを使用して誰かが回答を送信したらシートに書き込みます。
メリットは集計を完全に自動化できることです。アンケートを送信さえしてしまえばあとは勝手に回答が集計シートに溜まっていくので頃合いを見てレポート（data portal）を見に行くだけです。
デメリットは、まず回答者1人につき1回処理が実行されるため、全員の回答が集まってから実施するような集計処理を行うには一工夫必要な点があります。トリガーから実行する場合の処理は以下のような雰囲気です。
function onSubmit(event: GoogleAppsScript.Events.FormsOnFormSubmit) {
// イベントから回答を取得する
const itemResponses = event.response.getItemResponses()
const responseRows = itemResponses.map((response, index) => {
return [
index,
Number(response.getResponse())
]
})
//...以下、集計やSheetへの出力処理は省略
}
ポイントは、フォームのサブミットとトリガーにする場合GoogleAppsScript.Events.FormsOnFormSubmitなるものが渡されてきて、response.getItemResponses()で回答の集合を得ることができます。例ではすべて5段階の数値で回答するアンケートですので個々の回答をNumber(response.getResponse())で数字にCastしています。アンケートの項目の種類によって返ってくる値の型は異なります。
上の実装では「いつ」「誰が」の情報がなくなっていますが、これは集計の都合やアンケートが匿名か否かなどによって変わってくると思うので運用に合わせて値を決めて集計してください。フォームから送信された値を利用する場合は以下のようになると思います。
// ... 略 ...
const itemResponses = event.response.getItemResponses()
// 送信した人のe-mailを取得（収集する設定の場合）
itemResponses.getRespondentEmail()
// 送信時刻を取得
itemResponses.getTimestamp()
GASでTriggerを自動設定する
トリガーを使用して集計する場合には、GASでひな形から複製したフォームにトリガーを設定する必要があります。
最初に紹介したフォームを複製する処理に少し追加してトリガーも一緒に追加しましょう。
以下のコードはフォームのサブミット時に上記のonSubmit関数を実行するようなトリガーを作成しています。
ちなみに最初に非推奨としたフォームのGASプロジェクトでフォームを複製する方法だとなぜか設定したトリガーが実行時エラーになりました。
function makeForm() {
const templateFormFile = DriveApp.getFileById('{フォームのひな形のID}')
// ...略...
const formFile = templateFormFile.makeCopy(name, destinationFolder)
+ // フォームにトリガーを設定する
+ const formId = formFile.getId()
+ const form = FormApp.openById(formId)
+
+ // 先ほど実装したonSubmit関数を指定
+ ScriptApp.newTrigger('onSubmit')
+ .forForm(form)
+ .onFormSubmit()
+ .create()
}
回答が集まりきったところですべての結果をまとめて出力する
全ての回答者が集まったらgoogleFormの機能で結果をspreadsheetに出力し、そこからデータをまとめて集計シートに転記する方法です。
メリットは全員分の結果をまとめて扱うため全体の集計を作りやすいです。
デメリットは集計をしたいタイミングで集計対象のフォームを指定して集計処理をキックしないといけないことです。ただし、あらかじめ決まっている締切日までに集まったものだけ集計すればよい場合などはトリガーにして自動化できますね。
function makeFormSummary() {
const form = FormApp.openById('18GlGkbelzrvhjgPVRq5VxTGhPpC1illsOxJ2x5iwz20')
// フォームからこれまで送信された全ての回答を取得
const allResponses = form.getResponses()
const responseRows = []
allResponses.forEach(responses => {
// 匿名アンケートの場合は回答者毎に採番する
const userId = Math.floor(Math.random() * Math.floor(999999))
responses.getItemResponses().forEach((response, index) => {
responseRows.push(
[
responses.getTimestamp(),
userId,
index,
Number(response.getResponse())
]
)
})
})
//...以下、集計やSheetへの出力処理は省略
}
1件ずつ実施する場合との大きな違いは冒頭のform.getRessponses()だけです。全回答者の回答（allResponses）-> 一人の回答者のすべての項目（responses）-> 一つの項目（rensponse）という風に取り出しています。匿名アンケートの場合はdata portalで人ごとに集計するときのキーとしてランダムな数を採番しています。
まとめ
おさらいです。
- sheetやformをコピーするときは紐づいているGASプロジェクトも一緒にコピーされる点に注意する
- data portal から参照されるシートは無理に正規化せず、data portal側から使いやすいように設計する
- formから集計シートへの転記はサブミットごとに行うか後でまとめて実行するか決めておく
- トリガーを自動で設定すると運用の手間を少なくできる