Contents

Audience

This document is intended for Apex Developers who want to write Force.com Apps that can interact with Google's Spreadsheets Data API.

This document assumes that you understand the general ideas behind the Google Data APIs protocol.

For additional Google Spreadsheets Data API reference information, see the Google Spreadsheets Data API Reference Guide.

Getting started

Set up your Force.com Development environment.

The Force.com platform is the most scalable, secure and popular on-demand platform in the world today. You can develop and == instantaneously deploy applications without any infrastructure - 100% on-demand

Install the latest version of the Force.com IDE

The Force.com IDE allows developers a == full-featured, Eclipse-based coding environment, with capabilities like code completion, version control,collaborative development, and project sharing.

Create a Google Account

Google Contacts are associated with Google Accounts, so a Google Account will be required in order to proceed. To view your contacts without using the Contacts Data API, you can log in to Gmail and click the Contacts link.

Check out the source code

Check out the Force.com Toolkit for Google Data APIs from the Google repository.

Apex Code Examples

The following examples show how to send Google Spreadsheets Data API requests from Apex Code.

Authenticating to the Spreadsheets service

Create the Apex object that will handle the connection to Google Spreadsheets Data API:

SpreadsheetService service = new SpreadsheetService();  
service.setAuthSubToken(sessionAuthToken);  

Get a list of spreadsheets

Get a feed containing a list of the currently authenticated user's spreadsheets and dump. The result is a "metafeed," a feed that lists all of that user's spreadsheets; each entry in the feed represents a spreadsheet associated with the user.

GoogleData sheets = service.getSpreadsheets(); 
sheets.dump();

Interacting with worksheet-based feeds

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

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

Get a worksheet-based feed

Given a Spreadsheet feed you've already retrieved, you can print a list of all worksheets in this spreadsheet by simply calling the getWorksheets method and displaying the results. Each worksheet is an entry in the feed, and can be accessed using the entries array on the worksheetFeed object, as shown below.

GoogleData feed = service.getSpreadsheetsTitle('account');
        
xmldom.element oneSpreadSheet = feed.entries[0]; // expects one spreadsheet
 		
// get a new feed listing the worksheets in oneSpreadSheet
GoogleData worksheetsFeed = service.getWorksheets( oneSpreadSheet );

worksheetsFeed.dump(); 	// each worksheet is an entry

for ( xmldom.element ws: worksheetsFeed.entries ) { 
	system.debug ( GoogleData.getTitle( ws ) );	// title of each sheet
}

Add a worksheet

To add a worksheet to the spreadsheet, start by creating an xmldom.element object containing the relevant data, then use your SpreadsheetService object to send an insertion request. This method requires passing the spreadsheet to add the worksheet to, and the new worksheet object to add.

/*<entry>
<title>Expenses</title>
<gs:rowCount>50</gs:rowCount> 
<gs:colCount>10</gs:colCount>
</entry> */
xmldom.element wrkSheet = new xmldom.element('entry');
xmldom.element title = new xmldom.element('title') ;		
title.nodeValue = 'one more wk sheet';
wrkSheet.appendChild( title );
xmldom.element rc = new xmldom.element('gs:rowCount');
rc.nodeValue = '50';
wrkSheet.appendChild( rc); 	
xmldom.element cc = new xmldom.element('gs:colCount');
cc.nodeValue = '10';
wrkSheet.appendChild( cc); 	
		
GoogleData.Worksheet addedWorkSheet = new GoogleData.Worksheet( wrkSheet);
	
service.insertWorksheet( oneSpreadSheet , addedWorkSheet );

Update the metadata of a worksheet

To change the metadata of a worksheet, begin by getting the desired worksheet from the worksheet feed. Then update the worksheet's metadata and use the update method to send the new metadata to the server.

// get a new feed listing the worksheets in oneSpreadSheet
GoogleData worksheetsFeed = service.getWorksheets( oneSpreadSheet );
   		
// pick the first one to rename
GoogleData.Worksheet ws = new GoogleData.Worksheet( worksheetsFeed.entries[0] );
ws.title = 'new sheet name';   
service.updateWorksheet(ws);  // send the update

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

Remove a worksheet

To delete a worksheet, use its delete method after obtaining it as described above.

service.removeWorksheet( workSheet );

Interacting with list-based feeds

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

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

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

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

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

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

Get a list-based feed

To retrieve a worksheet's list feed, you might do the following. This assumes that service is an authenticated SpreadsheetService object, and one spreadsheet (xmldom.element) called oneSpreadsheet.

// get a new feed listing the worksheets in oneSpreadSheet
GoogleData worksheetsFeed = service.getWorksheets( oneSpreadSheet );
   		
// pick the first worksheet to list (quite arbitrary) 
GoogleData.Worksheet wrkSheet = 
new GoogleData.Worksheet( worksheetsFeed.entries[0] );

// query and dump the list feed
GoogleData listFeed = service.getFeed( wrkSheet.listFeedUrl );
listFeed.dump(); 

The SpreadsheetService returns a GoogleData list feed, which contains a list of all the rows in this worksheet. Each row is represented as a entry in the feed.

To retrieve the contents of a single row, iterate over the feed's entries property, where each element in the collection represents a row in that list.

Reverse-sort rows

