Created
February 5, 2014 21:10
-
-
Save hisnipes/8833162 to your computer and use it in GitHub Desktop.
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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)]}); | |
} |
Wich format is the folder name in "var folder = DocsList.getFolder('--Name of Your Folder--');"?
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
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?