G Suite Developers Blog: 2/1/12

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.

Come Learn About Apps Script in Washington, DC

February 16, 2012

Editor's note: This has been cross-posted from the Google Code blog -- Jan KleinertGoogle 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.

Charts, Gmail, Maps and Properties Services Graduating from Experimental

February 8, 2012

Google Apps ScriptLockCache

trackerforumCharts and Gmail ServicesMaps ServiceProperties Service

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.