Reference Guide

Important: This is an old version of this page. For the latest version, use the links in the left-side navbar.

This document provides detailed reference documentation for the Google Spreadsheets Data API.

Contents

Audience

This document is intended for programmers who want to write client applications that can interact with Spreadsheets.

It's a reference document; it assumes that you understand the concepts presented in the developer's guide, and the general ideas behind the Google Data APIs protocol.

Spreadsheets feed types

Google Spreadsheets provides a variety of representations of worksheet data.

A Spreadsheets feed URI takes the following form:

http://spreadsheets.google.com/feeds/feedType/key/worksheetId/visibility/projection

A Spreadsheets metafeed URI is similar, but doesn't always include all of those parameters. For more information, see Spreadsheet metafeed.

The feedType parameter can have any of the following values: spreadsheets, worksheets, list, or cells.

The key parameter identifies a spreadsheet.

The worksheetId parameter identifies a specific worksheet within the given spreadsheet; within a given spreadsheet, each worksheet has a unique identifier. Alternatively, in a list or cells feed you can refer to a worksheet by tab number (1 for the leftmost worksheet, 2 for the second worksheet, etc).

Note: Be careful referring to worksheets by tab number. If the order of the worksheets changes within the spreadsheet, you may end up accessing the wrong worksheet.

The visibility parameter has two possible values: private and public. In almost all cases, your client should use private. For more information, see Visibility values, below.

The projection parameter indicates what information is included in the representation. For example, if your client specifies a projection of basic, it's requesting an Atom feed without any Data API extension elements. For a list of values, see Projection values, below.

All combinations of visibility and projection are valid except for public/full.

Constructing URIs

Your client will usually determine feed URIs by examining the <link> elements of metafeeds, but in some cases you'll want to construct a feed URI by plugging in appropriate values.

For example, when you receive an invitation to share a spreadsheet created by someone else, the URL looks something like the following:

http://spreadsheets.google.com/ccc?key=o12167459629904738010.2120042623512936542

The key parameter in that URL identifies a spreadsheet; to obtain a feed for that spreadsheet, use the same key value to construct a feed URI.

Similarly, when you create a spreadsheet, the URL looks something like the following:

http://spreadsheets.google.com/ccc?id=o12167459629904738010.2120042623512936542.1231238971923719237.1892371892731892

In this case, the first two parts of the ID (o12167459629904738010.2120042623512936542) comprise the key value to use in constructing a feed URI.

Spreadsheet metafeed

The spreadsheets metafeed lists all the spreadsheets for the Google Account that's associated with the authenticated user:

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

Note that the spreadsheets metafeed URI never includes a worksheetId.

When the URI includes a key value, then the metafeed contains only the entry for the spreadsheet associated with the provided key:

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

Note that in this case the spreadsheet ID (key) appears at the end of the URI rather than in the middle.

Worksheet metafeed

The worksheets metafeed lists all the worksheets within the spreadsheet identified by the specified key, along with the URIs for list and cells feeds for each worksheet:

http://spreadsheets.google.com/feeds/worksheets/key/visibility/projection

Every worksheet has a unique ID, which you can determine using the <link rel="self"> element of an entry in a worksheet feed. You can include a worksheet ID in the URI, in which case the feed is limited to that worksheet:

http://spreadsheets.google.com/feeds/worksheets/key/visibility/projection/worksheetId

When visibility is public, the returned metafeed includes only published worksheets; unpublished worksheets are not listed in a public metafeed.

List-based feed

A list feed represents each row in a worksheet as a separate <entry> element. Each entry contains multiple fields, labelled by XML elements named after the column headers.

http://spreadsheets.google.com/feeds/list/key/worksheetId/visibility/projection

Every row has a unique row ID, which you can determine using the <link rel="self"> element of an entry in a list-based feed. You can include a row ID in the URI, in which case the feed is limited to that row:

http://spreadsheets.google.com/feeds/list/key/worksheetId/visibility/projection/rowId

You can set visibility to public only with published worksheets; if you request the feed for an unpublished worksheet using a URI with visibility set to public, you'll get an error.

Note: The list feed treats the values in the first row of the spreadsheet as the names of the columns. We strongly recommend that Spreadsheets users freeze the first row, using Sort > Freeze Rows. Otherwise, column names may change during sorting.

