Developer's Guide: The Protocol

The Google Spreadsheets Data API allows client applications to view and update Spreadsheets content in the form of Google Data API feeds. Your client application can request a list of a user's spreadsheets, edit or delete content in an existing Spreadsheets worksheet, and query the content in an existing Spreadsheets worksheet.

Contents

Audience

This document is intended for programmers who want to write client applications that can interact with Google Spreadsheets. It provides a series of examples of basic data API interactions using raw XML/HTTP, with explanations. After reading this document, you may wish to learn more about interacting with the API using our client libraries by reading the language-specific examples found on the other tabs at the top of this document.

For Spreadsheets Data API reference information, see the reference guide.

This document assumes that you understand the general ideas behind the Google Data APIs protocol. Each example in this document first shows how to use the bare protocol to interact with Spreadsheets.

Getting started

Assume you're creating a client application that needs to communicate with Spreadsheets. You want your client to be able to get a list of rows or a collection of individual cells from a worksheet, and to be able to add a row or cell to that worksheet. This developer guide will walk you through these actions.

Note: If you don't already have a Spreadsheets account, you may want to create one for testing purposes. To follow the examples in this document, you'll also have to manually create a sample spreadsheet, as described in the Create a spreadsheet section.

You can, of course, interact with Spreadsheets in other ways too: update an entry, delete an entry, and so on, all using the Google Data protocol or the client libraries.

Note: The size limits described in How big can each document/spreadsheet be? (in the Google Docs & Spreadsheets Help Center) also apply to spreadsheets that you interact with using the Google data protocol. For example, you can't have more than 10,000 rows or more than 50,000 cells total in all the worksheets of a given spreadsheet. In addition, Google data imposes a usage limit: you can't send more than twenty data updates per second.

For general information about Google Data APIs and more example code that you can adapt for use with Spreadsheets, see the list of Client Libraries.

Create a spreadsheet

It is possible to create a new spreadsheet by uploading a spreadsheet file via the Google Documents List Data API. The Spreadsheets Data API does not currently provide a way to delete a spreadsheet. For testing purposes, you may create a spreadsdheet manually or upload one.

The examples in this document use a spreadsheet containing the following data. Note that the Items Per Minute (IPM) row uses a formula: =FLOOR(R[0]C[-1]/(R[0]C[-2]*60),.0001).

	A	B	C	D
1	Name	Hours	Items	IPM
2	Bingley	10	2	0.0033
3	Captain Carter	200	75360	6.28
4	Dawson	200	100000	8.3333
5	Colonel Forster	50	300	0.1
6	William Goulding	100	25842	4.307
7	Lady Lucas	100	25670	4.2783
8	Sir William	190	98765	8.6635
9	Charlotte	60	18000	5

Authenticating with the Spreadsheets Service

To request or edit spreadsheet data for an unpublished spreadsheet, your client needs an authentication token. The following sections explain how to authenticate for different types of client applications.

Note: You don't need to authenticate to request data from a published spreadsheet. (A published spreadsheet is one that the user has marked as published using the Spreadsheets GUI.)

Your choice of authentication method should be determined by the kind of client you're writing: single-user desktop applications should use the ClientLogin system, while multi-user web apps should use the AuthSub system. For a more detailed discussion of these authentication systems, see the Google Account Authentication document.

Whichever method you choose, you can either send raw HTTP requests, or have Google's client libraries handle the authentication for you.

Single-user "installed" client authentication

To authenticate the user, send a POST request to the following URL:

https://www.google.com/accounts/ClientLogin

Include the relevant parameters in the body of the POST request, as described in the ClientLogin documentation. The service name for Spreadsheets is wise.

If the request succeeds, then the response contains an alphanumeric string labeled Auth.

Note: As described in the ClientLogin documentation, the authentication request may fail and request a CAPTCHA challenge. If you want Google to issue and handle the CAPTCHA challenge, then send the user to https://www.google.com/accounts/DisplayUnlockCaptcha?service=wise (rather than to the CAPTCHA-handling URL given in the ClientLogin documentation).

After a successful authentication request, use the Auth value to create an Authorization header for each request:

