G Suite Developers Blog: Create a Spreadsheet User Directory with Apps Script

G Suite Developers Blog

Information for G Suite Developers

Create a Spreadsheet User Directory with Apps Script

February 22, 2012

Google Apps Script What is the Google Spreadsheet User Directory?

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.

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.

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.

The Apps Script code can be easily shared through Google Spreadsheet templates and through the Google Script gallery.

Using the Google Spreadsheet User Directory

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.

Design of the Google Spreadsheet User Directoryvar 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; 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 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); } 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" Scheduling the Google Spreadsheet User Directory Script to Run

Conclusion

Google Visualization API query languagethis spreadsheet template

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.