April 5, 2013

How to parse ScriptDB JSON data into Spreadsheets


Using ScriptDB JSON data is very handy in many scenarios, specially when you need to avoid accidental overwriting when there are two or more instances trying to use the same data.

In "How to share/store Spreadsheeds as JSON data with ScriptDB!" I talked about how to populate your ScriptDB with a Spreadsheet parsed into flat JSON, now let's explore how to put that JSON data back into a Spreadsheet, and this can be done either manually for things like creating an Up-to-this-moment report, or programmatically, so you can keep only your most recent and fast-changing data in ScriptDB and keep a very well organized and readable backups inside Spreadsheets.

Let's take a look at our example Spreadsheet (it's the same one as in the previous article):



You can read an example about how to parse your ScriptDB JSON data into a new sheet in the documentation, so let's use it with a few adjustments so that every time you call the script, it dumps the data inside a sheet whose name is the date of such backup. This is very handy if you plan to use automated reports, doing it manually is very useful for things like "closing" a working shift of a Point of Sales, don't forget to empty the ScriptDB's contents in this scenarios after the data is safe inside the sheet.

Now its time to create a new script, let's get a look at our code:



Everytime we run our script, a new sheet (with the date as name) will be created. Our ScriptDB JSON data looks 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}

And once it's inside a new sheet, it will look like this:



Do note that the order of the rows and/or columns might be different from the original table, so plan your reports accordingly.

Feel free to let us know about your ideas, suggestions, requests, etc. at the bottom of this article.

Happy coding!