Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Apps Script: Using custom menu, save Google Spreadsheet as PDF with custom options (landscape/portrait, gridlines) in Google Drive and automatically email PDF as attachment to multiple recipients. Credit: produced in conjunction with Victor W. Yee.
// Create custom menu within Spreadsheet
function onOpen() {
var submenu = [{name:"Save and Email PDF", functionName:"spreadsheetToPDF"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu('Admin Functions', submenu);
}
// Start spreadsheetToPDF function
function spreadsheetToPDF(key) {
// Authentication stuff
var originalSpreadsheet = SpreadsheetApp.getActive();
var key = "--Enter Your Spreadsheet Key--"; // Need to check - how safe is it to include the key?
var oauthConfig = UrlFetchApp.addOAuthService("spreadsheets");
var scope = "https://spreadsheets.google.com/feeds"
oauthConfig.setConsumerKey("anonymous"); // Need to check - how safe is it to use anon/anon?
oauthConfig.setConsumerSecret("anonymous");
oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
oauthConfig.setAuthorizationUrl("https://accounts.google.com/OAuthAuthorizeToken");
oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
var requestData = {
"oAuthServiceName": "spreadsheets",
"oAuthUseToken": "always",
};
// Get the Date for this data for unique file name
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]; // Pull the first sheet
var range = sheet.getRange('AJ1'); // Pull cell AJ1 ("selected date" in my case) from the first sheet
var date = range.getValue(); // Get cell AJ1's value
var formattedDate = Utilities.formatDate(date, "PST", "MM-dd-yyyy"); // Reformat date otherwise it comes with lots of extra numbers and timezone
var name = DocsList.getFileById(key).getName()+formattedDate+".pdf"; // Name the PDF with Spreadsheet name and appended Date for the data
// Create the PDF using this hack with special option variables in the URL
// As of 2/4/14 this seems to be the only way to export PDF with custom options (landscape, no gridlines, etc)
// exportFormat = pdf / csv / xls / xlsx
// gridlines = true / false
// printtitle = true (1) / false (0)
// size = legal / letter/ A4 (according to: http://goo.gl/nPrfdj, but doesn't seem to work?? letter only)
// fzr (repeat frozen rows) = true / false
// portrait = true (1) / false (0)
// fitw (fit to page width) = true (1) / false (0)
// Set its name with the formatted Date
var pdf = UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+key+"&exportFormat=pdf&gridlines=true&printtitle=0&size=legal&fzr=true&portrait=1&fitw=true", requestData).getBlob().setName(name);
// Save file to specific folder in Drive
var folder = DocsList.getFolder('--Name of Your Folder--');
folder.createFile(pdf);
// I need to find the file now in order to email it
var pdfId = folder.find(name)[0].getId(); /// Get the new file ID
Logger.log(pdfId);
// Customize my email
var message = "Hello,\n\nPlease find attached the results for " + formattedDate + "." + "\n\nThank you!";
var subject = "Results for" + " " + formattedDate; // Pull in the date so notification emails are unique
var emailTo = "--Enter Comma-Separated List of Emails--";
var pdfFullName = "Results_" + formattedDate + ".pdf";
var file = DriveApp.getFileById(pdfId);
// Send the email with attachment
MailApp.sendEmail(emailTo, subject, message, {attachments:[file.getAs(MimeType.PDF)]});
}
@wagnerpk

This comment has been minimized.

Show comment
Hide comment
@wagnerpk

wagnerpk Oct 29, 2014

This is not working. I'm trying in Google Apps. The menu was created, but, when I click in the action, only shows a message 'script in execution (dismiss)' and nothing more happens. The email aren't get sending. Is anything else to do?

This is not working. I'm trying in Google Apps. The menu was created, but, when I click in the action, only shows a message 'script in execution (dismiss)' and nothing more happens. The email aren't get sending. Is anything else to do?

@wagnerpk

This comment has been minimized.

Show comment
Hide comment
@wagnerpk

wagnerpk Oct 29, 2014

Wich format is the folder name in "var folder = DocsList.getFolder('--Name of Your Folder--');"?

Wich format is the folder name in "var folder = DocsList.getFolder('--Name of Your Folder--');"?

@donle6

This comment has been minimized.

Show comment
Hide comment
@donle6

donle6 Jul 21, 2017

Can you edit this to export on active selected range? Can you show me where the edits need to be made.

donle6 commented Jul 21, 2017

Can you edit this to export on active selected range? Can you show me where the edits need to be made.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment