In Google sheets, I need a script to run on a form submit, but reference a separate sheet other than "form responses"

Question

I am trying to get a script to look at a named sheet in the workbook when a form is submitted. I used this script for a while and now need to add some calculations to the data that gets submitted. I have the form response data going to a sheet to add some calculations, then gets put in a doc and emailed out. The email doc function works great, just can not got the script to look at the data on the "Output" sheet vs. the "Form_Responses" sheet.

This is the script as it has been, I have tried several things but no luck, always pulls from "Form_Responses". I removed my trial code for clarity.

Any thoughts?

Thanks!

My code :

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

To get a value from a specific sheet, you need to get the spreadsheet (e.source), the sheet (getSheetByName()), the range (getRange()), and then the value (getValue()).

For example:

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