a peek into my MIND

August 19, 2010

Managing Google Spreadsheets using Documents List API and Spreadsheet API – Part 1

Filed under: General — Tags: , , — Bharat Kondeti @ 11:36 pm

This tutorial is first part in a series of tutorials for managing and writing into Google spreadsheet. Each post is a problem and a way to solve it. Example code in these tutorials are for single client JavaScript applications. Once concepts are understood, it’s very easy to implement the same in any language and for any application type. Comments or feedback are very much appreciated.

Google provides API to manage any documents on Google docs and different API to manage spreadsheets. API’s are in the form of restful services where in client application’s can manage documents by making HTTP requests to Google.

Google Documents List API: This API provides functionalities for managing documents. This includes searching, retrieving, uploading, updating, copying, downloading and deleting of documents, creating and moving folders, modifying folder and document permissions, managing document revisions and finally some special features like document translation and Optical character recognition.

Google Spreadsheet API: This API is specifically for managing data inside a spreadsheet. Clients can use this API to manage worksheets, tables, records and cells inside a spreadsheet. This API does not provide functionality to create and delete a spreadsheet, but this can be easily done by using documents list API.

To interact with these API’s one has to have a Google account and client needs to pass in an authorization key with every request. Before a client can start using the API it has to authenticate the user by passing in username and password along with the service name for which it needs authorization key for. An authorization key obtained for one service cannot be used for other service. Click for complete list of API’s and their respective service names.

Google provides couple of different ways to authenticate a client application. One is by using ClientLogin specifically for single user client applications and other is by using AuthSub proxy authentication for multi user web applications. Either way the idea is to acquire an authorization key or token that has to be passed for every client interaction with the API.

Every request a client makes to Google docs, it has to specify the GData-Version in the headers of the request. Current version is 3.0. Every document created or uploaded to Google docs is given a unique document ID. Clients has to use this ID to perform any further actions on the document.

Problem 1:

Create an empty spread sheet in Google docs by name ‘Example.xls’ and return the document ID. Before, check to see if the spreadsheet is already present in Google docs, if present return its document ID without creating new one again.

  • 1) Authenticate with Documents List API and get a Auth key
  • 2) Search for the spreadsheet by name ‘Example.xls’, If spreadsheet exists retrieve the ID
  • 3) If spreadsheet is not found create the a blank spreadsheet by name ‘Example.xls

Document’s List API is a service of type ‘writely’. A HTTP post request is made to ClentLogin URL to get the auth key.

//URL for ClientLogin
var url = 'https://www.google.com/accounts/ClientLogin';

//Content type has to be this
var ct = 'application/x-www-form-urlencoded';

// account type is HOSTED or GOOGLE when unsure use following
var at = 'HOSTED_OR_GOOGLE';

var un = 'your Google email';
var pwd = 'your password';

var serv = 'writely'

//This can be any fixed string. Used for logging by Google
var src = 'application identifier’

var request = new Ajax.Request(url, {
  method: 'post',
  contentType: ct,
  parameters: {accountType: at, Email: un, Passwd: pwd, service: serv, source: src},
  
  onSuccess: function(response){
    // Parse the auth key from response and save it somewhere
    //do something else like call search method to find the spreadsheet
  }.bind(this),
     onFailure: function(response){
     // puke, look for status and act accordingly
  }.bind(this)
});

To determine authorization key one has to parse the response. The response is returned as Key = Value pairs and client needs to look for ‘Auth’ key. If for any reason Google fails to authenticate the user it returns a response along with error code.

var a = null;
var pairs = response.responseText.split("\n");
	 
for (var i=0; i <pairs.length; i++){
  var fields = pairs[i].split("=");
  var key = fields[0];
  if(fields[0] == 'Auth') {
    a = fields[1]
    break;
  }
}

Searching for documents is very straight forward. One can perform queries on document types, names , locations and meta-data. For our example I am looking for a spreadsheet with name ‘Example.xls’ in my private folders.

var spreadsheetName = 'Example.xls';

// authWritely is the key obtained as part of ClientLogin
var auth = 'GoogleLogin auth='+this.authWritely;

var url = "https://docs.google.com/feeds/default/private/full/-/spreadsheet?title="+spreadsheetName+"&title-exact=true&showdeleted=false";

//Content type
var ct = 'application/atom+xml';

var request = new Ajax.Request(url,{
   method: 'get',
   evalJSON: 'false',
   contentType:ct,
   requestHeaders:{ Authorization: auth , 'GData-Version':'3.0'},
   onSuccess: function(response){
     //Search for document Id
     //If no spreadsheet is returned create one 	
   }.bind(this),
   onFailure: function(response){
     // puke, look for status and act accordingly
   }.bind(this)
 });

If a document is found with the search criteria a list of document entries are returned along with document ID and URL’s to manipulate the document. The easiest way to determine the document ID is to search for ‘resourceId’ in the response XML.

var id = null;
var resp = response.responseXML;
var entries = resp.getElementsByTagName('entry');
	
for(var i=0; i<entries.length; i++) {
  var resourceId = entries[i].getElementsByTagName('resourceId')[0].firstChild.nodeValue;
  var resourceIdSplit = resourceId.split(':');
  if (resourceIdSplit[0] == 'spreadsheet') {
    id = resourceIdSplit[1];
    break;
  } 			 				
}

To create a new document one has to post an entry request to Google docs. Following code snippet creates a new spreadsheet with name ‘Example.xls’ in private/full location. Using same approach clients can create any type of document under any location.


var spreadsheetName = 'Example.xls';

//Content type
var ct = 'application/atom+xml';

var pbody = '<?xml version="1.0" encoding="UTF-8"?>\
		    <entry xmlns="http://www.w3.org/2005/Atom">\
		    <category scheme="http://schemas.google.com/g/2005#kind"\
		          term="http://schemas.google.com/docs/2007#spreadsheet"/>\
		      <title>'+this.spreadsheetName+'</title>\
		    </entry>';
	
var auth = 'GoogleLogin auth='+this.authWritely;

var url = "https://docs.google.com/feeds/default/private/full";

var request = new Ajax.Request(url, {
  method: 'post',
  contentType:ct,
  evalJSON: 'false',
  contentLength: pbody.length,
  postBody: pbody,
  requestHeaders:{ Authorization: auth , 'GData-Version':'3.0'},
  onSuccess: function(response){
    // Document is created
    // Get the document id 
  }.bind(this),
  onFailure: function(response){
    // puke, look for status and act accordingly
  }.bind(this)
}); 

This completes the problem to authenticate a user, search for a spreadsheet and create a new spread sheet.

Advertisements

4 Comments »

  1. Hi.
    Thank you for this well-written article.
    I have spent so much time to investigate how to save user data in google spreadsheets.
    You helped me. !!!! THANX !!!!

    Comment by Oleg — November 30, 1999 @ 12:00 am

  2. hi, can you give a full sample of this tutorial. I really need it badly. Please be my angel. thanks in advance

    Comment by colborn — May 13, 2012 @ 2:02 am

    • sent code to your gmail account

      Comment by Bharat Kondeti — May 13, 2012 @ 9:53 pm

      • Hi, chould you send me the code too? Thanks in advance.

        Comment by Rick Watson — July 4, 2012 @ 7:42 pm


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: