Iterate ~80x faster through spreadsheet using map function in Apps Script

let me begin with a confession: i don't totally understand the full extent of what i'm about to share but what i stumbled upon was extremely overwhelming that i had to put it out there so folks smarter than me could better help make sense of it all & for the ones who didn't already know something like this existed, perhaps could start researching and thinking through things along these lines.

full disclosure: this isn't something new or innovative and folks who're already familiar with javascript map method may not find this as enlightening. what i've done here is run a test case multiple times to capture the duration with which different methods provide the output.

context

while working with larger data sets in google apps script either via custom functions, web apps, add-ons or even a simple/normal script, there's always this issue of making the script work around a specific duration in order to avoid hitting timeouts (as per g suite's official quota limitations based on different tiers).

this led me to explore how to optimise my javascript - which, when i say out loud may sound fancy, but one needs to know and understand the fundamentals (which i don't given that i'm not a programmer) before they get to the more "advanced" constructs.

any who, with minimum googling i found a ton of blogs/tutorials already available that talk about the non-apps script related javascript methods/function that deal with just that. for some reason i don't recall exactly what led me towards the use of a map function but i bet you can find one that suits your level of engagement and interest.

external references

• javascript multidimensional array given that spreadsheet spits out 2d array
• array map() method:
  • ref w3schools
  • ref mdn web docs
• apps script's createTextFinder
  • ref available here

i'd not be covering what the map function does as there are already a ton of resources available out there which do that but instead, in this post, i'd rather want to highlight the test case i ran on a 1-million-cells sized data set and its results + a way for you to test 'em yourselves.

stats

• created a million cells of data by filling the sheet with random numbers using:
  • =ArrayFormula(ROUND(RANDARRAY(10000,100)*100000000))
  • the above sheets function translates to 10,000 columns and 100 rows
• next, i wrote a for loop to iterate through the data and find the farthest right bottom corner value i.e. 'CT9998' (which was unique)
  • its the 3rd last row and the 3rd last column
  • on an average, this method took ~9 seconds for it to get the output

• then came the map function
  • in about 300 times that i ran the code, it took ~0.15 seconds for it to find CT9998
• i also gave createTextFinder a shot too (not as extensively) but even that took ~4 seconds on average to find the output

codebase

you can make a copy from the sheet provided in the demo below but let me break this down in 3 different steps -

1. loading the data into the execution script:

var startData = new Date().getTime(); // marking the start time to invoke SpreadsheetApp related functions
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var values = ss.getDataRange().getValues(); // generated using =ArrayFormula(ROUND(RANDARRAY(10000,100)*100000000))
var endData = new Date().getTime(); // marking the end time of data being loaded for execution
var diffData = (endData - startData)/1000;
Logger.log("Data size: " + values.length + " rows & " + values[0].length + " columns.")
Logger.log("Time to load data: " + diffData + " sec.");

2. using for loop to find the output:

var find = 63041851;  // this sits at cell 'CT9998' in sheet '1YyRQHuoE_t3FPRVWf3FX0floMk6Mo3cE2BQ-N2pxKIo' closely towards the right bottom corner

// Case1: Using `for` loops
var startFor = new Date().getTime(); // marking the start of `for` loops
var found = false;
for (var i = 0; i < values.length; i++) {
  for (var j = 0; j < values[0].length; j++) {
    if (values[i][j] == find) { // this refers to the "i"th row & "j"th column
      var outputFor = ss.getRange(i+1, j+1).getA1Notation(); // A1Notation means cell reference; like A1, BC23 etc.
      found = true;
      break;
    }
  }
  if (found) {
    break;
  }
}
var endFor = new Date().getTime(); // marking the end of `for` loops
var diffFor = (endFor - startFor)/1000;
Logger.log("Output using For Loop: " + outputFor + "; Time taken: " + diffFor + " sec.");

3. finally, finding the same value using the map function:

// Case2: Using `map` function  
var startArr = new Date().getTime(); // marking the start for using `map` function
var outputArr = values.map(function (rowData, rowIndex) {
  var colIndex = rowData.indexOf(find);
  if (colIndex > -1) {
    return ss.getRange(rowIndex+1, colIndex+1).getA1Notation(); // A1Notation means cell reference; like A1, BC23 etc.
  }
}).filter(function (cellData) { // eliminating all null elements from the array returned using `map`
  return cellData != null;
});
var endArr = new Date().getTime(); // marking the end of using `map` + `filter` function
var diffArr = (endArr - startArr)/1000;
Logger.log("Output using Array.map: " + outputArr + "; Time taken: " + diffArr + " sec.");

additionally, you can also experiment with the createTextFinder function as well that is native to SpreadsheetApp but use it in a different function than the code above:

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var find = 63041851;
var start = new Date().getTime();
var finder = ss.createTextFinder(find).findAll();
for (var i = 0; i < finder.length; i++) {
  var findee = finder[i];
  var ref = findee.getA1Notation();
  Logger.log(ref)
}
var end = new Date().getTime();
var diff = (end-start)/1000;
Logger.log(diff)

demo

• spreadsheet with 1 million cell records
• sheets bound script ➡️

output

once you run the script, you should get to see the following in the logs -

alternately, you can also switch to the Output tab in the spreadsheet, where i've ensured to log every such test case.

brag