我已经阅读了很多关于在提交表单时通过电子邮件通知某人并在电子邮件中包含表单数据的帖子,但我想做的是识别对现有信息的更新,并将其放入电子邮件并发送。我想这将通过识别最新的时间戳并抓取相应的条目来完成。然而,我极其有限的编程技能意味着我无法找出答案。
您可以使用 Google App Script 执行此操作,打开存储表单回复的电子表格。转到工具->脚本编辑器->脚本编辑器将打开,复制以下功能。
创建一个触发器以在表单提交时调用此函数资源 -> 当前项目的触发器 -> 选择"从电子表格"和"提交表单时"并创建一个触发器。
function sendEmailNoti(e) {
var timeStamp = e.values[0];
var userEmail = e.values[1];
var products = e.values[2];
var subject = "New Subscription Request";
var email = "abcd@yourdomain.com";
var message = "User email : " + userEmail + "<BR>Products opted : " + products + "<BR>Timestamp : " + timeStamp;
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: message
});
}
碰巧我的技巧袋里有这样的功能!
此函数假定你有一个触发器函数formSubmitted(e)
用于表单提交,该函数利用它接收的事件对象。正如您所推测的,它标识了最新的更新。然后,它创建一个模拟的表单提交事件,并调用表单提交触发器函数。
将onChange()
设置为在电子表格更改时触发。
/**
* 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
}
}
我对这个问题的处理方式略有不同。而不是创建新触发器 - 稍微修改了表单提交触发器本身,以从电子表格而不是表单响应中为每个键选择值。
修改后的代码如下所示
// Include a copy of all responses
for ( var keys in columns ) {
var key = columns[keys];
message += key + ' :: '+ sheet.getRange(resultUrls.length+1, (data[0].indexOf(key)+1)).getValues() + "<br />";
}