Editor's Note: Ferris Argyle is going to present Salesforce Workflow Automation with Google Spreadsheet and Apps Script at Cloudforce. Do not miss Ferris's talk - Saurabh Gupta

As part of Google's Real Estate and Workplace Services (REWS) Green Team, the Healthy Materials program is charged with ensuring Google has the healthiest workplaces possible. We collect and review information for thousands of building materials to make sure that our offices are free of formaldehyde, heavy metals, PBDEs and other toxins that threaten human health and reduce our productivity.

A Case for using Google Docs and Salesforce.com

My team, as you might imagine, has a great deal of data to collect and manage. We recently implemented Salesforce.com to manage that data, as it can record attributes of an object in a dynamic way, is good at tracking correspondence activity and allows for robust reports on the data, among many other functions.

We needed Saleforce.com to integrate with our processes in Google Apps. We wanted to continue collecting data using a Google Docs form but needed it integrated with Salesforce.com because we:

1. Liked the way the form looked and functioned
2. Wanted to retain continuity for our users, including keeping the same URL
3. Wanted a backup of submissions

And this is where Google Apps Script came to our rescue. We found that we could use Google Apps Script to create a new Case or Lead in Salesforce.com when a form is submitted through our Google Docs form. This allowed us to continue using our existing form and get our data directly and automatically into Salesforce.com.

Google Docs + Apps Script + Salesforce.com = Integrated Goodness!

Salesforce.com has two built-in options for capturing data online - Cases and Leads. Google Docs Forms can capture data for both of them. Set up your Case or Lead object with the desired fields in Salesforce.com. The next step is to generate the HTML for a form. You will use the IDs in the Salesforce.com-generated HTML when writing your Google Apps script.

A) Getting the HTML in Salesforce.com:

1. Login to Salesforce.com and go to Your Name > Setup > Customize > Leads or Self-Service (for Cases) > Web-to-Lead or Web-to-Case

2. Make sure Web-to-Lead/Web-to-Case is enabled. Click on Edit (Leads) or Modify (Cases) and enable if it is not.

3. Click on the 'Create Web to Lead Form' button (for Leads) or the 'Generate the HTML' link (for Cases)

4. Select the fields you want to capture and click 'Generate'. Save the HTML in a text file. You can leave 'Return URL' blank

B) Setting up Google Apps Form/Spreadsheet:

Create your form and spreadsheet (or open up the one you already have and want to keep using). This is very easy to do. Go to your Docs and click on 'Create' to open a new form. Use the form editor to add the desired fields to your form- they'll show up as column headings in the corresponding spreadsheet. When someone fills out your form, their answers will show up in the right columns under those headings.

C) Writing the Google Apps Script:

The script is set up to take the data in specified cells from the form/spreadsheet and send it into designated fields in your Salesforce.com instance (identified by the org id in the HTML generated above). For example, the form submitter's email is recorded through the form in one cell, and sent into the email field in either the Lead or Case object in Salesforce.com.

1. Create a new script (Tools > Script Manager > New).

2. Write the script below using the pertinent information from your Salesforce.com-generated code (shown further down).

function SendtoSalesforce() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = sheet.getLastRow();
  var firstname = sheet.getRange(row, 2).getValue();
  var lastname = sheet.getRange(row, 3).getValue();
  var email = sheet.getRange(row, 4).getValue();
  var company = sheet.getRange(row, 5).getValue();
  var custom = sheet.getRange(row, 6).getValue();
  var resp = UrlFetchApp
      .fetch(
          'https://www.salesforce.com/servlet/servlet.WebToLead?encoding=UTF-8',
          {
            method: 'post',
            payload: {
              'orgid' : '00XXXXXXXX',
              'first_name' : firstname,
              'last_name' : lastname,
              'email' : email,
              'company' : company,
              '00YYYYYYYY' : custom,
              'external' : '1'
            }
          });
  Logger.log(resp.getContentText());
}

Define your variables by directing the script to the correct cell (row, column number). Then in the payload section, match the field id from your Salesforce.com HTML (red) to the variable you defined (blue). For example, the email address of the submitter is defined as variable 'email', can be found in the 4th column of the last row submitted, and the id for that field in Salesforce.com is 'email'.

Note that any custom fields you've created will have an alpha-numeric id.

