REST for the Traveling Salesman: Using Google Data on Salesforce.com

Lane LiaBraaten, Google Developer Programs
November 2007

Introduction

Salesforce.com and Google host popular "Software as a Service" applications and both organizations provide APIs that allow developers to access the large stores of data that power these applications-and things start to get interesting when we combine both sets of APIs. G Suite continues to be used more and more in the enterprise setting and Salesforce.com has built an extensive platform for building custom business applications, so there are many opportunities for developers (that's you!) to combine the power of Google and Salesforce.com.

This article will show you how to use the RESTful Google Data APIs to start building enterprise mashups, even if you're just starting out with the Salesforce.com platform or the Google Data protocol. I'll help you set up your development environment, install an existing Salesforce.com and Google mashup, and then write your own.

Hello, Salesforce.com!

If you're a Salesforce.com newbie like I was, you'll need to start by signing up for a developer account on ADN, the Apex Developer Network. The developer account gives you a full-featured Salesforce.com account, plus access to the Apex wiki and discussion boards.

Next you'll want to get the Apex Toolkit for Eclipse. The toolkit requires Java 1.5 and Eclipse 3.2.2, or better. If you know your way around Eclipse, the software update site for the toolkit is http://www.adnsandbox.com/eclipsetoolkit/10.0/. If you're not familiar with Eclipse plugins, or something goes awry during your installation, the Apex wiki includes detailed installation instructions.

Once the toolkit is installed, you can access the Apex help content that is integrated into Eclipse's Help system. In Eclipse, go to Help | Help Contents | Apex Toolkit for Eclipse to see this content. One of the help resources is a quickstart tutorial that will show you how to create a new project and add S-Controls, classes, and triggers. If you haven't worked with Apex code before, you should walk through this tutorial and create an Apex project before moving on.

Exporting events to Google Calendar

Ron Hess wrote a Google Calendar Mash-up application that lets you export events from Salesforce.com to Google Calendar. Ron also wrote an article that explains how his mashup works. The Google Spreadsheets application that I'll show you how to build later is modeled after Ron's Google Calendar mash-up. Thanks Ron!

If you're a Salesforce.com power user, then you can probably integrate and use Ron's Google Calendar application without any instructions. I needed a little help at first, so here's what I did to see Ron's app in action.

  1. Install the application:
    • Go to the Google Calendar Mash-up page and click Get It Now.
    • Enter your ADN credentials and click Continue.
    • Read the Terms & Conditions and click Continue.
    • Click Next through the "Examine package contents" page.
    • Choose a security level and click Next.
    • Click Install.
  2. Configure the Salesforce.com AJAX proxy
    • In the "Administration Setup" menu, click Security Controls | Remote Site Settings.
    • Click New Remote Site.
    • Enter Google as the "Remote Site Name" and for the "Remote Site URL" use https://www.google.com.
    • Click Save.
  3. Add the "Add to Google" button to the Event Detail page:
    • In the "App Setup" menu, click Customize | Activities | Event Page Layouts.
    • Click Edit in the "Event Layout" row.
    • Double click the "Detail Page Buttons" area.
    • Highlight "Add to Google" and click the right (>) arrow to add the button.
    • Click OK.
    • Click Save on the "Page Layout Properties" page.
  4. Export an event
    • Click Home in the upper left, to view your calendar.
    • If you don't have any events, click New Event to create one.
    • Click on an event to view the Event Detail page.
    • Click the Add to Google button.
    • Click OK through the JavaScript alert
    • Sign in with your Google username and password.
    • Click Grant access to give the SalesForce application write access to your Google Calendar.
    • View the event in your Google Calendar

Building a Google Spreadsheets application

Okay, you're probably tired of clicking around the Salesforce.com pages and ready to write some code. Fire up Eclipse again and you'll see that the Apex project you created now contains the S-Controls for Ron's Google Calendar app. This is because the Apex Toolkit for Eclipse is constantly synchronizing with Salesforce.com-pretty cool, huh?

You can build your own Google Data app reusing some of the functionality of the Google Calendar Mash-up, such as the authentication controls. In the rest of this section, I'll show you how to build an app that exports your Salesforce.com contacts into a Google Spreadsheet.

Publishing a simple S-Control

An S-Control is a file that is hosted by Salesforce.com and executed in web browsers when users access your application. An S-Control can contain any type of content that you can display or run in a web browser, such as HTML, CSS, or JavaScript.

There are quite a few moving parts in a Salesforce.com and Google mashup, so the first thing I did was add an "Export to Google" button to the contacts list page that invokes a simple S-Control-just to make sure all the plumbing was correct before I was knee deep in JavaScript.

In your Apex project, right-click the "S-Controls" folder and choose Apex | New S-Control. Give the new S-Control a label and name of export_contacts, leave the type as Custom HTML and click Finish.

The new S-Control will contain a skeleton HTML file. You'll be adding a bunch of JavaScript in the <head>, but you can fill in the <body> first, just so there's something to show the user while the contacts are being exported. Copy this HTML into the body of your S-Control to show the "waiting dots" and a Google Spreadsheets logo:

<div id="waiting" align="center" />
  <img src="/img/icon/home32.png" height="64" width="64" />
  <img src="/img/waiting_dots.gif" alt="Please wait..." title="Please wait..." height="25" width="196" />
  <img src="http://docs.google.com/images/doclist/logo_docs.gif" />
  <p><h3>Exporting contacts to Google Spreadsheets, please wait...</h3></p>

</div>

Now you can publish the S-Control and set up a button that will invoke it.

  1. Use Eclipse to publish the S-Control file:
    • Right-click your Apex project and select Apex | Synchronize with Salesforce.
    • Find your new S-Control in the directory tree, right-click it and choose Override remote and publish to server.
    • If you can't find your S-Control, the Apex Toolkit may have already uploaded it for you, but it's a good idea to use the synchronize perspective to ensure that the latest code is running on the server.
  2. Use the Salesforce.com UI to define a button that will invoke this S-Control:
    • In the "App Setup" menu, click Customize | Contacts | Buttons and Links.
    • In the "Custom Buttons and Links" section, click New.
    • Enter Export to Google for the label, and keep Export_to_Google as the name.
    • Choose "List Button" as the Display Type.
    • Choose "Custom S-Control" as the Content Source.
    • Choose "Display in existing window with sidebar" as the Behavior.
    • Select "export_contacts" from the Custom S-Controls menu.
  3. Add the button to the list of contacts:
    • In the "App Setup" menu, click Customize | Contacts | Search Layouts.
    • Click Edit in the "Contacts List View" row.
    • Highlight "Export to Google" and click the right (>) arrow to add the button.
    • Click Save.
  4. Take it for a test drive:
    • Click the Contacts tab.
    • Select "All Contacts" as the View and click Go!.
    • Click your shiny new Export to Google button.
    • Watch the "waiting_dots" but don't expect anything else to happen.

Interacting with Google Spreadsheets

If you look at the source for the Google Calendar Mash-up, you'll see that the gcal_snippet.scf file contains an abstraction for a Google Calendar server. To interact with Google Spreadsheets, you'll need to create a similar file for a Google Spreadsheets server. I reused Ron Hess's code for using the Salesforce.com AJAX proxy and authenticating with Google AuthSub and replaced the function that writes events to Google Calendar with one that writes information to Google Spreadsheets. The complete source for this file is available in gspreadsheet_snippet.scf.

Next, I added JavaScript to the export_contacts.scf S-Control to query Salesforce.com for contact information and write it to a Google Spreadsheet. Getting data out of Salesforce.com is easy. Just construct a query and provide a callback function to execute when the data is returned. For example:

  var query = 'Select FirstName, LastName, Phone From Contact';
  var queryResult = sforce.connection.query(query, queryCallback);

Once you have the contact information from Salesforce.com you have to figure out where to export it. In the RESTful Google Data protocol, each spreadsheet can be identified by a unique URL. You can get the list of a user's spreadsheets (and the associated URLs) by querying the metafeed URL: http://spreadsheets.google.com/feeds/spreadsheets/private/full. The following method iterates through these spreadsheets, looking for a spreadsheet with a specific title. When it finds the correct spreadsheet, it first gets the list of worksheets, then returns the cells feed URL for the first worksheet.

function getCellFeedUrl() {
  var SPREADSHEET_TITLE = 'Salesforce.com Contacts';
  var WORKSHEET_REL = 'http://schemas.google.com/spreadsheets/2006#worksheetsfeed';
  var CELLSFEED_REL = 'http://schemas.google.com/spreadsheets/2006#cellsfeed';

  // Query to find the spreadheet called "Salesforce.com Contacts"
  var spreadsheets = g.getFeed('http://spreadsheets.google.com/feeds/spreadsheets/private/full');
  var entries = g.getEntryList(spreadsheets);
  for (var e in entries) {
    if (g.getTitle(entries[e]) == SPREADSHEET_TITLE) {
      var worksheetsFeedUrl = g.link(entries[e],WORKSHEET_REL);
      var worksheets = g.getFeed(worksheetsFeedUrl);
      var worksheetEntries = g.getEntryList(worksheets);
      return g.link(worksheetEntries[0], CELLSFEED_REL);
    }
  }
}

More information on the feeds available in the Google Spreadsheets data API can be found in the Reference Guide.

The queryCallback function uses the getCellFeedUrl method to find the cells feed URL needed to send requests for updating cells, then writes the contact information a cell at a time.

function queryCallback(queryResult) {
  var cellFeedUrl = getCellFeedUrl();
  var contacts = queryResult.getArray('records');
  for (var i=0; i<contacts.length; i++) {
    g.updateCell(cellFeedUrl, i+1, 1, contacts[i].LastName + ", " + contacts[i].FirstName);
    g.updateCell(cellFeedUrl, i+1, 2, contacts[i].Phone);
  }
  
  jumpback(); // Return to the contacts page when your done
}

The updateCell method exists in the gspreadsheet_snippet.scf S-Control. The method gets the edit URL of the cell in the given row and column, then sends an HTTP PUT message that contains the Google Data representation of the updated cell:

GoogleSpreadsheet.prototype.updateCell = function(cellsFeedUrl, row, column, content ) {
  var cellEntry = this.getFeed(cellsFeedUrl+'/R'+row+'C'+column);
  var cellEditUrl = this.link(cellEntry,'edit');

  var newCellEntry = "<atom:entry xmlns:atom='http://www.w3.org/2005/Atom'>" +
      "<atom:category scheme='http://schemas.google.com/spreadsheets/2006' " +
      "term='http://schemas.google.com/spreadsheets/2006#cell' />" +
      "<gs:cell xmlns:gs='http://schemas.google.com/spreadsheets/2006' " +
      "row='" + row + "' col='" + column + "' inputValue='" + content + "' />" +
      "</atom:entry>";

  this.putEntry(cellEditUrl, newCellEntry);
}

More information on how to update cells with the Google Spreadsheets data API can be found in the Developer's Guide.

The Google Spreadsheets data API uses the subdomain https://spreadsheets.google.com so before the above code will work, you'll need to configure the Salesforce.com AJAX proxy server and add https://spreadsheets.google.com.

You'll also need to go to Google Docs and create a new spreadsheet to hold the data. Make sure you save it as Salesforce.com Contacts.

Once you upload these S-Controls, you'll be able to export your contact information to a Google Spreadsheet. What about the other way? With your new knowledge of Salesforce.com and Google Data APIs you can also write some code to import contact information from Google Spreadsheets into Salesforce.com.

Conclusion

This article has barely scratched the surface, but now that you're up to speed on the Salesforce.com platform and the Google Data APIs, think of all the applications you can write to leverage these powerful systems. The Google Data family of APIs is always growing, exposing more information for you to leverage in your applications, and the Salesforce.com platform offers many useful tools that weren't covered in this article. I'll be looking for your next enterprise mashup in The AppExchange.

Happy coding!

Resources