Archives for March 1, 2012

Create a Spreadsheet User Directory with Apps Script

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");
var options1 = {
    oAuthServiceName : "googleProfiles",
    oAuthUseToken : "always",
    method : "GET",
    headers : {
      "GData-Version" : "3.0"
    contentType : "application/x-www-form-urlencoded"
var theUrl = "";
if (nextUrl == "") {
  theUrl =
    "" + 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$$  fullName.$  t; } catch (ex) {} //fullname
    try { rowArray[1] = entryArray[i].gd$$  givenName.$  t; } catch (ex) {} //firstname
    try { rowArray[2] = entryArray[i].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();
var outputRange = stageSheet.getRange(updateRow, 1, 1, 12);

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 {
    var endTime = new Date();
    if (parseInt(getSettingFromArray_("StagingCopiedToProduction",settingsArray)) == 0) {
     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
} // 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.


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


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.

Google Apps Developer Blog

Attend an Upcoming DFA Training

Whether you’re new to DFA or you’re a seasoned trafficker, the training team has several training options to help expand your knowledge of DFA. We offer classroom and webinar training, so choose the option that best fits your needs. Visit the DFA Help Center to register (sign-in required).

Classroom Training

DFA Fundamentals: This full-day course (10am-5pm) is for new DFA users looking to understand the fundamentals of third-party ad serving including best practices for campaign trafficking, serving your ads and pulling reports.

  • Chicago – February 15
  • San Francisco – February 23
  • Los Angeles – February 28
  • Cambridge – March 1

DFA Trafficking Lab: Get more practice trafficking campaigns. These sessions cover the basics such as assigning landing pages to creatives, updating campaigns, setting up geo-targeting and using creative rotation.

Class Prerequisite:Attendees should have taken the DFA Fundamentals class in the previous two months or have less than two months trafficking experience.

  • Chicago – February 16
  • San Francisco – February 24
  • Los Angeles – February 29
  • Cambridge – March 2

MediaVisor: Learn more about planning and creating campaigns, advertisers, site placements and IOs as well as how to send RFPs.

  • Los Angeles – February 29
  • Cambridge – March 2

Webinar Training

Managing Creatives in DFA: The Fundamentals – February 14
Learn about the different types of creatives and the basic creative trafficking process.

Making Use of Creative Templates and Optimization – February 21
Learn more about optimizing performance on your creative assets by setting up criteria for rotating your ads based on campaign goals.

Making Sure Your Creatives Work and Troubleshooting – February 28
Learn how to troubleshoot common creative questions.

DFA for New Users – February 22
An introduction to the fundamentals of DFA.

Be sure to check back with us on this blog for regular updates to our training schedule. We look forward to seeing you in class.

DoubleClick Advertiser Blog