Manage List-based and Cell-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.

Warning: The list feed writes static values into cells that have formulas applied to them. To modify cells with formulas while keeping the formulas applied, use the Cells feed.

Work with list-based feeds

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. The API dynamically creates XML elements named after the contents of header-row cells. Users needing list-based API feed access to a worksheet must only put column headers in the first row of the worksheet.

Warning: If users modify the contents of the header row in a worksheet, an application may break.

Applications needing to dynamically determine the contents of the header row must use the Cells feed to access the contents of the header row by its position.

Important: For a column to be accessible in a list feed, it must have data in its header cell. Columns without data in the header cell are not exposed in the list feed.

Retrieve a list-based feed

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.

Note: The list feed supports both the private and public visibilities, and both the full and basic projections.

HTTP

To determine the URL of a list-based feed for a given worksheet, get the worksheets feed and examine the content element in which src 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 authorization header:

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

The API returns a feed resembling the following:

<feed xmlns="http://www.w3.org/2005/Atom"
    xmlns:openSearch="http://a9.com/-/spec/opensearch/1.1/"
    xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended"
    xmlns:gd="http://schemas.google.com/g/2005"
    gd:etag='W/"D0cERnk-eip7ImA9WBBXGEg."'>
  <id>https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full</id>
  <updated>2006-11-17T18:23:45.173Z</updated>
  <title type="text">Sheet1</title>
  <content type="application/atom+xml;type=feed"
      src="https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
  <link rel="alternate" type="text/html"
      href="https://spreadsheets.google.com/ccc?key=key"/>
  <link rel="http://schemas.google.com/g/2005#feed"
      type="application/atom+xml"
      href="https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
  <link rel="http://schemas.google.com/g/2005#post"
      type="application/atom+xml"
      href="https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
  <link rel="self" type="application/atom+xml"
      href="https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
  <author>
    <name>Fitzwilliam Darcy</name>
    <email>[e-mail]</email>
  </author>
  <openSearch:totalResults>8</openSearch:totalResults>
  <openSearch:startIndex>1</openSearch:startIndex>
  <openSearch:itemsPerPage>8</openSearch:itemsPerPage>
  <entry gd:etag='"S0wCTlpIIip7ImA0X0QI"'>
    <id>https://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="https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId"/>
    <link rel="edit" type="application/atom+xml"
        href="https://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 gd:etag='"AxQDSXxjfyp7ImA0ChJVSBI."'>
    <id>https://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="https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId"/>
    <link rel="edit" type="application/atom+xml"
        href="https://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 or other non-alphanumeric character, it will be removed in the gsx element name.

Java

The following code prints all rows of the first worksheet returned for the first spreadsheet in the spreadsheets feed.

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;

import java.io.IOException;
import java.net.*;
import java.util.*;

public class MySpreadsheetIntegration {
  public static void main(String[] args)
      throws AuthenticationException, MalformedURLException, IOException, ServiceException {

    SpreadsheetService service =
        new SpreadsheetService("MySpreadsheetIntegration-v1");

    // TODO: Authorize the service object for a specific user (see other sections)

    // Define the URL to request.  This should never change.
    URL SPREADSHEET_FEED_URL = new URL(
        "https://spreadsheets.google.com/feeds/spreadsheets/private/full");

    // Make a request to the API and get all spreadsheets.
    SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
        SpreadsheetFeed.class);
    List<SpreadsheetEntry> spreadsheets = feed.getEntries();

    if (spreadsheets.size() == 0) {
      // TODO: There were no spreadsheets, act accordingly.
    }

    // TODO: Choose a spreadsheet more intelligently based on your
    // app's needs.
    SpreadsheetEntry spreadsheet = spreadsheets.get(0);
    System.out.println(spreadsheet.getTitle().getPlainText());

    // Get the first worksheet of the first spreadsheet.
    // TODO: Choose a worksheet more intelligently based on your
    // app's needs.
    WorksheetFeed worksheetFeed = service.getFeed(
        spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);

    // Fetch the list feed of the worksheet.
    URL listFeedUrl = worksheet.getListFeedUrl();
    ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);

    // Iterate through each row, printing its cell values.
    for (ListEntry row : listFeed.getEntries()) {
      // Print the first column's cell value
      System.out.print(row.getTitle().getPlainText() + "\t");
      // Iterate over the remaining columns, and print each cell value
      for (String tag : row.getCustomElements().getTags()) {
        System.out.print(row.getCustomElements().getValue(tag) + "\t");
      }
      System.out.println();
    }
  }
}

.NET

The following code prints all rows of the first worksheet returned for the first spreadsheet in the spreadsheets feed.

using System;
using Google.GData.Client;
using Google.GData.Spreadsheets;

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");

      // TODO: Authorize the service object for a specific user (see other sections)

      // Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
      SpreadsheetQuery query = new SpreadsheetQuery();

      // Make a request to the API and get all spreadsheets.
      SpreadsheetFeed feed = service.Query(query);

      if (feed.Entries.Count == 0)
      {
        // TODO: There were no spreadsheets, act accordingly.
      }

      // TODO: Choose a spreadsheet more intelligently based on your
      // app's needs.
      SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
      Console.WriteLine(spreadsheet.Title.Text);

      // Get the first worksheet of the first spreadsheet.
      // TODO: Choose a worksheet more intelligently based on your
      // app's needs.
      WorksheetFeed wsFeed = spreadsheet.Worksheets;
      WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

      // Define the URL to request the list feed of the worksheet.
      AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

      // Fetch the list feed of the worksheet.
      ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
      ListFeed listFeed = service.Query(listQuery);

      // Iterate through each row, printing its cell values.
      foreach (ListEntry row in listFeed.Entries)
      {
        // Print the first column's cell value
        Console.WriteLine(row.Title.Text);
        // Iterate over the remaining columns, and print each cell value
        foreach (ListEntry.Custom element in row.Elements)
        {
          Console.WriteLine(element.Value);
        }
      }
    }
  }
}

Important: Rows are ordered in the same order in which they appear in the worksheet user-interface, unless the query URL includes the orderby query parameter.

Sort rows

By default, rows in the list feed appear in the same order as the corresponding rows in the user-interface; 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.

HTTP

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

Java

// Based on the sample above for getting rows in a worksheet
URL listFeedUrl = new URI(worksheet.getListFeedUrl().toString() + "?reverse=true").toURL();

