by +Cesar Anton
@reicek
Unlike a normal Data Base, spreadsheets can pre-process the information it receives and automatically keep calculated values updated (like total, averages, etc...) they are extremely flexible and with proper planning they can be a great alternative in small-medium applications.
Tip: You can programmatically create new spreadsheets to keep the information light and organized (remember Spreadsheets has a limit quota of about 400,000 cells), its like having a new Database each week/quarter/month. You can even have an extra one that keeps the totals for each year (storing only totals taken from smaller Spreadsheets), and on top of that, a "Global" one that keeps the totals of all the yearly ones... And by being stored that way, creating reports and charts its very easy.
Extra tip: For the "Central" Databases, use Spreadsheets for simple Data, Fusion for advanced central data or Big Query if you need fast processing on very, very large amounts of data.
The easiest way to store and administer the information flow to a Spreadsheet its via Apps Script, and the easiest way to send information to Apps Script it's using UI services. For more advanced applications I suggest the use of servlets, which are basically small Apps Script programs acting as very specialized servers to query, process and deliver specific data to and from your Webapp (using the Content Services) or even JDBC-compliant data base (Google Cloud SQL, MySQL, Microsoft SQL Server, Oracle, étc.) using Jdbc Services, you can call it a "trusted-robot-in-the-middle" approach to make things easier and faster.
Advanced Professional Tip: Properly using Servlets along with your usual JDBC-compliant Database it's extremely useful since it lets you greatly improve performance because Apps Script Servlets has fast, direct access to all Google Services and once authorized, they don't need to deal with oAuth every time they make a request, which is very convenient. Also, when Google makes updates, you don't need to update your server's code to regain functionality, only the servlet and you can do that from virtually any modern browser (even some mobiles!). Also it's easy to identify the servlet that needs updating. Since the output from the servlet will remain constant through time (any text array like JSON or a binary blob), you don't need to worry about updating your servers code. Also, all this parallel processing will greatly reduce the load on your server, saving you a lot of money on hardware and bandwidth. Additionally, Google Spreadsheets, if published, can provide direct RSS or ATOM feed.
So, first let's Manually create a Spreadsheet, for this example I created one called "spreadsheetDatabase" and then I set it's access to "public on the web" so it gets a public URL, you can be more restrictive and allow only specific users to interact with the spreadsheet. For now, you only need the "Key-ID" of the spreadsheet, you get it from the URL in your browser while you have it open, in our example, the key is the part highlighted in red.
https://docs.google.com/spreadsheet/ccc?key=0AvRClkFMLkcpdDZ4VHlGd016cTQ1dUo4LXVaenhYSWc#gid=0
Now we need to pre-fill the spreadsheet with the name of the fields we want to use, it's not necessary for the script to work, but will make your Spreadsheet DB as readable as any normal spreadsheet, it can also be used to automatically apply headers to charts and reports based on the Spreadsheet. For our example we will only need a field for "name" and a field for "comment", its a best practice to delete all the unused rows and columns from your spreadsheet, so it stays as short as possible. Our example will look like this:
So far no sweat right? Ok, then let's hit the code!
So first things first, you need to create a new Google Apps Script project. In it, we are going to need 2 functions, one that's going to create and deliver the Webapp's U.I. and another one to send the data to Google Spreadsheets.
Let's start with the U.I, for this we are going to be using Google Apps Script U.I. Services, our code will look like this:
Note that sendData it's the name we choose for the function() that we will use to send our data to our spreadsheets, let's see how it looks for our example:
Now let me explain a little more about what's going on that last piece of code... First the e you see on sendData(e) is called an event object, we use it to pass information from one function to another.
We start with the .insertRowAfter() method to add a new row to the sheet (so we can keep it always using the exact number of cells we need).
Then we use the .appendRow() method to insert the data carried by the event object (e) data into the new row.
Then we open a Popup telling the user that the information has been sent, this is very important both, for the user to confirm that the information was sent, and secondly to give the Spreadsheet time to process properly the new row.
Important Note: In this scenario, Spreadsheets are not very good at handling multiple insertions at the same time, ScriptDB and (and JSON) it's a better alternative as described on this other article.
Now let's see it all together:
Tip: To save you the trouble later, go to File -> Upgrade authorization experience...
Now let's give it a first run so it get's your authorization to access Spreadsheets.
Since it's the first time, you are going to need to Save a new version (you can give it any name you like, tip: try to be descriptive with your version names).
Then you need to say the identity the app its going to use when making requests, in this case it need to me as me so the information is sent on my name (and with my permissions!), if you choose that the App is run as the User accessing the app then the spreadsheet will either need to be publicly editable, or only users with edit rights could use the Webapp, pretty convenient huh?
And last but definitely not least, we need to set up who has access to the app, Only myself works great for private apps and beta testing, Anyone means that any user with a Google Account can use the App, this is important if you need to know the user's identity or if your App requires to act on behalf of the other user and finally, Anyone, even anonymous in this case the App will need to run as you to get access rights to Google Services (or you can use a publicly editable spreadsheet).
The user's will get an instant update of the code when you change the published version! Something went wrong with the latest update to your code? Don't worry, you can always go back to a previous version until you make the necessary changes to the code and save a new version. If necessary, you also get a special link to disable the Webapp, so you can quickly plug it on and off if necessary.
And now its finally time for our first run! Let's take a look at it:
It's probably not so flashy, but it works like a charm, plus you can always use HTML services (for advanced users) or embed the Webapp on a Google Site to add some styling.
Why don't you try out our example and leave me a comment? Here you can see the spreadsheet that stores all the data, remember it gets updated every 5 minutes, so feel free come back later to see your comment on this post!
Remember that besides the embeddable iframe you saw before, Spreadsheets can also be served as (click on the name to launch that versions feed!):
So, I hope you liked using Spreadsheets as Database with Apps Script, feel free to post your doubts, comments, ideas, etc. at the bottom of this article.
Happy Coding!
No comments:
Post a Comment
Do your have any questions or suggestions about this blog? Let me know! I'm always happy to receive feedback.