2

I have a spreadsheet that needs to make many hundreds of calls to functions I've written in JavaScript in the Google script editor, to fill many hundreds of cells.

The problem is I get the error that I should use utilities.sleep(1000) because of calls being too frequent. But then the spreadsheet would take an hour to update each time I change a value!

I'm guessing it makes a round trip to the server for each call. In that case, I could write a wrapper function that calculates everything in one go which returns the results for all cells. But is there an easier solution which simply runs the code from the PC? Otherwise it's barely worth using the spreadsheet in the first place.

(It's just doing a bunch of calculations with data passed to it from the spreadsheet; it's not fetching stuff from URLs or other resources.)

For example, I might have 4 columns, each with 500 rows. The left most would simply be numbering from 1 to 500. Then all 500 cells of the other three columns might call formulas such as:

Row 5:

  • Column 2: =col2_myfunc(A5)
  • Column 3: =col3_myfunc(A5, B5)
  • Column 4: =col4_myfunc(A5, B5, C5)

Then the code might look like this:

function col2_myfunc(input)
{
    // It only processes data provided in the arguments, and 
    // the only data this function returns is used.
    // The actual functions I'd use would be more complicated
    // so unlike this one, would not be suitable for directly inputting
    // into a cell.
    return (Math.tan(input % 10) / 3 > 1) ? Math.sin(input) : Math.pow(input, 3);
}

The HTML page idea suggested by Ruben sounds perfect - what would be the steps needed to implement the above?

  • 1
    Maybe you could use the Google Apps Script HTML service to do the JavaScript calculations on the client side but the question is too broad to be certain of that. – Rubén Jul 26 '16 at 16:20
  • @Rubén, I've updated my question, I hope it clarifies enough? – CL22 Jul 26 '16 at 19:48
  • The question looks a bit clearer for me now but it still has a lot of room for improvement. The problem looks to be specifically about how to improve the performance of a spreadsheet that uses a lot of custom functions. If your custom functions are set in a continuous range, one approach to solve the problem is to improve the spreadsheet performance by replacing all the formulas in contiguous cells by a single formula that returns an array of values. – Rubén Jul 26 '16 at 20:09
  • 1
    Please don't modify the question so much that it makes the existing answer invalid. You asked how to run scripts locally, and the short answer is that you can't. If you have a new question about how to get around that, please make a new question post. – ale Jul 26 '16 at 20:14
  • Regarding HTML Service it allows to run JavaScript code on the client side but to update the spreadsheet you should call services that run on the server side. IMHO it will not work to improve the performance of a spreadsheet that has hundreds of custom functions. – Rubén Jul 26 '16 at 20:50
4

Apps Script code is executed on Google servers. There is no way to run it locally.

calculates everything in one go which returns the results for all cells

This is probably what you should do, based on pretty vague description in the post. If you have calls to methods like setValue within a loop that runs many times, that should be changed to array manipulation.

  • Thanks for the info. This is the stupidest idea I've ever seen. It really boggles the mind. – Andre Terra Mar 20 '17 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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