Download all sheets to PDF

user_16873230 0
Viewed 37

I found the code below which does exactly what I want but only for a particular sheet. I want to download all the active sheets.

function PDF() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetId = ss.getSheetByName("Sheet 1").getSheetId(); 
  var url = "https://docs.google.com/a/mydomain.org/spreadsheets/d/" + ss.getId() + "/export?exportFormat=pdf&gid=" + sheetId + "&access_token=" + ScriptApp.getOAuthToken();
  var str = '<input type="button" value="Download" onClick="location.href=\'' + url + '\'" >';
  var html = HtmlService.createHtmlOutput(str);
  SpreadsheetApp.getUi().showModalDialog(html, "Click the button to download");
}

Any helpt would be great!

google-apps-script google-sheets
1 Answers
  • Use getSheets() to retrieve all sheets of a spreadsheet
  • Use isSheetHidden() to determine either a sheet is hidden or not
  • For all sheets that are not hidden, run the same request as in your intial code - iterating with a loop, e.g. forEach()

Sample

function PDF() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  allSheets.forEach(function(sheet){
    if(!sheet.isSheetHidden()){
      var sheetId = sheet.getSheetId(); 
      var url = "https://docs.google.com/a/mydomain.org/spreadsheets/d/" + ss.getId() + "/export?exportFormat=pdf&gid=" + sheetId + "&access_token=" + ScriptApp.getOAuthToken();
      var str = '<input type="button" value="Download" onClick="location.href=\'' + url + '\'" >';
      var html = HtmlService.createHtmlOutput(str);
    SpreadsheetApp.getUi().showModalDialog(html, "Click the button to download");
    }
  })
}
user_115997890
Related