1

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);
}
Zehrazjp20
  • 51
  • 1
  • 9
  • 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 Jul 10 '15 at 16:08
  • See [How can I test a trigger function in GAS?](http://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas). – Mogsdad Jul 11 '15 at 03:39

1 Answers1

3

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.

Jesse Scherer
  • 1,492
  • 9
  • 26
  • The hardwired cell addresses are there for a reason: the form responses are put through a host of very complicated calculations and the resultant cells are those hardwired ones. Thanks so much for your answer though I will give it another go when I get back into the office on Monday! I hadn't thought about the fact that when you're not manually pressing "run", the spreadsheet isn't active anymore. Thanks so much jjjjoe :) – Zehrazjp20 Jul 10 '15 at 16:28
  • Oh, that's tough. In that case the most likely way to get around that would be to grab a reference to the spreadsheet by using its specific ID. – Jesse Scherer Jul 10 '15 at 18:52
  • Actually, better to back-track from `e`, by using `e.range.getSheet()` (for the sheet receiving form submissions) and `e.range.getSheet().getParent()` (for spreadsheet). Using the specific ID limits the portability of the code. Oh, and make sure you're not repeating the same service calls, because they are ssslllooowww. – Mogsdad Jul 11 '15 at 03:33
  • @Mogsdad very good point. I guess I forgot about that possibility because `e` doesn't contain the data we actually need in this context. – Jesse Scherer Jul 13 '15 at 03:56
  • jjjoe I have implemented all of your suggestions and now I'm getting trigger failure notification emails saying "invalid email" :/ I suppose the getValue() method returns an object so maybe i need to convert my userEmail variable to a string? – Zehrazjp20 Jul 15 '15 at 10:07
  • Can you post your current code? When you do so, tell us which line sends the "invalid email" too. – Jesse Scherer Jul 16 '15 at 01:49