.NET

// Based on the sample above for getting rows in a worksheet
listQuery.Reverse = true;

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

HTTP

GET https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?orderby=column:lastname

Java

// Based on the sample above for getting rows in a worksheet
URL listFeedUrl = new URI(worksheet.getListFeedUrl().toString() + "?orderby=column:lastname").toURL();

.NET

// Based on the sample above for getting rows in a worksheet
listQuery.OrderByColumn = "column:lastname";

reverse and orderby can also be combined in order to sort by a column in descending order.

HTTP

GET https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?reverse=true&orderby=column:lastname

Java

// Based on the sample above for getting rows in a worksheet
URL listFeedUrl = new URI(worksheet.getListFeedUrl().toString()
    + "?reverse=true&orderby=column:lastname").toURL();

.NET

// Based on the sample above for getting rows in a worksheet
listQuery.Reverse = true;
listQuery.OrderByColumn = "column:lastname";

Important: The column name used in the orderby parameter is case sensitive and must match the name of the column as it appears in the API. The column names are the header values of the worksheet lowercased and with all non-alpha-numeric characters removed. For example, if the cell A1 contains the value "Time 2 Eat!" the column name would be "time2eat".

Send a structured query for rows

Use the structured query parameter sq to produce a feed with entries that meet the specified criteria. To find all people in a worksheet older than 25 years, with a height less than 175cm, one would use the query:

age > 25 and height < 175

To run this query using the API directly, add it as the value of the sq parameter.

Note: Always URL-encode query parameters.

HTTP

GET https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?sq=age>25%20and%20height<175

Java

// Based on the sample above for getting rows in a worksheet
URL listFeedUrl = new URI(worksheet.getListFeedUrl().toString()
    + "?sq=age>25%20and%20height<175").toURL();

.NET

The GData .NET client library takes care of URL-encoding the query parameters. .NET developers do not need to do anything to URL-encode them.

// Based on the sample above for getting rows in a worksheet
listQuery.SpreadsheetQuery = "age > 25 and height < 175";

The queries you can perform here are somewhat limited, because you cannot query over columns that, for instance, begin with a digit. The names used here must match their respective element names in lists feed results.

Add a list row

Adding rows to a worksheet via the list feed involves specifying values for each column in the worksheet, and sending this row representation to the API.

HTTP

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

<entry xmlns="http://www.w3.org/2005/Atom"
    xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">
  <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 https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full

Java

The following code inserts a new row into the first worksheet of the first spreadsheet in the spreadsheets feed. The row has the data:

{firstname: 'Joe', lastname: 'Smith', age: 26, height: 176}

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;

import java.io.IOException;
import java.net.*;
import java.util.*;

public class MySpreadsheetIntegration {
  public static void main(String[] args)
      throws AuthenticationException, MalformedURLException, IOException, ServiceException {

    SpreadsheetService service =
        new SpreadsheetService("MySpreadsheetIntegration-v1");

    // TODO: Authorize the service object for a specific user (see other sections)

    // Define the URL to request.  This should never change.
    URL SPREADSHEET_FEED_URL = new URL(
        "https://spreadsheets.google.com/feeds/spreadsheets/private/full");

    // Make a request to the API and get all spreadsheets.
    SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
        SpreadsheetFeed.class);
    List<SpreadsheetEntry> spreadsheets = feed.getEntries();

    if (spreadsheets.size() == 0) {
      // TODO: There were no spreadsheets, act accordingly.
    }

    // TODO: Choose a spreadsheet more intelligently based on your
    // app's needs.
    SpreadsheetEntry spreadsheet = spreadsheets.get(0);
    System.out.println(spreadsheet.getTitle().getPlainText());

    // Get the first worksheet of the first spreadsheet.
    // TODO: Choose a worksheet more intelligently based on your
    // app's needs.
    WorksheetFeed worksheetFeed = service.getFeed(
        spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);

    // Fetch the list feed of the worksheet.
    URL listFeedUrl = worksheet.getListFeedUrl();
    ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);

    // Create a local representation of the new row.
    ListEntry row = new ListEntry();
    row.getCustomElements().setValueLocal("firstname", "Joe");
    row.getCustomElements().setValueLocal("lastname", "Smith");
    row.getCustomElements().setValueLocal("age", "26");
    row.getCustomElements().setValueLocal("height", "176");

    // Send the new row to the API for insertion.
    row = service.insert(listFeedUrl, row);

  }
}

.NET

The following code inserts a new row into the first worksheet of the first spreadsheet in the spreadsheets feed. The row has the data:

{firstname: 'Joe', lastname: 'Smith', age: 26, height: 176}

using System;
using Google.GData.Client;
using Google.GData.Spreadsheets;

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");

      // TODO: Authorize the service object for a specific user (see other sections)

      // Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
      SpreadsheetQuery query = new SpreadsheetQuery();

      // Make a request to the API and get all spreadsheets.
      SpreadsheetFeed feed = service.Query(query);

      if (feed.Entries.Count == 0)
      {
        // TODO: There were no spreadsheets, act accordingly.
      }

      // TODO: Choose a spreadsheet more intelligently based on your
      // app's needs.
      SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
      Console.WriteLine(spreadsheet.Title.Text);

      // Get the first worksheet of the first spreadsheet.
      // TODO: Choose a worksheet more intelligently based on your
      // app's needs.
      WorksheetFeed wsFeed = spreadsheet.Worksheets;
      WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

      // Define the URL to request the list feed of the worksheet.
      AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

      // Fetch the list feed of the worksheet.
      ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
      ListFeed listFeed = service.Query(listQuery);

      // Create a local representation of the new row.
      ListEntry row = new ListEntry();
      row.Elements.Add(new ListEntry.Custom() { LocalName = "firstname", Value = "Joe" });
      row.Elements.Add(new ListEntry.Custom() { LocalName = "lastname", Value = "Smith" });
      row.Elements.Add(new ListEntry.Custom() { LocalName = "age", Value = "26" });
      row.Elements.Add(new ListEntry.Custom() { LocalName = "height", Value = "176" });

      // Send the new row to the API for insertion.
      service.Insert(listFeed, row);
    }
  }
}

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

Update a list row

The Sheets API allows rows to be manipulated in place.

HTTP

To update the contents of an existing row, first retrieve the row to update, modify it as desired, and then send a PUT request, with the updated row in the message body, to the row's edit URL.

