Aside: Could I get this easily by searching EDS<\/a>? Sure. Unfortunately, my data set might be thousands of records long and while I could certainly ask our friends in identity management to get me the bulk data I want, they’re busy and I might need it right away. Or at least, I’m impatient and don’t want to wait. Oh, and I need something like this probably once or twice a week so I’d just get on their nerves. Moving on…<\/p>\n So that’s when I began to explore Google Apps Script<\/a>. It’s a Javascript-based environment to interact with various Google Apps, perform custom operations, or access external APIs<\/em>. This last part is what got my attention.<\/p>\n First, let’s think about how we might use a feature like this. Imagine the following function:<\/p>\n If I passed it the NetID and attribute I wanted, perhaps this function would be so kind as to go fetch it for me. Sounds cool to me.<\/p>\n Now the data I want to append lives in LDAP, but Javascript (and GA Script) can’t talk to LDAP directly. But Google Apps Script can interpret JSON (as well as SOAP or XML), so I needed a little LDAP web service. Let’s begin shaving the yak<\/a>. Or we could shave a wookiee<\/a>.<\/p>\n First, I cobbled together a quick PHP microapp and threw it into my Notre Dame web space. All it does is take two parameters, Here’s an example:<\/p>\n And this returns:<\/p>\n View the full PHP script here<\/a><\/p>\n For a little extra convenience, the script allows Next, the Google Apps Script needs to actually make the request. Let’s create the custom function. In Spreadsheets, this is found under Tools > Script Editor.<\/p>\n This accepts our parameters, passes them along to the PHP web service in a GET request, parses the response as JSON, and returns the attribute value.<\/p>\n Nota bene: Google enforces quotas<\/a> on various operations including URL Fetch. The PHP script and the Google Apps Script function could be optimized, cache results, etc. I didn’t do those things. You are welcome to.<\/p>\n Anyway, let’s put it all together and see how it works:<\/p>\n Well, there you have it. A quick little PHP microapp, a simple custom Javascript function, and now a bunch of cool things I can imagine doing with Google Apps. And just in case you only clicked through to this article because of the title:<\/p>\n <joke>I tried the Wookiee Steak, but it was a little chewy.<\/joke><\/p>\n I’ve been a Google Apps user for as long as I could score an invite code to Gmail. Then came Google Calendar, Google Docs and Spreadsheets, and a whole slew of other stuff. But as I moved from coder to … Continue reading =getLDAPAttribute(\"cgrundy1\",\"ndPrimaryAffiliation\")<\/code><\/p>\n![\"Google](\"http:\/\/blogs.nd.edu\/devops\/files\/2014\/01\/apps-scripts-api-flowchart.png\")
<\/p>\nq<\/code> and attribute<\/code>, query LDAP, and return the requested attribute in JSON format.<\/p>\nindex.php?q=cgrundy1&attribute=ndPrimaryAffiliation<\/code><\/p>\n{
\n \"title\": \"LDAP\",
\n \"attributes\": {
\n \"netid\": \"cgrundy1\",
\n \"ndprimaryaffiliation\": \"Staff\"
\n }
\n}<\/code><\/p>\nq<\/code> to be a NetID or email address. Inputs are sanitized to avoid LDAP injections, but there’s no throttling at the script level so for now it’s up to the LDAP server to enforce any protections against abuse.<\/p>\n
\nfunction getLDAPAttribute(search,attribute) {
\nsearch = search.toLowerCase();
\nattribute = attribute.toLowerCase();
\nvar attr_value = \"\";
\nvar url = 'https:\/\/www3.nd.edu\/~cgrundy1\/gapps-ldap-test\/?'
\n+ 'q=' + search
\n+ '&attribute=' + attribute;
\nvar response = UrlFetchApp.fetch(url);
\nvar json = response.getContentText();
\nvar data = JSON.parse(json);
\nattr_value = data.attributes[attribute];
\nreturn attr_value;
\n};
\n<\/code><\/p>\n![\"Screenshot](\"http:\/\/blogs.nd.edu\/devops\/files\/2014\/01\/apps-script-formula-example.png\")
<\/p>\n![\"\"](\"http:\/\/blogs.nd.edu\/devops\/files\/2014\/01\/shaven-wookiee.jpg\")
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"