calling functions asynchronously from Google Sheets using script

Question

Is there any way to make Google Script call functions asynchronously? My scenario is that I have a main spreadsheet that information is entered into and a script then passes the relevant information to other spreadsheets.

There are then other functions that manipulate the data in those other spreadsheets. Unfortunately, because of the high volume of data, calling all the functions on one action causes the script to hit the 6 minute time out.

I tried using the onEdit trigger in the other spreadsheets, but it doesn't seem to work unless the sheets are opened by a user.

The way it is just now the user would have to hit 4 different buttons to trigger the various functions and not get a time out.

Thanks for any help

Blair

Answer 1

Depending on how realtime the updates need to be, you could consider creating a queue that contains all of the updates to be made (perhaps stored in the PropertiesService as a stringified JSON object).

Then your update code could be triggered regularly, say every 5 minutes, and read the next element of the queue and execute the update, before removing that entry from the queue. This would mean each individual update fitted within the 6 minute window, but it would also mean that if there were 4 additional updates for every update to the main sheet it might be up to 24 minutes before all of them had been made.