Be sure that the id value in the entry you PUT exactly matches the id of the existing entry. The edit URL is highlighted in the following row entry:

<entry gd:etag='"S0wCTlpIIip7ImA0X0QI"'>
  <id>https://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="https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId"/>
  <link rel="edit" type="application/atom+xml"
    href="https://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>

Java

The following code snippet updates the first row of the first worksheet of the first spreadsheet in the spreadsheets feed.

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;

import java.io.IOException;
import java.net.*;
import java.util.*;

public class MySpreadsheetIntegration {
  public static void main(String[] args)
      throws AuthenticationException, MalformedURLException, IOException, ServiceException {

    SpreadsheetService service =
        new SpreadsheetService("MySpreadsheetIntegration-v1");

    // TODO: Authorize the service object for a specific user (see other sections)

    // Define the URL to request.  This should never change.
    URL SPREADSHEET_FEED_URL = new URL(
        "https://spreadsheets.google.com/feeds/spreadsheets/private/full");

    // Make a request to the API and get all spreadsheets.
    SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
        SpreadsheetFeed.class);
    List<SpreadsheetEntry> spreadsheets = feed.getEntries();

    if (spreadsheets.size() == 0) {
      // TODO: There were no spreadsheets, act accordingly.
    }

    // TODO: Choose a spreadsheet more intelligently based on your
    // app's needs.
    SpreadsheetEntry spreadsheet = spreadsheets.get(0);
    System.out.println(spreadsheet.getTitle().getPlainText());

    // Get the first worksheet of the first spreadsheet.
    // TODO: Choose a worksheet more intelligently based on your
    // app's needs.
    WorksheetFeed worksheetFeed = service.getFeed(
        spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);

    // Fetch the list feed of the worksheet.
    URL listFeedUrl = worksheet.getListFeedUrl();
    ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);

    // TODO: Choose a row more intelligently based on your app's needs.
    ListEntry row = listFeed.getEntries().get(0);

    // Update the row's data.
    row.getCustomElements().setValueLocal("firstname", "Sarah");
    row.getCustomElements().setValueLocal("lastname", "Hunt");
    row.getCustomElements().setValueLocal("age", "32");
    row.getCustomElements().setValueLocal("height", "154");

    // Save the row using the API.
    row.update();
  }
}

.NET

The following code snippet updates the first row of the first worksheet of the first spreadsheet in the spreadsheets feed.

using System;
using Google.GData.Client;
using Google.GData.Spreadsheets;

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");

      // TODO: Authorize the service object for a specific user (see other sections)

      // Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
      SpreadsheetQuery query = new SpreadsheetQuery();

      // Make a request to the API and get all spreadsheets.
      SpreadsheetFeed feed = service.Query(query);

      if (feed.Entries.Count == 0)
      {
        // TODO: There were no spreadsheets, act accordingly.
      }

      // TODO: Choose a spreadsheet more intelligently based on your
      // app's needs.
      SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
      Console.WriteLine(spreadsheet.Title.Text);

      // Get the first worksheet of the first spreadsheet.
      // TODO: Choose a worksheet more intelligently based on your
      // app's needs.
      WorksheetFeed wsFeed = spreadsheet.Worksheets;
      WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

      // Define the URL to request the list feed of the worksheet.
      AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

      // Fetch the list feed of the worksheet.
      ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
      ListFeed listFeed = service.Query(listQuery);

      // TODO: Choose a row more intelligently based on your app's needs.
      ListEntry row = (ListEntry)listFeed.Entries[0];

      // Update the row's data.
      foreach (ListEntry.Custom element in row.Elements)
      {
        if (element.LocalName == "firstname")
        {
          element.Value = "Sarah";
        }
        if (element.LocalName == "lastname")
        {
          element.Value = "Hunt";
        }
        if (element.LocalName == "age")
        {
          element.Value = "32";
        }
        if (element.LocalName == "height")
        {
          element.Value = "154";
        }
      }

      // Save the row using the API.
      row.Update();
    }
  }
}

Delete a list row

The Sheets API supports deleting rows in place. When a row is deleted, all rows beneath that row are pushed up by one.

HTTP

To delete a row, first retrieve the row to delete, then send a DELETE request to the row's edit URL. This is the same URL used to update the row.

DELETE https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full/rowId/rowVersion

If you want to make sure that you don't delete a row that has been changed by another client since you retrieved it, include an HTTP If-Match header that contains the original row's ETag value. You can determine the original row's ETag value by examining the entry element's gd:etag attribute.

If you want to delete the row regardless of whether someone else has updated it since you retrieved it, then use If-Match: * and don't include the ETag. (In this case, you don't need to retrieve the row before deleting it.)

Java

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;

import java.io.IOException;
import java.net.*;
import java.util.*;

public class MySpreadsheetIntegration {
  public static void main(String[] args)
      throws AuthenticationException, MalformedURLException, IOException, ServiceException {

    SpreadsheetService service =
        new SpreadsheetService("MySpreadsheetIntegration-v1");

    // TODO: Authorize the service object for a specific user (see other sections)

    // Define the URL to request.  This should never change.
    URL SPREADSHEET_FEED_URL = new URL(
        "https://spreadsheets.google.com/feeds/spreadsheets/private/full");

    // Make a request to the API and get all spreadsheets.
    SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
        SpreadsheetFeed.class);
    List<SpreadsheetEntry> spreadsheets = feed.getEntries();

    if (spreadsheets.size() == 0) {
      // TODO: There were no spreadsheets, act accordingly.
    }

    // TODO: Choose a spreadsheet more intelligently based on your
    // app's needs.
    SpreadsheetEntry spreadsheet = spreadsheets.get(0);
    System.out.println(spreadsheet.getTitle().getPlainText());

    // Get the first worksheet of the first spreadsheet.
    // TODO: Choose a worksheet more intelligently based on your
    // app's needs.
    WorksheetFeed worksheetFeed = service.getFeed(
        spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);

    // Fetch the list feed of the worksheet.
    URL listFeedUrl = worksheet.getListFeedUrl();
    ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);

    // TODO: Choose a row more intelligently based on your app's needs.
    ListEntry row = listFeed.getEntries().get(0);

    // Delete the row using the API.
    row.delete();
  }
}

.NET

