在 Google 表格中，我需要一个脚本才能在表单提交上运行，但引用的不是 "form responses"

Question

我正在尝试获取一个脚本，以便在提交表单时查看工作簿中的命名工作表。我使用了这个脚本一段时间，现在需要为提交的数据添加一些计算。我将表单响应数据放到一张表中添加一些计算，然后放入文档中并通过电子邮件发送出去。电子邮件文档功能工作得很好，只是无法让脚本查看"输出"表与"Form_Response"表上的数据。

这就是剧本，我尝试了几件事，但没有成功，总是从"Form_Responses"中提取。为了清楚起见，我删除了试用代码。

有什么想法吗？

谢谢！

我的代码：

var docTemplate = "1oeUqdaesyHM-WzUTFIlzHFzAqIm1DN4OiiGywgs622w"; // template ID 
var docName = "Service Invoice";
function onFormSubmit(e) {
var email_address = "<>";
var email_address2 = "<>";
var job_number = e.values[1];
var date = e.values[2];
var tech_name = e.values[7];
var customer_name = e.values[3];
var customer_email = e.values[6];
var address = e.values[4];
var phone = e.values[5];
var work_description = e.values[9];
var project_manager = e.values[8];
var project_manager_email = "<>";
var known_issues = e.values[10];
var hours = e.values[11];
var labor_total = e.values[21];
var material_total = e.values[20];
var total = e.values[22];
var paid = e.values[15];
var type = e.values[16];
var check_num = e.values[17];
var copyId = DriveApp.getFileById(docTemplate)
.makeCopy(docName + ' for ' + job_number)
.getId();
var copyDoc = DocumentApp.openById(copyId);
var copyBody = copyDoc.getActiveSection();
copyBody.replaceText('keydate:', date);
copyBody.replaceText('keyjobnumber:', job_number);
copyBody.replaceText('keycustomername:', customer_name);
copyBody.replaceText('keyaddress:', address);
copyBody.replaceText('keyphonenumber:', phone);
copyBody.replaceText('keycustomeremailaddress:', customer_email);
copyBody.replaceText('keyemployeename:', tech_name);
copyBody.replaceText('keyworkdescription:', work_description);
copyBody.replaceText('keyprojectmanager:', project_manager);
copyBody.replaceText('keyfollowup:', known_issues);
copyBody.replaceText('keyhours:', hours);
copyBody.replaceText('keylabortotal:', labor_total);
copyBody.replaceText('keymaterialtotal:', material_total);
copyBody.replaceText('keytotal:', total);
copyBody.replaceText('keypaid:', paid);
copyBody.replaceText('keypaymenttype:', type);
copyBody.replaceText('keycheck:', check_num);
copyDoc.saveAndClose();
var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");
var subject = "ESCON Group Service Invoice";
var body = "Please find attached your invoice for the service work performed by ESCON Group.";
MailApp.sendEmail(customer_email, subject, body, {
htmlBody: body,
attachments: pdf
});
var subject = "ESCON Group Service Invoice" + job_number;
var body = "Here is the Service Invoice for: " + job_number + "";
MailApp.sendEmail(project_manager_email, subject, body, {
htmlBody: body,
attachments: pdf
});
var subject = "ESCON Group Service Invoice" + job_number;
var body = "Here is the Service Invoice for: " + job_number + " -Please contact Trevor Gross with any questions.";
MailApp.sendEmail(email_address, subject, body, {
htmlBody: body,
attachments: pdf
});
var subject = "ESCON Group Service Invoice" + job_number;
var body = "Here is the Service Invoice for " + job_number + "";
MailApp.sendEmail(email_address2, subject, body, {
htmlBody: body,
attachments: pdf
});
DriveApp.getFileById(copyId).setTrashed(true);
}

Answer 1

要从特定工作表中获取值，需要获取电子表格(e.source(、工作表(getSheetByName()(、范围(getRange()(，然后再获取值(getValue()(。

例如：

function onFormSubmit(e) {
var outputSheet = e.source.getSheetByName("Output");
var outputColumnA = outputSheet.getRange("A:A").getValues();
var outputA1 = outputColumnA[0][0];
console.log(outputA1);
}