@reicek
One really cool thing about Google Spreadsheets is that you can effectively use them as an extremely user-friendly cloud Data Base for your WebApps.
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.
And finally it's time to deploy the app, to do so go to:
Publish ->
Deploy as Webapp...
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).
When you hit the
Deploy button you'll get 2 different URLs, the
Current webapp URL which is the
published version of your webapp, this shows the code saved under the
Project Version tag, and a special developer link were it says:
Test web app for your latest code, which runs the current version of your script code, this way you can make (and save) changes to the code, and once you get the desired behaviour, you save a new version and change the published version.
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!
