Developer's Guide: .NET

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.

In addition to providing some background on the capabilities of the Spreadsheets Data API, this document provides examples for interacting with the API using the .NET client library. For help setting up the client library, see the Getting Started Guide.

If you're interested in understanding more about the underlying protocol used by the .NET Client Library to interact with the Spreadsheets Data API, please see the protocol tab.

Contents

Audience

This document is intended for programmers who want to write client applications using the Google data .NET client library that can interact with Google Spreadsheets.

Getting started

For help setting up the client library, see the Getting Started Guide. To use the .NET client library, you'll need the .NET 1.1 runtime, and you should also be current on all patches. After downloading the client library, you'll find the DLLs you need to get started in the lib/Release subdirectory of the distribution.

A full working copy of this sample is available in the Google Data project in the project hosting section of code.google.com. The sample is located at /trunk/clients/cs/samples/spreadsheets/console/SpreadsheetDemo.cs in the SVN repository accessible from the Source tab.

After compiling the sample, run it as follows:

SpreadsheetDemo <userName> <password>

The userName and password values are the same credentials that you use to login to Google Spreadsheets.

The sample performs a number of operations on a worksheet of your choice to demonstrate the use of the Spreadsheets Data API. Please see the RunSample method of the code for more details as to which operations are performed.

To compile the examples in this document into your own code, you'll need the following using statements:

using Google.GData.Client;
using Google.GData.Extensions;
using Google.GData.Spreadsheets;

The SpreadsheetsService class represents a client connection (with authentication) to a Spreadsheets service. The general procedure for sending a query to a service using the client library consists of the following steps:

1. Obtain or construct the appropriate URL.
2. If you're sending data to a service (for example, if you're inserting a new entry), then transform the raw data into objects using the client library classes. (This step doesn't apply if you're just requesting a feed, as in this example.)
3. Create a new SpreadsheetsService instance, setting your application's name (in the form companyName-applicationName-versionID).
4. Set the appropriate credentials.
5. Call a method to send the request and receive any results.

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.

Authenticating to the Spreadsheets service

The .NET client library can be used to work with either public or private feeds. Public feeds are read-only, but do not require any authentication. Private feeds require that you authenticate to the spreadsheets servers. This can be done via ClientLogin username/password authentication or AuthSub proxy authentication.

Please see the authentication documentation for more information on AuthSub and ClientLogin.

Single-user "installed" client authentication

To use ClientLogin (also called "Authentication for Installed Applications"), invoke the setUserCredentials method of SpreadsheetsService, specifying the ID and password of the user on whose behalf your client is sending the query. For example:

SpreadsheetsService myService = new SpreadsheetsService("exampleCo-exampleApp-1");
myService.setUserCredentials("jo@gmail.com", "mypassword");

For more information about authentication systems, see the Google Account Authentication documentation.

Multiple-user web application client authentication

As of version 1.09 of the .NET client library, it is possible for applications to authenticate using the AuthSub login protocol. This is useful, for example, if you have an ASP application that requires Spreadsheets authentication.

To acquire an AuthSub token for a given user and a given service, your application must redirect the user to the AuthSubRequest URL, which prompts them to log into their Google account. (For more information on the AuthSubRequest URL, see the AuthSub documentation.)

Suppose the following ASP hyperlink is defined in our page:

<asp:HyperLink ID="GotoAuthSubLink" runat="server"/>

To construct the AuthSubRequest URL for your application, make a .NET client library call as follows:

GotoAuthSubLink.Text = "Login to your Google Account";
GotoAuthSubLink.NavigateUrl = AuthSubUtil.getRequestUrl("http://www.example.com/RetrieveToken",
                                                        "http://spreadsheets.google.com/feeds/",
                                                        false,
                                                        true);

The getRequestUrl method takes several parameters (corresponding to the query parameters used by the AuthSubRequest handler): the "next" URL (which is the URL that Google will redirect to after the user logs into their account and grants access); the scope (as determined in the previous section); and two Booleans, one to indicate whether the token will be used in registered mode or not, and one to indicate whether the token will later be exchanged for a session token or not. The above example shows a call in unregistered mode (the first Boolean is false), for a token that will be exchanged for a session token later (the second Boolean is true); adjust the Booleans appropriately for your application.