Authorization: GoogleLogin auth=yourAuthValue

Multiple-user web application client authentication

To acquire an AuthSub token for a given Spreadsheets user, your application must redirect the user to the AuthSubRequest URL at Google, which prompts them to log into their Google account.

After the user logs in, the AuthSub system redirects them to the URL you specified in the next query parameter of the AuthSubRequest URL. The AuthSub system appends an authentication token to the next URL, as the value of the token query parameter. Your application then uses that authentication token in subsequent interactions with Spreadsheets.

The below example shows a request to authorize foo.com

https://www.google.com/accounts/AuthSubRequest?scope=http%3A%2F%2Fspreadsheets.google.com%2Ffeeds%2F&session=1&secure=0&next=http%3A%2F%2Fwww.foo.com%2Fhandletoken.pyc

Upgrading to a session token

By default, the AuthSub token can only be used for one request. To receive a token which can be used for multiple requests it must be upgraded by making an HTTP GET request which contains the single use token as an Authorization header. A token may only be upgraded if the initial AuthSub request included session=1 as a URL parameter.

GET /accounts/AuthSubSessionToken HTTP/1.1
Content-Type: application/x-www-form-urlencoded
Authorization: AuthSub token="yourAuthToken"
User-Agent: Java/1.5.0_06
Host: https://www.google.com
Accept: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2
Connection: keep-alive

If the token was upgraded successfully, the server's response contains the new token information in the HTTP headers. Here's an example response:

Token=DQAA...7DCTN
Expiration=20061004T123456Z

Use the authentication token to create an Authorization header for each request:

Authorization: AuthSub token="yourAuthToken"

For details, including information on registering your application with Google and other topics, see the AuthSub documentation.

Note: The token value in the Authorization header should be surrounded by quotation marks.

Get a list of spreadsheets

You can get a feed listing the spreadsheets for a particular user by sending an authenticated GET request to the following URL:

GET http://spreadsheets.google.com/feeds/spreadsheets/private/full

The result is a "metafeed," a feed that lists all of that user's feeds of a particular type; in this case, each entry in the feed represents a spreadsheet associated with the user.

This feed is accessible only using an authentication token.

An entry in the spreadsheets metafeed might resemble the following. The following example has been edited a little to make it a little more readable by humans; in particular, a real spreadsheets metafeed contains actual spreadsheet IDs where the following listing says key.

<entry>
  <id>http://spreadsheets.google.com/feeds/spreadsheets/private/full/key</id>
  <updated>2006-11-17T18:24:18.231Z</updated>
  <title type="text">Groceries R Us</title>
  <content type="text">Groceries R Us</content>
  <link rel="http://schemas.google.com/spreadsheets/2006#worksheetsfeed"
    type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/worksheets/key/private/full"/>
  <link rel="alternate" type="text/html"
    href="http://spreadsheets.google.com/ccc?key=key"/>
  <link rel="self" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/spreadsheets/private/full/key"/>
  <author>
    <name>Fitzwilliam Darcy</name>
    <email>fitz@gmail.com</email>
  </author>
</entry>

Note that the key value that appears here is the same as the key value that appears in the URL displayed in the address bar of your browser window when you open the spreadsheet manually.

The URI given in the <link> element with rel="http://schemas.google.com/spreadsheets/2006#worksheetsfeed" is the URI of the worksheets metafeed for that spreadsheet, which lists all the worksheets the user has access to in that spreadsheet. If that URI ends with /private/values, that indicates that the current authenticated user can only view the spreadsheet, not edit it. If it ends with /private/full, that indicates that the current authenticated user is authorized to add and edit data in the worksheets.

Note: User access levels are set in the GUI. If a user owns a given worksheet or is listed as an editor, then they have read/write access; if they're listed only as a viewer, then they have read-only access. In addition, if the worksheet is published then even unauthenticated users and users not listed as viewers have read access.

For further information about the spreadsheets metafeed, see the Spreadsheet metafeed reference documentation.

If the metafeed contains no entries, it means there are no spreadsheets created or shared by this account. Create a spreadsheet manually and request the metafeed again.

