I have a spreadsheet that has three tabs:
The first tab is raw data imported from a website. it's about 10 columns and up to 300 rows. it has a mix of numbers in decimal form and percent. it all comes over as text though
The second tab is for ranking the values in the first tab. it stores the min and max values for the rankings 3 to -3. each column from the raw data tab has its own ranking ranges.
the third tab is a mirror of the raw data tab, but instead of number values, it calculates the rank of the value in this tab.
I'm trying to figure out the best way to do the calculations. it is a lot of data and the way I am currently doing it bogs down the system tremendously.
I call a function that has two parameters – column and value. the function calculates the ranking ranges for that column and stores it in local variables in the function, then a large nested if statement compares the value to the range variables and returns the ranking to that cell. it runs for each cell in 300 rows, 10 columns. also, I have to convert the percent values from text to numeric values in the second parameter of each function call, so it is a lot of calculating.
I there an easier, more efficient way to get this ranking? Would nested if statements in the cell formula field run smoother? or should I add more tabs to get the data into a more user-friendly format before calling my function?
I get a lot of errors in the cells saying I call the function too many times
Best Answer
Because the error message it looks that you are using a custom function that returns one value at a time instead of returning an array of values.
A very common suggestion for Apps Script optimization is to avoid to call
getValue/setValueor other Apps Script specific methods on loops (i.e. for, while). Instead, make an array process it on the loop and then usesetValuesto return the result to a spreadsheet on a single call.Reference