Run script across multiple spreadsheets (googlesheet)

Question

I'm currently running a script that takes a sheet, converts it into PDF and emails it out to an email address nightly. I am doing this across three separate spreadsheets. Instead of it running separately on each spreadsheet, I wanted to see if there was a to call each spreadsheet, and run the script once and attach all the PDFs into one email. It works great by itself, but I feel like it would make sense if I can have one spreadsheet run the function once and incorporate the other spreadsheets as well. Any direction would be helpful Thank you!

The script I am using is as follows

function PDFtoEmail() {
  var email = "[email protected]"; 
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     SpreadsheetApp.setActiveSheet(ss.getSheetByName('Schedule 1'));
     var sheet = ss.getActiveSheet();
  var subject = "Nightly Scheduled PDF";
  var body = "";
  var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());
  var url_ext = 'exportFormat=pdf&format=pdf'
  + '&size=letter'   
  + '&portrait=true'
  + '&fitw=true&source=labnol'    
  + '&sheetnames=false&printtitle=false' 
  + '&pagenumbers=false&gridlines=false' 
  + '&fzr=true'
  + '&gid=';  

  var token = ScriptApp.getOAuthToken();
  var sheets = ss.getSheets(); 
  var PDFtoEmail = [];
   for(var i = 0; i < 3; i++){
      var response = UrlFetchApp.fetch(url + url_ext +    sheets[i].getSheetId(), {
headers: {
  'Authorization': 'Bearer ' +  token
}
});
   PDFtoEmail[i] = response.getBlob().setName("Schedule.pdf");
}
if (MailApp.getRemainingDailyQuota() > 0) 
GmailApp.sendEmail(email, subject, body, {
  attachments:PDFtoEmail,
});

}

Or have a single Sheet file do the combinations for you and odd from just that file. — JSDBroughton
Great. I was able to create a separate Apps Script and called all 3 spreadsheets. Thank you! — User125

Jared Pinkham Jared Pinkham · Accepted Answer · 2016-05-13T00:06:37

SpreadsheetApp.openByUrl() can open (for use by the script, they don't open in browser window) spreadsheets other than the one that is currently active. openByID is equally valid.

var sheets = [firstsheeturl, secondsheeturl,thirdsheeturl];
   for(var i = 0; i < sheets.length; i++){
     var ss = SpreadsheetApp.openByUrl(sheets[i]);
     //code to do on all three sheets
   }

Run script across multiple spreadsheets (googlesheet)

1 Answers