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

  • nested if statements would run smoother – user0 Sep 7 at 15:15

I get a lot of errors in the cells saying I call the function too many times

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 / setValue or other Apps Script specific methods on loops (i.e. for, while). Instead, make an array process it on the loop and then use setValues to return the result to a spreadsheet on a single call.

Reference

  • Here is a subset of the spreadsheet. docs.google.com/spreadsheets/d/… – HardingIT Aug 8 at 18:31
  • Thank you for your feedback. Here is a subset of the spreadsheet. docs.google.com/spreadsheets/d/… Batting Ranks tab has the ranges for each ranking. This is used to rank each stat from FG_Bat_Raw (raw data import) and FG_Batting (Formatted raw data source). The BATTING tab has the call to the function. How would I go about using an array to populate BATTING!K:S? – HardingIT Aug 8 at 18:35
  • Also, would using a nested if statement in each cell be more efficient than calling a custom function in this instance? – HardingIT Aug 8 at 18:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.