3. Save your script and do a test run.

D) Wiring Script to a Form Submission.

To send your data automatically into Salesforce.com, you need to set a trigger that will run the script every time a form is submitted. To do this, go to your script and click Resources>Current script's triggers.

1. Create a Trigger for your function so that it runs when a form is submitted.

2. Post the link to your form on your website, send it in an email, link to it on G+, etc. Get it out there!

That's it! Now when someone submits a form, the information will come into your spreadsheet, and then immediately be sent into Salesforce.com. You can adjust your Salesforce.com settings to create tasks when the information comes in, send out an auto-response to the person filling out the form and set up rules for who is assigned as owner to the record. You'll also have the information backed up in your spreadsheet.

This has been a great solution for our team, and we hope others find it useful as well!

Beth Sturgeon   Beth Sturgeon is a member of Google's Green Team in Mountain View, which makes sure that Google's offices are the healthiest, most sustainable workplaces around. Prior to Google, she had a past life as a wildlife researcher.

Editor’s note: This is a guest post by Thomas Coudray, Amaury de la Vieuville, and Ahmed Bougacha. Thomas, Amaury, and Ahmed attended the Google Apps Script Hackathon in Paris, and in this post they are sharing their creative use of Google Apps Script to render a 3D scene in a Google Spreadsheet. -- Jan Kleinert

Recently, we heard about the Google Apps Script Hackathon arriving in Paris, France. We did not know much about Apps Script - heck, even JavaScript! Perfect occasion to learn something. We spent most of the event hacking around with the ever-growing collection of Google APIs. As a tribute to the folks over at id Software, we settled on one of the most fun (however useless) ways to use it: rendering a 3D scene in a spreadsheet.

The rendering is done using a technique called ray-casting, made popular by the 90s id Software game Wolfenstein 3D. Ray-casting is a really brilliant and straightforward algorithm:

First, we render the background: color the upper (sky) and lower (floor) halves of the screen in different colors. We store the pixel colors in a matrix, the screen buffer:

screen = new Array(SIZE_Y);
for (var lin = 0; lin < SIZE_Y; lin++) {
  screen[lin] = new Array(SIZE_X);
  for (var col = 0; col < SIZE_X; col++) {
    screen[lin][col] = colorToString((lin < MID) ? UPPER_BG_COLOR
                                                 : LOWER_BG_COLOR);
  }
}

Note that we draw the screen only once the buffer is fully colored, to avoid the overhead of coloring cells individually.

Then for each column of the screen:

1. Cast a ray
2. Move along the ray until hitting a wall, calculate the distance to that wall
3. Draw a column whose height is inversely proportional to that distance

The trick is in the drawing: the upper and lower halves of the screen are symmetrical in shape, and the only computed value is the display height of the wall. The screen really is just a fancy formatting for an integer array of columns.

The camera is represented using:

• Its (real-valued) x/y coordinates in the map plane
• Its angle relative to some predefined direction

We store these 3 values at the bottom of the sheet, to ensure persistence (else, each refresh would bring us back to the start location!).

function Camera() {
  this.x = CAMERA_X;
  this.y = CAMERA_Y;
  this.theta = CAMERA_THETA;
  
  this.saveToSheet = function(sheet) {
    // The player state has to be saved between each frame
    sheet.getRange(STORE_LIN, 1, 1, 1).setValue(this.x);
    sheet.getRange(STORE_LIN, 2, 1, 1).setValue(this.y);
    sheet.getRange(STORE_LIN, 3, 1, 1).setValue(this.theta);
  };
  
  this.readFromSheet = function(sheet) {
    this.x = sheet.getRange(STORE_LIN, 1, 1, 1).getValue();
    this.y = sheet.getRange(STORE_LIN, 2, 1, 1).getValue();
    this.theta = sheet.getRange(STORE_LIN, 3, 1, 1).getValue();
  };

  ...
}

The map is a logical matrix, thus limiting us to discrete boxes for walls: for every cell, there either is (1), or is not (0), a wall:

