Cache in custom functions in GSheets

[R Tichy]

In the doc, it says that Cache is not very useful in custom functions.  ( https://ctrlq.org/google.apps.script/docs/guides/sheets/functions.html )

But why is that?  Why is it not very useful?  Is it too slow?  Or is it just that a public cache has few uses inside a custom function, in the writer's opinion?

Rob

[Clark Lind]

Hi Rob,  I don't know if Amit will respond. But I can see his point of view. I'm thinking it is just the nature of a custom function that makes using cache not very useful.

Cache is certainly useful in other functions and macros, but not the defined 'custom function'** since they just return a result based on input. If you ever needed to store something in cache for later retrieval outside the custom function, you are probably not using a custom function. 

**Custom function is a defined term relating only to Sheets and the purpose is to allow user to create new sheet functions or formulas when the built-in functions (e.g. "=left()", "=countif()", "=trim()", etc) aren't enough.

[Clark Lind]

Also, the official Docs are here, so you are referring to what Google wrote, not Amit on ctrlq.org :)

[R Tichy]

Also, the official Docs are here, so you are referring to what Google wrote, not Amit on ctrlq.org :)

True.  But I was trying to understand his/her point of view.  I have since learned that all custom function results are cached by GSheets unless the inputs are altered/different.

Rob

[Jonathan Butler]

I honestly have no idea why that was added to the docs. I have used the cache service heavily with custom functions. In fact, it is the fastest way to read large amounts of data quickly. At least in my experience. You can also use the sheets API, but you can run into quotas if doing a lot of reads/writes back to back. 

On Sun, Jun 19, 2022 at 11:11 AM 'R Tichy' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

Also, the official Docs are here, so you are referring to what Google wrote, not Amit on ctrlq.org :)

True.  But I was trying to understand his/her point of view.  I have since learned that all custom function results are cached by GSheets unless the inputs are altered/different.

Rob

On Sunday, June 19, 2022 at 10:58:05 AM UTC-5 cwl...@gmail.com wrote:

Also, the official Docs are here, so you are referring to what Google wrote, not Amit on ctrlq.org :)

On Sunday, June 19, 2022 at 11:54:36 AM UTC-4 Clark Lind wrote:

Hi Rob,  I don't know if Amit will respond. But I can see his point of view. I'm thinking it is just the nature of a custom function that makes using cache not very useful.

Cache is certainly useful in other functions and macros, but not the defined 'custom function'** since they just return a result based on input. If you ever needed to store something in cache for later retrieval outside the custom function, you are probably not using a custom function. 

**Custom function is a defined term relating only to Sheets and the purpose is to allow user to create new sheet functions or formulas when the built-in functions (e.g. "=left()", "=countif()", "=trim()", etc) aren't enough.

On Thursday, June 16, 2022 at 1:31:39 PM UTC-4 robt...@AOL.com wrote:

In the doc, it says that Cache is not very useful in custom functions.  ( https://ctrlq.org/google.apps.script/docs/guides/sheets/functions.html )

But why is that?  Why is it not very useful?  Is it too slow?  Or is it just that a public cache has few uses inside a custom function, in the writer's opinion?

Rob

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ea066fa9-3f79-4bf1-be4e-0cf716f0663dn%40googlegroups.com.

[R Tichy]

@butlerjon...

Can you describe more about the circumstances under which you thought the cache was useful within a custom function?  I mean, it sounds like what you are saying is that you might take a data set out of a sheet, work on it (repeatedly, while storing work in process in the cache) and then write the results back to the sheet at the end?

[Jonathan Butler]

I use it to load large amounts of data into the cache quickly to be available for reading. If for example there are an unknown amount of formulas in a 20,000-row sheet, you may be tempted to grab all of the data into the cache after each write call. However, this can be a very long synchronous process (10s of seconds on slow apps script days). Reading data is extremely fast from custom functions. So I have a group of custom function cache separate chunks of data which will be pieced together on reads.

This process happens async in about 3 seconds instead of 20 - 40 second reads.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/fe601735-e51c-48f3-9581-299a1ef7bbd2n%40googlegroups.com.