Create a Spreadsheet User Directory with Apps Script

Wednesday, February 22, 2012 | 12:30 PM

Labels: ,

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.

16 comments:

www.karga.gr said...

thanks for info

mhawksey said...

... and with Google Apps Script UI Services you can also wrap your spreadsheet in some custom UI publishing as a service or a gadget on Google Sites http://mashe.hawksey.info/2011/07/gas-staff-directory/
Martin

Ian Weatherhogg said...

I've done something similar which "syncs" contacts from multiple, differently structured Google Spreadsheets, into Shared Contacts. Useful where you want to do regular exports from legacy applications like accounts/CRM/MRP.

Shared Contacts in Google Apps via spreadsheet

Unknown said...

Hi Shel. Thanks for the script.
I've installed it and run the first function but keep getting the error: Attribute provided with invalid value: consumerKey (line 60)

The key is definitely right and the API's all enabled.

Shel Davis said...

I'm glad people are finding the script useful. "Unknown," the error you are describing should be pretty straightforward to trouble-shoot. It means the value stored in the Script Property named "Consumer_Key" cannot be interpreted or does not match your Google domain.

To discuss the second part first, my Google domain is: http://www.google.com/a/cloudsherpas.com, so my "Consumer_Key" is just cloudsherpas.com.

To address the things that could make your key invalid, make sure your key has no leading or trailing spaces, or any other non-ASCII characters.

If everything above checks out, try updating line 60 in the script with a text value for your domain key, so it does not refer to a Script Property at all. So, like this: oAuthConfig1.setConsumerKey("cloudsherpas.com"). (Obviously use your own domain.)

Let me know if any of the above helps, and if it doesn't, I'd be curious to know which version of Google Apps you are using, for further trouble-shooting.

Good luck!

Chris MacLean said...

I'm getting the same error it seems...

Trevor Iampen said...

Thanks man! Been looking for this type of solution, but wasn't sure how to implement it...much better than utilizing contact info via GAS.

JC said...

The error: Attribute provided with invalid value: consumerKey (line 60) can be corrected by changing the code on lines 60 and 61 to:

oAuthConfig1.setConsumerKey("Consumer_Key");
oAuthConfig1.setConsumerSecret("Consumer_Secret");

Otherwise script is great, thank you.

Shel Davis said...

Thanks, JC. It's been a while, since I've looked at this blog post. The way the script is written, you have to use "Consumer_Key" and "Consumer_Secret" (with the same case and with an underscore) as your ScriptProperty names, to get the script to work.

I should also mention that you need to make sure there are no invalid characters (such as leading or trailing spaces, for example) in your key or secret as well.

Due to some other feedback I received on this post, I created a second spreadsheet that can handle larger numbers of users than this one can... (You may have noticed that the copy/pasting code in this version limited the number of users it could maintain.)

In the new one, I just remove the "production" sheet and replace it with the "staging" one, so it can handle up to about 10,000 users.

The new one also creates word "stems" of the first and last names, to support type-ahead functionality in certain gadgets I've written.

Shel Davis said...

Thanks, JC. It's been a while, since I've looked at this blog post. The way the script is written, you have to use "Consumer_Key" and "Consumer_Secret" (with the same case and with an underscore) as your ScriptProperty names, to get the script to work.

I should also mention that you need to make sure there are no invalid characters (such as leading or trailing spaces, for example) in your key or secret as well.

Due to some other feedback I received on this post, I created a second spreadsheet that can handle larger numbers of users than this one can... (You may have noticed that the copy/pasting code in this version limited the number of users it could maintain.)

In the new one, I just remove the "production" sheet and replace it with the "staging" one, so it can handle up to about 10,000 users.

The new one also creates word "stems" of the first and last names, to support type-ahead functionality in certain gadgets I've written. The url is: https://docs.google.com/a/cloudsherpas.com/spreadsheet/ccc?key=0AqzFlfwsM0NrdGZreUdPV1owUjVlOFZpMktnVWQybVE

JC said...

Very nice - Is it possible to restrict entries of users in this process based on organizational structure in Google Apps? Didn't see any reference for it in the API profile docs, but maybe you have some ideas...

Anthony Cosentino said...

Sorry, kinda new to this. I got this to work fine, but how do I add it to my Google Site to look like the picture you have in this article?
Thanks

Gay Aida Dumaguing said...

Hi Shel,

I'm an IT graduate but I'm not working as a programmer at the moment but have been coding occassionally. I've got my current project which I was supposed to submit for the Google developers challenge but was not able to finish it. So I would like to ask some questions like how to protect my code? Just like in spreadsheet, the script can be opened by someone who owns the spreadsheet right?

I'm planning to create an app for Google drive where it creates a copy of the spreadsheet I have.

I really want to finish this project since it will help me with blogging, making money using the Google Affiliate Network and also help my fellow bloggers.

Thanks!

ingrith natalie said...

marvelous idea.

mmccarn said...

For other non-programmers (like me) who want to use this, here's what I did to get this working:

1) Enable OAuth for my domain in the google apps control panel at https://www.google.com/a/cpanel/mydomain/SetupOAuth
(Replace mydomain with the appropriate value for your domain)

2) open the template (look for "this spreadsheet template" near the middle of the original post)

3) Save a copy

4) Click 'Tools', 'Script Manager'

5) Select scanUserProfiles and click 'Edit'

6) In the script manager, select 'File', 'Project Properties'

7) Select the 'Project Properties' tab

8) Add two new properties:
Consumer_Key
value = mydomain

Consumer_Secret
value = OAuth key from Google Apps OAuth setup page

note: the property names must exactly match those shown above, and both the property names and values must not contain extra blank spaces

9) Click 'Save' in the script project properties window

10) Click 'Run' in the script project edit menu, and 'Authorize' the script as described in the original post.

Shel Davis said...

I've gotten some feedback that OAuth 1.x is no longer working in this spreadsheet solution, so I've created an OAuth 2.x module that can be added to this script, using Arun Nagarajan's post on the subject on StackOverflow here.


The source code and the instructions on how to use the new module were kind of long to add as a comment to this post, so I've linked it here and at the end of this comment.


Feel free to use this OAuth2 module in any of your Apps Scripts that require OAuth. Happy coding! (https://docs.google.com/a/cloudsherpas.com/document/d/1ouTg55DitnijJfUlrSpiltERxTeowIKAvxvobrtQJxc/view)