onFormSubmit trigger not working in google script

Question

I wonder if someone can help me.. I've written this code which is supposed to carry out the following workflow:

user fills in the form, form responses are recorded in excel sheet and various calculations take place, final values are appended into a template pdf, and this pdf is sent via email to the user.

The script does do all of that but only when I manually click "run", whereas I want it to execute whenever a form is submitted and I can't understand why it doesn't.

I would add a screenshot of my trigger but I can't as I don't have 10 reputation yet; but my trigger is set up as follows:

Run: onFormSubmit Events: From spreadsheet, On form submit

I'll paste my code below, does anyone have any ideas as to why it might not be working? Any help would be hugely appreciated.

//Set out global variables
var docTemplate = ("1Ff3SfcXQyGeCe8-Y24l4EUMU7P9TsgREsAYO9W6RE2o");
var docName=("Calculations");

function onFormSubmit(e){

//Variables
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName("Sheet3"));
var totalOutstandingPrincipalDebt = SpreadsheetApp.getActiveSheet().getRange("G25").getValue();
var totalOutstandingInterest = SpreadsheetApp.getActiveSheet().getRange("H25").getValue();
var totalOutstandingCompensation = SpreadsheetApp.getActiveSheet().getRange("I25").getValue();
var dailyInterestRate = SpreadsheetApp.getActiveSheet().getRange("J25").getValue();
var grandTotal = SpreadsheetApp.getActiveSheet().getRange("K25").getValue();
var userEmail = SpreadsheetApp.getActiveSheet().getRange("H24").getValue();


//Template Info

var copyId=DriveApp.getFileById(docTemplate).makeCopy(docName+' for '+userEmail).getId();
var copyDoc = DocumentApp.openById(copyId);
var copyBody = copyDoc.getActiveSection();

//Putting the data into the file
copyBody.insertParagraph(1,'Total Outstanding Principal Debt: £' + totalOutstandingPrincipalDebt);
copyBody.insertParagraph(2,'Total Outstanding Interest: £'+ totalOutstandingInterest );
copyBody.insertParagraph(3,'Total Outstanding Compensation: £'+ totalOutstandingCompensation);
copyBody.insertParagraph(4,'Grand Total: £' + grandTotal);
copyBody.insertParagraph(5,'Daily Interest Rate: £'+ dailyInterestRate);
copyDoc.saveAndClose();

//email pdf document as attachment 
var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");
var subject = "Calculations";
var body = "Thank you very much for using our online calculator. Please find your results attached.";
MailApp.sendEmail(userEmail, subject, body, {htmlBody: body, attachments: pdf});
//Deletes temporary Document
DriveApp.getFileById(copyId).setTrashed(true);
}

You'll need to go through a process of elimination. Create another function, that does nothing but output something to the log. Logger.log('it ran!); Create a new trigger for that new function, then submit the form, and look in the LOGS under the VIEW menu. If that works, start transferring lines of code into the new function, and see if it stops working at some point. If the new trigger isn't working at all, then it has nothing to do with the code. — Alan Wells

Jesse Scherer Jesse Scherer · Accepted Answer · 2015-07-10T16:20:35

It looks like instead of using the values attribute of the form submit event you are trying to look directly at the spreadsheet tab that gets values written to it. Think of it this way: you could attach this script to a Form instead of a spreadsheet and have it still work. At the time a form submission happens, the event object e has everything you need.

This is a problem because you have hardwired cell addresses, but the form will keep writing more rows. This means you won't see new rows as they accumulate.

But the big problem is that you're looking at that tab using getActiveSpreadsheet(). When a user submits a form response, there is no "active spreadsheet." The "active" thing was the form. So, in order to get the data that was submitted, you'll need to look in e.values -- this will contain the row of data you're currently trying to get to in lines 9-14.

btw, the reason it works when run manually is because "active spreadsheet" means something. As soon as you walk away, that spreadsheet isn't active.

EDIT:

Zehrazjp20 points out that they are using the spreadsheet for computation, not just reading raw values as from the Form Submit event. In this case, the best way is to replace:

var ss = SpreadsheetApp.getActiveSpreadsheet();`

with

var ssID = 'abcdefghijklmnop';
var ss = SpreadsheetApp.openById(ssID);

...using your spreadsheet's real ID, of course.

onFormSubmit trigger not working in google script

1 Answers