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

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:

MySql sorting fields in a certain order.

MySql provides nice functionality to sort fields in certain order. This is very useful in scenarios where we have an enum Field represented as varchar and the values that it can hold are fairly static.

CHANNELS
——————————–
| ID | NAME | TYPE |
——————————–

TYPE field is a varchar that can take only SD, HD and 3D.

PROBLEM:

To get a list of channels sorted by TYPE in an order of SD, HD and 3D. This can be done in couple of different ways:

-- Using by FIELD
SELECT * FROM CHANNELS ORDER BY FIELD(TYPE, 'SD', 'HD', '3D'); 

-- Using by FIND_IN_SET
SELECT * FROM CHANNELS ORDER BY FIND_IN_SET(TYPE, 'SD,HD,3D');

If you know any other way please leave a comment

DbUnit and setting up changing data.

DbUnit is used to set up test data for integration tests that rely on a database. One way of importing and exporting database data in DbUnit is by XML data-sets. Assuming we have a table like this…

CUSTOMER
—————————————————————————–
| ID | FirstName | LastName | LicenceExpiryDate |
—————————————————————————–

We are testing a method called isLicenceExpired(id). To unit test this method we can setup some dummy data using DbUnits.

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <CUSTOMER ID="1" FIRSTNAME="FirstName1" LASTNAME="LastName1" LICENCEEXPIRTDATE="2010-08-25" 
  <CUSTOMER ID="2" FIRSTNAME="FirstName2" LASTNAME="LastName2" LICENCEEXPIRTDATE="2010-09-15" 
  <CUSTOMER ID="3" FIRSTNAME="FirstName3" LASTNAME="LastName3" LICENCEEXPIRTDATE="2011-01-01" 
</dataset>

Problem with above setup is test-cases would eventually fail because of ‘LicenceExpiryDate’. We need to setup this date such that it’s relative to current date. This is where ReplacementDataSet comes into picture. This class decorates FlatXmlDataSet class to provide replacement functionality. Now we can define your data set as