Jun 4, 2011

Creating spreadsheets in Google Docs... mission possible?

Exporting data as spreadsheets into Google Docs sounds easy and quite feasible. However, it also has a few gotcha's. After struggling for a day, I've decided to share my notes on approaching this problem.

Creating the spreadsheet
My initial requirement for my application is to export my data into a brand new spreadsheet where the user will have ownership of it. A quick stroll through the Spreadsheet API led me to a section describing the need to upload a spreadsheet beforehand or create one manually. This was definitely against my requirements.

However, buried inside the Google Documents List Data API is a mechanism for creating empty documents or from a template (I'll come to this in a minute). Voila!

Changing the term attribute to http://schemas.google.com/docs/2007#spreadsheet surely does the trick if I want a completely blank spreadsheet.

Feeding data into the spreadsheet
This is where it becomes more tricky -- as it turns out, there isn't any convenient way to use the list approach (something I preferred using as opposed to the cell approach) to populate an empty spreadsheet without first telling Google Docs the schema that your list would use. One bit of observation to make is that the list schema is derived from the header row in your spreadsheet, therefore if the A1 cell stores "sample text" as its value, every subsequent row will have the "A" column described as <gsx:sampletext>. In my case, I opted to use a pre-built template from which to create my spreadsheet (see above about copying documents); however, there might be also a workaround involving batch cell update.

Some random tips
Here are some random bits that I found useful during this exercise:
- Always pay attention to the response object when posting new items (be it documents, worksheets, list entries, etc.) to Google Docs. The response object will provide you with some very useful information (stored inside the link element of the feed/entry) about what are the next logical APIs to call.
- The _worksheetId_ for the default worksheet in a newly created spreadsheet is... yes, "default". Should you need to add a row to it, you can simply call POST https://spreadsheets.google.com/feeds/list/[spreadsheet key]/default/private/full.


Edit: I went back to experiment with alternative ways of setting the header row of the default worksheet. As expected, one can set the header row by fetching the cells there and updating them with whatever contents they need to have.

5 comments:

Saqib Ali said...

Radi,

Thanks for posting this. I have been running into a similar issue. In my case I want to make a Google Docs copy of an Excel spreadsheet that was not converted during the upload process. Google provides this function in the DocsList Web UI file drop down, but I haven't have been to figure out how to invoke this using the APIs.

Any thoughts?

Thanks,
Saqib

radi said...

Hi Saqib,

You should check out http://code.google.com/apis/documents/docs/3.0/developers_guide_protocol.html#UploadingDocs. There are some examples of how to upload your file for conversion and how to provide metadata with the upload if needed. However, one important bit to keep in mind is the size limit (currently 1Mb) that Google enforces on files that go through conversion.

Saqib Ali said...

Radi,

The problem is that it is the 100s existing of non-converted Excel spreadsheet in the Google Docs that we need to convert to native Google Docs. I need a API way to convert these to native Google Docs.

Saqib

radi said...

Hi Saqib,

Alas I didn't see such an option in the Google Data API. If you need to preserve the original document locations, I'd recommend updating the entire document (ref: here) making sure that the convert parameter in the URL is set to true (ref: here). Otherwise, you can re-upload the files (again with convert set to true) and delete the old copies.

I'm not sure if there is an easier way around it. If you find something, please share. :)

Saqib Ali said...

Yea, I don't think there is an easier way either. I have been racking my brain on this for weeks. Haven't found anything :(

I'm hoping that Google exposes that Make a Google Docs copy function through the APIs.

Hope springs eternal :)

Saqib