I work in a school and we are using a variant of TJHouston’s script to manage invoices sent out to people who use the autoshop’s service. The guy who set it all up retired and he made a copy of all of his documents related to the invoicing system. The process consists of:
- A google form
- the sheet that is attached to the form
- a document that the script uses as a template to fill out and mail to ‘customers’
Here is the beginning of the script (I’ve included up to line 16, it fails consistently at line 13). I’ve numbered them manually here for clarity.
1// TJ Houston tjhouston.com ([email protected])
2// Bus Conduct Report
3// Get template from Google Docs and name it
4 var docTemplate = "1r1jI_7s6KU5vRwZGjeQnzfGvizmgOq2IiY2z8KGxxMY"; // *** replace with your template ID ***
5 var docName = "SCHSAutoShopInvoice";
6
7// When Form Gets submitted
8 function onFormSubmit(e) {
9 var sheet = SpreadsheetApp.getActiveSheet();
10 var row = SpreadsheetApp.getActiveSheet().getLastRow();
11
12 //Get information from form and set as variables
13 var keyDate = e.values[0];
14 var keyName = e.values[1];
15 var keybName = e.values[26];
16 var keyMail = e.values[2];
After having put in the document template ID and (later on in the script) the folder it’s supposed to dump to, the script keeps failing at line 13.
var keyDate = e.values[0];
It’s like it isn’t reading the spreadsheet’s columns as the variables. I suppose it could be failing later on in the script and it’s just throwing the error at line 13. Here’s a screenie of the beginning few columns of the sheet the form fills
enter image description here
The odd thing is using the forums in his folder system worked fine, just once you copy them all and the script seems to fail. It’s like the script can’t find the sheet. Any help would be greatly appreciated.
I’ve ensured all of the google ID’s are correct, I moved files out of the organization’s shared folders and into my own and adjusted permissions in case it had something to do with that (it didn’t help). Nothing I adjust seems to fix this error:
enter image description here