google-apps-scriptgoogle-sheetsgoogle-drive-apiurlfetchhttp-status-code-429

Truncated Server Response while generating PDFs


I have a script that will export all of my sheets into separate PDF documents into the folder named Invoices, however it is creating 6 documents and after that I'm getting the error code below, this has been tried on a Gsuite account also and I'm getting the same error:

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

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();
  
  var folders = DriveApp.getFoldersByName('Invoices');  
  var folder = folders.hasNext() ? folders.next() : parents.next(); 

  var sheets = ss.getSheets();
  for (var i=0; i<sheets.length; i++) {
    var sheet = sheets[i];
    if (optSheetId && optSheetId !== sheet.getSheetId()) continue; 
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
      + '&gid=' + sheet.getSheetId()   //the sheet's Id
      // following parameters are optional...
      + '&size=letter'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw=true'        // fit to width, false for actual size
      + '&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 valor = sheet.getRange('D5').getValue();  // Modified
    var blob = response.getBlob().setName(valor + '.pdf');
    folder.createFile(blob);
  }
}

Any thoughts?


Solution

  • See my very long answer with a more complicated work-around if execution time is an important factor here

    The easier work around:

    At the bottom of my answer in the above link, you'll see a reference to the number of seconds that I was able to delay the recursive part of the function to extract any number of PDFs from a sheet indefinitely at 8 seconds, so all you need to do is add the following to your code at the end of the for loop:

    Utilities.sleep(8000)
    

    The whole code would be:

    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();
      
      var folders = DriveApp.getFoldersByName('Invoices');  
      var folder = folders.hasNext() ? folders.next() : parents.next(); 
    
      var sheets = ss.getSheets();
      for (var i=0; i<sheets.length; i++) {
        var sheet = sheets[i];
        if (optSheetId && optSheetId !== sheet.getSheetId()) continue; 
        var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
          + '&gid=' + sheet.getSheetId()   //the sheet's Id
          // following parameters are optional...
          + '&size=letter'      // paper size
          + '&portrait=true'    // orientation, false for landscape
          + '&fitw=true'        // fit to width, false for actual size
          + '&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 valor = sheet.getRange('D5').getValue();  // Modified
        var blob = response.getBlob().setName(valor + '.pdf');
        folder.createFile(blob);
        Utilities.sleep(8000);
      }
    }