After the user follows the link to the AuthSub page at Google and logs in, the AuthSub system then redirects the user back to your application, using the "next" URL you provided.

When Google redirects back to your application, the token is appended to the "next" URL as a query parameter. So in the case of the above "next" URL, after the user logs in, Google redirects to a URL like http://www.example.com/RetrieveToken?token=DQAADKEDE. Therefore, the token is accessible as a variable in the ASP page's Request.QueryString object.

Upgrading to a session token

The token you initially retrieve is always a one-time use token. You can exchange this token for a session token using the AuthSubSessionToken URL, as described in the AuthSub documentation. Your application can make this exchange using the .NET client library as follows:

SessionsessionToken = AuthSubUtil.exchangeForSessionToken(Request.QueryStringtoken, null);

You pass your one-time use token to the exchangeForSessionToken method, along with either null (for unregistered mode) or a private key (for registered mode), and the AuthSub interface returns a session token. For more information about registered applications and private keys, refer to the Signing requests section of the AuthSub documentation.

Now you are ready to use the session token to authenticate requests to the Spreadsheets server by placing the token in the Authorization header. To tell the .NET client library to automatically send the Authorization header (containing the session token) with each request, do the following:

GAuthSubRequestFactory authFactory = new GAuthSubRequestFactory("wise", "SpreadsheetsSampleApp");
authFactory.Token = SessionsessionToken.ToString();
SpreadsheetsService service = new SpreadsheetsService(authFactory.ApplicationName);
service.RequestFactory = authFactory;

Get a list of spreadsheets

You can get a feed containing a list of the currently authenticated user's spreadsheets by sending an authenticated GET request to the following URL:

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

The result is a "meta-feed," a feed that lists all of that user's spreadsheets; each entry in the feed represents a spreadsheet associated with the user. This feed is accessible only using an authentication token.

To request the feed using the .NET client library, do the following:

SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);

Console.WriteLine("Your spreadsheets:");
foreach (SpreadsheetEntry entry in feed.Entries)
{
    Console.WriteLine(entry.Title.Text);
}

The SpreadsheetQuery class encapsulates a query to the meta-feed URL shown above, and the resulting SpreadsheetFeed object represents a response from the server. Among other things, this feed contains a list of SpreadsheetEntry objects (feed.Entries), each of which represents a single spreadsheet. SpreadsheetEntry encapsulates the information shown in the protocol document.

Interacting with worksheet-based feeds

A given spreadsheet may contain multiple worksheets. For each spreadsheet, there's a worksheets metafeed listing all of the worksheets in that spreadsheet. The following sections describe how to retrieve 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 list of worksheets

Given a SpreadsheetEntry you've already retrieved, you can print a list of all worksheets in this spreadsheet as follows:

AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);

WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed = service.Query(query);

foreach (WorksheetEntry worksheet in feed.Entries)
{
    Console.WriteLine(worksheet.Title.Text);
}

The above code first finds the AtomLink (corresponding to a <link> element in the spreadsheet entry) that has rel="http://schemas.google.com/spreadsheets/2006#worksheetsfeed". That link's href value provides the URL for the spreadsheet's worksheets metafeed. Next, construct a WorksheetQuery to query this URL. The SpreadsheetsService returns a WorksheetFeed, which contains a list of all the worksheets in this spreadsheet; each WorksheetEntry in the feed is a single worksheet.

This code is equivalent to sending an authenticated GET request to the URL:

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

where key is the permanent unique ID of a specific worksheet within the spreadsheet. This operation can also be performed directly in the program:

WorksheetQuery query =  new WorksheetQuery(key, "private", "full");
WorksheetFeed feed = service.Query(query);

Add a worksheet

You can add a new worksheet by constructing a WorksheetEntry object and inserting it to the feed:

WorksheetEntry newWorksheet = new WorksheetEntry(10, 15, "new worksheet");
WorksheetEntry createdWorksheet = (WorksheetEntry) feed.Insert(newWorksheet);
Console.WriteLine(createdWorksheet.Id.AbsoluteUri);

This code creates a worksheet called "new worksheet" with 10 rows and 15 columns and then print the URI of the newly created worksheet.

Update the metadata of a worksheet

Existing worksheets can be updated by changing their properties and invoking the Update method:

    myWorksheet.Cols = 50;
    myWorksheet.Rows = 50;
    myWorksheet.Title = "New Title";
    myWorksheet.Update();

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

Worksheets can be easily deleted using the Delete method of a WorksheetEntry:

    myWorksheet.Delete();

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 retrieve a worksheet's list feed, you might do the following. This example assumes that service is an authenticated SpreadsheetsService object, and entry is a WorksheetEntry.

AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed feed = service.Query(query);

Console.WriteLine("Worksheet has {0} rows:", feed.Entries.Count);
foreach (ListEntry worksheetRow in feed.Entries)
{
    ListEntry.CustomElementCollection elements = worksheetRow.Elements;
    foreach (ListEntry.Custom element in elements) {
        Console.Write(element.Value + "\t");
    }
    Console.WriteLine();
}

First find the AtomLink (corresponding to a <link> element in the worksheet entry) that has

rel="http://schemas.google.com/spreadsheets/2006#listfeed"

.

That link's href value provides the URL for the worksheet's list feed. Next, construct a ListQuery to query this URL. The SpreadsheetsService returns a ListFeed, which contains a list of all the rows in this worksheet; each ListEntry in the feed is a single row.

To retrieve the contents of a single row, iterate over the ListEntry's Elements member. This is a ListEntry.CustomElementCollection object; each element in the collection (of the ListEntry.Custom class) is a cell in that row. Finally, ListEntry.Custom has the string properties LocalName, which stores the header of that cell's column, and ListEntry.Value, which stores the text in the cell.

This code is equivalent to sending an authenticated GET request to the URL:

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

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, set the OrderByPosition and Reverse properties of the ListQuery object to true:

ListQuery query = new ListQuery(listFeedLink.HRef.ToString());

query.OrderByPosition = true;
query.Reverse = true;

This is equivalent to sending an authenticated GET request to the URL:

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

Note that if you want to order (or reverse sort) by a particular column, rather than by position in the worksheet, you can set the OrderByColumn property of the ListQuery object to the header of that column. This is analogous to adding an orderby query parameter in the outbound GET request. Setting OrderByColumn automatically sets OrderByPosition to false. Similarly, setting OrderByPosition to true automatically sets OrderByColumn to null.

Send a structured query

You can set a ListQuery's SpreadsheetQuery property to produce a feed with entries that meet the specified criteria. For example, suppose you have a worksheet containing personnel data, in which each row represents information about a single person. You wish to retrieve all rows in which the person's name is "John" and the person's age is over 25. To do so, you would set SpreadsheetQuery as follows:

ListQuery query = new ListQuery(listFeedLink.HRef.ToString());

query.SpreadsheetQuery = "name=John and age>25";

This is equivalent to sending an authenticated GET request to the URL:

http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?sq=yourQuery

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

Add a row

To insert a new row in a list-based feed, first construct a new ListEntry and set its Elements property to contain the cells in the row. For example, given the ListEntry that represents an existing row, you might prompt the user for the values of each column as follows:

ListEntry newRow = new ListEntry();

foreach (ListEntry.Custom element in existingRow.Elements)
{
    Console.Write("Enter the value of column {0}: ", element.LocalName);
    String elementValue = Console.ReadLine();

    ListEntry.Custom curElement = new ListEntry.Custom();
    curElement.LocalName = element.LocalName;
    curElement.Value = elementValue;

    newRow.Elements.Add(curElement);
}

Then insert the new row in the ListFeed as follows:

ListEntry insertedRow = feed.Insert(newRow) as ListEntry;

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. This code is equivalent to sending an authenticated POST request to the URL:

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

with the corresponding XML document in the POST body.

Edit a row

To change the contents of a row, first modify the desired ListEntry.Custom objects corresponding to cells in that row. For example, to change the cell in the first column to "John" and the cell in the second column to "Doe", do the following:

ListEntry.Custom firstColumn = entry.Elements[0];
firstColumn.Value = "John";

ListEntry.Custom secondColumn = entry.Elements[1];
secondColumn.Value = "Doe";

