First, let me say that I'm not well versed with app scripts and have been trying to leverage hours to reference past examples and content threads within this site that has been wonderful. This is probably a silly error on my part.
Problem Statement: Want to obtain a value from a sheet (InvoiceTemplate1 as an example) and then define it as a variable that can be included in my html file that can we consumed in a system generated email.
Current Status Have an existing app script that will convert a sheet to PDF and then emails a recipient based on a reference email address. The email uses a hmtl file (Template1.html). The email is well formatted but I would like to consume the value of 'message1'. I'm probably not using the getValue function correctly.
function getEmailHtml() {
var htmlTemplate = HtmlService.createTemplateFromFile("Template1.html");
htmlTemplate.message1 =
SpreadsheetApp.getActive().getSheetByName('InvoiceTemplate1').
getRange('M11').getValue();
var htmlBody = htmlTemplate.evaluate().getContent();
return htmlBody;
}
function emailSpreadsheetAsPDF1() {
DocumentApp.getActiveDocument();
DriveApp.getFiles();
var htmlBody = getEmailHtml();
// This is the link to my spreadsheet with the Form responses and the Invoice Template sheets // replace the text in the link between "d/" and "/edit" const ss = SpreadsheetApp.openByUrl("my sheet");
// Obtain the email address from the cell "Q14" from the "InvoiceTemplate1" sheet // Change the reference of the cell or the name of the sheet if it is different const value = ss.getSheetByName("InvoiceTemplate1").getRange("Q14").getValue(); const email = value.toString();
//Display the hidden desired sheet - Template1 - that will be used to generate the PDF
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('D13').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Template1'), true);
spreadsheet.getRange('A3').activate()
SpreadsheetApp.getActive().getSheetByName('Template1').showSheet();
// Subject of the email message const subject = 'New Invoice: ' + ss.getSheetByName("InvoiceTemplate1").getRange("M11").getValue().toString() + ' ' + ss.getSheetByName("InvoiceTemplate1").getRange("C15").getValue().toString()
// Again, the URL to your spreadsheet but now with "/export" at the end // Change it to the link of your spreadsheet, but leave the "/export" const url = 'my sheet / export?';
const exportOptions =
'exportFormat=pdf&format=pdf' + // export as pdf
'&size=letter' + // paper size letter / You can use A4 or legal
'&portrait=true' + // orientation portal, use false for landscape
'&fitw=false' + // fit to page width false, to get the actual size
'&top_margin=0.10&bottom_margin=0.00&left_margin=0.33&right_margin=0.00' +
'&sheetnames=false&printtitle=false' + // hide optional headers and footers
'&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
'&fzr=false' + // do not repeat row headers (frozen rows) on each page
'&gid=ID'; // the sheet's Id. Change it to your sheet ID.
// You can find the sheet ID in the link bar.
// Select the sheet that you want to print and check the link,
// the gid number of the sheet is on the end of your link.
var params = {method:"GET",headers:{"authorization":"Bearer "+
ScriptApp.getOAuthToken()}};
var templ = HtmlService.createTemplateFromFile('Template1');
// Generate the PDF file
var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
// Send the PDF file as an attachement
GmailApp.sendEmail(email, subject, body, {
htmlBody: htmlBody,
attachments: [{
fileName: "Invoice No.: " +
ss.getSheetByName("InvoiceTemplate1").getRange("M11").getValue().toString() + ".pdf",
content: response.getBytes(),
mimeType: "application/pdf"
}]
});
