This post is a continuation of this one. Last time I finished with acquiring your AuthSub session token, and now I’ll show you how to use it to manipulate your spreadsheet.

Reading Your Spreadsheet

It’s really easy to read one of your spreadsheet’s cells, especially with RESTClient. You just need to know your spreadsheet’s ID, which is the key value I mentioned earlier. Simply send a GET request to

http://spreadsheets.google.com/feeds/cells/key/1/private/full/cell

With headers:

Authorization: AuthSub token="yourSessionAuthToken"
GData-Version: 3.0

Replace key with your spreadsheet’s key, and cell with a string in the format “RXCY”, with X and Y being positive integers (row and column numbers).

Note that the section /1/ indicates that you’re reading the first worksheet in the spreadsheet, i.e. “Sheet1″. If you’d like to work with “Sheet2″, well, you can figure that out. GData-Version simply specifies which version of the API protocol you’re using.

Anyway, the server will return a bunch of XML — just look for inputValue='example cell value' and you’re good to go.

Modifying Your Spreadsheet

In order to modify the spreadsheet, you’ll send a PUT request to the same URL:

http://spreadsheets.google.com/feeds/cells/key/1/private/full/cell

The body of the request should be:

<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gs="http://schemas.google.com/spreadsheets/2006">
  <link rel="edit" type="application/atom+xml" href="http://spreadsheets.google.com/feeds/cells/key/1/private/full/cell"/>
  <gs:cell row="X" col="Y" inputValue="whatever"/>
</entry>

Replace key and cell as before, and X, Y, and whatever self-explanatorily.

Finally, the headers of the request must be:

Content-Type: application/atom+xml
Authorization: AuthSub token="yourSessionAuthToken"
GData-Version: 3.0
If-Match: *

That is all pretty easy except the If-Match. The asterisk value for that header tells the spreadsheet to update even if there has been another recent write to that cell (destroying thread safety). If you want to be more punctilious, look here.

Complaints And Possible Expansions

If all went well, you can now programmatically read and write a Google Spreadsheet. I’ll add some slightly more sophisticated sample code in the next post, but for now, here are a couple weaknesses I’ve found.

First, the spreadsheet mostly updates values in real-time, but occasionally (seemingly after a long time-out) requires a page refresh to update. Yeah, pretty minor. The other problem is more serious, though. Sometimes (presumably when traffic is high) App Engine’s urlfetch throws errors because the Spreadsheets server takes too long to respond to a request. This means that some updates aren’t recorded. Could be a problem if you’re really counting on statistics, but, as mentioned, then you should be trying something else. However, there are also opportunities for expansions that could alleviate both these issues.

The best expansion/optimization I can think of would be to use App Engine’s Memcache API. You could buffer spreadsheet updates, and rollback whenever the Spreadsheets server timed out. And you could also send the buffered requests in batches (more on that in the sample code), minimizing traffic. My itch got scratched before this point, but if you do get something like this working, let me know and I’ll use it happily! The other expansion would be to use tables instead of cell-level access. This seems to be the preferred method of quasi-database Google Spreadsheets usage, but it seemed like unnecessary hassle to me. If you get serious, though, it could be something to look into.