Google Apps Script, using UrlFetchApp.fetch with large number of URL's

Question

I've been trying to figure out how to get the UrlFetchApp.fetch method to work correctly on an array of 100+ url's that I need it to fetch for a script. I either hit error 429's (too many requests), or if I put any kind of sleep between iterations the amount of time the URLFetchApp takes is consistently increasing until each call takes 8+ seconds and I hit the timeout limit of 6 minutes. Here is my code as it stands right now. Any input would be appreciated.

function migrateSheetsToFiles() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ssId = ss.getId();
    var parentfolder = DriveApp.getFileById(ssId).getParents().next();
    var mySheets = ss.getSheets();
    var fileID = ss.getId();
    var FetchArray = [];
    var sheetnameArray = [];
    var responseArray = [];

    var params = {
        method : "GET", 
        headers : {"authorization": "Bearer " + ScriptApp.getOAuthToken()}
    };

    for(var i=0; i<mySheets.length; i++){
        var currentSheet = mySheets[i];
        var sheetname = currentSheet.getName();
        sheetnameArray[i] = sheetname;
        var currentSheetId = currentSheet.getSheetId();
        var url = "https://docs.google.com/spreadsheets/d/" + fileID + "/export?format=xlsx&gid=" + currentSheetId;
        FetchArray[i] = url;
    }

    for (i=0; i<FetchArray.length; i++) {
        responseArray = UrlFetchApp.fetch(FetchArray[i], params);
        Utilities.sleep(1000);
    }

    for (i=0; i<FetchArray.length; i++) { 
        parentfolder.createFile(responseArray[i]).setName(sheetnameArray[i]);
    }
}

Tanaike Tanaike · Accepted Answer · 2017-08-03T23:31:15

It seems that you want to separate sheets from the spreadsheet to individual sheet and create each sheet as one spreadsheet. Is my understanding of this correct? For this, how about following methods?

After it separated the data of mySheets, it creates spreadsheets from each separated data using a trigger.
- I think that it is possible. But the time is required to finish the work.
It separates sheets from spreadsheet without UrlFetchApp.fetch() using SpreadsheetApp and/or Sheets API v4.
- I don't know whether the limitation error occurs. But if this works, it can finish the work by only one time running.

If this is not useful for you, I'm sorry.

Google Apps Script, using UrlFetchApp.fetch with large number of URL's

1 Answers