【GAS】googleformを提出したかわかる『提出状況確認シート』を作成しよう

googleformでアンケートや小テストなど作成します。
その後、googleformが提出されているか名簿と照らし合わせながら確認し、未提出の人がいたら催促していませんか？googleformを配布する度にチェックするのは正直めんどくさいです。でも、googleformを出しているか確認すべき時もあると思います。提出する本人が出しているのか把握していない場合もあるので。

そこで、googleformを提出したかがわかる『提出状況確認シート』をスプレッドシートとGASを使って作成しましたので、紹介します。

やりたいこと

①回答を収集しているスプレッドシートからメールアドレス・提出時間を取得し、『提出状況確認シート』に出力する
② ①の処理を定期的に実行する

完成イメージ・運用

①運営はgoogleformでアンケートを作成し、配布します。
②回答者はgoogleformでアンケートに回答します。
③運営はgoogleformの編集画面からアンケートの内容や提出状況を確認します。
④回答者は【提出状況確認シート】をみて回答しているか確認します。

この見取り図の「googleformアンケート」「googleform編集画面(回答確認画面)」は通常通り作成します。「spreadsheet回答集計シート」は「googleform編集画面(回答確認画面)」からボタン一つで作成できます。「spreadsheet【提出状況確認シート】」を作成し、「spreadsheet回答集計シート」から回答情報を自動で取得できるようにGASを記述します。

---

「googleformアンケート」はサンプルとして↓のようなものを作成しました。

「googleform編集画面(回答確認画面)」はこちら↓です。

「spreadsheet回答集計シート」はこちら↓です。

「spreadsheet【提出状況確認シート】」はこちら↓です。

作り方

「googleformアンケート」を作成しよう

「googleformアンケート」から紹介していきます。「googleform編集画面」から設問、回答方法などを設定していきます。（詳しくは割愛します。）

1点ポイントがあります。誰が提出したかを把握するために、「設定」→「メールアドレスを収集する」にチェックをいれましょう。アンケートに名前を記入してもらうことでも回答はわかりますが、今回はメールアドレスから誰が出したのか把握するようにします。

「spreadsheet回答集計シート」を作成しよう

次は「spreadsheet回答集計シート」を作成します。
「googleform編集画面」で「回答」→スプレッドシートを示す緑のアイコンを押下します。「回答先の選択」というモーダルが表示されますので、そのまま「作成」を押下します。これでスプレッドシート自体は完成です。googleformの回答情報は自動で、「spreadsheet回答集計シート」に反映されます。便利ですね。

スプレッドシートの「spreadsheet回答集計シート」ができたら、「シート名」を変更しましょう。GASでシート名が必要になるので忘れずに！今回は「フォームの回答」とシート名を付けました。

「spreadsheet回答集計シート」の情報を「spreadsheet【提出状況確認シート】」に連係します。ので、連係に必要なスプレッドシートキーも取得しておきましょう。
「スプレッドシートキー」とはスプレッドシートのURL↓の例でいう「〇」の部分です。この後、必要になりますので、コピーしておきましょう。

https://docs.google.com/spreadsheets/d/〇〇〇〇/edit#gid=0

「spreadsheet【提出状況確認シート】」を作成しよう

では、いよいよ「spreadsheet【提出状況確認シート】」を作成しましょう。
フォルダ構成は以下のようにしています。

スプレッドシートは通常通り作成します。
まず、ヘッダーとして↓を1行目に書きましょう。

セル	記入内容	備考
A列	通し番号	提出した順で番号をふります
B列	提出時間	タイムスタンプを連携します
C列	名前	メールアドレスを回答者名とします
D列	提出状況	提出済と表示されます

また、シート名を変更しましょう。今回は「提出状況確認シート」としました。

次に、GASを記述していきます。
「拡張機能」→「Apps Script」を押下します。（以前は「ツール」→「スクリプトエディタ」だったのですが、変更されたようです）

下のコードに「スプレッドシートキー」を加えて、スクショのように記述します。
保存マークを押して実行すると、処理が動きます。