using System;
using Google.GData.Client;
using Google.GData.Spreadsheets;

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");

      // TODO: Authorize the service object for a specific user (see other sections)

      // Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
      SpreadsheetQuery query = new SpreadsheetQuery();

      // Make a request to the API and get all spreadsheets.
      SpreadsheetFeed feed = service.Query(query);

      if (feed.Entries.Count == 0)
      {
        // TODO: There were no spreadsheets, act accordingly.
      }

      // TODO: Choose a spreadsheet more intelligently based on your
      // app's needs.
      SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
      Console.WriteLine(spreadsheet.Title.Text);

      // Get the first worksheet of the first spreadsheet.
      // TODO: Choose a worksheet more intelligently based on your
      // app's needs.
      WorksheetFeed wsFeed = spreadsheet.Worksheets;
      WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

      // Define the URL to request the list feed of the worksheet.
      AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

      // Fetch the list feed of the worksheet.
      ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
      ListFeed listFeed = service.Query(listQuery);

      // TODO: Choose a row more intelligently based on your app's needs.
      ListEntry row = (ListEntry)listFeed.Entries[0];

      // Delete the row using the API.
      row.Delete();
    }
  }
}

Work with cell-based feeds

In a cell feed, each entry represents a single cell. Cells are referred to by position.

There are two positioning notations used in Google Sheets. The first, called A1 notation, uses characters from the Latin alphabet to indicate the column, and integers to indicate the row of a cell. The second, called R1C1 notation, uses integers to represent both the column and the row, and precedes each by a corresponding R or C, to indicate row or column number. The addresses A1 and R1C1 are equivalent, as are the addresses B5 and R5C2.

Cell ranges also have notations in Google Sheets, but those are less relevant here.

Formulas often refer to other cells or cell ranges by their position. The cells feed allows formulas to be set on cells. However, the API always returns cell addresses for formulas on cells in R1C1 notation, even if the formula was set with A1 notation.

The title of a cell entry is always the A1 position of the cell.

The id URI of a cell entry always contains the R1C1 position of the cell.

The inputValue attribute of a cell entry always contains the value that a user would otherwise type into the Google Sheets user interface to manipulate the cell (i.e. either a literal value or a formula). To set a formula or a literal value on a cell, provide the text of the formula or value as the inputValue attribute. Remember that formulas must start with = to be considered a formula. If a cell contains a formula, the formula is always provided as the inputValue when cell entries are returned by the API.

The numericValue attribute of a cell entry, when present, indicates that the cell was determined to have a numeric value, and its numeric value is indicated with this attributed.

The literal value of the cell element is the calculated value of the cell, without formatting applied. If the cell contains a formula, the calculated value is given here. The Sheets API has no concept of formatting, and thus cannot manipulate formatting of cells.

Retrieve a cell-based feed

Note: The cells feed supports both the private and public visibilities, and both the full and basic projections.

HTTP

To determine the URL of a cell feed for a given worksheet, get the worksheets feed 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 authorization header:

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

The API returns a feed resembling the following:

<feed xmlns="http://www.w3.org/2005/Atom"
    xmlns:openSearch="http://a9.com/-/spec/opensearch/1.1/"
    xmlns:gs="http://schemas.google.com/spreadsheets/2006"
    xmlns:gd="http://schemas.google.com/g/2005"
    gd:etag='W/"D0cERnk-eip7ImA9WBBXGEg."'>
  <id>https://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="https://spreadsheets.google.com/ccc?key=key"/>
  <link rel="http://schemas.google.com/g/2005#feed" type="application/atom+xml"
    href="https://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="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/batch"/>
    href="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full"/>
  <link rel="self" type="application/atom+xml"
    href="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full"/>
  <author>
    <name>Fitzwilliam Darcy</name>
    <email>[e-mail]</email>
  </author>
  <openSearch:startIndex>1</openSearch:startIndex>
  <openSearch:itemsPerPage>36</openSearch:itemsPerPage>
  <gs:rowCount>100</gs:rowCount>
  <gs:colCount>20</gs:colCount>
  <entry gd:etag='"ImA9D1APFyp7"'>
    <id>https://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="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C1"/>
    <link rel="edit" type="application/atom+xml"
      href="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C1/bgvjf"/>
    <gs:cell row="1" col="1" inputValue="Name">Name</gs:cell>
  </entry>
  <entry gd:etag='"YD0PS1YXByp7Ig.."'>
    <id>https://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="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C2"/>
    <link rel="edit" type="application/atom+xml"
      href="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R1C2/1pn567"/>
    <gs:cell row="1" col="2" inputValue="Hours">Hours</gs:cell>
  </entry>

  ...

  <entry gd:etag='"ImB5CBYSRCp7"'>
    <id>https://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="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R9C4"/>
    <link rel="edit" type="application/atom+xml"
      href="https://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>

Java

The following code prints all of the cells in the first worksheet of the first spreadsheet in the spreadsheets feed.

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;

import java.io.IOException;
import java.net.*;
import java.util.*;

public class MySpreadsheetIntegration {
  public static void main(String[] args)
      throws AuthenticationException, MalformedURLException, IOException, ServiceException {

    SpreadsheetService service =
        new SpreadsheetService("MySpreadsheetIntegration-v1");

    // TODO: Authorize the service object for a specific user (see other sections)

    // Define the URL to request.  This should never change.
    URL SPREADSHEET_FEED_URL = new URL(
        "https://spreadsheets.google.com/feeds/spreadsheets/private/full");

    // Make a request to the API and get all spreadsheets.
    SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
        SpreadsheetFeed.class);
    List<SpreadsheetEntry> spreadsheets = feed.getEntries();

    if (spreadsheets.size() == 0) {
      // TODO: There were no spreadsheets, act accordingly.
    }

    // TODO: Choose a spreadsheet more intelligently based on your
    // app's needs.
    SpreadsheetEntry spreadsheet = spreadsheets.get(0);
    System.out.println(spreadsheet.getTitle().getPlainText());

    // Get the first worksheet of the first spreadsheet.
    // TODO: Choose a worksheet more intelligently based on your
    // app's needs.
    WorksheetFeed worksheetFeed = service.getFeed(
        spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);

