Google表单应用脚本：如何检查当前提交是编辑回复还是新回复 [英] Google Forms App Script : How to check if current submission is editing response or a new response

问题描述

我正在通过应用程序脚本使用Google表单。我想确定当前正在提交的表单是处于编辑模式还是新的响应？我如何在onSubmit事件中检查这一点。

如果用户正在编辑以前提交的回复，而不是我想将电子表格中的值更改为是。

下面是我的代码片段：

  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] +\\\
\ N; 
} 
 
 MailApp.sendEmail（email，Submitted Data Test，message）; 
 
 var af = FormApp.getActiveForm（）; 
 //af.setCustomClosedFormMessage(\"The表单正在处理提交内容，请刷新页面。）; 
 af.setConfirmationMessage（'Thanks for respond！'）
 //af.setAcceptingResponses(false）; 
 var rowKey =o+ sheet.getLastRow（）; 
 var editCell = sheet.getRange（rowKey）.setValue（'no'）; 
 
 
 
 
解决方案
 Google表单表单提交事件没有一个字段可以帮助知道响应是新响应还是响应编辑。正如桑迪通过问题评论所述，Form Servicer的类和方法，以及响应值都包含了一些可能对此有帮助的东西。
另一方面，Google表格提交事件有一个范围字段，可以帮助。以下脚本绑定到Google电子表格中记录响应行： 
 
 
 函数onFormSubmit（e）{
 var response = e.range; 
 Logger.log（response.getRow（））; 
} 
  
以上可以用来保持更新列以保存修订计数器。如果相应的单元格是空白的，那么响应是一个新的响应，也可以是响应编辑。
 
 
 
以下脚本将绑定到接收表单回复。它需要一个表单提交可安装的触发器。  
 
 
  / * 
 * 
 *全局变量
 * 
 * / 
 
 / * 
 *表单名称用作表单响应的目的地
 * / 
 var sheetName ='Form回应; 
 
 / * 
 *用于保存响应修订计数器的列的名称
 *它应该完全匹配相关列的标题，
 *其他它什么都不会做。 
 * / 
 var revisionsColumn ='Rev'; 
 
 / * 
 *响应起始行
 * / 
 var startRow = 2; 
 
函数setRevisionCounts（e）{
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（sheetName）; 
 var headers = sheet.getRange（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）; 
} 
  
 
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".

below is a snippet of my code:
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] + "\n\n";
  }

  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');

}

解决方案
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.

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); 
}


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