Interacting with worksheet-based feeds

A given spreadsheet may contain multiple worksheets. For each spreadsheet, there's a worksheets metafeed listing all the worksheets in that spreadsheet.

The following sections describe how to get a worksheet-based feed, add a worksheet to a spreadsheet, update the metadata of a worksheet, and remove a worksheet from the spreadsheet.

Get a worksheet-based feed

To determine the URL of a given spreadsheet's worksheets metafeed, find that spreadsheet's entry in the spreadsheets metafeed, as described in the previous section. Then examine the <link> element that has rel="http://schemas.google.com/spreadsheets/2006#worksheetsfeed". That element's href value provides the URL for that spreadsheet's worksheets metafeed.

To request a list of worksheets in a given spreadsheet, issue a GET request to that URL, with an appropriate authentication token:

GET http://spreadsheets.google.com/feeds/worksheets/key/private/full

There are several other visibility and projection values that you can use instead of private and full; for more information, see Spreadsheets feed types in the reference document.

Spreadsheets returns an Atom feed resembling the following. This example has been edited a little to make it a little more readable by humans; in particular, a real worksheets metafeed contains actual spreadsheet IDs where the following listing says key. Note that the string worksheetId, which appears several times in the example, is the permanent unique ID of a specific worksheet within the spreadsheet.

<feed xmlns="http://www.w3.org/2005/Atom"
    xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/"
    xmlns:gs="http://schemas.google.com/spreadsheets/2006">
  <id>http://spreadsheets.google.com/feeds/worksheets/key/private/full</id>
  <updated>2006-11-17T18:23:45.173Z</updated>
  <title type="text">Groceries R Us</title>
  <link rel="alternate" type="text/html"
    href="http://spreadsheets.google.com/ccc?key=key"/>
  <link rel="http://schemas.google.com/g/2005#feed"
    type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/worksheets/key/private/full"/>
  <link rel="self" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/worksheets/key/private/full"/>
  <link rel="http://schemas.google.com/g/2005#post" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/worksheets/key/private/full"/>
  <author>
    <name>Fitzwilliam Darcy</name>
    <email>fitz@gmail.com</email>
  </author>
  <openSearch:totalResults>1</openSearch:totalResults>
  <openSearch:startIndex>1</openSearch:startIndex>
  <openSearch:itemsPerPage>1</openSearch:itemsPerPage>
  <entry>
    <id>http://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId</id>
    <updated>2006-11-17T18:23:45.173Z</updated>
    <title type="text">Sheet1</title>
    <content type="text">Sheet1</content>
    <link rel="http://schemas.google.com/spreadsheets/2006#listfeed"
      type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
    <link rel="http://schemas.google.com/spreadsheets/2006#cellsfeed"
      type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full"/>
    <link rel="self" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId"/>
    <link rel="edit" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId/version"/>
    <gs:rowCount>100</gs:rowCount>
    <gs:colCount>20</gs:colCount>
  </entry>
</feed>

For further information about the worksheets metafeed, see the Worksheet-based feed section of the reference document.

Add a worksheet

To add a worksheet to the spreadsheet, start by creating an <entry> element containg the relevant data:

<entry>
  <title>Expenses</title>
  <gs:rowCount>50</gs:rowCount>
  <gs:colCount>10</gs:colCount>
</entry>

Then determine the appropriate URL to send the entry to, known as the POST URL. You can find the POST URL in the worksheet feed's <link> element that has rel="http://schemas.google.com/g/2005#post".

Next, do any necessary authentication, and create an Authorization header for a new POST request as described in the authentication sections of this document.

In the body of the POST request, place the Atom <entry> element you created above, using the application/atom+xml content type.

Now send the request to the POST URL:

POST http://spreadsheets.google.com/feeds/worksheets/key/private/full

Spreadsheets inserts the new worksheet immediately after the last worksheet that appears in Spreadsheets UI.

Update the metadata info of a worksheet

To change the metadata of a worksheet, begin by getting the desired worksheet from the worksheet feed. Send a PUT request with the desired entry contents to the URL provided in an edit link. The edit URL is highlighted in the XML below.

<entry>
  <id>
    http://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId
  </id>
  <updated>2007-07-30T18:51:30.666Z</updated>
  <category scheme="http://schemas.google.com/spreadsheets/2006" 
    term="http://schemas.google.com/spreadsheets/2006#worksheet"/>
  <title type="text">Income</title>
  <content type="text">Expenses</content>
  <link rel="http://schemas.google.com/spreadsheets/2006#listfeed"
    type="application/atom+xml" href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
  <link rel="http://schemas.google.com/spreadsheets/2006#cellsfeed" 
    type="application/atom+xml" href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full"/>
  <link rel="self" type="application/atom+xml" 
    href="http://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId"/>
  <link rel="edit" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId/version"/>
  <gs:rowCount>45</gs:rowCount>
  <gs:colCount>15</gs:colCount>
</entry>

Note: Metadata includes the title of the worksheet and the number of rows and columns. Setting the number of rows or columns to less than the original will delete the cells and contents that do not exist within the new boundaries WITHOUT WARNING.

Remove a worksheet

To delete a worksheet, perform a DELETE request on the edit URL after obtaining it as described above.

Note: If you delete a worksheet, that worksheet is completely removed from the spreadsheet and all data contained on that worksheet is discarded.

Interacting with list-based feeds

A given worksheet generally contains multiple rows, each containing multiple cells. You can request data from the worksheet either as a list-based feed, in which each entry represents a row, or as a cell-based feed, in which each entry represents a single cell. For information on cell-based feeds, see Interacting with cell-based feeds.

The following sections describe how to get a list-based feed, add a row to a worksheet, and send queries with various query parameters.

The list feed makes some assumptions about how the data is laid out in the spreadsheet.

In particular, the list feed treats the first row of the worksheet as a header row; Spreadsheets dynamically creates XML elements named after the contents of header-row cells. Users who want to provide Google Data feeds should not put any data other than column headers in the first row of a worksheet.

The list feed contains all rows after the first row up to the first blank row. The first blank row terminates the data set. If expected data isn't appearing in a feed, check the worksheet manually to see whether there's an unexpected blank row in the middle of the data. In particular, if the second row of the spreadsheet is blank, then the list feed will contain no data.

A row in a list feed is as many columns wide as the worksheet itself.

Get a list-based feed

To determine the URL of a list-based feed for a given worksheet, get the worksheets metafeed and examine the <link> element in which rel is http://schemas.google.com/spreadsheets/2006#listfeed. The href value in that element is the list feed's URI.

To retrieve a list-based feed, send the following GET request, using the list feed's URI and an appropriate authentication token:

GET http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full

Google Spreadsheets returns an Atom feed resembling the following:

<feed xmlns="http://www.w3.org/2005/Atom"
    xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/"
    xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">
  <id>http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full</id>
  <updated>2006-11-17T18:23:45.173Z</updated>
  <title type="text">Sheet1</title>
  <link rel="alternate" type="text/html"
    href="http://spreadsheets.google.com/ccc?key=key"/>
  <link rel="http://schemas.google.com/g/2005#feed"
    type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
  <link rel="http://schemas.google.com/g/2005#post"
    type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
  <link rel="self" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
  <author>
    <name>Fitzwilliam Darcy</name>
    <email>fitz@gmail.com</email>
  </author>
  <openSearch:totalResults>8</openSearch:totalResults>
  <openSearch:startIndex>1</openSearch:startIndex>
  <openSearch:itemsPerPage>8</openSearch:itemsPerPage>
  <entry>
    <id>http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId</id>
    <updated>2006-11-17T18:23:45.173Z</updated>
    <category scheme="http://schemas.google.com/spreadsheets/2006"
      term="http://schemas.google.com/spreadsheets/2006#list"/>
    <title type="text">Bingley</title>
    <content type="text">Hours: 10, Items: 2, IPM: 0.0033</content>
    <link rel="self" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId"/>
    <link rel="edit" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId/version"/>
    <gsx:name>Bingley</gsx:name>
    <gsx:hours>10</gsx:hours>
    <gsx:items>2</gsx:items>
    <gsx:ipm>0.0033</gsx:ipm>
  </entry>

  ...

  <entry>
    <id>http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId</id>
    <updated>2006-11-17T18:23:45.173Z</updated>
    <category scheme="http://schemas.google.com/spreadsheets/2006"
      term="http://schemas.google.com/spreadsheets/2006#list"/>
    <title type="text">Charlotte</title>
    <content type="text">Hours: 60, Items: 18000, IPM: 5</content>
    <link rel="self" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId"/>
    <link rel="edit" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId/version"/>
    <gsx:name>Charlotte</gsx:name>
    <gsx:hours>60</gsx:hours>
    <gsx:items>18000</gsx:items>
    <gsx:ipm>5</gsx:ipm>
  </entry>
</feed>

Each entry represents a row in the worksheet. The <title> element contains the contents of the first cell in the row, and the <content> element contains a textual representation of the data from all the other cells in the row.

Each item in the gsx namespace represents the contents of a cell in the row, with elements in the gsx namespace named after the contents of the header cells—that is, the cells in the first row of the worksheet. If a particular header cell is empty, then a unique column ID is used for the gsx element name. If the header cell's content contains a space, it will be removed in the gsx element name. For more information, see the gsx namespace elements section of the reference document.

Note: In a column with an empty header cell, empty cells don't appear as gsx elements; in such a column, only cells that contain data appear as gsx elements.

The entries are ordered in the same order in which they appear in the worksheet GUI, unless the query includes the orderby query parameter.

For further information about the list feed, see the List-based feed reference section.

Reverse-sort rows

By default, rows in the feed appear in the same order as the corresponding rows in the GUI; that is, they're in order by row number. To get rows in reverse order, send the following request, using the reverse query parameter to specify the sort order:

GET http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?reverse=true

To sort by the values in a particular column, rather than by row number, use the orderby query parameter.

For more information about orderby and reverse, see the List-feed query parameters reference section.

Send a structured query

You can use the structured query parameter sq to produce a feed with entries that meet the specified criteria. Suppose in our example you wanted to determine which workers at Groceries R Us are not meeting the minimum of 4 items per minute, but you realize it is unfair to consider new employees (those with no more than 40 hours). The HTTP request would be as follows:

GET http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?sq=ipm<4%20and%20hours>40

For more information about the structured query syntax, see the List-feed query parameters reference section.

Add a row

To add a row to the table, start by creating an <entry> element containing the relevant data:

<entry>
  <gsx:hours>1</gsx:hours>
  <gsx:ipm>1</gsx:ipm>
  <gsx:items>60</gsx:items>
  <gsx:name>Elizabeth Bennet</gsx:name>
</entry>

Then determine the appropriate URL to send the entry to, known as the POST URL. You can find the POST URL in the list feed's <link> element that has rel="http://schemas.google.com/g/2005#post".

Next, do any necessary authentication, and create an Authorization header for a new POST request as described in the authentication sections of this document.

In the body of the POST request, place the Atom <entry> element you created above, using the application/atom+xml content type.

Now send the request to the POST URL:

POST http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full

Spreadsheets inserts the new row immediately after the last row that appears in the list-based feed, which is to say immediately before the first entirely blank row.

Edit a row

To change the contents of a row, begin by getting the desired row from the list feed. Send a PUT request with the desired entry contents to the URL provided in an edit link. The edit URL is highlighted in the XML below.

<entry>
  <id>http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId</id>
  <updated>2006-11-17T18:23:45.173Z</updated>
  <category scheme="http://schemas.google.com/spreadsheets/2006"
    term="http://schemas.google.com/spreadsheets/2006#list"/>
  <title type="text">Bingley</title>
  <content type="text">Hours: 10, Items: 2, IPM: 0.0033</content>
  <link rel="self" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId"/>
  <link rel="edit" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId/version"/>
  <gsx:name>Bingley</gsx:name>
  <gsx:hours>20</gsx:hours>
  <gsx:items>4</gsx:items>
  <gsx:ipm>0.0033</gsx:ipm>
