Google Apps Platform

Google Spreadsheets API version 3.0

Important: Versions 1 and 2 of the Google Spreadsheets API are no longer available as of October 20, 2012. Migration to v3 should be simple, as it includes all the features of v1 and v2, thus you only need to stop specifying v1 or v2 with your requests.

The Google Spreadsheets API enables developers to create applications that read and modify the data in Google Spreadsheets.



Introduction

This guide discusses how to use the Google Spreadsheets API version 3.0.

What can this API do?

This API is useful for:

  • managing the worksheets in a Google spreadsheet
  • consuming the rows of a worksheet
  • managing cells in a worksheet by position

Common use cases of this API include:

  • computing financial data stored in an inaccessible system
  • presenting statistics to users in a web browser
  • consuming data entered into a spreadsheet by a user

Audience

This guide is intended for software developers needing a technical reference for using the Google Spreadsheets API. The information you find in this guide is written by Google engineers, and is an authoritative reference of the API.

Terminology used in this guide

Throughout this guide, a number of terms are used which are intended to have specific meanings.

spreadsheet
Google Spreadsheet document, created with the Google Docs user interface or with the Google Drive API.
worksheet
Named collection of cells within a spreadsheet. All spreadsheets must have at least one worksheet, and have one worksheet by default.
list row
Row of cells in a worksheet, represented as a key-value pair, where each key is a column name, and each value is the cell value. The first row of a worksheet is always considered the header row when using the API, and therefore is the row that defines the keys represented in each row.
cell
Single piece of data in a worksheet.

Which version of the API should be used?

Version 3.0 (the version described in this guide), is the recommended version of the API. There are no advantages to using previous versions of the API. For this reason, we no longer document versions 1 or 2 of the Google Spreadsheets API, as version 3.0 is a superset of versions 1 and 2. Versions 1 and 2 are deprecated as of April 20, 2012, and any developer actively using these versions is urged to upgrade.

Additional resources and information about this guide

Occasionally, we make a mistake in this documentation, or unknowingly break a feature that you depend on.

If this has happened to you, sorry! You can help us fix the issue by posting in the forum or by filing a bug. We appreciate your help!

Setting up your client library

A number of client libraries are provided in various languages. These client libraries make it easier to interact with the Spreadsheets API.

This section is not relevant to applications using the Spreadsheets API at the protocol level, but all application developers should strongly consider using a GData client library to interact with the API.

Java

The Java client library is maintained in an open-source project on Google Project hosting. The Java library itself is distributed as a ZIP file, containing a number of JARs that applications can import in order to gain access to the library.

The Java client library requires JDK 5.0 or greater. The latest version of the Oracle JDK is available from http://www.oracle.com/technetwork/java/index.html.

Unfortunately, due to restrictive licensing, we cannot at this time just give developers a ZIP of all JARs required to interact with the API. Some of the JARs are not available without additional license agreements. Because of this, some of the JARs must be downloaded individually. We sincerely apologize for this inconvenience.