// starting 10x10 map
var S = 10;
var map =
  [
    [1, 1, 1, 1, 1, 1, 1, 1, 1, 1],
    [1, 0, 1, 0, 0, 0, 0, 0, 0, 1],
    [1, 0, 1, 0, 1, 0, 1, 1, 0, 1],
    [1, 0, 0, 0, 0, 0, 0, 0, 0, 1],
    [1, 1, 1, 0, 1, 1, 0, 1, 0, 1],
    [1, 0, 0, 0, 1, 0, 0, 1, 0, 1],
    [1, 0, 0, 0, 1, 0, 0, 1, 0, 1],
    [1, 0, 1, 1, 1, 1, 0, 1, 0, 1],
    [1, 0, 0, 0, 0, 0, 0, 0, 0, 1],
    [1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
  ];

It is also possible to modify the map in real-time: write a character in the boxes you want to swap, then hit Refresh map.

Moving involves adding (or subtracting for backwards movements) to the xy coordinates, using basic trigonometry, but only after checking the validity of the move (i.e. that it will not collide with a wall):

function Camera() {
  ...

  this.move = function(distance) {
    // return whether valid move or not
    x = this.x + Math.cos(this.theta) * distance;
    y = this.y + Math.sin(this.theta) * distance;
    if (isValidPos(x, y)) {
      this.x = x;
      this.y = y;
      return true;
    }
    return false;
  };
}

function moveUp() {
  readMapFromSheet(sheet);     // Retrieve the map from the sheet
  var camera = new Camera();
  camera.readFromSheet(sheet); // Retrieve the camera state from the sheet
  camera.move(0.5);
  raycast(camera);
}

Turning left (respectively right) is even simpler, adding (respectively subtracting) small constants to the camera angle (mod 2 PI):

function Camera() {
  ...

  this.rotate = function(alpha) {
    this.theta = (this.theta + alpha + 2 * Math.PI) % (2 * Math.PI);
  };
}

function lookRight() {
  readMapFromSheet(sheet);
  var camera = new Camera();
  camera.readFromSheet(sheet);
  camera.rotate(-0.25);
  raycast(camera);
}

Actual actions (moving/turning) are shown in a menu:

spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var subMenus = [
      {name:"Reset",functionName:"onOpen"},
      {name:"Refresh map",functionName:"refresh"},
      {name:"Move forward",functionName:"up"},
      {name:"Look left",functionName:"left"},
      {name:"Look right",functionName:"right"},
      {name:"Move backward",functionName:"down"},
      {name:"Turn around",functionName:"turn"},
  ];
  spreadsheet.addMenu("Sheetcaster", subMenus);

The ray is cast as follows:

• Its origin is the camera's 2D coordinates in the map plane
• Its direction is calculated off the camera's and the column index (the center column will have the exact same direction as the camera; the other columns' directions depend on the field of view parameter)

/*
 * Given a value on the x axis (screen column),
 * return the ray that will be cast
 */
function getRay(camera, x) {
  var cos = Math.cos(camera.theta);
  var sin = Math.sin(camera.theta);

  // from -1 to 1: 0 being when x is the middle column
  var k = ((SIZE_X / 2) - x) / SIZE_X; 

  return new Vector_(
    cos / 2 - k * sin * K_FOV,
    sin / 2 + k * cos * K_FOV
  );
}

Moving the ray is the most involved step:

• Calculate the distance to the next vertical and horizontal borders
• Move to the closest border

while (!hit) {
  // Next potential wall is on the x axis
  if (dist.x < dist.y) { 
    // Distance from the camera, delta: 
    /  Distance between each horizontal wall along the ray  
    dist.x      += delta.x; 
    // step.x is either 1 or -1, depending on the ray direction
    mapCoord.x  += step.x;  
    hit = readMap_(mapCoord.x, mapCoord.y);
  } else { // Next potential wall is on the y axis                 
    dist.y     += delta.y;
    mapCoord.y += step.y;
    hit = readMap_(mapCoord.x, mapCoord.y);
  }
}

The height of the drawn column is nothing fancy: the further the wall, the smaller-looking the wall, hence the smaller the height of the column.

Again, nothing really complicated. However, the simplicity of this wall-height technique is the reason behind its major caveat: there is no clean way to look up or down: you can only turn left or right, and move forward or backward.

Displaying the rendered image is done using a spreadsheet. Each cell becomes a small square pixel, its color being the background color of the cell. We pass our scren buffer matrix to the handy setBackgroundColors:

sheet.getRange(1, 1, SIZE_Y, SIZE_X).setBackgroundColors(screen);

