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?
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);
}
}