    // Fetch the cell feed of the worksheet.
    URL cellFeedUrl = worksheet.getCellFeedUrl();
    CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);

    // Iterate through each cell, printing its value.
    for (CellEntry cell : cellFeed.getEntries()) {
      // Print the cell's address in A1 notation
      System.out.print(cell.getTitle().getPlainText() + "\t");
      // Print the cell's address in R1C1 notation
      System.out.print(cell.getId().substring(cell.getId().lastIndexOf('/') + 1) + "\t");
      // Print the cell's formula or text value
      System.out.print(cell.getCell().getInputValue() + "\t");
      // Print the cell's calculated value if the cell's value is numeric
      // Prints empty string if cell's value is not numeric
      System.out.print(cell.getCell().getNumericValue() + "\t");
      // Print the cell's displayed value (useful if the cell has a formula)
      System.out.println(cell.getCell().getValue() + "\t");
    }
  }
}

.NET

The following code prints all of the cells in the first worksheet of the first spreadsheet in the spreadsheets feed.

using System;
using Google.GData.Client;
using Google.GData.Spreadsheets;

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");

      // TODO: Authorize the service object for a specific user (see other sections)

      // Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
      SpreadsheetQuery query = new SpreadsheetQuery();

      // Make a request to the API and get all spreadsheets.
      SpreadsheetFeed feed = service.Query(query);

      if (feed.Entries.Count == 0)
      {
        // TODO: There were no spreadsheets, act accordingly.
      }

      // TODO: Choose a spreadsheet more intelligently based on your
      // app's needs.
      SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
      Console.WriteLine(spreadsheet.Title.Text);

      // Get the first worksheet of the first spreadsheet.
      // TODO: Choose a worksheet more intelligently based on your
      // app's needs.
      WorksheetFeed wsFeed = spreadsheet.Worksheets;
      WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

      // Fetch the cell feed of the worksheet.
      CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
      CellFeed cellFeed = service.Query(cellQuery);

      // Iterate through each cell, printing its value.
      foreach (CellEntry cell in cellFeed.Entries)
      {
        // Print the cell's address in A1 notation
        Console.WriteLine(cell.Title.Text);
        // Print the cell's address in R1C1 notation
        Console.WriteLine(cell.Id.Uri.Content.Substring(cell.Id.Uri.Content.LastIndexOf("/") + 1));
        // Print the cell's formula or text value
        Console.WriteLine(cell.InputValue);
        // Print the cell's calculated value if the cell's value is numeric
        // Prints empty string if cell's value is not numeric
        Console.WriteLine(cell.NumericValue);
        // Print the cell's displayed value (useful if the cell has a formula)
        Console.WriteLine(cell.Value);
      }
    }
  }
}

Fetch specific rows or columns

The Sheets API allows users to fetch specific rows or columns from a worksheet by providing additional URL parameters when making a request.

Warning: Column numbers used in these queries are not permanent. The result of the query will change if rows or columns are added or removed.

HTTP

To query for every row after the first row, and only the fourth column, send the request:

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

The response includes all cells in column 4, starting with row 2.

Java

The following code prints all of the cells in every row after the first row, and only the fourth column, of the first worksheet of the first spreadsheet in the spreadsheets feed.

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;

import java.io.IOException;
import java.net.*;
import java.util.*;

public class MySpreadsheetIntegration {
  public static void main(String[] args)
      throws AuthenticationException, MalformedURLException, IOException, ServiceException {

    SpreadsheetService service =
        new SpreadsheetService("MySpreadsheetIntegration-v1");

    // TODO: Authorize the service object for a specific user (see other sections)

    // Define the URL to request.  This should never change.
    URL SPREADSHEET_FEED_URL = new URL(
        "https://spreadsheets.google.com/feeds/spreadsheets/private/full");

    // Make a request to the API and get all spreadsheets.
    SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
        SpreadsheetFeed.class);
    List<SpreadsheetEntry> spreadsheets = feed.getEntries();

    if (spreadsheets.size() == 0) {
      // TODO: There were no spreadsheets, act accordingly.
    }

    // TODO: Choose a spreadsheet more intelligently based on your
    // app's needs.
    SpreadsheetEntry spreadsheet = spreadsheets.get(0);
    System.out.println(spreadsheet.getTitle().getPlainText());

    // Get the first worksheet of the first spreadsheet.
    // TODO: Choose a worksheet more intelligently based on your
    // app's needs.
    WorksheetFeed worksheetFeed = service.getFeed(
        spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);

    // Fetch column 4, and every row after row 1.
    URL cellFeedUrl = new URI(worksheet.getCellFeedUrl().toString()
        + "?min-row=2&min-col=4&max-col=4").toURL();
    CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);

    // Iterate through each cell, printing its value.
    for (CellEntry cell : cellFeed.getEntries()) {
      // Print the cell's address in A1 notation
      System.out.print(cell.getTitle().getPlainText() + "\t");
      // Print the cell's address in R1C1 notation
      System.out.print(cell.getId().substring(cell.getId().lastIndexOf('/') + 1) + "\t");
      // Print the cell's formula or text value
      System.out.print(cell.getCell().getInputValue() + "\t");
      // Print the cell's calculated value if the cell's value is numeric
      // Prints empty string if cell's value is not numeric
      System.out.print(cell.getCell().getNumericValue() + "\t");
      // Print the cell's displayed value (useful if the cell has a formula)
      System.out.println(cell.getCell().getValue() + "\t");
    }
  }
}

.NET

The following code prints all of the cells in every row after the first row, and only the fourth column, of the first worksheet of the first spreadsheet in the spreadsheets feed.

using System;
using Google.GData.Client;
using Google.GData.Spreadsheets;

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");

      // TODO: Authorize the service object for a specific user (see other sections)

      // Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
      SpreadsheetQuery query = new SpreadsheetQuery();

      // Make a request to the API and get all spreadsheets.
      SpreadsheetFeed feed = service.Query(query);

      if (feed.Entries.Count == 0)
      {
        // TODO: There were no spreadsheets, act accordingly.
      }

      // TODO: Choose a spreadsheet more intelligently based on your
      // app's needs.
      SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
      Console.WriteLine(spreadsheet.Title.Text);

      // Get the first worksheet of the first spreadsheet.
      // TODO: Choose a worksheet more intelligently based on your
      // app's needs.
      WorksheetFeed wsFeed = spreadsheet.Worksheets;
      WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

      // Fetch the cell feed of the worksheet.
      CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
      cellQuery.MinimumRow = 2;
      cellQuery.MinimumColumn = 4;
      cellQuery.MaximumColumn = 4;
      CellFeed cellFeed = service.Query(cellQuery);

      // Iterate through each cell, printing its value.
      foreach (CellEntry cell in cellFeed.Entries)
      {
        // Print the cell's address in A1 notation
        Console.WriteLine(cell.Title.Text);
        // Print the cell's address in R1C1 notation
        Console.WriteLine(cell.Id.Uri.Content.Substring(cell.Id.Uri.Content.LastIndexOf("/") + 1));
        // Print the cell's formula or text value
        Console.WriteLine(cell.InputValue);
        // Print the cell's calculated value if the cell's value is numeric
        // Prints empty string if cell's value is not numeric
        Console.WriteLine(cell.NumericValue);
        // Print the cell's displayed value (useful if the cell has a formula)
        Console.WriteLine(cell.Value);
      }
    }
  }
}

