Programatically updating a spreadsheet hosted on google docs

Question

I've got a pre-existing spreadsheet hosted on google docs. Each month I update this document. I've got a template workseet in the spreadseet that I'd like to clone and then update.

I'd prefer to clone the worksheet rather than create it from scratch as it has some pretty complex formulas.

I'm using the Python api for the google docs here:

http://code.google.com/apis/spreadsheets/data/1.0/developers%5Fguide%5Fpython.html

Does anyone know how to clone and copy a worksheet in a pre-existing document?

Edit

I seemed to have confused one reader. I don't have an excel spreadsheet. I only have a Google Docs spreadsheet that has a template worksheet.

I'd like to clone this worksheet, rename it and then edit it programatically.

Answer 1

This is really complicated. I understand that you can edit your spreadsheets with Python using their API, Google tends to offer that ability on many of their web services and it's all done by sending HTTP post requests made of XML somehow, I hope you know that part, I don't.

According to this you can at least add worksheets, read rows from other worksheets and write rows to worksheets. if you must, you could copy it one row at a time, however sending an additional POST request for each row seems like a horrible idea.

EDIT

I'm learning more and more about this, but still a long way off from solving your original problem. This overview of REST principles goes over the basic style of interaction that goes on between programs on the web. Google seems to be following it religiously.

It all takes place within the HTTP protocol, something I knew nothing about before today. In this HTTP specification the basic game is spelled out. Its not as dry as it looks, and maybe Im just a huge geek, but I find it an inspiring read. Not unlike The Constitution of The United States.

So since you want to "clone" a document, your going to be using a GET request for a particular worksheet, and then sending that worksheet back as the payload of POST.

Getting closer :) -Nate

Answer 2

Couldn't you could export your spreadsheet as a xls and then upload it as a new doc with a (slightly) different name, specifying the new name in the XML metadata?

The Download and Create/upload Document sections at http://code.google.com/apis/documents/overview.html should be beneficial.

I can't immediately see any import/export functionality in the Python API docs, but sending a few http requests isn't so bad.

Answer 3

First of all, I've never worked with Python before - but I'll tell you how I did this in C++.

I've used cURL to make a GET request to the google documents API. The binary data of the file was returned and I wrote that to a file. Now I had the XLS file and then I used a C/C++ library that could read XLS files to manipulate the downloaded file. The API that I used supported a variety of options; you could do anything that you could do in Excel. After modification I uploaded it again to Google Docs.