google apps script to create new pdf document of updated cells after form submission and email this pdf error message:

Question

Please bear with me as I am very much a beginner at this. I am trying to write a Google script which creates a new spreadsheet of the relevant data after a form is submitted, converts this to a PDF, and sends it as an email attachment to an email address which is submitted to the form. I am writing the code in a script which is bound to the sheet which performs some calculations on the data once it is submitted. The form is also bound to the sheet. I will paste my code below and here is a link to the sheet: https://docs.google.com/spreadsheets/d/1oQTeQFwl7IfWF8D17oK1Mutp4TXidQXUT4lv46ktuwM/edit?usp=sharing

I am trying to email the small green summary section on the sheet named INPUTS/OUTPUTS as a pdf.

The Template file "MyCalculations" is just an empty spreadsheet apart from one line saying "here are your calculation results". The summary section updates itself well and correctly after a form is submitted I am just having real trouble accessing the sheet because I can't code!

Here is the code so far:

function onFormSubmit(e){

//Variables
var userEmail = SpreadsheetApp.getActiveSheet().getRange("H24").getValue();
var totalOutstandingPrincipalDebt = SpreadsheetApp.getActiveSheet().getRange("G22").getValue();
var totalOutstandingInterest = SpreadsheetApp.getActiveSheet().getRange("H22").getValue();
var totalOutstandingCompensation = SpreadsheetApp.getActiveSheet().getRange("I22").getValue();
var dailyInterestRate = SpreadsheetApp.getActiveSheet().getRange("J22").getValue();
var grandTotal = SpreadsheetApp.getActiveSheet().getRange("G23").getValue();

var docTemplate = DriveApp.getFilesByName("MyCalculations");

//Template Info
var docName="Calculations";
var copyDoc= docTemplate.makeCopy();
var copyId = copyDoc.getId();
var copyBody = docTemplate.getActiveSection();

copyBody.getRange(2, 1).setValue("Total Outstanding Principal Debt");
copyBody.getRange(2, 2).setValue("Total Outstanding Interest");
copyBody.getRange(2, 3).setValue("Total Outstanding Compensation");
copyBody.getRange(2, 4).setValue("Grand Total");
copyBody.getRange(2, 5).setValue("Daily Interest Rate");
copyBody.getRange(3, 1).setValue(totalOutstandingPrincipalDebt);
copyBody.getRange(3, 2).setValue(totalOutstandingInterest);
copyBody.getRange(3, 3).setValue(totalOutstandingCompensation);
copyBody.getRange(3, 4).setValue(grandTotal);
copyBody.getRange(3, 5).setValue(dailyInterestRate);

//Save as PDF and send e-mail
var pdf = 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);

}

I also don't think that in this code, I have specified which sheet on my spreadsheet I am wanting to get the data from, but I don't know how to do that either!

Any help would be so so appreciated!

Answer 1

I managed to get this code working:

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

function onFormSubmit(e){

//Variables
var userEmail = SpreadsheetApp.getActiveSheet().getRange("H24").getValue();
var totalOutstandingPrincipalDebt =SpreadsheetApp.getActiveSheet().getRange("G22").getValue();
var totalOutstandingInterest = SpreadsheetApp.getActiveSheet().getRange("H22").getValue();
var totalOutstandingCompensation = SpreadsheetApp.getActiveSheet().getRange("I22").getValue();
var dailyInterestRate = SpreadsheetApp.getActiveSheet().getRange("J22").getValue();
var grandTotal = SpreadsheetApp.getActiveSheet().getRange("G23").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(0,'Total Outstanding Principal Debt: ' + totalOutstandingPrincipalDebt);
copyBody.insertParagraph(1,'Total Outstanding Interest: '+ totalOutstandingInterest );
copyBody.insertParagraph(2,'Total Outstanding Compensation: '+ totalOutstandingCompensation);
copyBody.insertParagraph(3,'Grand Total: ' + grandTotal);
copyBody.insertParagraph(4,'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);

}

Answer 2

var docTemplate = DriveApp.getFilesByName("MyCalculations");

getFilesByName returns a FileIterator not a Spreadsheet so you have to change that to:

var docTemplate = DriveApp.getFilesByName("MyCalculations").next().getAs(MimeType.GOOGLE_SHEETS);

Next, in var copyBody = docTemplate.getActiveSection(); there is no such method called getActiveSection change that to:

var copyBody = docTemplate.getActiveSheet();

Lastly, the line var pdf = getFileById(copyId).getAs("application/pdf");

should be

var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");

Implement those changes and let me know how it works. =)

Update

I fiddled around and as far as I can tell, DriveApp automatically converts almost all files to a PDF. So, as I understand it, you are using the MyCalculations spreadsheet as a template so that you can convert it into a PDF and send it.

We can accomplish this with a little bit of a roundabout way.

First, we create a new sheet in our original spreadsheet, and hide it from view so that the user can't see it.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var myCalculations = spreadsheet.insertSheet("MyCalculations",spreadsheet.getNumSheets());
myCalculations.hideSheet();

Secondly, we set all the values that you want. So change

copyBody.setRange ...

to

myCalculations.setRange ...

Thirdly, once we have all our data set. We are going create a temporary Spreadsheet to copy all our data into so that we may export it as a PDF and send it in an email

var tempSpreadsheet = SpreadsheetApp.create('Temporary');

Fourth, we copy our calculations page into the temporary spreadsheet.

myCalculations.copyTo(tempSpreadsheet);

Perfect! Now we just convert the temporary spreadsheet into a PDF and do a little clean-up. Be sure to note the change to the line where we delete the temporary spreadsheet. I get the id by just calling the getId() method

var pdf = tempSpreadsheet.getAs(MimeType.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(tempSpreadsheet.getId()).setTrashed(true);

That should do the trick. Comment how it works for you. =)