Google Apps Script - EXPORT all sheets INDIVIDUALLY to PDF-CodePudding

I've adatped the following script to my demands, but for some reason it always terminates after the 5th or 6th sheet of my spreadsheet, no matter what the content of the sheet actually is...can anyone spot an error or improve the script generally to get it working stable?

This is the error msg I'm receiving:

Fehler
Exception: Request failed for https://docs.google.com returned code 429. Truncated server response: <meta name="viewport" ... (use muteHttpExceptions option to examine full response) savePDFs @ savePDFs.gs:59

function savePDFs( optSSId, optSheetId ) {

  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();
  var url = ss.getUrl().replace(/edit$/,'');
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }
    // Get array of all sheets in spreadsheet
  var sheets = ss.getSheets();
  
  // Loop through all sheets, generating PDF files.
  for (var i=0; i<sheets.length; i  ) {
    var sheet = sheets[i];
    
    // If provided a optSheetId, only save it.
    if (optSheetId && optSheetId !== sheet.getSheetId()) continue; 
    
    //export parameters
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
          '&gid='   sheet.getSheetId()   //the sheet's Id
        // following parameters are optional...
          '&size=A4'      // paper size
          '&portrait=true'    // orientation, false for landscape
          '&scale=4'          // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
          '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
          '&gridlines=false'  // hide gridlines
          '&fzr=false';       // do not repeat row headers (frozen rows) on each page

    var options = {
      headers: {
        'Authorization': 'Bearer '    ScriptApp.getOAuthToken()
      }
    }

    var response = UrlFetchApp.fetch(url   url_ext, options);
    
    var blob = response.getBlob().setName(ss.getName()   ' - '   sheet.getName()   '.pdf');
    //the pdf is named according to the sheet - this is important for me!

  }
}

CodePudding user response：

When I saw your script, I'm worried the following situations.

Continuous request using UrlFetchApp.fetch.
The hidden sheets might be included in the Spreadsheet.

When these points are reflected in your script, it becomes as follows.

Modified script:

function savePDFs(optSSId, optSheetId) {
  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();
  var url = ss.getUrl().replace(/edit$/, '');
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  } else {
    folder = DriveApp.getRootFolder();
  }
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i  ) {
    var sheet = sheets[i];

    var isSheetHidden = sheet.isSheetHidden(); // <--- Added
    if (isSheetHidden) continue; // <--- Added

    if (optSheetId && optSheetId !== sheet.getSheetId()) continue;
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
        '&gid='   sheet.getSheetId()   //the sheet's Id
        '&size=A4'      // paper size
        '&portrait=true'    // orientation, false for landscape
        '&scale=4'          // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
        '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
        '&gridlines=false'  // hide gridlines
        '&fzr=false';       // do not repeat row headers (frozen rows) on each page
    var options = { muteHttpExceptions: true, headers: { 'Authorization': 'Bearer '   ScriptApp.getOAuthToken() } }
    var response = UrlFetchApp.fetch(url   url_ext, options);
    var blob = response.getBlob().setName(ss.getName()   ' - '   sheet.getName()   '.pdf');

    Utilities.sleep(5000); // <--- Added

  }
}

Please adjust 5000 of Utilities.sleep(5000) for your actual situation by testing this script.

Modified script:

References: