Apps Script For within For Faster-CodePudding

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:00:00.00Z&to=" todaydate "T00:00:00.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?

CodePudding user response：

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)
    const cisurl = `https://testapi.com/v1/workspaceDurationMetrics?workspaceId=${encodeData}&aggregation=daily&measurement=timeUsed&from=${thirtydate}T00:00:00.00Z&to=${todaydate}T00:00:00.00Z`
    const options = {
      "method": "get",
      "headers": {
          "Content-type": "application/json",
          "Authorization": `Bearer ${apikey}`
      }
    }
    return [cisurl, options]
  
  })

  /** 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:

CodePudding user response：

Description

I took the liberty of editing your script to replace all getValue/setValue with getValues/setValues. And I moved all variable that only need to be set once outside the loop. First I get all workspacedata, then in side the loop, index into that array for each row. Next since your results are contiguous in rows and columns, I collect all the results and make one call to setValues to place in the sheet.

Although I am not able to test it since input data is not available I believe it will work and will run much faster.

Although Google has made improvements in it performance of getValue/setValue by caching requests I try to organize my spreadsheets so that I will always use getValues/setValues. Same for other getters and setters.

Script

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;
  var workspacedata = workspacesheet.getRange(2,2,lastRows-1,1).getValues();
  var ciswsLocation = cisss.getSheetByName("HourlyUsed")
  var lastRow = ciswsLocation.getLastRow();

  for(var im = 0; im < lastRows; im  )  {      
    var encodedata = encodeURIComponent(workspacedata[im][0]);
    var cisurl = "https://testapi.com/v1/workspaceDurationMetrics?workspaceId=" encodedata "&aggregation=daily&measurement=timeUsed&from=" thirtydate "T00:00:00.00Z&to=" todaydate "T00:00:00.00Z";

    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);
       
    var results = [];
    for(var i = 0; i < cisdata['items'].length; i  ) {
      let row = []
      row[0] = cisdata["workspaceId"];
      row[1] = Utilities.formatDate(new Date(cisdata["items"][i]['start']), "UTC", "yyyy-MM-dd");
      row[2] = cisdata["items"][i]['duration'];
      results.push(row);
    }
    ciswsLocation.getRange(lastRow 1,1,results.length,results[0].length).setValues(results);      
  }
}