Error when creating multiple PDFs in Google Apps Script

Question

I've created a function that saves a spreadsheet (sheet) as PDF to a specified folder. The function works great but when I run it in multiple times (I need to do it 20 times), I get an error after the 7th, 8th or 9th run. The error is 429. It doesn't give me a whole lot of info and I can't seem to find what the error is and how to correct. I've tried adding a Utilities.sleep(xxx) and it does work when I do a 5 second sleep (but not when it's less than 5 seconds)!

Here's my code (with Utilities.sleep):

/**
 * Creates a PDF file 
 *
 * 2019-12-17 Simon: Created
 *
 * @param {?} token                ScriptApp.getOAuthToken();
 * @param {?} spreadsheet          Spreadsheet (SpreadsheetApp.getActiveSpreadsheet())
 * @param {string} sheetName       Name of the sheet to print
 * @param {string} pdfName         Name of the pdf file (excluding .pdf)
 * @param {string} folder          Folder to save in
 * @param {string} portrait        true=portrait, false=landscape
 * @param {number} scale           1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
 * @param {number} margins         In inches. Dot as decimal separator, e.g. '0.2'
 * @param {string} range           Optional. E.g. 'D4:AX74'
 */ 
function savePdf(spreadsheet, sheetName, pdfName, folder, portrait, scale, margins, range) { 
  var rangeUse = (range ? '&range=' + range : '');
  var sheetId = spreadsheet.getSheetByName(sheetName).getSheetId();
  var url_base = spreadsheetId.getUrl().replace(/edit$/,'');
  var url_ext = 'export?'
  + '&gid=' + sheetId  
  + rangeUse
  + '&format=pdf'                   // export format
  + '&size=a4'                      // A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
  + '&portrait=' + portrait         // true = Potrait / false= Landscape
  + '&scale=' + scale               // 1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
  + '&top_margin=' + margins        // all four margins must be set!
  + '&bottom_margin=' + margins     // all four margins must be set!
  + '&left_margin=' + margins       // all four margins must be set!
  + '&right_margin=' + margins      // all four margins must be set!
  + '&gridlines=false'              // true/false
  + '&printnotes=false'             // true/false
  + '&pageorder=2'                  // 1 = Down, then over -- 2 = Over, then down
  + '&horizontal_alignment=CENTER'  // LEFT/CENTER/RIGHT
  + '&vertical_alignment=MIDDLE'    // TOP/MIDDLE/BOTTOM
  + '&printtitle=false'             // print title --true/false
  + '&sheetnames=false'             // print sheet names -- true/false
  + '&fzr=true'                     // repeat row headers (frozen rows) on each page -- true/false
  + '&fzc=true'                     // repeat column headers (frozen columns) on each page -- true/false
  + '&attachment=false'             // true/false
  var token = ScriptApp.getOAuthToken();
  var url_options = {headers: {'Authorization': 'Bearer ' + token, 'muteHttpExceptions': true,}};
  Utilities.sleep(5000);
  var response = UrlFetchApp.fetch(url_base + url_ext, url_options);
  var blob = response.getBlob().getAs('application/pdf').setName(pdfName + '.pdf');
  folder.createFile(blob);
}

429 is Too Many Requests. The 5 seconds delay explains why this goes away. — SiKing, Dec 17 '19 at 23:30
Hi SiKing, Thanks for that – do you know of any way to find out WHICH request is "too many"? I get the error after looping the function 7, 8 og 9 times. — Simon Gade, Dec 18 '19 at 09:01
I doubt it will be a specific request. They probably keep track of all your requests and limit you to X requests per Y time. — SiKing, Dec 18 '19 at 15:33
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 05:04

score 1 · Answer 1 · edited Jul 11 '20 at 08:20

Changed the script to make a copy of the spreadsheet in the beginning and working on that copy – and trash it at the end.

Here's the final working script:

    /**
     * Creates a PDF file 
     *
     * 2019-12-17 Simon: Created
     *
     * @param {?} spreadsheet          Spreadsheet (SpreadsheetApp.getActiveSpreadsheet())
     * @param {string} sheetName       Name of the sheet to print
     * @param {string} pdfName         Name of the pdf file (excluding .pdf)
     * @param {string} folder          Folder to save in
     * @param {string} portrait        true=portrait, false=landscape
     * @param {number} scale           1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
     * @param {number} margins         In inches. Dot as decimal separator, e.g. '0.2'
     * @param {string} range           Optional. E.g. 'D4:AX74'
     */ 
    function savePdf(spreadsheet, sheetName, pdfName, folder, portrait, scale, margins, range) { 
      var rangeUse = (range ? '&range=' + range : '');
      var ssNew = spreadsheet.copy('temp');
      var sheetId = spreadsheet.getSheetByName(sheetName).getSheetId();
      var url_base = spreadsheet.getUrl().replace(/edit$/,'');
      var url_ext = 'export?'
      + '&gid=' + sheetId  
      + rangeUse
      + '&format=pdf'                   // export format
      + '&size=a4'                      // A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
      + '&portrait=' + portrait         // true = Potrait / false= Landscape
      + '&scale=' + scale               // 1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
      + '&top_margin=' + margins        // all four margins must be set!
      + '&bottom_margin=' + margins     // all four margins must be set!
      + '&left_margin=' + margins       // all four margins must be set!
      + '&right_margin=' + margins      // all four margins must be set!
      + '&gridlines=false'              // true/false
      + '&printnotes=false'             // true/false
      + '&pageorder=2'                  // 1 = Down, then over -- 2 = Over, then down
      + '&horizontal_alignment=CENTER'  // LEFT/CENTER/RIGHT
      + '&vertical_alignment=MIDDLE'    // TOP/MIDDLE/BOTTOM
      + '&printtitle=false'             // print title --true/false
      + '&sheetnames=false'             // print sheet names -- true/false
      + '&fzr=true'                     // repeat row headers (frozen rows) on each page -- true/false
      + '&fzc=true'                     // repeat column headers (frozen columns) on each page -- true/false
      + '&attachment=false'             // true/false
      var token = ScriptApp.getOAuthToken();
      var url_options = {headers: {'Authorization': 'Bearer ' + token, 'muteHttpExceptions': true,}};
      var response = UrlFetchApp.fetch(url_base + url_ext, url_options);
      var blob = response.getBlob().getAs('application/pdf').setName(pdfName + '.pdf');
      folder.createFile(blob);
      DriveApp.getFileById(ssNew.getId()).setTrashed(true);
    }

I love simple solutions! This would create quite a few 'trashed' versions that could clutter up your trashed files in Drive (even at once per day with 20 pdfs created this would generate 1200 duplicates in the trash). If you don't ever look in or use the trash for anything but that I can't see any other consequences if this gets the job done. — Rob, Dec 22 '19 at 19:17
@SimonGade (have been passing by) Please do not use code snippets for scripts that cannot be run without external dependencies or platforms. Use proper code formatting [Ctrl+K] instead: single backticks (“`”) for one-liners, property names and methods, code fences (“````”) for code blocks. Also please avoid chit-chat in questions, SO is not a forum, it is a Q&A website. — Oleg Valter is with Ukraine, Jul 11 '20 at 08:18

Rob · Accepted Answer · 2019-12-18T17:40:49.687

I ran into this once and was able to fix it by removing the call to ScriptApp.getOAuthToken() out of the recursive portion of the function. I think the easiest way to do this in your case with minimal complication would be to use the CacheService.

replace the line var token = ScriptApp.getOAuthToken();

with:

var token;
  if(CacheService.getScriptCache().get('token')!=null) {
    token = CacheService.getScriptCache().get('token');
  } else {
    token = ScriptApp.getOAuthToken();
    CacheService.getScriptCache().put('token',token,120);   
  }

This will store the token value in the CacheService instead of calling it recursively with your script loop. Hopefully this solves your issue as it did mine.

Edit:

After the above didn't fix your issue I looked back at what I had done and wrongly assumed that it was fetching the token that had caused my issue, but it was a rate limit on exporting from the Google Sheets API. Here's what I noticed ACTUALLY fixed MY issue at the time:

The rate limit (see my last paragraph for my thoughts on this) is per SHEET and not per user -- my recursive script was accessing two different sheets at the time so the natural delay in the function created just enough time delay for my script to run its course without problems.

Now to a fix for your drama:

After duplicating your problem I modified my parent function to create a copy of the main spreadsheet:

var mainsheetcopy = mainsheet.copy('Copy of main sheet')

and then switch between the two spreadsheets to send the call to the function to extract the PDF. I was able to iterate through the extraction of 20 pdfs with a sleep delay of only 750ms, and 18 iterations with no delay at all built in.

for(var i=0; i<20; i++) {
    if(isEven(i)) {
      sheetid = mainsheet.getId();
    } else { 
      sheetid = mainsheetcopy.getId()}

for reference, the isEven function is below:

function isEven(n) {
   return n % 2 == 0;
}

Then, at the end of the script I deleted the copy:

DriveApp.getFileById(mainsheetcopy.getId()).setTrashed(true);

If time is a factor, you could create a third copy of the main spreadsheet and through only the delay that the function takes naturally it would put you outside of the rate limit that they have on exporting the sheet as a PDF.

The actual rate limit is a bit elusive, but one sheet every 7.5-8 seconds seems to skirt this. I was able to iterate up to 5 PDF files exported per sheet with no rate limit 100% of the time and 6 occasionally.

Thanks for the suggestion, Rob. It didn't work :-( I also tried (earlier) to put getOAuthToken in the calling function so it only ran once and that didn't help. So it must be something else – I just have no idea what… — Simon Gade, Dec 18 '19 at 08:59
I've been able to duplicate your issue, and created a work-around that will help speed the function up a bit, but there is indeed a rate limit on exporting PDFs through the Google Sheets API that doesn't appear to be documented in their API. — Rob, Dec 18 '19 at 16:55
Thanks a bunch! I got it working. I changed it a bit and "just" made a copy of the sheet in the beginning of the script and working on that copy – and the of course trashing it at the end. I'll accept your answer and will write my script. — Simon Gade, Dec 19 '19 at 14:05

Error when creating multiple PDFs in Google Apps Script

2 Answers2

Linked