confirmationSheet.gs

function myFunction() {

  //参照元のスプレッドシートを取得
　var referenceSourceSheet = SpreadsheetApp.openById("リクエストid");
  //参照先（本シート）のスプレッドシートを取得
  var referenceSheet = SpreadsheetApp.getActive();

  //参照元シートを取得
  var sheet = referenceSourceSheet.getSheetByName('フォームの回答');
  //参照先シートを取得
  var activeSheet = referenceSheet.getSheetByName('提出状況確認シート');

　//参照元シートからデータを取得
  var lastRow　= sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var answerList = [];
  answerList = sheet.getRange(2,1,lastRow-1,lastColumn).getValues();

  //No用数字
  var number = 0;

  //参照先シートにデータを設定する
  for(var i = 0 ;i < answerList.length ; i++){

    number = i + 1;
    //Noを設定
    activeSheet.getRange(i+2,1).setValue(number);
    //提出時間を設定
    activeSheet.getRange(i+2,2).setValue(answerList[i][0]);
    //メールアドレスを設定
    activeSheet.getRange(i+2,3).setValue(answerList[i][5]);
    //提出状況
    activeSheet.getRange(i+2,4).setValue("提出済");
  }

}

ここまでで、やりたいこと「①回答を収集しているスプレッドシートからメールアドレス・提出時間を取得し、『提出状況確認シート』に出力する」ができました。

次は、「② ①の処理を定期的に実行する」を見ていきましょう。
GASには時間ごとに定期的に処理を実行する機能があるようです。

画面左側の「時計マーク」を押下します。

画面右下の「トリガーを追加」を押下します。

トリガーを指定していきます。「実行する関数を選択」「エラー通知設定」「実行するデプロイを選択」はデフォルトのままです。「イベントのソースを選択」で「時間主導型」を選択します。「時間ベースのトリガーのタイプを選択」で「時間ベースのタイマー」を選択しました。これは実行処理の間隔を決めるものです。ので、必要に応じて設定しましょう。1分おきでも設定できます。「時間の間隔を選択（時間）」で「8時間おき」を選択しました。1日3回くらい動けばよいかなと思いました。設定できたら、「保存」を押します。

設定できているようですね！

定期実行が成功しているか、失敗しているかは画面左端の「時計マーク」の下の「3本線マーク」をクリックすると、実行結果が見れるようです。

注意 ①トリガーの設定をする前に、正しく動作するか確認しましょう ②定期実行をやめる場合は、トリガーを削除しましょう

これでやりたいこと「② ①の処理を定期的に実行する」ができました。

終わりに

調べてみると、GASには定期実行など様々な機能がありました。とても使い勝手がよいですし、googleformやスプレッドシートはよく使うので、GASで便利化してみるとよいですね。
定期実行に関してパフォーマンスは検証していないので、実行時間が長くならないようにするなど対策が必要かもです。
スプレッドシートからgoogleformを自動生成する記事も書いていますので、よかったらどうぞ！

本記事は　企画・実装メモ1時間　実装4時間　執筆 7時間　で書き終えました。

参考文献

いつも隣にITのお仕事『【初心者向けGAS】ログを表示するconsole.logの使い方とテンプレート文字列』（2022年1月30日利用）
https://tonari-it.com/gas-logger-log/

note『GAS(Google Apps Script)入門講座① - スプレッドシートを触ってみよう』（2022年1月30日利用）
https://note.com/skipla/n/n0803937a0887#pUrmc

エクセルの神髄『第15回.複数のスプレッドシートを扱う』（2022年1月30日利用）
https://excel-ubara.com/apps_script1/GAS015.html

Qiita『【Google Apps Script】その4　日付を綺麗に整形する』（2022年1月30日利用）
https://qiita.com/rf_p/items/ff841885ef6346afe5d4

Qiita『【Google Apps Script】その8　スクリプトを定期実行し、ビットコインの1分ごとの価格を自動取得する』（2022年1月30日利用）
https://qiita.com/rf_p/items/267a8d9daa8c9f1ef027