1

Been tinkering with this script I found, but it's limited on what it can do. What I need is the below functionality but for it to a) run a batch of spreadsheets in a folder on the Google Drive, each of which would be sent to an email address (email is provided in cell A1 of each spreadsheet).
A trigger to run it once a month would be helpful.

/* Send Spreadsheet in an email as PDF, automatically */
function emailSpreadsheetAsPDF() {

// Send the PDF of the spreadsheet to this email address
var email = "amit@labnol.org"; 

// Get the currently active spreadsheet URL (link)
// Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");
var ss = SpreadsheetApp.getActiveSpreadsheet();

// Subject of email message
var subject = "PDF generated from spreadsheet " + ss.getName(); 

// Email Body can  be HTML too with your logo image - see ctrlq.org/html-mail
var body = "Install the <a href='http://www.labnol.org/email-sheet'>Email Spreadsheet add-on</a> for one-click conversion.";

// Base URL
var url = 
 "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

/* Specify PDF export parameters
From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
*/

var url_ext = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
+ '&size=letter'                       // paper size legal / letter / A4
+ '&portrait=false'                    // orientation, false for landscape
+ '&fitw=true&source=labnol'           // fit to page width, false for actual size
+ '&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=';                             // the sheet's Id

var token = ScriptApp.getOAuthToken();
var sheets = ss.getSheets(); 

//make an empty array to hold your fetched blobs  
var blobs = [];

for (var i=0; i<sheets.length; i++) {

// Convert individual worksheets to PDF
var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
  headers: {
    'Authorization': 'Bearer ' +  token
  }
});

//convert the response to a blob and store in our array
blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');

}

//create new blob that is a zip file containing our blob array
var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip'); 

//optional: save the file to the root folder of Google Drive
DriveApp.createFile(zipBlob);

// Define the scope
Logger.log("Storage Space used: " + DriveApp.getStorageUsed());

// If allowed to send emails, send the email with the PDF attachment
if (MailApp.getRemainingDailyQuota() > 0) 
  GmailApp.sendEmail(email, subject, body, {
    htmlBody: body,
    attachments:[zipBlob]     
  });  
}  

Now has been edited to become this...

function emailSpreadsheetAsPDF(id) {
 var ss = SpreadsheetApp.openById(id);
 var email = ss.getSheets()[0].getRange(1, 1).getValue();
 //  as in the script, starting with var subject = ...

// Subject of email message
var subject = "PDF generated from spreadsheet " + ss.getName(); 

// Email Body can  be HTML too with your logo image - see ctrlq.org/html-mail
var body = "Install the <a href='http://www.labnol.org/email-sheet'>Email 
Spreadsheet add-on</a> for one-click conversion.";

// Base URL
var url = 
"https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

/* Specify PDF export parameters
From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
*/

var url_ext = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
+ '&size=letter'                       // paper size legal / letter / A4
+ '&portrait=false'                    // orientation, false for landscape
+ '&fitw=true&source=labnol'           // fit to page width, false for actual size
+ '&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=';                             // the sheet's Id

var token = ScriptApp.getOAuthToken();
var sheets = ss.getSheets(); 

//make an empty array to hold your fetched blobs  
var blobs = [];

for (var i=0; i<sheets.length; i++) {

  // Convert individual worksheets to PDF
  var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
  headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  //convert the response to a blob and store in our array
  blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');

}

//create new blob that is a zip file containing our blob array
var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip'); 

//optional: save the file to the root folder of Google Drive
DriveApp.createFile(zipBlob);

// Define the scope
Logger.log("Storage Space used: " + DriveApp.getStorageUsed());

// If allowed to send emails, send the email with the PDF attachment
if (MailApp.getRemainingDailyQuota() > 0) 
  GmailApp.sendEmail(email, subject, body, {
    htmlBody: body,
    attachments:[zipBlob]     
  });  

function emailAllSpreadsheets() {
var folder = DriveApp.getFolderById("0B4zzmqQYDRm2bloyamxWVF85NjQ");
var files = folder.getFilesByType("application/vnd.google-apps.spreadsheet");
while (files.hasNext()) {
  var file = files.next();
  emailSpreadsheetAsPDF(file.getId());
}
}

}
1

First, the function would need to be slightly modified to accept spreadsheet Id as a parameter, and also to extract email from cell A1 of the first sheet in the spreadsheet.

function emailSpreadsheetAsPDF(id) {
   var ss = SpreadsheetApp.openById(id);
   var email = ss.getSheets()[0].getRange(1, 1).getValue();
   //  as in the script, starting with var subject = ...
}

Then you need a function that will get all spreadsheets in a folder and call the emailSpreadsheetAsPDF function for each of them. Here it is.

function emailAllSpreadsheets() {
  var folder = DriveApp.getFolderById("put id here, it's the last part of folder's URL");
  var files = folder.getFilesByType("application/vnd.google-apps.spreadsheet");
  while (files.hasNext()) {
    var file = files.next();
    emailSpreadsheetAsPDF(file.getId());
  }
}

To set a monthly trigger, go to Edit > Current project's triggers in the script editor, and add a trigger:

trigger

  • Am I on the right track with the edit above? – Bardo Aug 7 '17 at 4:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.