Cell-based feed

The cells feed represents each cell in the worksheet as a separate <entry> element.

http://spreadsheets.google.com/feeds/cells/key/worksheetId/visibility/projection

You can also include a cell ID in the URI, which retrieves the entry for the indicated cell. Cell IDs are in R1C1 notation; for example, cell C8 in the GUI has cell ID R8C3.

http://spreadsheets.google.com/feeds/cells/key/worksheetId/visibility/projection/cellId

If you retrieve a cell that's empty, behavior is undefined.

You can set visibility to public only with published worksheets; if you request the feed for an unpublished worksheet using a URI with visibility set to public, you'll get an error.

Visibility values

The following table describes the supported visibility values:

Visibility	Description	Updatability	Security Notes
private	For use whenever you can authenticate the request.	Read/write access if the authenticated user has edit privileges; otherwise, read-only.	Requires authentication.
public	For use in unauthenticated requests for published feeds.	Always read-only.	Does not require authentication. Inaccessible if the worksheet is not published.

In almost all cases, your client should use private.

If a worksheet's owner has published the worksheet (using the Spreadsheets GUI), and if you want to be able to get a list-based or cell-based data feed from that worksheet without authenticating, then you can construct a feed URI that uses public visibility. For example, if you want to allow a feed reader to subscribe to a published worksheet without authenticating, you can give the feed reader the data feed's URL with private changed to public.

Note that public visibility feeds are available only for published worksheets.

Projection values

The following table describes the supported projection values:

Projection Name	Description	Updatability
full	Full-fidelity feed; contains all spreadsheet data including formulas.	Full read/write access.
values	Feed includes all data except for formulas. Formulas are replaced with their evaluations. Includes Data API extension elements.	Read-only access.
basic	Feed includes all data except for formulas. Formulas are replaced with their evaluations. This feed uses only Atom elements, leaving out all Data API extension elements.	Read-only access.

Spreadsheets query parameters reference

Spreadsheets supports the following standard Data API query parameters:

Parameters	Support
alt, start-index, max-results	Supported in all feed types.
author, updated-min, updated-max	Supported only in spreadsheets metafeed.
q	Supported only in list and cell feeds.

Spreadsheets does not support Data API category queries. For more information about the standard parameters, see the Google Data APIs protocol reference document.

Note that since updated-min and updated-max are supported only for the spreadsheets metafeed, you can't get a list of cells or rows that have been updated since the last time you queried.

In addition to the standard Data API query parameters, the Spreadsheets Data API uses the parameters described in the following sections.

Spreadsheet-feed query parameters

Parameter	Meaning	Notes
title	Specifies the search terms for the title of a document.	This parameter used without title-exact will only submit partial queries, not exact queries.
title-exact	Specifies whether the title query should be taken as an exact string.	Meaningless without title. Possible values are true and false.

Worksheet-feed query parameters

Parameter	Meaning	Notes
title	Specifies the search terms for the title of a document.	This parameter used without title-exact will only submit partial queries, not exact queries.
title-exact	Specifies whether the title query should be taken as an exact string.	Meaningless without title. Possible values are true and false.

List-feed query parameters

Parameter	Meaning	Notes
orderby	Specifies what column to use in ordering the entries in the feed.	By position (the default): orderby=position returns rows in the order in which they appear in the GUI. Row 1, then row 2, then row 3, and so on. By column: orderby=column:columnName sorts rows in ascending order based on the values in the column with the given columnName, where columnName is the value in the header row for that column.
reverse	Specifies whether to sort in descending or ascending order.	Reverses default sort order: reverse=true results in a descending sort; reverse=false (the default) results in an ascending sort.
sq	Structured query on the full text in the worksheet.	[columnName][binaryOperator][value] Supported binaryOperators are: () for overriding order of operations = or == for strict equality <> or != for strict inequality and or && for boolean and or or || for boolean or Note that the binaryOperators need to be url-encoded.

Cell-feed query parameters

Parameter	Meaning	Notes
min-row	Positional number of minimum row returned in query.	Queries indicate an inclusive range.
max-row	Positional number of maximum row returned in query.
min-col	Positional number of minimum column returned in query.
max-col	Positional number of maximum column returned in query.
range	A single cell or a range of cells.	Use standard spreadsheet cell-range notations, using a colon to separate start and end of range. Examples: range=A1 and range=R1C1 both specify only cell A1. range=D1:F3 and range=R1C4:R3C6 both specify the rectangle of cells with corners at D1 and F3.
return-empty	If true then empty cells will be returned in the feed. The default is false.

Spreadsheets elements reference

In addition to the standard Data API elements, the Spreadsheets Data API uses the gs and gsx namespaces.

For information about the standard Data API elements, see the Atom specification and the Kinds document.

gs namespace element reference

Google Spreadsheets provides the gs namespace for use in the cell feed and worksheets metafeed. The schema URL for the gs namespace is http://schemas.google.com/spreadsheets/2006.

gs:rowCount

Indicates the number of total rows in the worksheet, including rows that contain only empty cells.

The <gs:rowCount> element can appear as a child of <atom:entry> or <atom:feed>.

Properties

Property	Type	Description
text()	xs:unsignedInt	The number of rows in the worksheet.

Example

<gs:rowCount>100</gs:rowCount>

Schema

namespace gs = "http://schemas.google.com/spreadsheets/2006"
start = rowCount 

rowCount =
  element gs:rowCount {
    xsd:unsignedInt
  }

gs:colCount

Indicates the number of columns in the worksheet, including columns that contain only empty cells.

The <gs:colCount> element can appear as a child of <atom:entry> or <atom:feed>.

Properties

Property	Type	Description
text()	xs:unsignedInt	The number of columns in the worksheet.

Example

<gs:colCount>20</gs:colCount>

Schema

namespace gs = "http://schemas.google.com/spreadsheets/2006"
start = colCount 

colCount =
  element gs:colCount {
    xsd:unsignedInt
  }

gs:cell

A cell in the worksheet.

The <gs:cell> element can appear only as a child of <atom:entry>.

Properties

Property	Type	Description
@row	xs:unsignedInt	The row number of the cell.
@col	xs:unsignedInt	The column number of the cell.
@inputValue	xs:string	The input string of the cell.
@numericValue?	xs:float	The numeric value of the cell if the input value begins with an "=" and the equation evaluates to a numeric.
text()	xs:string	The displayed value of the cell.

Example

<gs:cell row="2" col="4" inputValue="=FLOOR(R[0]C[-1]/(R[0]C[-2]*60),.0001)"
    numericValue="0.0033">0.0033</gs:cell>

Schema

namespace gs = "http://schemas.google.com/spreadsheets/2006"
start = cell 

cell =
  element gs:cell {
    attribute row { xsd:unsignedInt },
    attribute col { xsd:unsignedInt },
    attribute inputValue { xsd:string }?,
    attribute numericValue { xsd:float }?,
    xsd:string
  }

gsx namespace element reference

Google Spreadsheets provides a custom tag namespace, gsx, for use in list feeds. The schema URL for the gsx namespace is http://schemas.google.com/spreadsheets/2006/extended.

A gsx element can appear only as a child of <atom:entry>.

Each column's header value (the contents of the cell in row 1 in that column) becomes a custom element name in the gsx namespace. If a header cell is empty, then a unique column ID is used for the gsx element name.

Spaces in a column name are removed from the name of the corresponding gsx element.

Caution: The columnNames are case-insensitive. For example, if you see a <gsx:e-mail> element in a feed, you can't know whether the column heading in the original worksheet was "e-mail" or "E-Mail".

Note: If two or more columns have the same name, then subsequent columns of the same name have _n appended to the columnName. For example, if the first column name is "e-mail", followed by columns named "E-Mail" and "E-mail", then the columnNames will be gsx:e-mail, gsx:e-mail_2, and gsx:e-mail_3 respectively.

gsx:columnName

A value in a column of the list entry.

Properties

Property	Type	Description
text()	xs:string	The string representation of the value in the column or the returned list record.

Example

A value in a retrieved list record of a column named "e-mail":

<gsx:e-mail>fitz@gmail.com</gsx:e-mail>

Schema

namespace gsx = "http://schemas.google.com/spreadsheets/2006/extended"
start = columnName 

columnName =
  element gsx:* {
    xsd:string
  }

Back to top