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()

Answer 2

I will provide my particular approach to this problem, as I think it may be of interest to the community.

Since the OP has not provided the type of response the API provides (and refers that it is for private use), I will use a public API for the example, Google Books APIs in this case. I will also consider that the calls are made to the same API, so the response is assumed to be identical.

I think the problem can be divided into 4 major steps.

1. Generate the URLs of the calls (depends on the API).
2. Get the data via UrlFetchApp.fetchAll(Object)
3. Normalize the data (this is the most critical step, as it depends on the API response). The main point is to obtain an array of arrays (Object[][]) as required for the next step.
4. Write the data to the sheet using setValues(Object[][]).

Full example here.

Generate URLs

const generateUrl = (authors) => authors.map(author => `https://books.googleapis.com/books/v1/volumes?q=${author}&country=US`)

Get The Data

const fetchAndNormalizeData = (urlList) => {
  const resAll = UrlFetchApp.fetchAll(urlList).map(res => res.getContentText())
  return resAll.map(normalizeResponse).flat()
}

Normalize The Data

const normalizeResponse = (res) => {
  /* This depends on the RestAPI response */
  const { items } = JSON.parse(res)
  return items.map((book) => {
    const { selfLink, volumeInfo: { title, authors, publishedDate } } = book
    const parsedAuthors = authors ? authors.join('\n') : ""
    return [title, parsedAuthors, selfLink, publishedDate]
  })
}

Write to Sheet

const writeToSheet = (data) => {
  sS
    .getRange(sS.getLastRow() + 1, 1, data.length, data[0].length)
    .setValues(data)
  console.log("DATA SAVED")
}

Main function

const SS_ID = "<SS_ID>"
const sS = SpreadsheetApp.openById(SS_ID).getSheetByName('BookData')

const main = () => {
  const urlList = generateUrl(["Twain", "Joyce", "Pasternak"])
  const data = fetchAndNormalizeData(urlList)
  writeToSheet(data)
}

---

In the case of the OP just have to modify the normalizeResponse (callback for the map function) and generateUrl to adapt it to their needs.

Documentation:

• Array.prototype.map()
• Array.prototype.flat()

Answer 3

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")

  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%3A00%3A00.00Z&to="+todaydate+"T00%3A00%3A00.00Z";
    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);
       
    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);      
  }
}