Run Google Apps Scripts locally to process spreadsheet data

Question

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?

Answer 1

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.