时间戳

首页 > 解决方案 > 在 Google 表格中，我需要一个脚本来在表单提交上运行，但引用一个单独的表格而不是“表单响应”

google-apps-script - 在 Google 表格中，我需要一个脚本来在表单提交上运行，但引用一个单独的表格而不是“表单响应”

问题描述

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

这是一直以来的脚本，我尝试了几件事，但没有运气，总是从“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);
}

标签： google-apps-scriptgoogle-sheetsgoogle-forms

解决方案

要从特定工作表中获取值，您需要获取电子表格 ( 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);
}

推荐阅读