Reducing Apps Script Execution Time By Writing Everything All At Once

Recently I managed to reduce a function’s execution time from 998 seconds (16 minutes!) to 13 seconds. An operation that runs for so long is written inefficiently. It’s inefficient to the extent that it raises the question of whether it’s even usable.  

I wanted to check if there’s something I can do to make the code complete its execution faster. I found that by moving to batch operations, I was able to reduce its running time by almost 99%.

What was inefficient in the code?

One of my everyday tasks is to upload contact lists to a CRM. Before uploading the long lists, the data should be cleaned of unwanted stuff like line breaks, language accents (because the CRM handles them poorly), unifying the data like having the US States with their full names instead of their two-letter abbreviations, and so on.

As you can see there are many tests to run before uploading the list to the system. The idea of running a script with all the tests seemed more efficient, quicker, and less time-consuming than doing it manually.

And here’s where things went wrong. 

I wrote a function that takes a list of columns, like first_name, last_name, person’s title, and company, state, country, etc. The function runs and checks each input value. If the value is ready to be uploaded, then nothing is modified. If not, the changes are applied upon the input and written back to the Google Sheet instantaneously. 

And after using this for a few days I found it unusable. This method took ages to complete. 

Batch Operations: Write all at once

After looking for a better way to run the code, I found Google’s Best Practices page, which encourages coders to use Batch Operations. 

Google explains that apps scripts has a built-in optimization “such as using look-ahead caching to retrieve what a script is likely to get and write caching to save what is likely to be set.”

With that in mind, they recommend reading the data into an array, perform an operation upon it, and then write the new array back to the spreadsheet.

Taking this advice saved me the long waiting time for the script to complete its execution.

The new function fetched all the new values into an array, looped through all of them, and when the loop was completed, all the new values were pushed into the spreadsheet’s column all at once. 

This way, every cell was analyzed, and if there was nothing to change, the value was returned to the new array. Values were changed whenever they needed fixing, and the new values were added to the array. 

Here’s how one of my operations, removing accents, looked like at the end:

function removeAccents(columns_array) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  let max_rows = sheet.getMaxRows()
 
  // looping through the array of columns
  for (let c = 0; c < columns_array.length; c++) {
 
    let col = columns_array[c]; // picking one column
    let all_current_vals = sheet.getRange(2, col, max_rows - 1).getValues(); // getting all column values
    // removing line breaks and accents from each value and saving in a new variable  
    let new_strs = all_current_vals.map(value => {
 
      value = value[0]
      value = removeLineBreaksFromString(value);
      value = removeAccentsFromString(value);
 
      return [value]
    })
    // Writing back the data to the sheet
    sheet.getRange(2, col, max_rows - 1).setValues(new_strs);
  }
}
 
// A function to remove accents from a given string 
function removeAccentsFromString(text) {
  var result = text.normalize('NFD').replace(/[\u0300-\u036f]/g, "")
  return result;
};
 
// A function to remove break lines from a given string
function removeLinesBreaksFromString(text) {
  var result = text.replace(/(?:\r\n|\r|\n)/g, ', ')
  return result;
};