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.