0

I get an error 429 "Too many requests" with this script.

The script is functional with 4 or 5 sheets in the spreadsheet... but when I try on my spreadsheet with 15+ sheets it return an error "Too many requests".

How can I fix this problem please ?

PS : it's a script that I have taken from your website.

With this script, I need to take a sheet (by name) and download it to PDF and send to a specific folder in the drive.

I have 5 identical scripts, with the name changed for the PDF needed.

In one of the scripts, I need two files.

    function PDF4( optSSId, optSheetId ) 

var ss  = SpreadsheetApp.getActiveSpreadsheet();
var url1 = ss.getUrl().replace(/edit$/,'');
var Ref = ss.getSheetByName('Caché 2').getRange('E8').getValues()
var name = ss.getSheetByName('Budget').getRange('C10').getValues()
var factnumb = ss.getSheetByName('Facture Intermédiaire BIS').getRange('C16').getValues()    
var htva = ss.getSheetByName('Facture Intermédiaire BIS').getRange("H24").getValues();   
var tva = ss.getSheetByName('Facture Intermédiaire BIS').getRange("G25").getValues();   
var total = ss.getSheetByName('Facture Intermédiaire BIS').getRange("H26").getValues();
var INVFolder = ss.getSheetByName('Liste').getRange('H8').getValues()   
var folder = DriveApp.getFolderById(INVFolder).createFolder(factnumb + ' - ' + Ref + ' - ' + name)
var Blist = ss.getSheetByName("Liste").getRange('H2').getValues()   
var sp2 = SpreadsheetApp.openById(Blist).getSheetByName("Liste"); 
var target = sp2.getLastRow();   sp2.getRange(target+1,1,name.length,name[0].length).setValues(name); 
var cols = sp2.getActiveCell().getColumn(); 
var colsplus = cols + 1;   
var colsplus2 = cols + 2;   
var colsplus3 = cols + 3;   
var colsplus4 = cols + 4; 
var igi = sp2.getRange(target+1,colsplus); 
igi.setValues(factnumb);   
var htv = sp2.getRange(target+1,colsplus2);
htv.setValues(htva);   
var tv = sp2.getRange(target+1,colsplus3);  
tv.setValues(tva);   
var tot = sp2.getRange(target+1,colsplus4);   
tot.setValues(total);   
var no = sp2.getRange(target+1,cols);   
no.setValues(name);

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'
            + '&gid=' + sheet.getSheetId()
            // following parameters are optional...
            + '&size=letter'
            + '&portrait=true'
            + '&fitw=true'
            + '&sheetnames=false&printtitle=false&pagenumbers=false'
            + '&gridlines=false'
            + '&fzr=false'; 
var url = url1 + url_ext
var options = {
          headers: {
            'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
          }
        }
var response = UrlFetchApp.fetch(url, options);
var blob = response.getBlob().setName(ss.getName() + ' - ' + sheet.getName() + '.pdf');

folder.createFile(blob);   }

        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Récap.CDE' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Caché' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Caché 2' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Budget' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Budget Mini' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Métré MOD' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'CDC - Regroupement' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'CDC' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Planning' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Liste' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Prestataires' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Suppléments' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Dim.Pièces' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Dimensions pièces mini' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Feuille 22' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'MOD' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'CDC Prestataires' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Métré Prestataires' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Facture Acompte' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Facture Solde' + '.pdf').next().setTrashed(true);
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Facture Intermédiaire' + '.pdf').next().setTrashed(true);           
        DriveApp.getFilesByName(ss.getName() + ' - ' + 'Devis' + '.pdf').next().setTrashed(true);

        var newname = factnumb + ' - ' + Ref + ' - ' + name + '.pdf'
          var file = DriveApp.getFilesByName(ss.getName() + ' - ' + 'Facture Intermédiaire BIS' + '.pdf').next()
          file.setName(newname)  }
MOD
  • 1
  • 2
  • 1
    Please edit to fix that ill-formatted mess you've posted. Remove all of the `>` characters, properly indent the code, select it all, and use Ctrl+K or the {} button on the toolbar to format it. Thanks. – Ken White Mar 04 '16 at 14:26
  • Never say that you are sorry for your language on StackOverflow. Always do your best to write proper English, and assume that readers will do their best to understand you. The most important thing in your question is your code, and that will be understood in all human languages. – Amedee Van Gasse Mar 04 '16 at 14:45
  • It doesn't sound like there is a real issue with the script provided other than it is going over the rate limit that the Google Drive API has built into it. Take a look at this other StackOverflow question that talks about the limits applied to Google Drive API : http://stackoverflow.com/a/10313416/924 You can also take a look at the description of status code in the Google Drive API documentation : https://cloud.google.com/storage/docs/json_api/v1/status-codes – Brandon Haugen Mar 05 '16 at 19:48
  • Not sure if this adds anything. But I wrote a script a few weeks ago to bulk export PDFs. It worked like clockwork everytime, exporting 30+ PDF's to drive. Now it does 3-7 and fails with error 429 everytime. I've changed nothing in my script. Has Google perhaps changed it's limits or something? – Munkey Mar 19 '16 at 15:48

1 Answers1

0

Thanks a lot for your answers,

Is it OK with the format of my question now ?

I think there is a problem cause I've tried to select less than 5 sheets with for (var i=0; i<sheets.length - 17; i++) (My spreadsheet have 23 different sheets)

It work some times... but it continues to send me the same error some times...

MOD
  • 1
  • 2