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

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

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

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

#完成イメージ・運用

![スライド1.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/65e364bc-e842-b650-44e3-3f63fca403e7.jpeg)

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

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

***
「googleformアンケート」はサンプルとして↓のようなものを作成しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/d61736ec-ce02-2787-c66e-5f3fcc0676ea.png)

「googleform編集画面(回答確認画面)」はこちら↓です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/560615f1-c1d5-5a85-51f5-4a277826e6b0.png)

「spreadsheet回答集計シート」はこちら↓です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/b3e19012-4756-0a95-96f1-2f7ab94f11a6.png)

「spreadsheet【提出状況確認シート】」はこちら↓です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/42d28cf2-a3a8-bfd1-51af-e48c895e91f7.png)

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

1点ポイントがあります。誰が提出したかを把握するために、「設定」→「メールアドレスを収集する」にチェックをいれましょう。アンケートに名前を記入してもらうことでも回答はわかりますが、今回はメールアドレスから誰が出したのか把握するようにします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/c952d26c-f9da-9fda-1fd1-57b46e3373ea.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/81b1be71-bbc8-9c2c-1c67-8a965741f3ee.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/a5ddaca7-4cf1-c4c0-70bd-2a29587bd5e6.png)

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

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

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

![220130_提出状況確認シート_フォルダ構成.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/f2d7db08-7019-f1e8-708f-e57f81046399.jpeg)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/130f7f01-7eed-e6b2-319f-c060f2a950c0.png)

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

次に、GASを記述していきます。
「拡張機能」→「Apps Script」を押下します。（以前は「ツール」→「スクリプトエディタ」だったのですが、変更されたようです）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/54d9c933-3d4a-454e-4213-988ed2c449d3.png)

下のコードに「スプレッドシートキー」を加えて、スクショのように記述します。
保存マークを押して実行すると、処理が動きます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/10549029-4c45-bd04-859c-382519ba7878.png)

```javascript: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には時間ごとに定期的に処理を実行する機能があるようです。

画面左側の「時計マーク」を押下します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/2e8b61b9-6c73-8d13-7b2f-2c74af0ee964.png)

画面右下の「トリガーを追加」を押下します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/d9de2cf5-90a3-1fab-871b-c13d8a64bfb5.png)

トリガーを指定していきます。「実行する関数を選択」「エラー通知設定」「実行するデプロイを選択」はデフォルトのままです。「イベントのソースを選択」で「時間主導型」を選択します。「時間ベースのトリガーのタイプを選択」で「時間ベースのタイマー」を選択しました。これは実行処理の間隔を決めるものです。ので、必要に応じて設定しましょう。1分おきでも設定できます。「時間の間隔を選択（時間）」で「8時間おき」を選択しました。1日3回くらい動けばよいかなと思いました。設定できたら、「保存」を押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/18a7493e-27aa-5932-e883-450ce0575285.png)

設定できているようですね！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/605915/15fbae7b-55e6-80c5-c4bd-1c870868f76b.png)
定期実行が成功しているか、失敗しているかは画面左端の「時計マーク」の下の「3本線マーク」をクリックすると、実行結果が見れるようです。

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

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

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

https://qiita.com/aki_number16/items/e2ac30e7c4d2d44a02ad


本記事は　企画・実装メモ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