</entry>

Remove a row

To delete a row, perform a DELETE request on the edit URL after obtaining it as described above.

Note: If you delete a row, that row is completely removed from the worksheet.

Interacting with cell-based feeds

In a cell-based feed, each entry represents a single cell.

Note that it isn't recommended to interact with both a cell-based feed and a list-based feed for the same worksheet at the same time.

Get a cell-based feed

To determine the URL of a cell-based feed for a given worksheet, get the worksheets metafeed and examine the <link> element in which rel is http://schemas.google.com/spreadsheets/2006#cellsfeed. The href value in that element is the cell feed's URI.

To retrieve a cell-based feed, send the following GET request, using the cell feed's URI and an appropriate authentication token:

GET http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full

Google Spreadsheets returns an Atom feed resembling the following:

<feed xmlns="http://www.w3.org/2005/Atom"
    xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/"
    xmlns:gs="http://schemas.google.com/spreadsheets/2006">
  <id>http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full</id>
  <updated>2006-11-17T18:27:32.543Z</updated>
  <title type="text">Sheet1</title>
  <link rel="alternate" type="text/html"
    href="http://spreadsheets.google.com/ccc?key=key"/>
  <link rel="http://schemas.google.com/g/2005#feed" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full"/>
  <link rel="http://schemas.google.com/g/2005#post" type="application/atom+xml"
  <link rel="http://schemas.google.com/g/2005#batch" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/batch"/>
    href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full"/>
  <link rel="self" type="application/atom+xml"
    href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full"/>
  <author>
    <name>Fitzwilliam Darcy</name>
    <email>fitz@gmail.com</email>
  </author>
  <openSearch:startIndex>1</openSearch:startIndex>
  <openSearch:itemsPerPage>36</openSearch:itemsPerPage>
  <gs:rowCount>100</gs:rowCount>
  <gs:colCount>20</gs:colCount>
  <entry>
    <id>http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C1</id>
    <updated>2006-11-17T18:27:32.543Z</updated>
    <category scheme="http://schemas.google.com/spreadsheets/2006"
      term="http://schemas.google.com/spreadsheets/2006#cell"/>
    <title type="text">A1</title>
    <content type="text">Name</content>
    <link rel="self" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C1"/>
    <link rel="edit" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C1/bgvjf"/>
    <gs:cell row="1" col="1" inputValue="Name">Name</gs:cell>
  </entry>
  <entry>
    <id>http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C2</id>
    <updated>2006-11-17T18:27:32.543Z</updated>
    <category scheme="http://schemas.google.com/spreadsheets/2006"
      term="http://schemas.google.com/spreadsheets/2006#cell"/>
    <title type="text">B1</title>
    <content type="text">Hours</content>
    <link rel="self" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C2"/>
    <link rel="edit" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C2/1pn567"/>
    <gs:cell row="1" col="2" inputValue="Hours">Hours</gs:cell>
  </entry>

  ...

  <entry>
    <id>http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R9C4</id>
    <updated>2006-11-17T18:27:32.543Z</updated>
    <category scheme="http://schemas.google.com/spreadsheets/2006"
      term="http://schemas.google.com/spreadsheets/2006#cell"/>
    <title type="text">D9</title>
    <content type="text">5</content>
    <link rel="self" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R9C4"/>
    <link rel="edit" type="application/atom+xml"
      href="http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R9C4/srevc"/>
    <gs:cell row="9" col="4" inputValue="=FLOOR(R[0]C[-1]/(R[0]C[-2]*60),.0001)"
      numericValue="5.0">5</gs:cell>
  </entry>
</feed>

Send a cell range query

Suppose you want the employees to see how everyone is doing, but you do not want to post the employee names. You can request a cell feed containing only the IPM column:

GET http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full?min-row=2&min-col=4&max-col=4

That requests all the data in column 4, which in our example is the IPM column, starting with row 2 (because in our example, row 1 contains the column names).

The request explicitly specifies max-col in case more columns are added after column 4. Note that if any columns are inserted before column 4, the numbering here will no longer be accurate.

