Googleフォームの「回答を編集」をしてもスプレッドシートの回答が削除されない

Question

前提・実現したいこと

スプレッドシートからフォームを作成し、予約システムを構築しています。
フォームはプルダウンから3～4つの時刻を選択する形式です。

発生している問題・エラーメッセージ

「回答を編集」URLを回答者に送信することはできました。
回答を編集した際、回答を取り消してもスプレッドシートに値が残ったままです。
別の選択肢に代えた場合や、初回回答時に未回答だった部分の追加回答は、うまく反映されています。
ご教示いただけますと幸いです。

function getFormtest(e) {

//'を消すコード
  var range = e.range;
  range.setValues(range.getValues());

//フォームを取得
 var form = FormApp.openById('')
 var items = form.getItems()
 var allitem = form.getResponses()
 
//名前とメアドを回答から取得
 var name = e.values[1]
 var email = e.values[2]

//回答を編集用URLを取得
 var url = allitem[Number(allitem.length - 1)].getEditResponseUrl();

 var array = []; //配列を宣言

//質問項目＋回答を配列に入れる。回答がない場合はスキップ
 for (var i = 3; i < items.length; i++) {
     var ques = e.values[i +1]
  if(ques.length === 0){
   continue;
 }   var index = items[i].getTitle()
 array.push('【'+ index +'】' + ques)
}
// メール送信
  MailApp.sendEmail({
    to: email,
    subject: 'ご予約ありがとうございます',
    body: name + '様\n' + array + '\n'+ url + '\n' 
  });
}

function フォーム作成用() {
//シートの読み取り
  var sheet = SpreadsheetApp.getActive().getSheetByName('営業日')
  var sheet2 = SpreadsheetApp.getActive().getSheetByName('設定');

//フォーム名と説明文を取得し、フォームを作成
  var formTitle = sheet2.getRange(1,1).getValues();
  var formDescription = sheet2.getRange(1,2).getValues();
  var form = FormApp.create(formTitle);
  form.setDescription(formDescription);

//名前とメールアドレスを取得し、質問を作成
  form.addTextItem().setTitle('お子様の名前').setRequired(true);
    var validationEmail = FormApp.createTextValidation().requireTextIsEmail().build();
  form.addTextItem().setTitle('メールアドレス').setRequired(true).setValidation(validationEmail);

//営業日、選択肢を2次元配列として格納
 var alldata = sheet.getDataRange().getValues();
 alldata.shift()
 var timedata = sheet.getRange("D2:w32").getValues()

//営業日を1次元配列として格納
   function generateArray(alldata,colmn){
   return alldata.map(record => record[colmn])
 }
var ad = generateArray(alldata,0)
 
//日付と曜日を取得
for (let i=0; i<ad.length; i++){
timedata2 = timedata[i].filter(Boolean)
 if(timedata2.length === 0){
   continue;
 }
 aday = Utilities.formatDate(alldata[i][0],"JST", "dd日")
 var daydate = (aday+" ("+alldata[i][1]+")")

//その日の時間帯を取得し、リストを作成
form.addListItem()
.setTitle(daydate)
.setChoiceValues(timedata2)
  }
}

Answer 1

現状はフォームとスプレッドシートを直接連携していると思いますが、
下記のコードを使う場合は、下記の手順でフォーム送信時のトリガーを新たに連携させてください。
（少なくともスプレッドシートは新規作成することを推奨します）

1.フォーム編集画面の右上にある「縦に３つ点が並んでいるボタン」をクリックします。


2.メニューペインから「スクリプトエディタ」を選択してクリック。


3.出てきたエディタ画面に下記のスクリプトを入力します。（元のフォームに紐付いているスプレッドシートのスクリプトではありません）
4.左側に縦に並んでいるアイコンのうち「トリガー」ボタン（時計のマーク）を選択
（古いコードエディタの場合は、メニューの「編集」->「現在のプロジェクトのトリガー」を選択）
5.右下の「トリガーを追加」をクリック
・「実行する関数を選択」-> onFormSubmit (下記の関数名)
・「イベントのソースを選択」-> 「フォームから」
・「イベントの種類を選択」 -> 「フォーム送信時」
にそれぞれ変更、保存。

[スクリプト]
※スプレッドシートIDとシート名のところは変更してください。
※実行時はV8ランタイムを有効にしてください。