As you probably noticed, the low display density makes the sharp, jagged, edges really visible. Fear not, reader, for we also implemented anti-aliasing!

The anti-aliasing algorithm is even simpler:

1. Accumulate the length of runs (successions of same-sized columns)
2. Draw a gradient, from the background (wall and floor) to the wall, above (and below) the columns

When the runs are really small (< 5 columns), we attenuate the gradient intensity, as it would only add another pixel above (below) the column, thus rendering the antialiasing utterly useless.

Real-time was not an objective, the main problem being controlling the player/camera. Scripted movements should however be quite easy to implement with a fixed duration loop, restarting itself using an Apps Script recurrent time-driven trigger (a minute-long loop, repeated every minute). This is left as an exercise to the reader.

Please feel free to copy the script and walk around this Apps Script virtual world.

Thomas Coudray Thomas is interested in low level computing and application security.

Amaury de la Vieuville Amaury is passionate about algorithmic problem-solving and software engineering.

Ahmed Bougacha Ahmed is interested in kernels, compilers and theoretical computer science.

As a consultant helping companies move to the Google cloud, I receive many feature requests before, during, and after each migration. Often I’m asked about re-creating small and specific solutions that support particular business needs not fully covered by Google Apps out of the box. In many cases, a simple Google Apps Script solution satisfies the business requirement.

What is the Google Spreadsheet User Directory?

The “Google Spreadsheet User Directory” is a solution I’m frequently asked about. Google Apps Domain administrators can use a simple Apps Script that can be saved into a Google Spreadsheet and then set to run on a schedule, via a “time-driven” trigger. By using the Google Profiles API (available only for domain administrators), domain administrators can create a Google Spreadsheet which contains Google Apps domain user information.The user profile data can then be consumed and used by other business logic code, either in the spreadsheet itself or elsewhere.
Using Apps Script to provide this kind of solution was an obvious choice for the following reasons.

1. Apps Script makes the Google Spreadsheet User Directory a simple, flexible solution that the customer can quickly understand and extend. The JavaScript syntax is easy to learn and program in, and there is no need to compile and deploy code.
2. The Apps Script code is conveniently integrated into Google Spreadsheets, so there is no need to use any other software. Advanced functions can be exposed to end users for data manipulation through the spreadsheet menu, and scheduling an Apps Script to run at a regular interval is trivial via the Spreadsheet “Triggers” mechanism.
3. Google Apps Script provides services for accessing Google Profiles, Contact Info, and Google Groups plus Google Docs, Google Sites, Google Charts, and more.  The Google Spreadsheet User Directory script makes use of both the new Apps Script Domain Services API and the GData Profiles API, via the “UrlFetch” service.
4. The Apps Script code can be easily shared through Google Spreadsheet templates and through the Google Script gallery.

Using the Google Spreadsheet User Directory

The Google Spreadsheet User Directory code consists of a primary scanUserProfiles() function and some supporting “utility” functions. The three steps for setting up the code to run are: 1. Set up the “Consumer_Key” and “Consumer_Secret” ScriptProperties and run the scanUserProfiles() function in the Apps Script integrated development environment to get the first “Authorization Required” screen. (I’ve included an illustration below... Choose “Authorize.”).

2. Since scanUserProfiles() uses OAuth with UrlFetch to get User Profile information via the GData API, it needs to be run at least one more time inside of the Apps Script IDE, so that the OAuth “Authorize” prompt can be shown to the programmer and accepted.

3. After authorization, the scanUserProfiles() script is free to make authorized requests to the Google User Profiles feed, as long as the developer who saved it has “domain admin” rights.

Design of the Google Spreadsheet User Directory

The following snippets show the OAuth setup, the user profiles Url setup, and the initial UrlFetch.

var oAuthConfig1 = UrlFetchApp.addOAuthService("googleProfiles");
oAuthConfig1.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https:// www.google.com/m8/feeds/profiles");
oAuthConfig1.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
oAuthConfig1.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken?oauth_callback=https:// spreadsheets.google.com/macros");
oAuthConfig1.setConsumerKey(ScriptProperties.getProperty("Consumer_Key"));
oAuthConfig1.setConsumerSecret(ScriptProperties.getProperty("Consumer_Secret"));
var options1 = {
    oAuthServiceName : "googleProfiles",
    oAuthUseToken : "always",
    method : "GET",
    headers : {
      "GData-Version" : "3.0"
    },
    contentType : "application/x-www-form-urlencoded"
};
  

