Google表单提交更新后发送电子邮件 [英] Send email when Google Form submission is updated

问题描述

我已阅读大量关于通过电子邮件通知某人表单已提交并将表单数据包含在电子邮件中的帖子，但我想要做的是识别已存在信息的更新并将其放入电子邮件中并发送。我想这可以通过识别最新的时间戳并获取相应的条目来完成。然而，我非常有限的编程技巧意味着我一直无法找出答案。

解决方案

这个函数假设你有一个触发函数 formSubmitted（e）使用它接收的事件对象进行表单提交。正如你猜测的那样，它确定了最新的更新。然后它创建一个模拟表单提交事件，并调用表单提交触发器函数。

设置 onChange（） up以触​​发电子表格变更。
$ b

/ **
有时，Google Apps脚本吮吸。这是其中的一次。
*如果用户使用编辑链接更新以前提交的表单数据，
*数据将在表单响应表中更新。不幸的是，
*不会生成电子表格表单提交事件。 （请参阅说明：
* https://code.google.com/p/google-apps-script-issues/issues/detail?id=2969#c12）
*
*观察：表单编辑显示为具有activeSheet =Form Responses和activeRange =A1的电子表格的变更，类型为OTHER，源
*。我们
*将使用这些信息来限制我们强制执行正常表单提交
*处理程序的时间。不幸的是，这个事件并没有告诉我们哪个行发生了变化，
*但是应该是最新的timeStamp。
* /
函数onChange（e）{
if（e.changeType！==OTHER）return;
if（e.source.getActiveSheet（）。getName（）。indexOf（Form Responses）=== -1）return;
if（e.source.getActiveRange（）。getA1Notation（）！==A1）return;

//查找最新的表单提交
var sheet = e.source.getActiveSheet（）;
var timeStamps = sheet.getRange（A2：A）。getValues（）;

//确定最近的日期
var max = Math.max.apply（null，transpose（timeStamps）[0]）;
var properties = PropertiesService.getDocumentProperties（）;
var lastMax = properties.getProperty（maxDate）;
if（lastMax&& max.toString（）=== lastMax）return; //避免由来自onChange函数的更新触发的更改
Logger.log（onChange（）认为已经进行了表单编辑e =+ JSON.stringify（e））;
properties.setProperty（maxDate，max.toString（））;
var maxDate = new Date（max）;

//然后找到包含它的表格行
（var row = 0，found = false; row< timeStamps.length&&！found; row ++）{$ b $ （timeStamps [row] [0]&& timeStamps [row] [0] .getTime（）=== maxDate.getTime（））{
found = true;
休息;



if（！found）{
//我们是盲人...重新计算所有东西
// TODO
}
else {
//触发此行的提交事件
row + = 2; //调整表单行的偏移量
var dataRange = sheet.getDataRange（）;
var data = dataRange.getValues（）;
var headers = data [0];
var response = data [row];
var respRange = dataRange.offset（row-1,0,0，dataRange.getLastColumn（））;

//建立事件对象，范围，值和namedValues填入
var values = respRange.getValues（）[0];
var namedValues = {};
for（var h in headers）{namedValues [headers [h]] = [values [h]]; }
var e = {range：respRange，
values：values，
namedValues：namedValues
};
formSubmitted（e）; //模拟事件
}
}


I have read plenty of posts about notifying someone via email when a form has been submitted and including the form data in the email but what I would like to do is identify an update to already existing information and have that put in an email and sent. I imagine that this would be done by identifying the latest timestamp and grabbing the corresponding entry. However, my extremely limited programming skills mean that I have been unable to work out the answer.

解决方案

It just happens I have such a function in my bag of tricks!

This function assumes that you've got a trigger function formSubmitted(e) for form submission that utilizes the event object it receives. As you surmised, it identifies the most recent update. It then creates a simulated form submission event, and invokes the form submission trigger function.

Set onChange() up to trigger on spreadsheet changes.

/**
 * Sometimes, Google Apps Script sucks. This is one of those times.
 * If a user updates previously submitted form data by using the "edit" link,
 * that data will be updated in the Form Responses sheet. Unfortunately,
 * that will NOT generate a spreadsheet form submission event. (See explanation:
 * https://code.google.com/p/google-apps-script-issues/issues/detail?id=2969#c12)
 *
 * Observation: a form edit shows up as a change, with type of "OTHER", and source
 * of a spreadsheet with activeSheet="Form Responses" and activeRange="A1". We
 * will use that information to limit when we force our normal form-submission
 * handler to run. Unfortunately, the event doesn't tell us which row changed,
 * but that should be the one with the most recent timeStamp. 
 */
function onChange( e ) {
  if (e.changeType !== "OTHER") return;
  if (e.source.getActiveSheet().getName().indexOf("Form Responses") === -1) return;
  if (e.source.getActiveRange().getA1Notation() !== "A1") return;

  // Find latest form submission
  var sheet = e.source.getActiveSheet();
  var timeStamps = sheet.getRange("A2:A").getValues();

  // Determine most recent date
  var max = Math.max.apply(null,transpose(timeStamps)[0]);
  var properties = PropertiesService.getDocumentProperties();
  var lastMax = properties.getProperty("maxDate");
  if (lastMax && max.toString() === lastMax) return; // Avoid changes triggered by updates from onChange function
  Logger.log("onChange() thinks there's been a form edit. e="+JSON.stringify(e));
  properties.setProperty("maxDate", max.toString());
  var maxDate=new Date(max);

  // Then find the sheet row that contains it
  for (var row=0,found=false;row<timeStamps.length&&!found;row++) {
    if (timeStamps[row][0] && timeStamps[row][0].getTime() === maxDate.getTime()) {
      found = true;
      break;
    }
  }

  if (!found) {
    // We're blind... recalc everything
    // TODO
  }
  else {
    // Trigger the submission event for this row
    row += 2; // adjust for offset into sheet rows
    var dataRange= sheet.getDataRange();
    var data = dataRange.getValues();
    var headers = data[0];
    var response = data[row];
    var respRange = dataRange.offset(row-1, 0, 1, dataRange.getLastColumn());

    // Build event object with range, values and namedValues filled in
    var values = respRange.getValues()[0];
    var namedValues = {};
    for (var h in headers) { namedValues[headers[h]] = [values[h]]; } 
    var e = { range: respRange,
              values: values,
              namedValues: namedValues
            };
     formSubmitted(e);        // Simulate event
  }
}

这篇关于Google表单提交更新后发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！