Then call the ListEntry's Update method to perform the update:

ListEntry updatedRow = entry.Update() as ListEntry;

This is equivalent to sending an authenticated PUT request to the entry's edit link, with the updated XML as PUT data.

Remove a row

To delete a row, simply invoke the Delete method on the corresponding ListEntry object:

entry.Delete();

This is equivalent to performing an HTTP DELETE request on the row's edit URL.

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 retrieve a worksheet's cell-based feed, you might do the following. Again, this assumes that service is an authenticated SpreadsheetsService object, and entry is a WorksheetEntry.

AtomLink cellFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);

CellQuery query = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed feed = service.Query(query);

Console.WriteLine("Cells in this worksheet:");
foreach (CellEntry curCell in feed.Entries)
{
    Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row,
        curCell.Cell.Column, curCell.Cell.Value);
}

First find the AtomLink (corresponding to a <link> element in the worksheet entry) that has

rel="http://schemas.google.com/spreadsheets/2006#cellsfeed"

.

That link's href value provides the URL for the worksheet's list feed. Next, construct a CellQuery to query this URL. The SpreadsheetsService returns a CellFeed, which contains a list of all the cells in this worksheet; each CellEntry in the feed is a single cell. Given a CellEntry, you can easily find the cell's row, column and value by accessing its Cell.Row, Cell.Column, and Cell.Value properties, respectively.

This code is equivalent to sending an authenticated GET request to the URL:

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

Send a cell range query

Suppose you only want to retrieve the cells in the first column of a worksheet. You can request a cell feed containing only this column as follows:

CellQuery query = new CellQuery(cellFeedLink.HRef.ToString());
query.MinimumColumn = 1;
query.MaximumColumn = 1;
query.MinimumRow = 2;

CellFeed feed = service.Query(query);

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

Setting the MinimumColumn, MaximumColumn, MinimumRow, and MaximumRow properties of a CellQuery object is equivalent to providing values for the parameters min-col, max-col, min-row, and max-row (respectively) in the HTTP GET request.

Change contents of a cell

To modify the contents of an existing CellEntry that you have retrieved from a feed, first change its InputValue property. Then execute the update request as follows:

cellEntry.Cell.InputValue = "Jane";
AtomEntry updatedCell = cellEntry.Update();

This is equivalent to sending an authenticated PUT request, with the updated cell's XML representation, to the URL:

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

Note: Use Update() (i.e., HTTP PUT) to 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 empty string as the value instead of using a DELETE.

Update multiple cells with a batch request

Suppose you want to update the data in a column, row, or other set of cells. You could simply 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.

CellFeed cellFeed = myWorksheet.QueryCellFeed();

CellEntry toUpdate = (CellEntry) cellFeed.Entries[0];
toUpdate.Cell.InputValue = "new value";
toUpdate.BatchData = new GDataBatchEntryData("A", GDataBatchOperationType.update);

CellEntry toUpdate2 = (CellEntry) cellFeed.Entries[1];
toUpdate2.Cell.InputValue = "second new value";
toUpdate2.BatchData = new GDataBatchEntryData("B", GDataBatchOperationType.update);

AtomFeed batchFeed = new AtomFeed(cellFeed);

batchFeed.Entries.Add(toUpdate);
batchFeed.Entries.Add(toUpdate2);


CellFeed batchResultFeed = (CellFeed) service.Batch(batchFeed, new Uri(cellFeed.Batch));

foreach (CellEntry entry in batchResultFeed.Entries)
{
    if (entry.BatchData.Status.Code != 200)
    {
        throw new Exception("Cell update for " + entry.BatchData.Id +  "failed!");
    }
}

This code first retrieves the cells feed of a worksheet. It then updates the first cell to have the value "new value", and the second cell to have the value "second new value" in one operation. The results of each of these operations can be extracted from the CellFeed object. The last part checks the resulting batch feed for any failed operations. The 200 status code comes from the HTTP specification. There are only two operations here, but you can add many updates or queries into a single batch transaction. The size of the request must be under a megabyte; on the order of 1000 to 2000 cells, depending on the length of cell content. More information about batch operations can be found on the Batch Processing page.

Back to top