如何检查当前提交是编辑回复还是新回复 [英] How to check if current submission is editing response or a new response

问题描述

我正在通过应用脚本处理谷歌表单.我想确定当前提交的表单是处于编辑模式还是新回复?如何在 onSubmit 事件中检查这一点.

I am working with google forms via app script. I want to determine if the current form that is being submitted is in edit mode or a new response? How can I check this in the onSubmit event.

如果是该用户正在编辑之前提交的响应，我想将电子表格中的值更改为是".

If yes that user is edit a previously submitted response than I want to change the value in my spread sheet to "yes".

下面是我的代码片段:

function testExcel2() {

  var email = "email";
  var s = SpreadsheetApp.openById("id");
  var sheet = s.getSheets()[0];
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn() - 1).getValues()[0]; 
  var datarow = sheet.getRange(sheet.getLastRow(),1,1,sheet.getLastColumn() - 1).getValues()[0];
  var message = "";

  for(var i in headers)
  {
    message += "" + headers[i] + " : " + datarow[i] + "

";
  }

  MailApp.sendEmail(email, "Submitted Data Test", message); 

  var af = FormApp.getActiveForm();
  //af.setCustomClosedFormMessage("The form is currently processing a submission, please refresh the page.");
  af.setConfirmationMessage('Thanks for responding!')
  //af.setAcceptingResponses(false);
  var rowKey = "o" + sheet.getLastRow();
  var editCell = sheet.getRange(rowKey).setValue('no');

}

推荐答案

Google 表单表单提交事件没有可以帮助了解响应是新响应还是响应编辑的字段.正如 Sandy 已经在问题评论中所说的那样，Form Servicer 类和方法以及响应值都包含对此有所帮助的内容.

The Google Form form submit event doesn't have a field that could help to know if the response is a new response or if it's a response edit. As Sandy already said through question comments, the Form Servicer classes and methods, nor the response values include something that could help on this.

另一方面，Google Sheets submit 事件有一个可以提供帮助的范围字段.以下绑定到 Google 电子表格的脚本会记录响应行:

By the other hand, the Google Sheets submit event has a range field that could help. The following script bounded to a Google spreadsheet logs the response row:

function onFormSubmit(e){
  var response = e.range;
  Logger.log(response.getRow());
}

以上可用于保持更新一列以保存修订计数器.如果对应的单元格为空，则响应为新响应，否则为响应编辑.

The above could be used to keep updated a column to hold a revision counter. If the corresponding cell is blank, then the response is a new response, other way it's a response edit.

以下脚本将其绑定到接收表单响应的电子表格.它需要一个表单提交可安装触发器.其他修改说明包含在脚本注释中.

The following script it to be bounded to the spreadsheet that receives the Form responses. It requires a on form submit installable trigger. Other instructions to adapt it are included on the script comments.

/*
 *
 * Global Variables
 *
 */

/*
 * Sheet name used as destination of the form responses
 */
var sheetName = 'Form Responses';

/*
 * Name of the column to be used to hold the response revision counter 
 * It should match exactly the header of the related column, 
 * otherwise it will do nothing.
 */
var revisionsColumn = 'Rev';

/*
 * Responses starting row
 */
var startRow = 2;

function setRevisionCounts(e){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
  var revisionsIndex = headers[0].indexOf(revisionsColumn);
  var data = sheet.getDataRange().getValues();
  var response = e.range;
  var rowIndex = response.getRow()-1;
  var rev = data[rowIndex][revisionsIndex]+1;
  sheet.getRange(rowIndex+1, revisionsIndex+1).setValue(rev); 
}

这篇关于如何检查当前提交是编辑回复还是新回复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！