Google Apps Script: SpreadsheetApp, getRange, getLastRow, getDataRange
I was trying to rush out some Google Apps Script code to deal with a task on Google Sheets recently. Basically, I had to search through a heap of data and find certain values and do something too them.
My column was reaching across the page to something like Column BK and my rows were over 1000 deep. Running this code was taking forever!!!
My immediate instinct was:
What have I done wrong?
…and my instinct was right.
The Good and Bad Way to Search Through Code
So after looking at my code again, I discovered that for some reason I go it into my head that I should be searching each cell for the value I needed and then doing something with it.
Sounds logical right? It’s sorta what you are meant to do.
The problem is that I was calling the sever and asking for the range in each cell as I was looping through the entire document. This is super costly and inefficient in terms of time.