There are many cases were we want to share data between many Apps, and one of the most popular ways it's to use Google Spreadsheets as a Central DB, and they do a great job on letting you share, download and access the Database in a very human way, but most of the time we want to efficiently query Data, but with Spreadsheets you need to know which cell has your data, filter using the visualization API or do some parsing... this makes it heavy and slow on large Data sets opposed to ScriptDB who is great for querying specific data!
Another limitation of using Spreadsheets is that they are not very good for handling shared accumulators, it's prone to over-writing when two or more users are working with the same cell at the same time, let's imagine the cell's current value its 3, and both client's add a +1, the cell's value its likely to end up as a 4 instead of 5... To prevent this ScriptDB has
2 different kinds of "locks", one public and one private, the difference is that public only allows 1 instance of the APP to run at any given time and the private lock only allows each USER to run 1 instance of the App at any given time.
Each script project get's a "sandboxed" ScriptDB for it's own use, meaning it's isolated from the rest of the users data and other ScriptDB instances from the same user. But were it really shines it's when you use a single ScriptDB for many Apps as a share point, you just need to create a tiny Apps Script webapp and share it as a library to get shared access to it's ScriptDB... not shiny enough? well, you can create many specific ScriptDBs to make everything safer, and organized by simply creating different library's and calling them as needed (creating different library's allows you to limit specific user's access) ... not enough? well, ScriptDB can be easily transferred to a Spreadsheet for a much more readable long term storage of our data. With some careful planning you can make smart data handling that's its fast, efficient and
la piece de resistance... it's all free!
So, which are the limits of ScriptDB? According to the
Documentation, they are actually large enough for most cases, the quotas are per-user (so the limit is shared across all user's Apps, or in the case of the library, it goes against the owner of the library) and it depends on the kind of user:
> 50MB for consumer accounts.
> 100MB for Google Apps accounts
> 200MB for Google Apps for Business/Education/Government accounts.
Since it's all Text data wrapped inside JSON objects, this can be quite a lot of data, even for 50MB. So we are usually more limited to the query limits (default 200, maximum 50,000) and the time limit for an Apps Script instance (maximum 6 minutes processing time), in most cases it's a good idea to dump all the ScriptDB's contents inside an Apps Script sheet or sheets for more comfortable future reference and to avoid saturating the ScriptDB's query's responses.
Also keep in mind that ScriptDB it's specially useful with current and fast changing data, and Spreadsheets its better for long-term storage and for making automatic reports. In most scenarios the best approach it's to create a Sheet everyday with the Date as a name and dump the ScriptDB content on it... Also you can start a new Spreadsheet every month, all automatically and, if done properly, you will end up with a Database that your customers will love for being cheap, fast, flexible and the automatic reports are a great plus (you can even send a custom report via e-mail every time ScriptDB is dumped inside a Sheet).
Without more preamble, let's hit the code! We'll start by creating a Library that returns a ScriptDB instance that can be shared between different apps,
create a new script and you only need this 3 simple lines of code!
To use this script as a library, remember you need to write down the script's project key, to get it, go to File - > Project properties.
If you plan to share access to this library with other users, remember to set the "Sharing Settings" which can be found in File - > Share...
To import your library, create (or open) a new Apps Script project and go to Resources - > Manage Libraries and write you Library's project key, inside the "Find a library" box.
Now, it's time to put some JSON data inside our library's ScriptDB! in most cases, we want to get our data from a Spreadsheet either because the Data is already on one, or simply because it is easier to write, as an example let's use the following spreadsheet:
The Apps Script code needed to do this, can be found on
this part of the documentation, let's see a copy (with a few modifications):
This will make a flat JSON version of our Spreadsheet inside our shared ScriptDB! Flat JSON is very easy to work with, and remember you can put it back or make a copy in a Spreadsheet at any time.
There is a
example code in the documentation to make a quick-check of your ScriptDB content, just
create a new script with the following code:
Now you can see the ScriptDB's content by checking the Apps Script Log, our example spreadsheet data would look like this:
{"price":5,"name":"Razor blade","salesToday":0,"onStorage":50}
{"price":2.5,"name":"Toothbrush","salesToday":0,"onStorage":100}
{"price":3,"name":"Liquid Soap","salesToday":0,"onStorage":80}
{"price":3.7,"name":"Shampoo","salesToday":0,"onStorage":80}
{"price":2,"name":"Toothpaste","salesToday":0,"onStorage":150}
Feel free to let us know about your ideas, suggestions, requests, etc at the bottom of this article.
Happy coding!
