Apps Script For within For Faster

Question

I used the below code to get the data from API. I used for loop within for loop and it's taking long time and program stops as time exceeds.

function devicedetails(){
var apikey='YWQ0OWFhYjgtNTY2asiHSNSajiasn'
var todaydate = Utilities.formatDate(new Date(Date.now() - 1000*60*60*24), "UTC", "yyyy-MM-dd")
var thirtydate = Utilities.formatDate(new Date(Date.now() - 1000*60*60*24*30), "UTC", "yyyy-MM-dd")
var cisss= SpreadsheetApp.getActiveSpreadsheet();
var workspacesheet = cisss.getSheetByName("Device");
var lastRows = workspacesheet.getLastRow()+1;

for(var im = 2; im < lastRows; im++)
    {      
      var workspacedata = workspacesheet.getRange('B'+im).getValue();
      var encodedata = encodeURIComponent(workspacedata);
      var cisurl = "https://testapi.com/v1/workspaceDurationMetrics?workspaceId="+encodedata+"&aggregation=daily&measurement=timeUsed&from="+thirtydate+"T00%3A00%3A00.00Z&to="+todaydate+"T00%3A00%3A00.00Z";
    var cisss= SpreadsheetApp.getActiveSpreadsheet()
    var ciswsLocation = cisss.getSheetByName("HourlyUsed")
    var lastRow = ciswsLocation.getLastRow();

    var headers = {
        "Content-type": "application/json",
        "Authorization": `Bearer ${apikey} `
    };

    var options = {
        "method" : "get",
        "headers" : headers 
    };

    var response = UrlFetchApp.fetch(cisurl,options);
    var cisjson=response.getContentText();
    var cisdata=JSON.parse(cisjson);
       
    
    for(var i = 0; i < cisdata['items'].length; i++)
    {
       ciswsLocation.getRange(lastRow+1+i,1).setValue([cisdata["workspaceId"]]);
      ciswsLocation.getRange(lastRow+1+i,2).setValue(Utilities.formatDate(new Date([cisdata["items"][i]['start']]), "UTC", "yyyy-MM-dd"));
      ciswsLocation.getRange(lastRow+1+i,3).setValue([cisdata["items"][i]['duration']]);
    }      
}
}

Please help me how to reduce time of execution?

Answer 1

Exactly what liqidkat said.

With that, it may look something like this:

function devicedetails() {

  /** Variables **/
  const apikey ='YWQ0OWFhYjgtNTY2asiHSNSajiasn'

  const todaydate = Utilities.formatDate(new Date(Date.now() - 1000*60*60*24), "UTC", "yyyy-MM-dd")
  const thirtydate = Utilities.formatDate(new Date(Date.now() - 1000*60*60*24*30), "UTC", "yyyy-MM-dd")

  /** Sheet Variables **/
  const cisss = SpreadsheetApp.getActiveSpreadsheet()

  const workspacesheet = cisss.getSheetByName("Device")
  const workspaceData = workspacesheet.getRange(2, 2, workspacesheet.getLastRow()-1).getValues().flat()

  const ciswsLocation = cisss.getSheetByName("HourlyUsed")
  const lastRow = ciswsLocation.getLastRow()

  /** Request Handling **/
  const allRequests = workspaceData.map(i => {

    const encodeData = encodeURIComponent(i)
    return {
      "url": `https://testapi.com/v1/workspaceDurationMetrics?workspaceId=${encodeData}&aggregation=daily&measurement=timeUsed&from=${thirtydate}T00%3A00%3A00.00Z&to=${todaydate}T00%3A00%3A00.00Z`,
      "method": "get",
      "headers": {
          "Content-type": "application/json",
          "Authorization": `Bearer ${apikey}`
      }
    }
  
  })

  /** Response Handling **/
  const allResponses = UrlFetchApp.fetchAll(allRequests)

  const data = allResponses.map(response => {

    const cisjson = response.getContentText()
    const cisData = JSON.parse(cisjson)
    return cisData[`items`].map(i => [
                                       cisdata["workspaceId"], 
                                       Utilities.formatDate(new Date(i['start']), "UTC", "yyyy-MM-dd"),
                                       i['duration']
                                     ])

  })

  /** Set data **/
  ciswsLocation.getRange(lastRow+1, 3, data.length, data[0].length).setValues(data)
  
}

See Also:

• Array.map()
• UrlFetchApp.fetchAll()
• Range.setValues()