var theUrl = "";
if (nextUrl == "") {
  theUrl =
    "https://www.google.com/m8/feeds/profiles/domain/" + domain +
      "/full?v=3&max-results=" + profilesPerPass + "&alt=json";
} else {
  theUrl = nextUrl;
}

if (theUrl != "DONE") {
  var largeString = "";
  try {
    var response = UrlFetchApp.fetch(theUrl, options1);
    largeString = response.getContentText();
  } catch (problem) {
    recordEvent_(problem.message, largeString, ss);
  }
}

var provisioningJSONObj = null;
    var jsonObj = JSON.parse(largeString);
    var entryArray = jsonObj.feed.entry;  

The "nextUrl" variable above (line 74) is being pulled from a cell in the spreadsheet, where I'm saving the "next" link from the fetched data. (If there’s no “next” link, I save "DONE" to the same spreadsheet cell.) To fetch JSON, I’m appending the parameter &;alt=json on lines 75 and 76. After I’ve got my JSON object, I create an array to store the data that I will be writing out to the spreadsheet. I set the array default values and make liberal use of try-catch blocks in this code, since there’s no telling which of these fields will be populated, and which will not.

for (var i=0; i<entryArray.length; i++) {
    var rowArray = new Array();
    rowArray[0] = "";
    rowArray[1] = "";
    rowArray[2] = "";
    try { rowArray[0] = entryArray[i].gd$name.gd$fullName.$t; } catch (ex) {} //fullname
    try { rowArray[1] = entryArray[i].gd$name.gd$givenName.$t; } catch (ex) {} //firstname
    try { rowArray[2] = entryArray[i].gd$name.gd$familyName.$t; } catch (ex) {} //lastname

At the end of the data collection process for a single record/row, I add the rowArray to another single-element array called valueArray (line 207), to create a 2-D array that I can use with range.setValues to commit my data to the spreadsheet in one shot (line 209).

var updateRow = getNextRowIndexByUNID_(rowArray[3],4,stageSheet);
var valueArray = new Array();
valueArray.push(rowArray);
var outputRange = stageSheet.getRange(updateRow, 1, 1, 12);
outputRange.setValues(valueArray);

The function getNextRowIndexByUNID (line 205) just finds the next available row on the “staging” sheet of the spreadsheet, so I can write data to it. The code is inside of a “for” loop (starting on line 106) that executes once for each entry in the current JSON object (created lines 96 and 97).

} else {
    // COPY CHANGES TO "PRODUCTION" TAB OF SPREADSHEET
    var endTime = new Date();
    setSettingFromArray_("LastPassEnded",getZeroPaddedDateTime_(endTime),settingsArray,setSheet);
    if (parseInt(getSettingFromArray_("StagingCopiedToProduction",settingsArray)) == 0) {
     // THIS DOES A TEST-WRITE, THEN A "WIPE," THEN COPIES STAGING TO
      // PRODUCTION
     var copied = copySheet_(ss,"Staging","Employees");
     if (copied == "SUCCESS") {
       var sortRange = empSheet.getRange(2,1,empSheet.getLastRow(),empSheet.getLastColumn());
       sortRange.sort([3,2]); // SORT BY COLUMN C, THEN B
       // RESET SETTINGS
       setSettingFromArray_("NextProfileLink","",settingsArray,setSheet);
       setSettingFromArray_("LastRowUpdated",0,settingsArray,setSheet);
       setSettingFromArray_("StagingCopiedToProduction",1,settingsArray,setSheet);
     }
    }
} // end if "DONE"

  

If the script finds “DONE” in the “NextProfileLink” cell of the spreadsheet, it will skip doing another UrlFetch to the next feed link (line 81). Instead, it will copy all records from the “staging” sheet of the spreadsheet to the “production” one, via a utility function called “copySheet” (line 273). Then it will sort the range, reset the copy settings, and it will mark another designated cell, “StagingCopiedToProduction” as “1” in the spreadsheet, to stop any further runs that day.

Scheduling the Google Spreadsheet User Directory Script to Run

