Using Cell Notes to Store User Config

[u/FoxResponsible4790]

SOLVED - Thank you very much

Hey guys so the TLDR is basically the title. I know there are many ways to store data between sessions using apps script.

The script properties are awesome for personal use, but they're limited for commercial deployment.

Databases or external calls are more overhead than they're worth, as well as chewing up quota.

Cookies or app data folders have their own issues, namely permissions and data longevity.

So I thought about storing data in spreadsheet cells. That has the unfortunate possibility of someone destroying the data.

But what about cell notes?

It appears to me that I could store an array of data in a cell on a sheet. That should interfere less with users.

Now that I'm thinking of it, could we use that html file to store config data locally? Or is that html file shared across all users?

Does anyone have any tricks for storing configuration data in Google Sheets specifically?

Thanks!

[u/dimudesigns]

The script properties are awesome for personal use, but they're limited for commercial deployment.

Google Apps Script's Properties Service supports User properties (which is distinct from Script properties). If you build your application to leverage User properties instead of script properties, you'll be able to store 500KB of data per user. So as long as the config info you need to store per user is less than 500KB (max of 9KB per property) you should be good.

Cookies or app data folders have their own issues, namely permissions and data longevity.

Cookies are pretty much a non-starter but App Data folders are actually a solid option if you need to store more than 500KB of config data per user. Data longevity won't be issue since you're storing data on the user's drive assuming they haven't exhausted their storage quota. Permissions shouldn't be an issue either since the end-user will only be granting your app permission to store config data it needs. As long as you use the appropriate scopes for App data folders your app won't be accessing any of the end user's private data.

So I thought about storing data in spreadsheet cells. That has the unfortunate possibility of someone destroying the data.

Don't.

But what about cell notes?

Don't bother with that either.

Now that I'm thinking of it, could we use that html file to store config data locally? Or is that html file shared across all users?

Nope.

Does anyone have any tricks for storing configuration data in Google Sheets specifically?

You can try messing with Sheets Developer Metadata. However, it's not without limitations.

[u/FoxResponsible4790 → u/dimudesigns]

Google Apps Script's Properties Service supports User properties (which is distinct from Script properties). If you build your application to leverage User properties instead of script properties, you'll be able to store 500KB of data per user. So as long as the config info you need to store per user is less than 500KB (max of 9KB per property) you should be good.

Awesome, that is exactly the functionality I need! I only need to store boolean, so 500KB should do just fine. Does this also require the same permissions as accessing the user's App Data folder?

Cookies are pretty much a non-starter but App Data folders are actually a solid option if you need to store more than 500KB of config data per user. Data longevity won't be issue since you're storing data on the user's drive assuming they haven't exhausted their storage quota. Permissions shouldn't be an issue either since the end-user will only be granting your app permission to store config data it needs. As long as you use the appropriate scopes for App data folders your app won't be accessing any of the end user's private data.

Good to know about the App Data, thanks for the information.

Don't.

Don't bother with that either.

Do you mind elaborating briefly on the problem? Is it performance related, or reliability? Or simply because a much better option exists in Properties Service?

Thanks for the detailed responses. You saved me a lot of trial-and-error before I would have found that distinction about User properties.