Change contents of a cell

Cells can be modified in place. Unlike other feeds, cells are not directly added nor deleted. They are fixed in place, based on the dimensions of a given worksheet. To add or remove cells from a worksheet, use the worksheets feed to change the dimension of the worksheet. To empty a cell, simply update its value to be an empty string.

Important: The cells feed allows for setting a formula on a cell by starting the cell inputValue with an = character. The calculated value of the cell is provided as the cell's actual value.

HTTP

Note: Use 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 entry with an empty content element in your PUT request instead of using DELETE.

To modify a cell in a worksheet, start by creating an entry element containing the relevant data, which in this case is a formula:

<entry xmlns="http://www.w3.org/2005/Atom"
    xmlns:gs="http://schemas.google.com/spreadsheets/2006">
  <id>https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R2C4</id>
  <link rel="edit" type="application/atom+xml"
    href="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R2C4"/>
  <gs:cell row="2" col="4" inputValue="=SUM(A1:B6)"/>
</entry>

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

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

Now, send the PUT request to the appropriate Sheets edit URL retrieved above, (replacing key, worksheetId, and cell with their appropriate values):

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

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

Java

The following code updates the value of cell A1 to be 200. It then updates the formula of cell B1 to be =SUM(A1, 200).

Important: The method CellEntry.changeInputValueLocal() must be used to set the literal value of a cell or the formula of a cell. The intended behavior is determined exactly as it is in the Google Sheets user interface. If the value given begins with an = character, a formula will be set.

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;

import java.io.IOException;
import java.net.*;
import java.util.*;

public class MySpreadsheetIntegration {
  public static void main(String[] args)
      throws AuthenticationException, MalformedURLException, IOException, ServiceException {

    SpreadsheetService service =
        new SpreadsheetService("MySpreadsheetIntegration-v1");

    // TODO: Authorize the service object for a specific user (see other sections)

    // Define the URL to request.  This should never change.
    URL SPREADSHEET_FEED_URL = new URL(
        "https://spreadsheets.google.com/feeds/spreadsheets/private/full");

    // Make a request to the API and get all spreadsheets.
    SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
        SpreadsheetFeed.class);
    List<SpreadsheetEntry> spreadsheets = feed.getEntries();

    if (spreadsheets.size() == 0) {
      // TODO: There were no spreadsheets, act accordingly.
    }

    // TODO: Choose a spreadsheet more intelligently based on your
    // app's needs.
    SpreadsheetEntry spreadsheet = spreadsheets.get(0);
    System.out.println(spreadsheet.getTitle().getPlainText());

    // Get the first worksheet of the first spreadsheet.
    // TODO: Choose a worksheet more intelligently based on your
    // app's needs.
    WorksheetFeed worksheetFeed = service.getFeed(
        spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
    List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
    WorksheetEntry worksheet = worksheets.get(0);

    // Fetch the cell feed of the worksheet.
    URL cellFeedUrl = worksheet.getCellFeedUrl();
    CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);

    // Iterate through each cell, updating its value if necessary.
    // TODO: Update cell values more intelligently.
    for (CellEntry cell : cellFeed.getEntries()) {
      if (cell.getTitle().getPlainText().equals("A1")) {
        cell.changeInputValueLocal("200");
        cell.update();
      } else if (cell.getTitle().getPlainText().equals("B1")) {
        cell.changeInputValueLocal("=SUM(A1, 200)");
        cell.update();
      }
    }
  }
}

Once updated, cell A1 has the value 200. Cell B1 shows the value 400, but has a numeric value of 400.0 and an inputValue of =SUM(R1C1, 200).

.NET

The following code updates the value of cell A1 to be 200. It then updates the formula of cell B1 to be =SUM(A1, 200).

using System;
using Google.GData.Client;
using Google.GData.Spreadsheets;

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");

      // TODO: Authorize the service object for a specific user (see other sections)

      // Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
      SpreadsheetQuery query = new SpreadsheetQuery();

      // Make a request to the API and get all spreadsheets.
      SpreadsheetFeed feed = service.Query(query);

      if (feed.Entries.Count == 0)
      {
        // TODO: There were no spreadsheets, act accordingly.
      }

      // TODO: Choose a spreadsheet more intelligently based on your
      // app's needs.
      SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
      Console.WriteLine(spreadsheet.Title.Text);

      // Get the first worksheet of the first spreadsheet.
      // TODO: Choose a worksheet more intelligently based on your
      // app's needs.
      WorksheetFeed wsFeed = spreadsheet.Worksheets;
      WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

      // Fetch the cell feed of the worksheet.
      CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
      CellFeed cellFeed = service.Query(cellQuery);

      // Iterate through each cell, updating its value if necessary.
      foreach (CellEntry cell in cellFeed.Entries)
      {
        if (cell.Title.Text == "A1")
        {
          cell.InputValue = "200";
          cell.Update();
        }
        else if (cell.Title.Text == "B1")
        {
          cell.InputValue = "=SUM(A1, 200)";
          cell.Update();
        }
      }
    }
  }
}

Once updated, cell A1 has the value 200. Cell B1 shows the value 400, but has a numeric value of 400.0 and an inputValue of =SUM(R1C1, 200).

Note: Even though formula values may be set using A1 notation, the API always returns cell addresses in formulas in R1C1 notation.

Update multiple cells with a batch request

The API supports updating an entire column, row, or other set of cells, in a single request. This is offered to improve performance over making large sets of individual requests. This process is called "batching requests".

