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

はじめに

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

今回の例では「タイトル」と「何日経ったか/あと何日か」と「日付」を入力してもらい、

Googleフォームで「回答をスプレッドシートに表示」とすると、

スプレッドシートに「フォームの回答1」というシートが追加されます

以下のスプレッドシートの関数でそれぞれの結果を得たいと思います

// 何日経ったかを計算するスプレッドシートの関数
=datedif(フォームで回答された日付,today(),"D")

// あと何日かを計算するスプレッドシートの関数
=datedif(today(),フォームで回答された日付,"D")

DATEIF関数

問題点

dateif関数をあらかじめ、「フォームの回答1」の右側の空いているセルにあらかじめ仕込んでおいて、フォームに回答すると以下のようにズレてしまい関数での計算ができません。

「別シート」から参照しても

セル「A21」に「フォームの回答1のA21」への参照を埋め込んでいても、回答後はズレてしまい、「フォームの回答1のA22」の回答になってしまい、正しく参照することができません。

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

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

以下のような関数をGASで作ります

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の「トリガー」を選択し、

以下のように設定します。

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

解決法②　ARRAYFORMULA関数を利用する

Googleスプレッドシートの関数のARRAYFORMULA関数を利用するという手もあります。

計算結果を入力したい列の一番上（例はF2とG2）に

以下のような関数を埋め込みます

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

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

するとF3、G3以降には勝手に計算結果が出力されます

注意点

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

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

また、GASで以下のような操作をした場合、関数が設定されてあるところまでのデータを取得してしまうので、注意が必要です

const formData = formSheet.getDataRange().getValues();