GoogleFormで回答してもらった情報をSpreadSheetの関数で加工する

# はじめに
LINEBotでの開発をしていると、情報の登録フォームにGoogleFormを頼り、いろんな計算でGoogleSpreadSheetの関数を頼りと、Google様にお世話になりっぱなしです！！
GoogleFormで取得した内容を、SpreadSheetで弄るためのチップスです

今回の例では「タイトル」と「何日経ったか/あと何日か」と「日付」を入力してもらい、
![スクリーンショット 2023-01-18 8.37.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/f16d9818-570b-16de-d56f-6c507c3f439a.png)

Googleフォームで「回答をスプレッドシートに表示」とすると、
![スクリーンショット 2023-01-18 8.35.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/60237794-9d09-035f-3009-5d3d6774fb82.png)

スプレッドシートに「フォームの回答1」というシートが追加されます
![スクリーンショット 2023-01-18 8.36.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/8450170c-5c2e-13df-3739-342a2977bfd5.png)

以下のスプレッドシートの関数でそれぞれの結果を得たいと思います
```js
// 何日経ったかを計算するスプレッドシートの関数
=datedif(フォームで回答された日付,today(),"D")

// あと何日かを計算するスプレッドシートの関数
=datedif(today(),フォームで回答された日付,"D")
```
[DATEIF関数](https://support.microsoft.com/ja-jp/office/datedif-%E9%96%A2%E6%95%B0-25dba1a4-2812-480b-84dd-8b32a451b35c)

# 問題点
dateif関数をあらかじめ、「フォームの回答1」の右側の空いているセルにあらかじめ仕込んでおいて、フォームに回答すると以下のようにズレてしまい関数での計算ができません。
![スクリーンショット 2023-01-18 8.56.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/069ed374-604c-41c9-d812-3614181293b0.png)

「別シート」から参照しても
![スクリーンショット 2023-01-18 8.57.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/bfd30f5a-3660-92b4-1c84-47871fa75698.png)

セル「A21」に「フォームの回答1の**A21**」への参照を埋め込んでいても、回答後はズレてしまい、「フォームの回答1の**A22**」の回答になってしまい、正しく参照することができません。
![スクリーンショット 2023-01-18 8.59.16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/a8be6231-2ee7-0279-8b2d-be45e0338388.png)


# 解決法① GASを使ってフォームに回答があるたびに別シートにコピーする
GASを使ってフォームに回答があるたびに別シートにコピーする手が使えます。

以下のような関数をGASで作ります
```js
function copyFormToAnotherSheet() {
  const formSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1');
  const anotherSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('別シート');
  const formData = formSheet.getDataRange().getValues();
  const calculateData = anotherSheet.getDataRange().getValues();
  if(formData.length == calculateData.length){
    // フォームの回答1と別シートの回答の数が同じだった場合は何もしない
  } else {
    // フォームの回答1と別シートの回答の数が異なる場合、同じ数になるまでコピーを繰り返す
    let num = calculateData.length
    while(num < formData.length){
      anotherSheet.getRange(`A${num+1}`).setValue( formSheet.getRange(`A${num+1}`).getValue() )
      anotherSheet.getRange(`B${num+1}`).setValue( formSheet.getRange(`B${num+1}`).getValue() )
      anotherSheet.getRange(`C${num+1}`).setValue( formSheet.getRange(`C${num+1}`).getValue() )
      anotherSheet.getRange(`D${num+1}`).setValue( formSheet.getRange(`D${num+1}`).getValue() )
      anotherSheet.getRange(`E${num+1}`).setValue( formSheet.getRange(`E${num+1}`).getValue() )
　　　　　　　　　　　　// 関数を文字列としてセルに埋め込んでも、関数として処理される
      anotherSheet.getRange(`F${num+1}`).setValue( `=datedif(F${num+1},today(),"D")` )
      anotherSheet.getRange(`G${num+1}`).setValue( `=datedif(today(),G${num+1},"D")` )
      num++
    }
  }
}
```

GASの「トリガー」を選択し、
![スクリーンショット 2023-01-18 9.09.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/360308c7-a513-9063-6c53-330414785ad7.png)

以下のように設定します。
![スクリーンショット 2023-01-18 9.10.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/a9a24a43-359a-a274-965f-fdc1bfeb5565.png)

以上のように設定すると、フォームに回答があるたびに、別のシートに回答と関数がコピーされるようになります！


# 解決法②　ARRAYFORMULA関数を利用する
Googleスプレッドシートの関数の[ARRAYFORMULA関数](https://support.google.com/docs/answer/3093275?hl=ja)を利用するという手もあります。

計算結果を入力したい列の一番上（例はF2とG2）に
![スクリーンショット 2023-01-18 9.19.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/02ef8942-0d60-ba16-9a1b-641fa22a38ff.png)

以下のような関数を埋め込みます
```js
=ARRAYFORMULA(datedif(D2:D101,today(),"D"))

=ARRAYFORMULA(datedif(today(),D2:D101,"D"))
```

するとF3、G3以降には勝手に計算結果が出力されます
![スクリーンショット 2023-01-18 9.21.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27192/9c4bbe34-111b-d030-78a6-2c5fc9b90400.png)

## 注意点
「どこからどこまで」を計算するか？を決めておかないといけないので、例のように**D2:D101**と設定すると、それ以上の回答が行われた場合に、範囲を変更する必要があります。（常にメンテナンスを考えないといけません）
```js
=ARRAYFORMULA(datedif(D2:D101,today(),"D"))
```

また、GASで以下のような操作をした場合、関数が設定されてあるところまでのデータを取得してしまうので、注意が必要です
```js
const formData = formSheet.getDataRange().getValues();
```