Important: Only the cells feed supports batching requests.

A number of batch operations can be combined into a single request. The two types of batch operations supported are query and update. insert and delete are not supported because the cells feed cannot be used to insert or delete cells. Remember that the worksheets feed must be used to do that.

query and update can be combined in any size set, and sent to the API in a single request. The API responds with information on the status of each individual operation within the batch. For query requests, the result is the requested cell. For update requests, the result includes the manipulated cell with new values.

HTTP

Begin by creating a cells feed and setting the feed id to the href value of the post link. 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, send the XML using a POST request to the cells feed batch link, which has the format:

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

The format of the feed should be as follows:

<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>https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full</id>
  <entry>
    <batch:id>A1</batch:id>
    <batch:operation type="update"/>
    <id>https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R2C4</id>
    <link rel="edit" type="application/atom+xml"
      href="https://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>https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R2C5</id>
    <link rel="edit" type="application/atom+xml"
      href="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/R2C5/version"/>
    <gs:cell row="2" col="5" inputValue="moreInfo"/>
  </entry>
</feed>

Each cell entry element appended 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. For best results, set this value to some local identifier, so that the response can be identified once retrieved.

batch:operation

A batch:operation element, whose type attribute determines the type of request to be performed (query or update).

id

An id element containing the full URL to the cell to be updated. Be sure to examine the output XML when requesting the cells feed 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), in the following format: https://google.com/feeds/cells/key/worksheetId/private/full/R1C1CellIdentifier. 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, or the new formula for the cell.

Java

The following is a complete program for updating large numbers of cells.

import com.google.gdata.client.sample.util.SimpleCommandLineParser;
import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.Link;
import com.google.gdata.data.batch.BatchOperationType;
import com.google.gdata.data.batch.BatchStatus;
import com.google.gdata.data.batch.BatchUtils;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * A sample application showing how to efficiently use batch updates with the
 * Sheets API to create new cells.
 *
 * The specified spreadsheet key will be filled in with 'RnCn' identifier of
 * each cell, up to the {@code MAX_ROWS} and {@code MAX_COLS} constants defined
 * in this class.
 *
 * Usage: java BatchCellUpdater --key [spreadsheet-key]
 *
 * @author Josh Danziger
 */
public class BatchCellUpdater {

  /** The number of rows to fill in the destination workbook */
  private static final int MAX_ROWS = 75;

  /** The number of columns to fill in the destination workbook */
  private static final int MAX_COLS = 5;

  /**
   * A basic struct to store cell row/column information and the associated RnCn
   * identifier.
   */
  private static class CellAddress {
    public final int row;
    public final int col;
    public final String idString;

    /**
     * Constructs a CellAddress representing the specified {@code row} and
     * {@code col}.  The idString will be set in 'RnCn' notation.
     */
    public CellAddress(int row, int col) {
      this.row = row;
      this.col = col;
      this.idString = String.format("R%sC%s", row, col);
    }
  }

  public static void main(String[] args)
      throws AuthenticationException, MalformedURLException, IOException, ServiceException {

    // Command line parsing
    SimpleCommandLineParser parser = new SimpleCommandLineParser(args);
    String key = parser.getValue("key");
    boolean help = parser.containsKey("help", "h");

    if (help || key == null) {
      System.err.print("Usage: java BatchCellUpdater --key [spreadsheet-key]\n\n");
      System.exit(1);
    }

    long startTime = System.currentTimeMillis();

    // Prepare Spreadsheet Service
    SpreadsheetService ssSvc = new SpreadsheetService("Batch Cell Demo");

    // TODO: Authorize the service object for a specific user (see other sections)

    FeedURLFactory urlFactory = FeedURLFactory.getDefault();
    URL cellFeedUrl = urlFactory.getCellFeedUrl(key, "od6", "private", "full");
    CellFeed cellFeed = ssSvc.getFeed(cellFeedUrl, CellFeed.class);

    // Build list of cell addresses to be filled in
    List<CellAddress> cellAddrs = new ArrayList<CellAddress>();
    for (int row = 1; row <= MAX_ROWS; ++row) {
      for (int col = 1; col <= MAX_COLS; ++col) {
        cellAddrs.add(new CellAddress(row, col));
      }
    }

    // Prepare the update
    // getCellEntryMap is what makes the update fast.
    Map<String, CellEntry> cellEntries = getCellEntryMap(ssSvc, cellFeedUrl, cellAddrs);

    CellFeed batchRequest = new CellFeed();
    for (CellAddress cellAddr : cellAddrs) {
      CellEntry batchEntry = new CellEntry(cellEntries.get(cellAddr.idString));
      batchEntry.changeInputValueLocal(cellAddr.idString);
      BatchUtils.setBatchId(batchEntry, cellAddr.idString);
      BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE);
      batchRequest.getEntries().add(batchEntry);
    }

    // Submit the update
    Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
    CellFeed batchResponse = ssSvc.batch(new URL(batchLink.getHref()), batchRequest);

    // Check the results
    boolean isSuccess = true;
    for (CellEntry entry : batchResponse.getEntries()) {
      String batchId = BatchUtils.getBatchId(entry);
      if (!BatchUtils.isSuccess(entry)) {
        isSuccess = false;
        BatchStatus status = BatchUtils.getBatchStatus(entry);
        System.out.printf("%s failed (%s) %s", batchId, status.getReason(), status.getContent());
      }
    }