Below are the triggers I typically set up for the Spreadsheet User Directory. I recommend setting scanUserProfiles() to run on an interval of less than 30 minutes, since the Google-provided token in each “NextProfileLink” url lasts about that long. I also recommend running the WipeEventLog() utility function at the end of each day, just to clear data from prior runs from the EventLog tab of the spreadsheet.

Conclusion

Above I’ve outlined how to create a basic User Directory out of a Google Spreadsheet and Apps Script that will always keep itself current. Since Google Spreadsheets support the Google Visualization API and a query language for sorting and filtering data, all kinds of possibilities open up for creating corporate “directory” gadgets for Google Sites (see the image at right) and for enabling business processes that require workflows, role lookups, or the manipulation of permissions on content in the various Google Apps.
Using Apps Script made this solution quick and easy to produce and flexible enough to be extended and used in many different ways. The code is easy to share as well. If you’d like to give the Google Spreadsheet User Directory a try, then please copy this spreadsheet template, and modify and re-authorize it to run in your own domain. Enjoy!

Shel Davis Guest author Shel Davis is a senior consultant with Cloud Sherpas, a company recently named the Google Enterprise 2011 Partner of the Year. When Shel is not working on solutions for customers, he’s either teaching classes on Google Apps and Apps Script (Google Apps Script Training), or he’s at home, playing with his kids.

Editor's note: This has been cross-posted from the Google Code blog -- Jan Kleinert

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services. If you want to learn more about Google Apps Script, collaborate with other developers, and meet the Apps Script team, here’s your chance! We will be holding an Apps Script hackathon in Washington, DC on Wednesday, March 7 from 2pm - 8pm.

After we cover the basics of Apps Script, you can code along with us as we build a complete script, or you can bring your own ideas and get some help and guidance from the team. There will be food, power, and Apps Script experts available to help throughout the day. Just bring your laptop, ideas, enthusiasm, and basic knowledge of JavaScript. Check out out the details of the event and be sure to RSVP to let us know you’re coming.

Jan Kleinert profile | twitter Jan is a Developer Programs Engineer based in NYC, focusing on helping developers get the most out of Google Apps Script. Prior to Apps Script, she worked on Commerce, helping merchants integrate with Google Checkout and on Chrome, helping developers build great web apps.

The Google Apps Script team strives to achieve a very high level of responsiveness to our community. We iterate fast to deliver new features and functionality. Releasing new Apps Script services under the “Experimental” flag allows us to gather valuable feedback from you. Real world exposure to these new experimental Apps Script services makes it easy for us to prioritize features, improve API design and documentation, and identify bugs and use cases. On top of that, experimental releases give us a way to support our more advanced users, who are perfectly happy to live on the cutting edge and get their hands on cool new Apps Script services as early as possible.

We put thorough consideration into the use of the Experimental flag. Not every new Apps Script service that we launch is experimental. In October, we launched the Lock and Cache service as non-experimental. We believed that these two services were fundamentally mature, and thus able to be launched as non-experimental.

Today, we are graduating four services from experimental status:

1. Charts Service
2. Gmail Service
3. Maps Service
4. Properties Service

You can expect us to continue on this path of careful deliberation in deciding when to use the experimental flag. We will continue to evaluate the design, documentation, and overall strengths of each experimental API and continue to work hard to graduate them to fully supported APIs. We welcome your feedback in helping us make Apps Script better. You can post your feature requests on our tracker and ask any question in our forum. If you are curious on how to use the above mentioned APIs then check out the tutorials for the Charts and Gmail Services, the Maps Service and excellent example of using Properties Service.

The Apps Script team is standing by to help you!

Saurabh Gupta profile | twitter | blog Saurabh is a Developer Programs Engineer at Google. He works closely with Google Apps Script developers to help them extend Google Apps. Over the last 10 years, he has worked in the financial services industry in different roles. His current mission is to bring automation and collaboration to Google Apps users.

What’s the difference between reality and theory? In theory, there is no difference. But reality often imposes unanticipated constraints on developers. These may come in the form of bandwidth restrictions, memory limits, timeouts, or other requirements of the systems that interact with your application.

My team recently built an application that helps us analyze the scheduling and usage of conference rooms at Google. We use the new Calendar API v3 on Google App Engine to read the rooms’ schedules, which we combine with actual occupancy data to calculate utilization and other metrics.

