oTCG Developer Resource

Using Google Spreadsheets as a Database

Posted on October 20, 2013 | JavaScript, Tutorials

Google Spreadsheets, in conjunction with the Google's Spreadsheet Data API, makes for an extremely easy and simple way to organize complex data and allow others to collaborate with you. It's particularly great if you want to get a manageable, semi-automated list up on your site quickly - without having to tinker with PHP, MySQL, or worse, trying to manage your list manually with HTML.

Use Cases:

  • Upcoming Decks/Donations List
  • Contest Entries List
  • Team Events (Keep a list of participating members, scores, etc.)
  • Basically any kind of list that doesn't contain any private/sensitive data

And if you're managing your TCG the old-fashioned way, without automated join forms and user logins,  you could even use this to manage your members, cards, and activities lists!

Example: Upcoming Decks List

A list of upcoming decks, automatically synced with my Google Docs Spreadsheet. View the demo and completed code on JSFiddle!

For this example, we'll look at creating a simple "Upcoming Decks" list, which involves:

  1. Creating a spreadsheet in Google Docs
  2. Configuring the spreadsheet to allow data sharing
  3. Connecting to Google's Data API and retrieving the JSON feed
  4. Displaying the list of data on your website
  5. Adding contributors to your spreadsheet, so others (ie. deck makers) can access and update the list

1. Creating a Spreadsheet in Google Docs

Sign in to your Google account (or create one, if you need to) and navigate to your Google Drive.

Click on the "Create" button, and then proceed to select "Spreadsheet" from the menu.

You should now see an empty spreadsheet. Give your new spreadsheet a name, if you'd like. Then start populating the spreadsheet with your list data! You can view and/or copy my spreadsheet if you'd like a reference or starting point.

I recommend reserving the top row for your field names. You can "freeze" this first row by clicking "View" > "Freeze rows" > "Freeze 1 row".

Notice that all of your changes are saved automatically - no need to obsessively click CTRL + S! :D

2. Configuring the Spreadsheet to Allow Data Sharing

In order for the Google Data API request to work, you must "Publish" your spreadsheet. Navigate to "File" > "Publish to the web..." and click on the "Start Publishing" button.

You should also check "Automatically republish when changes are made" to ensure that the Google Data API can see any updates that you make to the spreadsheet.

3. Connecting to Google's Data API and Retrieving the JSON Feed

For this next step, you will need to identify your spreadsheet key. You can extract the key directly from the URL of your spreadsheet on Google Docs.

Example: https://docs.google.com/spreadsheet/ccc?key=0Aohls9-R-EcHdGtCSE81aFFpRGY3cGVPRUUwSVl5dUE

Once you've found your key, replace the "PASTE_KEY_HERE" placeholder in the code below with your key:


This code requires jQuery to be installed. If you don't already have jQuery installed on your site, just add the following code into the <head> tags of your page:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

You've successfully connected to the Google Data API! Now, let's do something with that spreadsheet data.

4. Displaying the List of Data on Your Website

Let's set up a basic table structure to display the Upcoming Decks list. Modify the table headers to match the column headers in your spreadsheet:

<table id="upcoming">
        <th>Deck Name</th>
        <th>File Name</th>
        <th>Deck Maker</th>

Now, we'll add to our code from step #3 in order to populate the table with the spreadsheet data. Place this at the bottom of your page, before the closing </body> tag:

.done( function(data) {
    var ssData = data.feed;
    $.each( ssData.entry, function() {
        // show a checkmark for completed decks
        var complete;
        if ( this.gsx$complete.$t == 1 ) {
            complete = '&amp;#9745;';
        else {
            complete = '&amp;#9744;';
        // create and populate rows
            + '<td>' + this.gsx$deckname.$t + '</td>'
            + '<td>' + this.gsx$filename.$t + '</td>'
            + '<td>' + this.gsx$category.$t + '</td>'
            + '<td>' + this.gsx$deckmaker.$t + '</td>'
            + '<td>' + complete + '</td>'
            + '</tr>'

Modify the + '<td>' + this.gsx$FIELD_NAME_HERE.$t + '</td>' lines to match your spreadsheet structure. The variables should match the column headers in your spreadsheet, but be converted to alphanumeric-only characters with spaces removed.

Example: A header named Deck Maker!! should be referenced as deckmaker in your code.

Also, you may have already guessed, but in order for these to the match up with your table headers, they should be added in the same order as your <th> tags.

You're done! You should now have a lovely, synced up table displaying the data from your Google Docs Spreadsheet. Whenever you want to update the list, just make your changes in Google Docs, and it will automatically update the table on your site.

5. Adding Contributors to Your Spreadsheet

Want to give your co-owners, staff, and/or deck makers access to the spreadsheet, so that they can update the list as well? It's mindbogglingly easy.

Click on the Share button in the top right corner of your spreadsheet screen. A Sharing Settings dialogue will pop up, which will allow you to control who can view and edit your spreadsheet.

You can limit editing permissions to specific users by adding their email address to the "Invite people" textbox at the bottom of the dialogue:

Make sure "Can edit" is selected if you want them to be able to update the spreadsheet.

Final Remarks

You can view the working demo and play with the code on JSFiddle: http://jsfiddle.net/oenkitt/5kYme/

This is only scratching the surface of what Google APIs can accomplish. Play with it, and see what you can come up with!