function printForward() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheets()[3];
// if you change the number, change it also in the parameters below
var shName = sh.getName()
var ssname = ss.getName();
var length = ssname.length;
var date = ssname.substring(0,length-15);
const body = "All, <br><br>Attached is the invoice for the week of "+ date+"."+"<br><br> Thank you, <br> Elizabeth";
sendSpreadsheetToPdf(3, shName, ss.getRange('Constants!T5').getValue(),"Invoices "+date, body, date);
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody, invcdate) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetId = spreadsheet.getId()
var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
var url_base = spreadsheet.getUrl().replace(/edit$/,'');
var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf
+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))
// following parameters are optional...
+ '&size=A4' // paper size
+ '&portrait=true' // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&sheetnames=true&printtitle=false&pagenumbers=true' //hide optional headers and footers
+ '&gridlines=false' // hide gridlines
+ '&fzr=false'; // do not repeat row headers (frozen rows) on each page
var options = {
headers: {
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
}
}
var response = UrlFetchApp.fetch(url_base + url_ext, options);
var blob = response.getBlob().setName("Forward Air Invoice "+invcdate+ '.pdf');
if (email) {
var mailOptions = {
attachments:blob, htmlBody:htmlbody
}
MailApp.sendEmail(
email,
subject,
"html content only",
mailOptions);
}
//save PDF
const nameFile = "Forward " + invcdate + ".pdf";
var forwardFolder = DriveApp.getFolderById(ID);
forwardFolder.createFile(blob); //HERE IS WHERE THE PDF FILE IS SAVED
I have the code above, it is supposed to email a pdf of a spreadsheet and then save a copy of the pdf in a folder. When I run the script, the email sends correctly and a pdf of the spreadsheet is saved to the folder. However, when the owner of the spreadsheet/script runs the script, an email sends, but an html file is attached not the pdf. So a bunch of text is sent, instead of the spreadsheet. I have no idea why this happens.
The attachments must be in an array. Maybe try a different configuration as follows:
MailApp.sendEmail(objectOfSettings);
Code:
var mailOptions = {};
var arrayOfAttachments = [];
if (blob) {
arrayOfAttachments.push(blob);
mailOptions.attachments = arrayOfAttachments;
}
if (htmlbody) {
mailOptions.htmlBody = htmlbody;
}
if (email) {
mailOptions.to = email;
mailOptions.subject = subject;
MailApp.sendEmail(mailOptions);
}
In order to use the FROM option, you need to make some changes in your Gmail settings. You will need to set up an alias email, AND/OR confirm an email address that you own. You can send an email from a non-Google email address, BUT you need to confirm that you own that email account. That confirmation process is started in your Gmail settings under the "Accounts OR Accounts and Import" tab. If you have a Workspace account, and you want to send an email as an alias, you will need to open the Workspace Admin Console,
https://admin.google.com/AdminHome
Go Back to the Main Admin Console Now do the following:
click on "Users" click on the Name of the user's account Add an alias. Here is a link to some Google help information.