Reference Data Management - Any suggestions

[Tony Broadbent]

Hi all, I look after a bunch of sheets covering a number of different departments.
I'm finding I'm copying the same 'reference data' into multiple spreadsheets and/or into the tops of Apps script code.
Things like sheet IDs, number of days before reporting, email address lists.
I'd like to store these in one place.
I could store them in a central spreadsheet, which would allow 'non-coders' to maintain them, but the overhead of reading them from there every time any function anywhere runs would slow everything down.
How would you handle this?
For supportability reasons we try to avoid using Add-ons.
Thanks,
Tony

[Andrew Roberts]

I would experiment to find out how much is does actually slow things down. Alternatively you could use the cache service. 

--
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/860b7b4a-3df0-4d92-9807-1a7dbbccecdcn%40googlegroups.com.

[Tony Broadbent]

Hi Andrew, 

I find reading the spreadsheet adds 250-500ms. 
Not a lot, but I have a lot of onEdit triggers, where this sort of delay is noticeable, so I try to keep spreadsheet reading/writing to an absolute minimum.

The cache service is sheet (or script) local, so couldn't be shared across multiple spreadsheets.

I guess I could read a remote sheet during my onopen trigger, and write the data to a local sheet (for any formula which use it) and to a script cache.

Would this be stable for many users opening the sheet while others are running functions or code?

Thanks,

Tony

[Bruce Mcpherson]

Hi Tony

You might be interested in this for your request.

https://groups.google.com/g/google-apps-script-community/c/2DBlqr5ALAs

[Tony Broadbent]

Thank you so much for letting me know about this Bruce, and whether it's true or not I'm going to pretend you wrote this in response to my request.

This should make my life a whole load easier, and I'll look at incorporating it over the next few weeks.

Keep up the great work, many thanks,

Tony

[Bruce Mcpherson]

Hi Tony

Your question prompted me to create it over the weekend - and I'm actually using just today to keep costs under control while developing some gemini stuff - so thanks for the suggestion - it's helping me too!

cheers

bruce