    System.out.println(isSuccess ? "\nBatch operations successful." : "\nBatch operations failed");
    System.out.printf("\n%s ms elapsed\n", System.currentTimeMillis() - startTime);
  }

  /**
   * Connects to the specified {@link SpreadsheetService} and uses a batch
   * request to retrieve a {@link CellEntry} for each cell enumerated in {@code
   * cellAddrs}. Each cell entry is placed into a map keyed by its RnCn
   * identifier.
   *
   * @param ssSvc the spreadsheet service to use.
   * @param cellFeedUrl url of the cell feed.
   * @param cellAddrs list of cell addresses to be retrieved.
   * @return a map consisting of one {@link CellEntry} for each address in {@code
   *         cellAddrs}
   */
  public static Map<String, CellEntry> getCellEntryMap(
      SpreadsheetService ssSvc, URL cellFeedUrl, List<CellAddress> cellAddrs)
      throws IOException, ServiceException {
    CellFeed batchRequest = new CellFeed();
    for (CellAddress cellId : cellAddrs) {
      CellEntry batchEntry = new CellEntry(cellId.row, cellId.col, cellId.idString);
      batchEntry.setId(String.format("%s/%s", cellFeedUrl.toString(), cellId.idString));
      BatchUtils.setBatchId(batchEntry, cellId.idString);
      BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.QUERY);
      batchRequest.getEntries().add(batchEntry);
    }

    CellFeed cellFeed = ssSvc.getFeed(cellFeedUrl, CellFeed.class);
    CellFeed queryBatchResponse =
      ssSvc.batch(new URL(cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM).getHref()),
                  batchRequest);

    Map<String, CellEntry> cellEntryMap = new HashMap<String, CellEntry>(cellAddrs.size());
    for (CellEntry entry : queryBatchResponse.getEntries()) {
      cellEntryMap.put(BatchUtils.getBatchId(entry), entry);
      System.out.printf("batch %s {CellEntry: id=%s editLink=%s inputValue=%s\n",
          BatchUtils.getBatchId(entry), entry.getId(), entry.getEditLink().getHref(),
          entry.getCell().getInputValue());
    }

    return cellEntryMap;
  }
}

.NET

The following is a complete program for updating large numbers of cells.

using System;
using System.Collections.Generic;
using Google.GData.Client;
using Google.GData.Spreadsheets;

/**
 * A sample application showing how to efficiently use batch updates with the
 * Sheets API to create new cells.
 *
 * The specified spreadsheet key will be filled in with 'RnCn' identifier of
 * each cell, up to the {@code MAX_ROWS} and {@code MAX_COLS} constants defined
 * in this class.
 *
 * Usage: BatchCellUpdater [user] [pass] [spreadsheet-key]
 *
 * @author Claudio Cherubino
 */
namespace MySpreadsheetIntegration
{
  class BatchCellUpdater
  {
    // The number of rows to fill in the destination workbook
    const int MAX_ROWS = 75;

    // The number of columns to fill in the destination workbook
    const int MAX_COLS = 5;

    /**
     * A basic struct to store cell row/column information and the associated RnCn
     * identifier.
     */
    private class CellAddress
    {
      public uint Row;
      public uint Col;
      public string IdString;

      /**
       * Constructs a CellAddress representing the specified {@code row} and
       * {@code col}. The IdString will be set in 'RnCn' notation.
       */
      public CellAddress(uint row, uint col)
      {
        this.Row = row;
        this.Col = col;
        this.IdString = string.Format("R{0}C{1}", row, col);
      }
    }

    static void Main(string[] args)
    {
      string key;

      // Command line parsing
      if (args.Length != 1)
      {
        Console.Error.WriteLine("Syntax: BatchCellUpdater <key>");
        return;
      }
      else
      {
        key = args[0];
      }

      // Prepare Spreadsheet Service
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");

      // TODO: Authorize the service object for a specific user (see other sections)

      CellQuery cellQuery = new CellQuery(key, "od6", "private", "full");
      CellFeed cellFeed = service.Query(cellQuery);

      // Build list of cell addresses to be filled in
      List<CellAddress> cellAddrs = new List<CellAddress>();
      for (uint row = 1; row <= MAX_ROWS; ++row)
      {
        for (uint col = 1; col <= MAX_COLS; ++col)
        {
          cellAddrs.Add(new CellAddress(row, col));
        }
      }

      // Prepare the update
      // GetCellEntryMap is what makes the update fast.
      Dictionary<String, CellEntry> cellEntries = GetCellEntryMap(service, cellFeed, cellAddrs);

      CellFeed batchRequest = new CellFeed(cellQuery.Uri, service);
      foreach (CellAddress cellAddr in cellAddrs)
      {
        CellEntry batchEntry = cellEntries[cellAddr.IdString];
        batchEntry.InputValue = cellAddr.IdString;
        batchEntry.BatchData = new GDataBatchEntryData(cellAddr.IdString, GDataBatchOperationType.update);
        batchRequest.Entries.Add(batchEntry);
      }

      // Submit the update
      CellFeed batchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));

      // Check the results
      bool isSuccess = true;
      foreach (CellEntry entry in batchResponse.Entries)
      {
        string batchId = entry.BatchData.Id;
        if (entry.BatchData.Status.Code != 200)
        {
          isSuccess = false;
          GDataBatchStatus status = entry.BatchData.Status;
          Console.WriteLine("{0} failed ({1})", batchId, status.Reason);
        }
      }

      Console.WriteLine(isSuccess ? "Batch operations successful." : "Batch operations failed");
    }

    /**
     * Connects to the specified {@link SpreadsheetsService} and uses a batch
     * request to retrieve a {@link CellEntry} for each cell enumerated in {@code
     * cellAddrs}. Each cell entry is placed into a map keyed by its RnCn
     * identifier.
     *
     * @param service the spreadsheet service to use.
     * @param cellFeed the cell feed to use.
     * @param cellAddrs list of cell addresses to be retrieved.
     * @return a dictionary consisting of one {@link CellEntry} for each address in {@code
     *         cellAddrs}
     */
    private static Dictionary<String, CellEntry> GetCellEntryMap(
        SpreadsheetsService service, CellFeed cellFeed, List<CellAddress> cellAddrs)
    {
      CellFeed batchRequest = new CellFeed(new Uri(cellFeed.Self), service);
      foreach (CellAddress cellId in cellAddrs)
      {
        CellEntry batchEntry = new CellEntry(cellId.Row, cellId.Col, cellId.IdString);
        batchEntry.Id = new AtomId(string.Format("{0}/{1}", cellFeed.Self, cellId.IdString));
        batchEntry.BatchData = new GDataBatchEntryData(cellId.IdString, GDataBatchOperationType.query);
        batchRequest.Entries.Add(batchEntry);
      }

      CellFeed queryBatchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));

      Dictionary<String, CellEntry> cellEntryMap = new Dictionary<String, CellEntry>();
      foreach (CellEntry entry in queryBatchResponse.Entries)
      {
        cellEntryMap.Add(entry.BatchData.Id, entry);
        Console.WriteLine("batch {0} (CellEntry: id={1} editLink={2} inputValue={3})",
            entry.BatchData.Id, entry.Id, entry.EditUri,
            entry.InputValue);
      }

      return cellEntryMap;
    }
  }
}