0

Here is my issue. I have a spreadsheet with multiple sheets, and each sheet has about 300-500 rows. I am using ScriptDb to store the data for each sheet.

What I am currently doing is calling a custom function in 300-500 cells in each sheet to populate certain cells with data, and what happens is that some will populate and the rest will error out saying i've queried the database too many times in a short period. Obviously having to query the database for each cell isn't the best solution.

How would I go about querying all the data for the current sheet and then having that data available to grab for each cell. What I've read is that you can't really have "global" variables in GAS, but have to use things such as CacheService or ScriptDB, which is what i'm trying to do. I'm just querying it too much.

Is there some way to populate all the cells from 1 function call instead of 1 call for each cell? What am I missing or what other solutions are there?

Just realized a similar question was asked earlier today: Google Spreadsheet Script invoked too many times per second for this Google user account

Rubén
  • 34,714
  • 9
  • 70
  • 166
bjo
  • 550
  • 2
  • 8
  • 15
  • 1
    Wouldn't it be possible to think differently, ie populate the cells from one single script instead of having each cell calling a script? I don't know what kind of data and queries you are working with so this is just a suggestion... – Serge insas Aug 13 '13 at 23:02
  • Why not store the data to populate the cells in the spreadsheet itself? In most cases you won't need a script at all and it's way faster than anything based on ScriptDb. – Taras Aug 14 '13 at 05:34
  • Does this answer your question? [Can I make custom function on google spreadsheet without Google Apps Script?](https://stackoverflow.com/questions/31816235/can-i-make-custom-function-on-google-spreadsheet-without-google-apps-script) – Rubén Jun 13 '20 at 18:06

1 Answers1

0

Yes its possible. Simply return an array from your function. It will work like an arrayformula. Of course your cells would need to be contiguous.

Zig Mandel
  • 19,571
  • 5
  • 26
  • 36