Integrating Google Spreadsheets - Part 2

In my last post, I elaborated on how I have used the Google Documents List Data API and the Spreadsheets Data API to integrate Google Docs in my application. In this post, I will explain on how I provided online access to the spreadsheets to my application users. The users were not required to sign on to Google manually, I used SAML to sign them on and access the docs. To read more about how SAML works with Google, go through this article. The article is very helpful and
Before you make any changes in the application, you need to have administrator access to the Google apps account. You can enable provisioning API for programmatic account mgmt(I didn't). You need the following information about your application:
1. Login page
2. Logout page
3. Change password page
4. Certificate file (more info here)

Once you have the above information, log on to your Google Apps account. Go to the "Advanced tools" tab and click on "Set up single sign-on (SSO)". Once on the SSO page, enable SSO, enter the page details and upload the certificate file. Remember to save the changes. You can also create more user accounts to allow for better access control and user management. That's pretty much it on configuring the Google Apps for SSO. After you have saved your changes, go to the Dashboard and copy the Docs Service url (should be http://docs.google.com/a/). Try hitting the url, if you are redirected to the login page you entered in the SSO setup, things are going fine!

Now you need to provide implementation for SAML in your application. Go through this article, it has reference implementation for SAML-based SSO for the docs. Download the code and run the application. It will help you understand the way SAML works by printing the request, response, reference URLs and so on.
I used the example as the base reference in my development. I utilized the util classes AS-IS, and wrote the servlet implementation for the ProcessResponseServlet. The login() method needs to be updated to utilize your application's authentication mechanism. I also put in custom implementations in the service(doGet() and doPost()) method for the SSO process.

After these changes are made, you are pretty much done. Deploy your application, hit the Docs Service url again, it should redirect you to the login page, enter your applications credentials and your should be redirected to the Google Docs page.

Just as a side note, in my implementation, users had to be already authenticated to my application to be able access the Google Docs. I attached one Google user account with a group of my application users. When a user tried to access Google Docs, my SAML response servlet picked the Google account associated with the user and processed the SAML response.

Integrating Google Spreadsheets - Part 1

Here is a detailed description of how I integrated Google Spreadsheets into my application (I have broken the article in two posts):

My project required online access of the spreadsheets as well as being able to use their APIs to fetch, search, upload, download, read and delete spreadsheets.

In this first post, I explain on how I used their APIs to interact with the online docs.

Before moving any further, check out their online documentation of the APIs - Google Documents List Data API and Google Spreadsheets Data API

Here's a list of operations I performed using the APIs:

• Upload spreadsheets - I created the spreadsheets in my application(POI), filled them with content and evaluated the formulae. Then , i created a SpreadsheetEntry object and called the insert() method of the DocsService class. After the upload, I stored the resourceId of the entry in my application (that's the key to the doc just uploaded)
  


• Download spreadsheets - The APIs give a variety of download options, I used the xls option (4), created the export URL using the resourceId, and used the MediaSource class. Remember, to download spreadsheets, you need a valid SpreadsheetService token.
  


• Fetch spreadsheets - Same as handling the download option.
  


• Search within spreadsheets - Used the DocumentQuery class to create the query and the used the getFeed() method of the DocsService class. Check out the various url options for the DocumentQuery class, they really help to fasten search.
  


• Delete spreadsheets - Fetch the DocumentListEntry object of the document u need to delete, then call the DocsService's delete() method.
  


• Update/Read/Write cell contents - I found working with their cell-based APIs little slow, and since my requirement always involved reading and writing a lot of content, I used to download the spreadsheet, work on the local copy and then upload it back.
  

In the next post, I will elaborate on how I provided online access to users of my application to access the Spreadsheets online.

Generating XSD from XML

I came across a nice tool to generate XSD file from a XML data file. It pretty clean and easy to use.
The tool is known as Trang and can be downloaded from here. I used the "trang-20030619.zip" version.

Here's how I generated the xsd file:
1. Extract/Unzip the zip file(trang-20030619.zip).
2. You will find a .jar file in the extracted folder - trang.jar
3. Go to the folder and use this jar to create the xsd from the xml file

c:\[trang folder path]> trang.jar [xml file].xml [xsd file].xsd

Done! Simple, isn't it!