For further information about the cell feed, see the Cell-based feed reference section.

Change contents of a cell

Suppose you want to find out the total hours that the employees of Groceries R Us have worked. You can create a formula for calculating that value, and then add the formula to a cell in the worksheet using a Google Data PUT request.

Note: Use PUT change the data in a cell, even if it is empty; the use of POST on empty cells is no longer recommended. To clear a cell, send an entry with an empty content element in your PUT request instead of using DELETE.

To add data to the worksheet, start by creating an <entry> element containing the relevant data, which in this case is a formula:

<entry>
  <id>http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/cellId</id>
  <link rel="edit" type="application/atom+xml"
    href="http://spreadsheets/google.com/feeds/cells/key/worksheetId/private/full/R2C4/version"/>
  <gs:cell row="2" col="4" inputValue="300"/>
</entry>

Find the edit URL for the desired cell by performing a GET request. The edit URL is in the href attribute of a <link> element which has a rel attribute set to "edit".

Next, do any necessary authentication, and create an Authorization header for a new PUT request as described in the authentication sections of this document.

In the body of the PUT request, place the Atom <entry> element you created above, using the application/atom+xml content type.

Now send the PUT request to the appropriate Spreadsheets edit URL:

PUT http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/cell/version

The new data is placed in the specified cell in the worksheet. If the specified cell contains data already, it will be overwritten.

Change the content of multiple cells using a batch request

Suppose you want to update an entire column, row, range, or set of scattered cells worth of data. You could simple use a for loop and update the cells one-by-one as described in the previous section, or you could minimize the number of HTTP requests by batching the updates together.

Begin by creating a cells feed and setting the feed <id> to the href value of the POST link, appending /batch. A sample URL may look like:

http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/batch

Refer to the Add a row section above). Then append all the entries to be updated to the feed as per the XML shown below and set the <batch:operation> to "update". Finally, POST the feed to the cells feed POST link.

<feed xmlns="http://www.w3.org/2005/Atom"
      xmlns:batch="http://schemas.google.com/gdata/batch"
      xmlns:gs="http://schemas.google.com/spreadsheets/2006">
  <id>http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full</id>
  <entry>
    <batch:id">A1</batch:id">
    <batch:operation type="update"/>
    <id>http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/cellId</id>
    <link rel="edit" type="application/atom+xml"
      href="http://spreadsheets/google.com/feeds/cells/key/worksheetId/private/full/R2C4/version"/>
    <gs:cell row="2" col="4" inputValue="newData"/>
  </entry>
  ...
  <entry>
    <batch:id">A2</batch:id">
    <batch:operation type="update"/>
    <title type="text">A2</title>
    <id>http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/cellId</id>
    <link rel="edit" type="application/atom+xml"
      href="http://spreadsheets/google.com/feeds/cells/key/worksheetId/private/full/R2C5/version"/>
    <gs:cell row="2" col="5" inputValue="moreInfo"/>
  </entry>
</feed>

Please note that each entry element that you append to the feed element must contain the following elements:

• batch:id — A batch:id element, uniquely identifying each individual cell update within the batch update request.
• batch:operation — A batch:operation element, whose 'type' attribute determines the type of request to be performed (update or delete).
• id — An id element containing the full URL to the cell to be updated. Be sure to examine the output XML when requesting the cellsFeed for more details.
• link — A link element whose 'rel' attribute is set to "edit". The link's 'type' attribute must be set to "application/atom+xml" and the 'href' attribute must contain the full path to the cell's id (as in the 'id' element discussed above) and also include the cell's version string in the format of: http://google.com/feeds/cells/key/worksheetId/private/full/R1C1/version. Refer to the contents of the cells feed for more details.
• gs:cell — A gs:cell element, whose 'row' attribute corresponds to the row being updated, whose 'col' attribute corresponds to the column being updated and whose 'inputValue' attribute contains the same information as the value of the content element discussed above.

Read Batch Processing for more information, including status handling.

Note: Only GET and PUT operations are supported for cells feed batch processing. Each element in the batch request should contain <batch:operation type="query"> and <batch:operation type="update"> respectively.

Back to top