1

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 numerical 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

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

1 Answer 1

0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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