0

My script is looping through a list, at each index it is saving a sheet in my workbook as a PDF, then saving it to my google drive folder. There are 7 people in the list. The problem is that my script throws a 429 Error after the 6th PDF is exported. I am trying to figure out how to solve this error but I am quite new to google scripting so I need some help in the right direction.

Error Image

function ToPortrait() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var validation_sheet = ss.getSheetByName('ValidDSM');
  var lastRow = validation_sheet.getLastRow();
  var inputs = ss.getSheetByName('ValidDSM').getRange('A2:A'+lastRow).getValues();
  var sheet2 = ss.getSheetByName('DSM');
  var tab = ss.getSheetByName('DSMD');

  var url = ss.getUrl().replace(/edit$/,'');
  var sheets = ss.getSheets();
  var optSheetId = 1123194386;

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


  // Loop through List of 7
  for (var i = 0; i < inputs.length; i++){
    sheet2.getRange('B3').setValue(inputs[i][0]);
    SpreadsheetApp.flush();
    Utilities.sleep(500);

    // 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; 

    //additional parameters for exporting the sheet as a pdf
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
        + '&gid=' + tab.getSheetId()   //the sheet's Id
        // following parameters are optional...
        + '&size=letter'      // paper size
        + '&portrait=false'    // 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


     // Get folder containing spreadsheet, for later export
    var name = sheet2.getRange('B3').getValue();    
    var folderID = "1IsffOYYQOHZJKggUzRTntfjmJKN0F6ga"; // Folder id to save in a folder.
    var folder = DriveApp.getFolderById(folderID);
    var response = UrlFetchApp.fetch(url + url_ext, options)
     var blob = response.getBlob().setName(name);

    //save the pdf to drive
    folder.createFile(blob);


}
}

I expect all 7 PDFs to be exported but only 6 are.

Rubén
  • 34,714
  • 9
  • 70
  • 166
P.Solo
  • 1
  • 2
  • 1
    You should use the `muteHttpExceptions` option. If you use it you will be able to extract a meaningful error message. How to use it is discussed in [this SO question](https://stackoverflow.com/questions/11718674/how-to-catch-urlfetchapp-fetch-exception/11729130). – Casper Jun 21 '19 at 12:51
  • Thanks! I used this. – P.Solo Jun 21 '19 at 13:15
  • Error 429 means project was not found, so there is an issue on looking for a particular file. Also, I see you begin the inputs range at A2, in spreadsheets and apps scripts, the ranges (arrays), begin their indexing at 0, while the rows/columns begin at 1, so they do not match as well. also, using `'A2:A'+lastRow` will get you every cell, populated or not, so if you have 100 empty rows under your last value it will return an array with your data and then 100 "" entries. – AMolina Jun 21 '19 at 13:23
  • thanks! I changed the Utilities.sleep to Utilities.sleep(5000) and it seems to be working now... – P.Solo Jun 21 '19 at 15:09
  • I did another test. Using this method, the maximum number of pdfs I am able to create is 13. Then i am getting the same 429 error like before. This leads me to believe it is a maximum capacity error and maybe not a "project not found error". Still trying to get to the bottom of it.......... – P.Solo Jun 21 '19 at 15:20
  • @Casper I meant to say I will use this. I am not exactly sure where to add the muteHttpExceptions. I tried to add it in the options var, but it wasnt allowing me to. – P.Solo Jun 21 '19 at 15:23
  • @P.Solo `options` is the place for it; they are your advanced parameters for `fetch`. Would you please edit your question to show how you included `muteHttpExceptions` in options. – Tedinoz Jun 23 '19 at 04:33
  • I believe your code is making too many requests, I would suggest increasing the sleep time to ensure you don't overdo it. According to [this documentation](https://developers.google.com/docs/api/limits) 60 write requests in 60 seconds would be easy to get to with your code, so a longer wait would help you. This would be especially true if your test with just the 7 users worked well. – AMolina Jun 25 '19 at 10:13
  • Does this answer your question? [Creating Multiple Google Sheets PDFs throws 429 error](https://stackoverflow.com/questions/47648338/creating-multiple-google-sheets-pdfs-throws-429-error) – Rubén Jul 11 '20 at 04:50

0 Answers0