function onFormSubmit(event) {
  const TITLE_ID = '回答ID';
  const TITLE_TIME = 'タイムスタンプ';
  const form = event.source;
  const sheet = SpreadsheetApp.openById('スプレッドシートID').getSheetByName('シート名');
  // スプシの1行目に項目名を書く。
  const titles = [TITLE_ID, TITLE_TIME, ...form.getItems().map(item => item.getTitle())];
  sheet.getRange(1, 1, 1, titles.length).setValues([titles]);
  
  // 最新の回答を取得
  const lastResponse = form.getResponses().slice(-1)[0];
  // 回答データを連想配列に格納
  // （キー：項目名、値：回答）
  const respHash = {};
  respHash[TITLE_ID] = lastResponse.getId();
  respHash[TITLE_TIME] = Utilities.formatDate(
    lastResponse.getTimestamp(), 'JST', 'yyyy/MM/dd HH:mm:ss');
  lastResponse.getItemResponses().forEach(e => {
    respHash[e.getItem().getTitle()] = e.getResponse();
  });
  
  // 取得した回答をスプシの項目名順に配置
  const record = titles.map(e => respHash[e] || '');

  // 回答IDがスプシ上にあるか調べる。
  // IDがなければ新規回答なので、スプシの末尾にレコードを追加。
  // IDがあれば編集した回答なので、既存行に上書きする。
  const recordKeys = sheet.getRange("A:A").getValues().map(e => e[0]);
  const oldDataRow = recordKeys.indexOf(respHash[TITLE_ID]) + 1;
  if (oldDataRow < 1) {
    sheet.appendRow(record);
  } else {
    sheet.getRange(oldDataRow, 1, 1, record.length).setValues([record]);
  }

  const url = lastResponse.getEditResponseUrl();
  const name = record[2];
  const email = record[3];
  const array = []; //配列を宣言
  //質問項目＋回答を配列に入れる。回答がない場合はスキップ
  for (var i = 4; i < record.length; i++) {
    if(record[i].length === 0) continue;
    array.push('【'+ titles[i] +'】' + record[i])
  }

  // メール送信
  MailApp.sendEmail({
    to: email,
    subject: 'ご予約ありがとうございます',
    body: name + '様\n' + array + '\n'+ url + '\n' 
  });
}

　
[考え方]
まず、元のスクリプトは、
フォームの回答送信 ->(自動) -> スプレッドシートへのデータ転記 ->（イベント発火）
-> スプレッドシートのイベントからデータ取り出し＆フォームデータの取り出し
-> スプレッドシートへ転記＆メール送信
となっています。

しかし、色々試したところ、フォームの回答を編集して送信したときスプレッドシート経由で渡されるイベントの中にあるデータは、下記のようなルールになっているらしいことがわかりました。
・選択->未選択に戻した項目は空欄データになる。
・編集前後で変わっていない項目も空欄データになる。
・未選択から選択、または選択肢を(未選択以外に)変えた項目だけがデータとして渡される。
・空欄として渡されたデータはスプレッドシート上で上書き修正されない
スプレッドシート経由でデータ連携している場合、スプレッドシートの自動修正はこれらのルールに従うため、編集して未選択に変えた回答項目が削除されず残ってしまうようです。

一方、Formオブジェクトから回答データを直接取得することで、すべての回答項目を把握することができます。
編集によって変更された項目も変更されていない項目も全部そのまま取得でき、未選択に変えられた項目も空白データとして渡されます。
setValues関数は空欄データを設定すれば既存セルのデータを消去できます。よって、取得したデータをsetValuesを使って手動で上書きすればよいということになります。

[新規回答と編集された回答をどう区別するか]
スプレッドシートと連携している場合、既存の回答を編集したときは自動的に回答を集約しているスプレッドシートの該当行を変更してくれました。（ただし未選択は削除できない）
しかしながら、スプレッドシート経由での連携をやめ、フォーム経由で連携した場合、自動でスプレッドシートが修正されないため、手動で修正する必要があります。
この時に使用するのが、回答（Response）オブジェクトが持っている固有IDです。編集された回答は常に作成当初と同一のIDを返します。スプレッドシートのA列に回答のIDを記録しておくことで、編集された回答行を判別しています。


[補足]　
質問の元のスクリプトだと、**スプレッドシートから渡されたデータ（ getFormtest関数の引数e）と、Formオブジェクトから取り出したデータ（Form.getResponses()）が混在してしまっていてデータの拠り所がわかりにくいように思いました。

そこで修正後のスクリプトでは、フォーム回答の送信をトリガーにしてスクリプトを実行し、回答データ元をFormオブジェクトに一元化して取り出すことで、データの諸元を統一化してみました。

修正後の流れ：
フォーム回答送信 -> （イベント発火） -> フォームデータ加工 -> スプレッドシートへ転記＆メール送信

Comments

[Ryu292937]

非常に丁寧な回答、ありがとうございます！
こちらの方法でうまくいきました。
フォームの選択肢に｢休み｣を追加するという対症療法を使っていたのですが、フォーム側から全回答を取得して上書きする発想がなかったです。また、新規回答かどうか区別する方法もありがとうございます。e.rangeからgetRowで回答行を取得していたのですが、回答IDを記載する方法があったのですね。
読み返して自力で理解できるよう頑張ります。ありがとうございました。