Google Apps Script Write While Executing

Question

I'm making use of google spread sheets and Apps Script to collect some financial data, there is one function that takes some time because it processes a lot of information.

because of this, google shows me that exceeded the maximun execution time, but even having a for loop that writes on the file each loop it wont do it until the function finishes.

can someone give me an idea of how to write the spreadsheet while its executing?

thanks.

Leaving here an example script.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var rangeSheet = activeSheet.getRange(1, 1, activeSheet.getLastRow() - 1, 1).getValues();
  // after cleared out i have an array of values so i do:
  rangeSheet.forEach(async (el, idx) => {
    let result = await anotherFunction(el) // <--- this is the function taking around 2 minutes to complete
    sheet.getRange(`$B${idx + 1}`).setValue(`${result}
  })
}

soMario soMario · Accepted Answer · 2021-02-02T20:53:10

This will give you some boost in performance:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var rangeSheet = activeSheet.getRange(1, 1, activeSheet.getLastRow() - 1, 1).getValues();
  var data = [];
  rangeSheet.forEach(async (el, idx) => {
    let result = await anotherFunction(el) // <--- this is the function taking around 2 minutes to complete
    data.push([result]);
  })
  sheet.getRange(1,2,data.length,data[0].length).setValues(data);
}

Read Best Practices for more info.

However, if anotherFunction takes too much time, then I am afraid you need to redesign your logic. I would advice you to use UrlfetchApp.fetchAll() if you want to fetch multiple URLs.

References:

google apps script with UrlfetchApp.fetchAll() or with async/ await for multiple http requests?

Google Apps Script Write While Executing

1 Answers