I'm currently creating a script that takes information from a google sheet that is populated by forms, and then inserts it into a doc template I've created. When I first created the script and tested it with sample questions, everything worked as I wanted it to, so I started building up the form and the doc template that the data would ultimately go to. For reference, I used this tutorial: https://youtu.be/ziLtj5-_D7c
The first issue I ran into is that I wanted the doc to include sheet data that would be created by a formula I've added to the sheet. New form submissions created new lines in the sheet that the formula would not automatically be applied to. I got around this by creating a new tab in the sheet and roughly duplicating the response tab using =query. I created new columns in the second tab of the sheet and applied the required formulas to those. Now, when I submit a new form, the responses go the first tab, the second tab is filled to match, and I get the additional information that I need from the formulas there. I did this by following this tutorial: https://youtu.be/rSBb84QIjwg
What I don't know how to do is edit the script so that it takes information from both tabs to insert into the doc template. For example, the following bit works fine to take data from the first tab and insert it into the doc:
function autofillGoogleDocFromForm(e) {
var timestamp = e.values[0];
var invoicenumber = e.values[1];
var datesubmitted = e.values[2];
var pdatebegin = e.values[3];
var pdateend = e.values[4];
var invoicesalary = e.values[5];
var invoicetravel = e.values[6];
var invoicesupplies = e.values[7];
var invoiceother = e.values[8];
var invoiceindirect = e.values[9];
What I want to do is add additional variables that would come from the second tab. Column K on the first tab would be [10], how do I reference Column K from the second tab?
EDIT: here's the complete script that did everything I needed it to do from the first sheet, prior to asking this question:
function autofillGoogleDocFromForm(e) {
var timestamp = e.values[0];
var invoicenumber = e.values[1];
var datesubmitted = e.values[2];
var pdatebegin = e.values[3];
var pdateend = e.values[4];
var invoicesalary = e.values[5];
var invoicetravel = e.values[6];
var invoicesupplies = e.values[7];
var invoiceother = e.values[8];
var invoiceindirect = e.values[9];
var templateFile = DriveApp.getFileById("1CRMLGw-dq8vsQASkFDuOVvg03DgqYInKeTscGu9jXn4");
var templateResponseFolder = DriveApp.getFolderById("1lCLWLKochBNW1uvG9hJkjZtu7EWNXhx6");
var copy = templateFile.makeCopy('012 Invoice Number: ' + invoicenumber, templateResponseFolder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText("{{Invoice number}}", invoicenumber);
body.replaceText("{{Date submitted}}", datesubmitted);
body.replaceText("{{Beginning date}}", pdatebegin);
body.replaceText("{{Ending date}}", pdateend);
body.replaceText("{{Invoice salary}}", invoicesalary);
body.replaceText("{{Invoice travel}}", invoicetravel);
body.replaceText("{{Invoice supplies}}", invoicesupplies);
body.replaceText("{{Invoice other}}", invoiceother);
body.replaceText("{{Invoice indirect}}", invoiceindirect);
doc.saveAndClose();
}
And here's my revised attempt after reading the comments here:
function autofillGoogleDocFromForm(e) {
var secondTab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Totals");
var timestamp = e.values[0];
var invoicenumber = e.values[1];
var datesubmitted = e.values[2];
var pdatebegin = e.values[3];
var pdateend = e.values[4];
var invoicesalary = e.values[5];
var invoicetravel = e.values[6];
var invoicesupplies = e.values[7];
var invoiceother = e.values[8];
var invoiceindirect = e.values[9];
var invoiceamount = secondTab.getRange("K:K").getValues();
var salarytotal = secondTab.getRange("L:L").getValues();
var traveltotal = secondTab.getRange("M:M").getValues();
var suppliestotal = secondTab.getRange("N:N").getValues();
var othertotal = secondTab.getRange("O:O").getValues();
var indirecttotal = secondTab.getRange("P:P").getValues();
var totaltodate = secondTab.getRange("Q:Q").getValues();
var templateFile = DriveApp.getFileById("1CRMLGw-dq8vsQASkFDuOVvg03DgqYInKeTscGu9jXn4");
var templateResponseFolder = DriveApp.getFolderById("1lCLWLKochBNW1uvG9hJkjZtu7EWNXhx6");
var copy = templateFile.makeCopy('012 Invoice Number: ' + invoicenumber, templateResponseFolder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText("{{Invoice number}}", invoicenumber);
body.replaceText("{{Date submitted}}", datesubmitted);
body.replaceText("{{Beginning date}}", pdatebegin);
body.replaceText("{{Ending date}}", pdateend);
body.replaceText("{{Invoice salary}}", invoicesalary);
body.replaceText("{{Invoice travel}}", invoicetravel);
body.replaceText("{{Invoice supplies}}", invoicesupplies);
body.replaceText("{{Invoice other}}", invoiceother);
body.replaceText("{{Invoice indirect}}", invoiceindirect);
body.replaceText("{{Invoice amount}}", invoiceamount);
body.replaceText("{{Salary total}}", salarytotal);
body.replaceText("{{Travel total}}", traveltotal);
body.replaceText("{{Supplies total}}", suppliestotal);
body.replaceText("{{Other total}}", othertotal);
body.replaceText("{{Indirect total}}", indirecttotal);
body.replaceText("{{Total outlays to date}}", totaltodate);
doc.saveAndClose();
}
This second version is definitely pulling data from the second sheet, which is great, but it's taking data from every cell in the column, and what I need is data from only the bottom cell of the column, which is created when the new row is added with each new form submission.