As you might imagine, Google has a lot of conference rooms (I believe the last official count was “more than twelve.”) And many of the rooms seem to be booked fairly solid. That means we need to read a lot of data from Calendar. So much, in fact, that our queries time out if we try to read an entire calendar at once. But the API team anticipated “Google scale” use and designed a mechanism that allows us to retrieve data in batches.

The idea is simple. When you create a request, you specify the page size: the maximum number of results you’d like Calendar to return in one batch. Calendar returns the data you requested, along with an opaque page token, which you can think of as a bookmark. To retrieve the next batch of data, you ask the API for the next page token and include the new token in your next request. The page token keeps track of the results you’ve already seen, so Calendar can send the next batch each time. You repeat this process until you’ve exhausted all the results.

Here’s how we did this in Java:

public void getRoomEvents(String roomEmail) throws IOException {
    // Create a request to list this room’s events (see code, below)
    Calendar.Events.List listRequest = getListRequest(roomEmail);
    do {
      // Retrieve one page of events
      Events events = executeListRequest(listRequest);
      List eventList = events.getItems();

      // Process each event
      for (Event event : eventList) {
        processEvent(event);
      }

      // Update the page token
      listRequest.setPageToken(events.getNextPageToken());

    // Stop when all results have been retrieved
    } while (listRequest.getPageToken() != null);
  }

  // Create a request to list the events for a room
  private Calendar.Events.List getListRequest(String roomEmail)
        throws IOException {
    return calendarClient.events().list(roomEmail)
        .setMaxResults(1000) // Limit each response to 1000 events
        .setPageToken(null)  // Start with the first page of results
        // Return an individual event for each instance occurrence of a
        // recurring event
        .setSingleEvents(true); 
  }

We call getRoomEvents() for each room, using the room’s email address to identify it to Calendar. (You can retrieve events from your own calendar by substituting your own email address.) Then getListRequest() creates a request that we will send to Calendar. The request asks for a list of up to 1000 events from the room’s calendar.

The remainder of getRoomEvents() is a loop that executes the request, processes the results, and updates the page token in preparation for the next request. The loop continues, retrieving and processing each subsequent page of results, until the entire list has been returned. The call to getNextPageToken() indicates the end of the results by returning a null value.

By paginating our requests we avoid timeouts and reduce memory requirements. As an added benefit, each request completes fairly quickly, which means it’s also quick to retry if an error should occur. And finally, a multithreaded application may be able to process one or more pages of results while it retrieves the next, speeding execution. These advantages have led developers at Google to adopt pagination as a best practice. Look for it in our APIs when you need to exchange large amounts of data, and consider adding it to your own services.

If you have questions about our services or APIs, or if you want to see what other developers are doing with Google Calendar, check the discussions and documentation in the Google Apps Calendar API forum.

Adam Liss profile Adam is an engineer who believes that "technical" shouldn't necessarily mean "difficult." He enjoys building infrastructure and tools that make Googlers more productive. Before joining Google in 2010, he built network-security appliances and one of the first wireless application delivery platforms.

Our newest set of APIs - Tasks, Calendar v3, Google+ to name a few - are supported by the Google APIs Discovery Service. The Google APIs Discovery service offers an interface that allows developers to programmatically get API metadata such as:

• A directory of supported APIs.
• A list of API resource schemas based on JSON Schema.
• A list of API methods and parameters for each method and their inline documentation.
• A list of available OAuth 2.0 scopes.

The APIs Discovery Service is especially useful when building developer tools, as you can use it to automatically generate certain features. For instance we are using the APIs Discovery Service in our client libraries and in our APIs Explorer but also to generate some of our online API reference.

Because the APIs Discovery Service is itself an API, you can use features such as partial response which is a way to get only the information you need. Let’s look at some of the useful information that is available using the APIs Discovery Service and the partial response feature.

List the supported APIs

You can get the list of all the APIs that are supported by the discovery service by sending a GET request to the following endpoint:

https://www.googleapis.com/discovery/v1/apis?fields=items(title,discoveryLink)

Which will return a JSON feed that looks like this:

{
    "items": [
        …
        {
            "title": "Google+ API",
            "discoveryLink": "./apis/plus/v1/rest"
        },
        {
            "title": "Tasks API",
            "discoveryLink": "./apis/tasks/v1/rest"
        },
        {
            "title": "Calendar API",
            "discoveryLink": "./apis/calendar/v3/rest"
        },
        …
    ]
}