By default, rows in the feed appear in the same order as the corresponding position in the UI; that is, they're in order by row number. To get rows in reverse order, use the reverse=true when fetching the feed:

GoogleData listFeed = service.getFeed( wrkSheet.listFeedUrl + '?reverse=true' );
listFeed.dump(); 

Note that if you want to order (or reverse sort) by a particular column, rather than by position in the worksheet, you can pass the name of the header for the column to the feed query

Send a structured query

Using the above method of appending query arguments, you can specify any parameters found in the Google Spreadsheets reference materials to produce a feed with entries that meet the specified criteria. For more information about the structured query syntax, see the Google List-feed query parameters reference section.

Add a row

To insert a new row in a list-based feed, first construct a new entry element and add the contents of each cell as using GoogleData.makeElement. Then insert the new row in the ListFeed as follows:

GoogleData listFeed = service.getFeed( wrkSheet.listFeedUrl );
  	
// construct a new row
/*  
gsx:contactid->003T0000005liEWIAY {}
gsx:firstname->Bob {}
gsx:lastname->Dubinina {}
*/
xmldom.element newrow = new xmldom.element('entry');
newrow.appendChild( 
	GoogleData.makeElement( 'gsx:contactid', '003T0000005liEWIAY' ));
		newrow.appendChild( 
		GoogleData.makeElement( 'gsx:firstname', 'Bob' ));
		newrow.appendChild( 
		GoogleData.makeElement( 'gsx:lastname', 'Dubinina' ));
		
// add the row to the worksheet
service.insertRow( listFeed.feed, newrow);

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

Edit a row

To change the contents of a row, first fetch the listfeed, then get the row you wish to update. Now change the node value of the element that will be updated, then call the service method updateRow passing the row to be updated.

GoogleData listFeed = service.getFeed( wrkSheet.listFeedUrl );

// pick one row (last row)
xmldom.element onerow = listFeed.entries[listfeed.entries.size()-1];
onerow.dumpAll();
// change Bob to Robert
onerow.getElementByTagName('gsx:firstname').nodeValue = 'Robert';
 	
service.updateRow( onerow ); 

Remove a row

To delete a row, simply invoke the removeRow method on the service object, passing corresponding row element:

service.removeRow( onerow ); 

Interacting with cell-based feeds

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

Note it is not recommended to interact with both a cell-based feed and a list-based feed for the same worksheet at the same time.

Get a cell-based feed

To retrieve a worksheet's cell-based feed, given a service object and a worksheet object, you might do the following:

string cellFeedUrl = worksheet.getCellFeedUrl();
// specify range to avoid data overflow
GoogleData feed = service.getFeed( cellFeedUrl );  
 
list<GoogleData.Cell> cells = ws.cellFactory( feed.entries );                  
for (GoogleData.Cell cel : cells  ) {
            cel.dump(); 
            system.debug ( cel.title + ' ' + cel.content );  
            system.debug ( cel.id + ' ' +cel.row + ' ' + cel.col + ' ' + cel.edit ); 
}

The cellFactory method on a worksheet returns a GoogleData.Cell list, which contains a list of all the cells in this worksheet. Each entry in the feed is a single cell.

NOTE: large sheets may exceed the size that can be retrieved using an Apex callout, if you encounter this see the range query below.

You can easily access a cell's content or information about the cell, such as the row or column number, using the various properties provided by the GoogleData.Cell class, some of which are shown here.

Send a cell range query

Suppose you only want to retrieve the cells within a given range of rows and columns of a worksheet. You can request a cell feed containing only these cells by using getFeedRange method. This method is important for sheets that are large as you may encounter the maximum data quantity that Apex can support in a callout.

string cellFeedUrl = worksheet.getCellFeedUrl();
// specify range to avoid data overflow
GoogleData feed = service.getFeedRange( cellFeedUrl ,'A1:C12');  

list<GoogleData.Cell> cells = ws.cellFactory( feed.entries );                  
for (GoogleData.Cell cel : cells  ) {
     cel.dump(); 
     system.debug ( cel.title + ' ' + cel.content );  
     system.debug ( cel.id + ' ' +cel.row + ' ' + cel.col + ' ' + cel.edit ); 
}

This requests all the data in rows from the cell A1 to C12, other range formats are supported, see Google Data reference.

Change contents of a cell

To modify the contents of an existing GoogleData.Cell that you have retrieved from a feed, first change its content property. Then execute the update request as follows, note you must pass in the worksheet:

existingCellEntry.content = 'formulaOrValue'; 
service.updateCell ( wrkSheet, existingCellEntry );  

Change the content of multiple cells with a batch request

Suppose you want to update a column, row, or other set of cells. You could simply use a for loop and update the cells one-by-one as described in the previous section, or you could minimize the number of requests by batching the updates together

// change all cells in this feed to contain 'new value'
list<GoogleData.Cell> cells = wrkSheet.cellFactory(feed.entries); 
for (GoogleData.Cell cel : cells  ) {
    cel.content = 'new value';
    }
service.updateCells ( wrkSheet, cells ); 

Here we first take the cells feed of a worksheet and create a list of Cells, then we update each cell to have the value "new value". This list is then passed to the updateCells method to perform the data modification in a batch. You can add many updates or queries into a single batch transaction.

More information about batch operations can be found on the Google Batch Processing page.

Related Content