CODEWITHSUNDEEP
javascriptformsgoogle-apps-scripttriggersgoogle-sheets
Zehrazjp20
Zehrazjp20

Reputation: 51

onFormSubmit trigger not working in google script

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);
}

Upvotes: 1

Views: 3087

Answers (1)

Jesse Scherer
Jesse Scherer

Reputation: 1512

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.

Upvotes: 3

Related Questions