Using the discoveryLink attribute in the resources part of the feed above you can access the discovery document of each API. This is where a lot of useful information about the API can be accessed.

Get the OAuth 2.0 scopes of an API

Using the API-specific endpoint you can easily get the OAuth 2.0 scopes available for that API. For example, here is how to get the scopes of the Google Tasks API:

https://www.googleapis.com/discovery/v1/apis/tasks/v1/rest?fields=auth(oauth2(scopes))

This method returns the JSON output shown below, which indicates that https://www.googleapis.com/auth/tasks and https://www.googleapis.com/auth/tasks.readonly are the two scopes associated with the Tasks API.

{
    "auth": {
        "oauth2": {
            "scopes": {
                "https://www.googleapis.com/auth/tasks": {
                    "description": "Manage your tasks"
                },
                "https://www.googleapis.com/auth/tasks.readonly": {
                    "description": "View your tasks"
                }
            }
        }
    }
}

Using requests of this type you could detect which APIs do not support OAuth 2.0. For example, the Translate API does not support OAuth 2.0, as it does not provide access to OAuth protected resources such as user data. Because of this, a GET request to the following endpoint:

https://www.googleapis.com/discovery/v1/apis/translate/v2/rest?fields=auth(oauth2(scopes))

Returns:

{}

Getting scopes required for an API’s endpoints and methods

Using the API-specific endpoints again, you can get the lists of operations and API endpoints, along with the scopes required to perform those operations. Here is an example querying that information for the Google Tasks API:

https://www.googleapis.com/discovery/v1/apis/tasks/v1/rest?fields=resources/*/methods(*(path,scopes,httpMethod))

Which returns:

{
    "resources": {
        "tasklists": {
            "methods": {
                "get": {
                    "path": "users/@me/lists/{tasklist}",                         
                    "httpMethod": "GET",
                    "scopes": [
                        "https://www.googleapis.com/auth/tasks",
                        "https://www.googleapis.com/auth/tasks.readonly"
                    ]
                },
                "insert": {
                    "path": "users/@me/lists",
                    "httpMethod": "POST",
                    "scopes": [
                        "https://www.googleapis.com/auth/tasks"
                    ]
                },
                …
            }
        },
        "tasks": {
            …
        }
    }
}

This tells you that to perform a POST request to the users/@me/lists endpoint (to insert a new task) you need to have been authorized with the scope https://www.googleapis.com/auth/tasks and that to be able to do a GET request to the users/@me/lists/{tasklist} endpoint you need to have been authorized with either of the two Google Tasks scopes.

You could use this to do some automatic discovery of the scopes you need to authorize to perform all the operations that your applications does.

You could also use this information to detect which operations and which endpoints you can access given a specific authorization token ( OAuth 2.0, OAuth 1.0 or Authsub token). First, use either the Authsub Token Info service or the OAuth 2.0 Token Info Service to determine which scopes your token has access to (see below); and then deduct from the feed above which endpoints and operations requires access to these scopes.

                        
[Access Token] -----(Token Info)----> [Scopes] -----(APIs Discovery)----> [Operations/API Endpoints]

Example of using the OAuth 2.0 Token Info service:

Request:

GET /oauth2/v1/tokeninfo?access_token= HTTP/1.1
Host: www.googleapis.com

Response:

HTTP/1.1 200 OK
Content-Type: application/json; charset=UTF-8
…

{
    "issued_to": "1234567890.apps.googleusercontent.com",
    "audience": "1234567890.apps.googleusercontent.com",
    "scope": "https://www.google.com/m8/feeds/ 
              https://www.google.com/calendar/feeds/",
    "expires_in": 1038
}

There is a lot more you can do with the APIs Discovery Service so I invite you to have a deeper look at the documentation to find out more.

Nicolas Garnier profile | twitter | events Nicolas joined Google’s Developer Relations in 2008. Since then he's worked on commerce oriented products such as Google Checkout and Google Base. Currently, he is working on Google Apps with a focus on the Google Calendar API, the Google Contacts API, and the Tasks API. Before joining Google, Nicolas worked at Airbus and at the French Space Agency where he built web applications for scientific researchers.