Google-sheets – Batch convert Google sheets in a specific directory to PDF and email

google sheetsgoogle-apps-script

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());
}
}

}

Best Answer

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