To setup a development environment for working with the Spreadsheets API, perform the following steps.

  1. Download the latest gdata-src.java-*.zip file from the project's Downloads page. Replace * in this case with something like 1.46.0.

  2. Extract the ZIP file into a new directory.

    unzip gdata-src.java-1.46.0.zip -d ./gdata-java-client
    
  3. Copy the JARs from gdata-java-client/gdata/java/lib into a directory included in the application's classpath.

    cp gdata-java-client/gdata/java/lib/* /path/to/application/lib
    
  4. Copy the JARs from gdata-java-client/gdata/java/deps into the application's classpath.

    cp gdata-java-client/gdata/java/deps/* /path/to/application/lib
    
  5. Download the JavaMail API (version 1.4 or greater) from here. Extract the ZIP file and copy mail.jar to the application's classpath.

    unzip javamail1_4_4.zip -d javamail
    cp javamail/javamail-1.4.4/mail.jar /path/to/application/lib
    
  6. If using the Oracle JDK version 1.5, download the JavaBeans Activation Framework from here. Extract the ZIP file and copy activation.jar to the application's classpath.

    unzip jaf-1_1_1.zip -d jaf
    cp jaf/jaf-1.1.1/activation.jar /path/to/application/lib
    
  7. To implement any of the code discussed in this document, use the following class template.

    import com.google.gdata.client.authn.oauth.*;
    import com.google.gdata.client.spreadsheet.*;
    import com.google.gdata.data.*;
    import com.google.gdata.data.batch.*;
    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 {
    
        // Application code here
    
      }
    }
    
  8. Once the above template is in place, continue reading the rest of this document, applying the Java code samples as necessary.

.NET

The .NET client library is maintained in an open-source project on Google Project hosting. The .NET library itself is distributed as a MSI installer, containing a number of DLLs that applications can import in order to gain access to the library.

The .NET client library requires the .NET Framework 2.0 or greater.

To setup a development environment for working with the Spreadsheets API, perform the following steps.

  1. Download the latest Google_Data_API_Setup_*.msi file from the project's Downloads page. Replace * in this case with something like 1.9.0.0.

  2. Run the installer and follow the instructions in the setup wizard.

  3. To implement any of the code discussed in this document, use the following class template.

    using Google.GData.Client;
    using Google.GData.Spreadsheets;
    
    namespace MySpreadsheetIntegration
    {
      class Program {
        static void Main(string[] args)
        {
    
          // Application code here
    
        }
      }
    }
    
  4. Once the above template is in place, continue reading the rest of this document, applying the .NET code samples as necessary.

Authorizing requests

When your application requests non-public user data, it must include an authorization token. The token also identifies your application to Google.

About authorization protocols

We recommend using OAuth 2.0 to authorize requests.

The Google Spreadsheets API also supports older authorization options, such as OAuth 1.0, AuthSub, or ClientLogin; however, in most cases we don't recommend using those other options. If your application already uses those options, we recommend migrating to OAuth 2.0 if possible.

If your application has certain unusual authorization requirements, such as logging in at the same time as requesting data access (hybrid) or domain-wide delegation of authority (2LO), then you cannot currently use OAuth 2.0 tokens. In such cases, you must instead use OAuth 1.0 tokens.

Authorizing requests with OAuth 2.0

Requests to the Google Spreadsheets API for non-public user data must be authorized by an authenticated user.

The details of the authorization process, or "flow," for OAuth 2.0 vary somewhat depending on what kind of application you're writing. The following general process applies to all application types:

  1. When you create your application, you register it with Google. Google then provides information you'll need later, such as a client ID and a client secret.
  2. Activate the Google Spreadsheets API in the Services pane of the Google APIs Console. (If it isn't listed in the Console, then skip this step.)
  3. When your application needs access to user data, it asks Google for a particular scope of access.
  4. Google displays an OAuth dialog to the user, asking them to authorize your application to request some of their data.
  5. If the user approves, then Google gives your application a short-lived access token.
  6. Your application requests user data, attaching the access token to the request.
  7. If Google determines that your request and the token are valid, it returns the requested data.

Some flows include additional steps, such as using refresh tokens to acquire new access tokens. For detailed information about flows for various types of applications, see Google's OAuth 2.0 documentation.

Here's the OAuth 2.0 scope information for the Google Spreadsheets API:

https://spreadsheets.google.com/feeds

Additionally, if an application needs to create spreadsheets, or otherwise manipulate their metadata, then the application must also request a Google Drive API scope; see Choose Auth Scopes in the Google Drive API docs for information about the available scopes.

To request access using OAuth 2.0, your application needs the scope information, as well as information that Google supplies during application registration (such as the client ID and/or the client secret).

Performing OAuth 2.0

.NET

The following example is copied from the google-gdata project's sample directory. It has been modified to accommodate the Spreadsheets API, and is provided here for ease of reference.

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

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      ////////////////////////////////////////////////////////////////////////////
      // STEP 1: Configure how to perform OAuth 2.0
      ////////////////////////////////////////////////////////////////////////////

      // TODO: Update the following information with that obtained from
      // https://code.google.com/apis/console. After registering
      // your application, these will be provided for you.

      string CLIENT_ID = "12345678.apps.googleusercontent.com";

      // This is the OAuth 2.0 Client Secret retrieved
      // above.  Be sure to store this value securely.  Leaking this
      // value would enable others to act on behalf of your application!
      string CLIENT_SECRET = "Gc0230jdsah01jqpowpgff";

      // Space separated list of scopes for which to request access.
      string SCOPE = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";

      // This is the Redirect URI for installed applications.
      // If you are building a web application, you have to set your
      // Redirect URI at https://code.google.com/apis/console.
      string REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob";

      ////////////////////////////////////////////////////////////////////////////
      // STEP 2: Set up the OAuth 2.0 object
      ////////////////////////////////////////////////////////////////////////////

      // OAuth2Parameters holds all the parameters related to OAuth 2.0.
      OAuth2Parameters parameters = new OAuth2Parameters();

      // Set your OAuth 2.0 Client Id (which you can register at
      // https://code.google.com/apis/console).
      parameters.ClientId = CLIENT_ID;

      // Set your OAuth 2.0 Client Secret, which can be obtained at
      // https://code.google.com/apis/console.
      parameters.ClientSecret = CLIENT_SECRET;

      // Set your Redirect URI, which can be registered at
      // https://code.google.com/apis/console.
      parameters.RedirectUri = REDIRECT_URI;

      ////////////////////////////////////////////////////////////////////////////
      // STEP 3: Get the Authorization URL
      ////////////////////////////////////////////////////////////////////////////

      // Set the scope for this particular service.
      parameters.Scope = SCOPE;

      // Get the authorization url.  The user of your application must visit
      // this url in order to authorize with Google.  If you are building a
      // browser-based application, you can redirect the user to the authorization
      // url.
      string authorizationUrl = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters);
      Console.WriteLine(authorizationUrl);
      Console.WriteLine("Please visit the URL above to authorize your OAuth "
        + "request token.  Once that is complete, type in your access code to "
        + "continue...");
      parameters.AccessCode = Console.ReadLine();

      ////////////////////////////////////////////////////////////////////////////
      // STEP 4: Get the Access Token
      ////////////////////////////////////////////////////////////////////////////

      // Once the user authorizes with Google, the request token can be exchanged
      // for a long-lived access token.  If you are building a browser-based
      // application, you should parse the incoming request token from the url and
      // set it in OAuthParameters before calling GetAccessToken().
      OAuthUtil.GetAccessToken(parameters);
      string accessToken = parameters.AccessToken;
      Console.WriteLine("OAuth Access Token: " + accessToken);

      ////////////////////////////////////////////////////////////////////////////
      // STEP 5: Make an OAuth authorized request to Google
      ////////////////////////////////////////////////////////////////////////////

      // Initialize the variables needed to make the request
      GOAuth2RequestFactory requestFactory =
          new GOAuth2RequestFactory(null, "MySpreadsheetIntegration-v1", parameters);
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
      service.RequestFactory = requestFactory;

      // Make the request to Google
      // See other portions of this guide for code to put here...
    }
  }
}

Authorizing requests with OAuth 1.0a

As with OAuth 2.0, the scope to request for OAuth 1.0a while working with the Spreadsheets API is:

https://spreadsheets.google.com/feeds

Again, if an application needs to create spreadsheets, or otherwise manipulate their metadata, then the application must also request the Google Documents Lists API scope:

https://docs.google.com/feeds

As mentioned previously, the Java client library does not currently support OAuth 2.0. Instead, to authorize with OAuth 1.0a, use the following code.

Performing 3-Legged OAuth 1.0a (OAuth 1.0a for Web Applications)

Java

The following example is copied from the gdata-java-client project's sample directory. It has been modified to accommodate the Spreadsheets API, and is provided here for ease of reference.

import com.google.gdata.client.authn.oauth.GoogleOAuthHelper;
import com.google.gdata.client.authn.oauth.GoogleOAuthParameters;
import com.google.gdata.client.authn.oauth.OAuthHmacSha1Signer;
import com.google.gdata.client.authn.oauth.OAuthRsaSha1Signer;
import com.google.gdata.client.authn.oauth.OAuthSigner;
import com.google.gdata.client.spreadsheet.*;
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.*;
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 {

    ////////////////////////////////////////////////////////////////////////////
    // STEP 1: Configure how to perform OAuth 1.0a
    ////////////////////////////////////////////////////////////////////////////

    boolean USE_RSA_SIGNING = false;

    // TODO: Update the following information with that obtained from
    // https://www.google.com/accounts/ManageDomains. After registering
    // your application, these will be provided for you.

    String CONSUMER_KEY = "yourappdomain.com";

    // TODO: Replace this with base-64 encoded private key conforming
    // to PKCS #8 standard, if and only if you will use RSA-SHA1
    // signing.  Otherwise, this is the OAuth Consumer Secret retrieved
    // above.  Be sure to store this value securely.  Leaking this
    // value would enable others to act on behalf of your application!
    String CONSUMER_SECRET = "Gc0230jdsah01jqpowpgff";

    // Space separated list of scopes for which to request access.
    String SCOPES = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";

    ////////////////////////////////////////////////////////////////////////////
    // STEP 2: Set up the OAuth objects
    ////////////////////////////////////////////////////////////////////////////

    // You first need to initialize a few OAuth-related objects.
    // GoogleOAuthParameters holds all the parameters related to OAuth.
    // OAuthSigner is responsible for signing the OAuth base string.
    GoogleOAuthParameters oauthParameters = new GoogleOAuthParameters();

    // Set your OAuth Consumer Key (which you can register at
    // https://www.google.com/accounts/ManageDomains).
    oauthParameters.setOAuthConsumerKey(CONSUMER_KEY);

    // Initialize the OAuth Signer.  If you are using RSA-SHA1, you must provide
    // your private key as a Base-64 string conforming to the PKCS #8 standard.
    // Visit http://code.google.com/apis/gdata/authsub.html#Registered to learn
    // more about creating a key/certificate pair.  If you are using HMAC-SHA1,
    // you must set your OAuth Consumer Secret, which can be obtained at
    // https://www.google.com/accounts/ManageDomains.
    OAuthSigner signer;
    if (USE_RSA_SIGNING) {
      signer = new OAuthRsaSha1Signer(CONSUMER_SECRET);
    } else {
      oauthParameters.setOAuthConsumerSecret(CONSUMER_SECRET);
      signer = new OAuthHmacSha1Signer();
    }

    // Finally, create a new GoogleOAuthHelperObject.  This is the object you
    // will use for all OAuth-related interaction.
    GoogleOAuthHelper oauthHelper = new GoogleOAuthHelper(signer);

    ////////////////////////////////////////////////////////////////////////////
    // STEP 3: Get the Authorization URL
    ////////////////////////////////////////////////////////////////////////////

    // Set the scope for this particular service.
    oauthParameters.setScope(SCOPES);

    // This method also makes a request to get the unauthorized request token,
    // and adds it to the oauthParameters object, along with the token secret
    // (if it is present).
    oauthHelper.getUnauthorizedRequestToken(oauthParameters);

    // Get the authorization url.  The user of your application must visit
    // this url in order to authorize with Google.  If you are building a
    // browser-based application, you can redirect the user to the authorization
    // url.
    String requestUrl = oauthHelper.createUserAuthorizationUrl(oauthParameters);
    System.out.println(requestUrl);
    System.out.println("Please visit the URL above to authorize your OAuth "
        + "request token.  Once that is complete, press any key to "
        + "continue...");
    System.in.read();

    ////////////////////////////////////////////////////////////////////////////
    // STEP 4: Get the Access Token
    ////////////////////////////////////////////////////////////////////////////

    // Once the user authorizes with Google, the request token can be exchanged
    // for a long-lived access token.  If you are building a browser-based
    // application, you should parse the incoming request token from the url and
    // set it in GoogleOAuthParameters before calling getAccessToken().
    String token = oauthHelper.getAccessToken(oauthParameters);
    System.out.println("OAuth Access Token: " + token);
    System.out.println();

    ////////////////////////////////////////////////////////////////////////////
    // STEP 5: Make an OAuth authorized request to Google
    ////////////////////////////////////////////////////////////////////////////

    // Initialize the variables needed to make the request
    URL feedUrl = new URL(variables.getFeedUrl());
    System.out.println("Sending request to " + feedUrl.toString());
    System.out.println();
    SpreadsheetService service =
        new SpreadsheetService("MySpreadsheetIntegration-v1");

    // Set the OAuth credentials which were obtained from the step above.
    service.setOAuthCredentials(oauthParameters, signer);

    // Make the request to Google
    // See other portions of this guide for code to put here...

    ////////////////////////////////////////////////////////////////////////////
    // STEP 6: Revoke the OAuth token
    ////////////////////////////////////////////////////////////////////////////

    System.out.println("Revoking OAuth Token...");
    oauthHelper.revokeToken(oauthParameters);
    System.out.println("OAuth Token revoked...");
  }
}

.NET

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

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      ////////////////////////////////////////////////////////////////////////////
      // STEP 1: Configure how to perform OAuth 1.0a
      ////////////////////////////////////////////////////////////////////////////

      // TODO: Update the following information with that obtained from
      // https://www.google.com/accounts/ManageDomains. After registering
      // your application, these will be provided for you.

      string CONSUMER_KEY = "yourappdomain.com";

      // This is the OAuth Consumer Secret retrieved
      // above.  Be sure to store this value securely.  Leaking this
      // value would enable others to act on behalf of your application!
      string CONSUMER_SECRET = "Gc0230jdsah01jqpowpgff";

      // Space separated list of scopes for which to request access.
      string SCOPE = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";

      ////////////////////////////////////////////////////////////////////////////
      // STEP 2: Set up the OAuth object
      ////////////////////////////////////////////////////////////////////////////

      // OAuthParameters holds all the parameters related to OAuth.
      OAuthParameters parameters = new OAuthParameters();

      // Set your OAuth Consumer Key (which you can register at
      // https://www.google.com/accounts/ManageDomains).
      parameters.ConsumerKey = CONSUMER_KEY;

      // Set your OAuth Consumer Secret, which can be obtained at
      // https://www.google.com/accounts/ManageDomains.
      parameters.ConsumerSecret = CONSUMER_SECRET;

      // HMAC-SHA1 is the only signature method supported by the .NET library.
      parameters.SignatureMethod = OAuthBase.HMACSHA1SignatureType;

      ////////////////////////////////////////////////////////////////////////////
      // STEP 3: Get the Authorization URL
      ////////////////////////////////////////////////////////////////////////////

      // Set the scope for this particular service.
      parameters.Scope = SCOPE;

      // This method also makes a request to get the unauthorized request token,
      // and adds it to the oauthParameters object, along with the token secret
      // (if it is present).
      OAuthUtil.GetUnauthorizedRequestToken(parameters);

      // Get the authorization url.  The user of your application must visit
      // this url in order to authorize with Google.  If you are building a
      // browser-based application, you can redirect the user to the authorization
      // url.
      string authorizationUrl = OAuthUtil.CreateUserAuthorizationUrl(parameters);
      Console.WriteLine(authorizationUrl);
      Console.WriteLine("Please visit the URL above to authorize your OAuth "
        + "request token.  Once that is complete, press any key to "
        + "continue...");
      Console.ReadLine();

      ////////////////////////////////////////////////////////////////////////////
      // STEP 4: Get the Access Token
      ////////////////////////////////////////////////////////////////////////////

      // Once the user authorizes with Google, the request token can be exchanged
      // for a long-lived access token.  If you are building a browser-based
      // application, you should parse the incoming request token from the url and
      // set it in OAuthParameters before calling GetAccessToken().
      OAuthUtil.GetAccessToken(parameters);
      string accessToken = parameters.Token;
      Console.WriteLine("OAuth Access Token: " + accessToken);

      ////////////////////////////////////////////////////////////////////////////
      // STEP 5: Make an OAuth authorized request to Google
      ////////////////////////////////////////////////////////////////////////////

      // Initialize the variables needed to make the request
      GOAuthRequestFactory requestFactory =
          new GOAuthRequestFactory(null, "MySpreadsheetIntegration-v1", parameters);
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
      service.RequestFactory = requestFactory;

      // Make the request to Google
      // See other portions of this guide for code to put here...
    }
  }
}

Authorizing requests with ClientLogin

ClientLogin allows applications to make requests to the Spreadsheets API using the username and application-specific password of a Google account. When using this mechanism, applications must take extreme caution when handling these credentials, as leaking the credentials could have severe consequences. Any application experiencing a security concern should immediately notify affected users.

To implement the ClientLogin mechanism, make an authorized request to the Spreadsheets API, with the appropriate Authorization header.

Protocol

Detailed information on implementing ClientLogin from a protocol perspective is available here.

When fetching a ClientLogin token for the Spreadsheets API, be sure to use the wise service name.

Java

The following is a code sample in which a SpreadsheetService object is configured to use ClientLogin for authorization.

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

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

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

    String USERNAME = "someuser@gmail.com";
    String PASSWORD = "testing123";

    SpreadsheetService service =
        new SpreadsheetService("MySpreadsheetIntegration-v1");
    service.setUserCredentials(USERNAME, PASSWORD);

    // TODO: See other portions of this guide for code to put here...
  }
}

.NET

The following is a code sample in which a SpreadsheetService object is configured to use ClientLogin for authorization.

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

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      string USERNAME = "someuser@gmail.com";
      string PASSWORD = "testing123";

      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
      service.setUserCredentials(USERNAME, PASSWORD);

      // TODO: See other portions of this guide for code to put here...
    }
  }
}

Specifying a version

Every request sent to the Google Spreadsheets API can optionally specify that the request is for version 3.0 of the API, although this is not necessary.

Protocol

To specify a version number, use the GData-Version HTTP header.

GData-Version: 3.0

Alternatively, version 3.0 can be specified as a URL parameter.

https://spreadsheets.google.com/feeds/spreadsheets/private/full?v=3.0

Java

Version 3.0 is specified by default in the GData Java client library. Java developers do not need to do anything to specify a version.

In the rare circumstance an application may need to specify another version, the line of code to do so reads:

SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration");
service.setProtocolVersion(SpreadsheetService.Versions.V3);
//service.setProtocolVersion(SpreadsheetService.Versions.V2);
//service.setProtocolVersion(SpreadsheetService.Versions.V1);

Python

Version 3.0 is specified by default in the GData Python client library. Python developers do not need to do anything to specify a version.

.NET

Version 3.0 is specified by default in the GData .NET client library. .NET developers do not need to do anything to specify a version.

Creating a spreadsheet

It is possible to create a new spreadsheet by uploading a spreadsheet file via the Google Drive API. The Spreadsheets API does not currently provide a way to delete a spreadsheet, but this is also provided in the Google Drive API. For testing purposes, you may create a spreadsheet manually or upload one.

Spreadsheets API URLs, visibilities, and projections

The Spreadsheets API supports visibilities and projections in request URLs. Visibilities determine whether or not authorization should be used for a request. Projections determine which data is returned in a response to a request. Visibilities and projections are set in request URLs to the API.

An example Spreadsheets API URL reads:

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

URLs similar to that one are returned by the Spreadsheets API in various link elements in feed responses. When working with the API, applications can change the keywords private and full, in order to manipulate API functionality slightly. URLs returned by the API after having changed these keywords will reflect the keywords used in the source request.

That is, if the following request is made to the API:

GET https://spreadsheets.google.com/feeds/worksheets/key/private/basic

Then the cells feed URLs in each worksheet entry are of the form:

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

And the list feed URLs in each worksheet entry are of the form:

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

Note that the basic projection is already set on these generated URLs. The same applies for the public visibility described below.

There are other visibility and projection values available besides private and full.

The private visibility can be replaced with the public visibility, which enables the feed to work without authorization for spreadsheets that have been "Published to the Web". The public visibility is supported on the worksheets, list, and cells feeds. The public visibility is useful for accessing the contents of a spreadsheet from the client context of a web page in JavaScript, for example.

Publishing a spreadsheet to the web can only be done from the Google Spreadsheets user interface. To start publishing a spreadsheet to the web, select File > Publish to the web from the Spreadsheets user interface, and then click the Start Publishing button.

The full projection can be replaced with the basic projection, causing the feed to return less information (i.e. fewer fields, and only the most important data). This is better for low-bandwidth environments. The basic projection is supported on the worksheets, list, and cells feeds.

Retrieving a list of spreadsheets

The Google Spreadsheets API supports fetching a list of spreadsheets for the authenticated user. Note however that you cannot create or delete spreadsheets via this API. For those operations, you must use the Google Drive API.

This API operation is accessible only with authorized requests. Requests that have not been authorized cannot access this feed (i.e. this feed is not public).

Protocol

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

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

The result is a feed that lists all of user's spreadsheets.

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

<entry gd:etag=""FDHGCHnx12"">
  <id>https://spreadsheets.google.com/feeds/spreadsheets/tNXTXMh83yMWLVJfEgOWTvQ</id>
  <updated>2011-05-27T15:08:37.102Z</updated>
  <category scheme="http://schemas.google.com/spreadsheets/2006" term="http://schemas.google.com/spreadsheets/2006#spreadsheet"/>
  <title>My Spreadsheet</title>
  <content type="application/atom+xml;type=feed" src="https://spreadsheets.google.com/feeds/worksheets/tNXTXMh83yMWLVJfEgOWTvQ/private/full"/>
  <link rel="http://schemas.google.com/spreadsheets/2006#tablesfeed" type="application/atom+xml" href="https://spreadsheets.google.com/feeds/tNXTXMh83yMWLVJfEgOWTvQ/tables"/>
  <link rel="alternate" type="text/html" href="https://spreadsheets.google.com/ccc?key=0Ak8c_1IVge120199weAHSjDHf1XTFZKZkVnT1dUdlE"/>
  <link rel="self" type="application/atom+xml" href="https://spreadsheets.google.com/feeds/spreadsheets/private/full/tNXTXMh83yMWLVJfEgOWTvQ"/>
  <author>
    <name>joe</name>
    <email>joe@yourdomain.com</email>
  </author>
</entry>

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

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

Java

The following code fetches a list of all spreadsheets owned or shared with an authorized user, and prints out the title of each spreadsheet.

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();

    // Iterate through all of the spreadsheets returned
    for (SpreadsheetEntry spreadsheet : spreadsheets) {
      // Print the title of this spreadsheet to the screen
      System.out.println(spreadsheet.getTitle().getPlainText());
    }
  }
}

.NET

The following code fetches a list of all spreadsheets owned or shared with an authorized user, and prints out the title of each spreadsheet.

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);

      // Iterate through all of the spreadsheets returned
      foreach (SpreadsheetEntry entry in feed.Entries)
      {
        // Print the title of this spreadsheet to the screen
        Console.WriteLine(entry.Title.Text);
      }
    }
  }
}

If no entries are returned, it means there are no spreadsheets created or shared by this account. Creating a spreadsheet and requesting the feed again yields a spreadsheet entry.

Working with worksheets

A given spreadsheet may contain multiple worksheets. Each spreadsheet has a worksheets feed listing all of its worksheets.

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

Retrieving information about worksheets

A set of metadata can be fetched about each worksheet, as well as information that enables further manipulation of data in the worksheet.

Protocol

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

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

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

The API returns a feed resembling the following. The word key in the following example is used only to make the sample more readable.

<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/worksheets/key/private/full</id>
  <updated>2006-11-17T18:23:45.173Z</updated>
  <title type="text">Groceries R Us</title>
  <link rel="alternate" type="text/html"
      href="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/worksheets/key/private/full"/>
  <link rel="self" type="application/atom+xml"
      href="https://spreadsheets.google.com/feeds/worksheets/key/private/full"/>
  <link rel="http://schemas.google.com/g/2005#post" type="application/atom+xml"
      href="https://spreadsheets.google.com/feeds/worksheets/key/private/full"/>
  <author>
    <name>Fitzwilliam Darcy</name>
    <email>fitz@gmail.com</email>
  </author>
  <openSearch:totalResults>1</openSearch:totalResults>
  <openSearch:startIndex>1</openSearch:startIndex>
  <openSearch:itemsPerPage>1</openSearch:itemsPerPage>
  <entry gd:etag='"YDwqeyI."'>
    <id>https://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId</id>
    <updated>2006-11-17T18:23:45.173Z</updated>
    <title type="text">Sheet1</title>
    <content type="text">Sheet1</content>
    <link rel="http://schemas.google.com/spreadsheets/2006#listfeed"
        type="application/atom+xml"
        href="https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
    <link rel="http://schemas.google.com/spreadsheets/2006#cellsfeed"
        type="application/atom+xml"
        href="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full"/>
    <link rel="self" type="application/atom+xml"
        href="https://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId"/>
    <link rel="edit" type="application/atom+xml"
        href="https://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId/version"/>
    <gs:rowCount>100</gs:rowCount>
    <gs:colCount>20</gs:colCount>
  </entry>
</feed>

Java

The following code fetches a list of worksheets for the first spreadsheet in a feed. It first prints out the title of the spreadsheet, and then prints a list of all worksheets in that spreadsheet. Each item in the list shows the title, number of rows, and number of columns of the worksheet.

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());

    // Make a request to the API to fetch information about all
    // worksheets in the spreadsheet.
    List<WorksheetEntry> worksheets = spreadsheet.getWorksheets();

    // Iterate through each worksheet in the spreadsheet.
    for (WorksheetEntry worksheet : worksheets) {
      // Get the worksheet's title, row count, and column count.
      String title = worksheet.getTitle().getPlainText();
      int rowCount = worksheet.getRowCount();
      int colCount = worksheet.getColCount();

      // Print the fetched information to the screen for this worksheet.
      System.out.println("\t" + title + "- rows:" + rowCount + " cols: " + colCount);
    }
  }
}

.NET

The following code fetches a list of worksheets for the first spreadsheet in a feed. It first prints out the title of the spreadsheet, and then prints a list of all worksheets in that spreadsheet. Each worksheet in the list shows the title, number of rows, and number of columns of the worksheet.

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);

      // Make a request to the API to fetch information about all
      // worksheets in the spreadsheet.
      WorksheetFeed wsFeed = spreadsheet.Worksheets;

      // Iterate through each worksheet in the spreadsheet.
      foreach (WorksheetEntry entry in wsFeed.Entries)
      {
        // Get the worksheet's title, row count, and column count.
        string title = entry.Title.Text;
        int rowCount = entry.Rows;
        int colCount = entry.Cols;

        // Print the fetched information to the screen for this worksheet.
        Console.WriteLine(title + "- rows:" + rowCount + " cols: " + colCount);
      }
    }
  }
}

Adding a worksheet

Worksheets can be created using the Spreadsheets API. To do this, an application must make a request to the API containing information about the new worksheet, like its title and initial size.

Protocol

To add a worksheet to the spreadsheet, authenticate and authorize, then simply make the following HTTP request to a spreadsheet's worksheet feed URL. You must be authenticated and authorized to add worksheets via the Spreadsheets API.

POST https://spreadsheets.google.com/feeds/worksheets/key/private/full
Content-Type: application/atom+xml

<entry xmlns="http://www.w3.org/2005/Atom"
    xmlns:gs="http://schemas.google.com/spreadsheets/2006">
  <title>Expenses</title>
  <gs:rowCount>50</gs:rowCount>
  <gs:colCount>10</gs:colCount>
</entry>

Java

The following code adds a new worksheet to the first spreadsheet returned from the API. The title of the created worksheet is New Worksheet. The new worksheet has 10 columns and 20 rows.

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());

    // Create a local representation of the new worksheet.
    WorksheetEntry worksheet = new WorksheetEntry();
    worksheet.setTitle(new PlainTextConstruct("New Worksheet"));
    worksheet.setColCount(10);
    worksheet.setRowCount(20);

    // Send the local representation of the worksheet to the API for
    // creation.  The URL to use here is the worksheet feed URL of our
    // spreadsheet.
    URL worksheetFeedUrl = spreadsheet.getWorksheetFeedUrl();
    service.insert(worksheetFeedUrl, worksheet);

  }
}

.NET

The following code adds a new worksheet to the first spreadsheet returned from the API. The title of the created worksheet is New Worksheet. The new worksheet has 10 columns and 20 rows.

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);

      // Create a local representation of the new worksheet.
      WorksheetEntry worksheet = new WorksheetEntry();
      worksheet.Title.Text = "New Worksheet";
      worksheet.Cols = 10;
      worksheet.Rows = 20;

      // Send the local representation of the worksheet to the API for
      // creation.  The URL to use here is the worksheet feed URL of our
      // spreadsheet.
      WorksheetFeed wsFeed = spreadsheet.Worksheets;
      service.Insert(wsFeed, worksheet);
    }
  }
}

The new worksheet will appear after the last worksheet in the Spreadsheets UI.

Modifying a worksheet's title and size

You can modify the title and size of a worksheet with the Spreadsheets API by sending a request to the API containing the new properties of the worksheet. If you need to modify the actual content of cells, see Adding a list row, or Changing contents of a cell.

Protocol

To change the title or size of a worksheet, begin by getting the desired worksheet from the worksheet feed. Then update the worksheet's metadata and send a PUT request with the desired entry contents to the URL provided in an edit link. The edit URL is highlighted in the XML below.

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

Java

The following code changes the title and dimensions of the first worksheet returned for the first spreadsheet in the spreadsheets feed. The title of the worksheet is changed to Updated Worksheet, the number of columns is set to 5, and the number of rows is set to 15.

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);

    // Update the local representation of the worksheet.
    worksheet.setTitle(new PlainTextConstruct("Updated Worksheet"));
    worksheet.setColCount(5);
    worksheet.setRowCount(15);

    // Send the local representation of the worksheet to the API for
    // modification.
    worksheet.update();
  }
}

.NET

The following code changes the title and dimensions of the first worksheet returned for the first spreadsheet in the spreadsheets feed. The title of the worksheet is changed to Updated Worksheet, the number of columns is set to 5, and the number of rows is set to 15.

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];

      // Update the local representation of the worksheet.
      worksheet.Title.Text = "Updated Worksheet";
      worksheet.Cols = 5;
      worksheet.Rows = 15;

      // Send the local representation of the worksheet to the API for
      // modification.
      worksheet.Update();
    }
  }
}

Deleting a worksheet

Worksheets can be deleted from a spreadsheet using the Spreadsheets API.

When a worksheet is deleted, all worksheets to the right of it in the Spreadsheets UI will be shifted left one position. The ID of the other worksheets will not change, however.

Protocol

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

DELETE https://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId/version

Java

The following code deletes 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);

    // Delete the worksheet via the API.
    worksheet.delete();
  }
}

.NET

The following code deletes 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];

      // Delete the worksheet via the API.
      worksheet.Delete();
    }
  }
}

Working 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 Working with cell-based feeds.

The following sections describe how to get a list-based feed of rows in a worksheet, and how to manage those rows. Also discussed is how to query for specific rows in a worksheet using advanced 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. 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.

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.

Retrieving 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.

Protocol

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>fitz@gmail.com</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 : feed.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);
        }
      }
    }
  }
}

Sorting 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.

Protocol

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.

Protocol

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

Java

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

.NET

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

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

Protocol

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

Java

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

.NET

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

Sending 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.

Protocol

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.

Adding 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.

Protocol

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.

Updating a list row

The Spreadsheets API allows rows to be manipulated in place.

Protocol

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 = feed.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();
    }
  }
}

Deleting a list row

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

Protocol

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 = feed.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();
    }
  }
}

Working 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 Spreadsheets. 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 Spreadsheets, 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 Spreadsheets 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 Spreadsheets API has no concept of formatting, and thus cannot manipulate formatting of cells.

Retrieving a cell-based feed

Protocol

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>fitz@gmail.com</email>
  </author>
  <openSearch:startIndex>1</openSearch:startIndex>
  <openSearch:itemsPerPage>36</openSearch:itemsPerPage>
  <gs:rowCount>100</gs:rowCount>
  <gs:colCount>20</gs:colCount>
  <entry 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 : feed.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);
      }
    }
  }
}

Fetching specific rows or columns

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

Protocol

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 : feed.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);
      }
    }
  }
}

Changing 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.

Protocol

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 Spreadsheets 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).

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 : feed.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).

Updating 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".

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.

Protocol

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

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

Then, append all the entries to be updated to the feed as per the XML shown below and set the batch:operation to update. Finally, post the feed to the cells feed edit link.

<feed xmlns="http://www.w3.org/2005/Atom"
      xmlns:batch="http://schemas.google.com/gdata/batch"
      xmlns:gs="http://schemas.google.com/spreadsheets/2006">
  <id>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. It uses ClientLogin for authentication and authorization.

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
 * Spreadsheets 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 --username [user] --password [pass] --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 username = parser.getValue("username", "user", "u");
    String password = parser.getValue("password", "pass", "p");
    String key = parser.getValue("key");
    boolean help = parser.containsKey("help", "h");

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

    long startTime = System.currentTimeMillis();

    // Prepare Spreadsheet Service
    SpreadsheetService ssSvc = new SpreadsheetService("Batch Cell Demo");
    ssSvc.setUserCredentials(username, password);

    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) {
      URL entryUrl = new URL(cellFeedUrl.toString() + "/" + cellAddr.idString);
      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. It uses ClientLogin for authentication and authorization.

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
 * Spreadsheets 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 username;
      string password;
      string key;

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

      // Prepare Spreadsheet Service
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
      service.setUserCredentials(username, password);

      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;
    }
  }
}

Autenticazione richiesta

Devi aver eseguito l'accesso a Google+ per svolgere questa operazione.

Accesso tra...

Google Developers richiede la tua autorizzazione per svolgere questa operazione.