Creating Multiple Google Sheets PDFs throws 429 error

Question

I'm stuck. I'm hoping someone out there has figured out a way to generate a bunch of PDF's off of Google Sheets.

I run the same report for a list of companies and generate PDF snapshots of each report which are saved in Drive. I get a 429 error every time with the only difference being how many PDFs I generate before getting the error.

The number of PDFs generated is directly correlated to how long I put in the Utilities.sleep function but still only a fraction of the total number of reports (i.e more sleep = slightly more PDFs generated before error).

I've made several attempts at using exponential backoff, but my attempts have been futile.

Any insights on how to avoid the following 429 are appreciated:

Request failed for docs.google.com/a/[redacted]/spreadsheets/d/[redacted]/… returned code 429. Truncated server response: <!DOCTYPE html><html lang="en"><head><meta name="description" content="Web word processing, presentations and spreadsheets"><meta name="viewport" c... (use muteHttpExceptions option to examine full response

Here's the relevant code:

var foldersave = DriveApp.createFolder('New Reports').getId()
var request = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken(), muteHttpExceptions: true}};

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var gid = sheet.getSheetId();

var values = ss.getActiveSheet().getRange(2, 8, sheet.getLastRow()-1, 1).getValues();

for (var i = 0; i < values.length; i++) {

//set up the new report
var org = sheet.getRange(5,4);
org.setValue(values[i])

//set the PDF parameters
var pdfOpts = '&size=0&fzr=false&portrait=true&fitw=true&gridlines=false&printtitle=false&sheetnames=false&pagenum=CENTER&attachment=false&gid='+gid;
var printRange = '&c1=0' + '&r1=0' + '&c2=4' + '&r2=42'; 
var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + pdfOpts + printRange;

//create the PDF
var response = UrlFetchApp.fetch(url, request).getBlob().setName(values[i] + ".pdf"); 
var dir = DriveApp.getFolderById(foldersave); 
var file = dir.createFile(response);
Utilities.sleep(5000);
}

The textual error should be added to the question. Also the question should include a [mcve]. Anyway I think that error occurs because something related to some of the files that you are trying to get. See [Debugging external host communication with UrlFetchApp](https://mogsdad.wordpress.com/2015/09/09/debugging-external-host-communication-with-urlfetchapp/) — Rubén, Dec 05 '17 at 20:37
Thanks, I've updated the question with your suggestions. As all the files are nearly identical and the error propagates randomly after x reports, the error appears to be related to some undocumented rate limiting for x. — Avid, Dec 05 '17 at 21:08

score 4 · Answer 1 · edited Mar 04 '21 at 20:21

4

I tried something similar to exponential backoff and it worked for me.

 var i = 0 
 {
 i++ ;
 Utilities.sleep(i*1000);
 }

edited Mar 04 '21 at 20:21

johnDanger

1,990
16
22

answered Mar 04 '21 at 16:42

Alexander Pedersen

41
1

1

Welcome to the Stack Overflow community Mr. Pedersen! Please check out this [guide](https://meta.stackoverflow.com/q/252149/11715606) for new users! – johnDanger Mar 04 '21 at 17:03

score 2 · Answer 2 · answered Dec 06 '17 at 13:06

2

429 error means too many requests. The obvious solution then is to slow down on the request by creating a time delay between calls using sleep(). This has been demoed in this SO post.

answered Dec 06 '17 at 13:06

ReyAnthonyRenacia

17,219
5
37
56

Creating Multiple Google Sheets PDFs throws 429 error

2 Answers2

Linked