Aug 13, 2014

Using a Google Drive Spreadsheet as a database for Blogger

One of the "limitations" to Blogger is that you don't have access to a database to create fields for populating content. In previous posts we've showed you how to create a form using Google Drive and to incorporate it into your Blogger website; in this tutorial we're going to show you how you can also use that same content, stored in the Google Drive Spreadsheet, to populate your site with the content, as well as content from any other Google Drive Spreadsheet.

This tutorial uses JQuery as the Javascript library, as well as JSON as the format to pull in the content. While the example only shows it in a simple list format, there are many different ways you can use this data and functionality, such as building an interactive rolodex for example.



The Steps:

  1. create spreadsheet in Google Drive or go to the spreadsheet created by your submission form
  2. grab the docKey from the URL that looks like https://docs.google.com/spreadsheets/d/THE-KEY/edit#gid=0
  3. use Row 1 in the spreadsheet to be the column headers that are used in the variables in the code. 1-word headers preferred for this example
  4. go to File -> Publish to the Web, check "automatically republish when changes are made", and hit "start publishing"

The code example:


<script src='//www.google.com/jsapi' type='text/javascript'/>
<script src='//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js' type='text/javascript'/>
<script type='text/javascript'>//<![CDATA[
/**
this script was written by Confluent Forms LLC http://www.confluentforms.com
for the BlogXpertise website http://www.blogxpertise.com
any updates to this script will be posted to BlogXpertise
please leave this message and give credit where credit is due!
**/

var docKey = '1Xg095tdG_9VY0b93FSh0eK52I2g6SjGg-g-pJ3u95YU'; // the docKey of your Google Docs spreadsheet
var insertAfter = '#Blog1'; // reference of object in your DOM to insert the object into
var objectType = '<div>'; // object that we'll be inserting as a container for the feed information
var objectID = 'thefeed'; // id of the object that is being inserted
var url = 'http://spreadsheets.google.com/feeds/list/' + docKey +'/od6/public/values?alt=json';

$(document).ready(function(){
google.load('feeds', '1', { 'callback' : function(){ 
($.getJSON(url, 'callback=?')).success(function(data){
$(insertAfter).after(
$(objectType).attr('id',objectID)
);
var wrapper = $('#'+objectID);
            $(data.feed.entry).each(function(i,entry){
var name = entry.gsx$name.$t;
var url = entry.gsx$url.$t;
var type = entry.gsx$type.$t;
var location = entry.gsx$location.$t;
wrapper.append(
$('<ul>').append(
$('<li>').text(name)
).append(
$('<li>').text(url)
).append(
$('<li>').text(type)
).append(
$('<li>').text(location)
)
)
});
        });
    }});
});
//]]></script>
</head>
<body>
<div id='Blog1'>this won't be necessary in your actual implementation, placeholder for the Blog1 object</div>
</body>
</html>



Notes:

Please note that the first two lines will likely not be necessary in your template as they are likely already there. Add everything from <script type='text/javascript'>//<![CDATA[   to  //]]></script>  into your template, before </head>, to get it to work.

A clean version of the code can be found here.

To use multiple worksheets within one Google Spreadsheet, you need to get the individual Worksheet IDs. To do that ask for your worksheet feed by going to:

https://spreadsheets.google.com/feeds/worksheets/YOUR_SPREADSHEET_ID/private/full

(replace the variable with your own ID)

Within that document, easier to see if you format the XML, you'll see the different ids beginning with the first labelled "od6".