Power Automate Active Directory Lookup

I am a stranger in a strange land . . .

One of our instructional designers was doing stuff in Power Automate, and it has led me into a place I normally avoid. Not really out of anything personal, it’s just easy to remain in tool spaces you know better. Most of the places I’ve worked have been heavier on the Google side (at least for the stuff I did). But to get what I wanted, I needed to dive into MS land.

Microsofts maze of products and names still baffles me but Power Automate is pretty fun so far. It reminds me a lot of Yahoo Pipes. It’s also the only way I can get at our active directory data in any sort of automated way.

In this case, I end up with Middlebury email addresses in lots of our academic products but no connection to whether these people are students, staff, faculty or even if they still have active accounts. So my goal was to be able to put a bunch of email addresses in and get that kind of useful information out.

I started by following this video to get rolling.

This video roughly. parallels the text directions below but there are some differences. My first try took just over 15 minutes. Insane. This will still likely feel like watching paint dry. You have been warned.

Rough Power Automate Instructions

Since I want to run the flow¹ manually, I set up an “Instant Cloud Flow” which helpfully lets me know it’s triggered manually as needed.

Now I can create a variable with whatever name I want and define its type. In this case, I’m going with “users” and setting it to be an array. This variable will hold the data I look up.

I create another variable which will hold the email addresses I want to look up. I name it . . . “emails to look up” and also set it as an array. I manually set the value to be an array of emails. In this case, it’s typical programming array style so something like this.

[
"fake@email.com",
"fake2@email.net",
"etc@etc.biz"
]

Right now, I have to modify this each time with my new set of emails. I may tweak it to use an Excel sheet as the source. Right now, I just have the data in Google Sheets² function to create this so I can paste it in. You might need to do the paste special > values only to avoid duplicate double quotes.

=TEXTJOIN("," & CHAR(10), TRUE, ARRAYFORMULA("""" & 'qualtric-usage-Export'!E2:E & """"))

So now we have an empty array to hold our results and a full array of emails to look up.

We add a for-each-loop (or apply each)³ based on our “emails to look up” variable. With in that loop, will use the “search for users (V2) object and input the current item (the email address).

Then for each lookup, we need to write that data to our empty variable (users). We use the append to array variable and handwrite the value. Since the values are all coming from the Search for users, you do a / and choose the insert dynamic content option. That gives you a whole lot of stuff you can pull in. In my case, I grabbed stuff like department, company, job title. Your data may vary. It should be browsable.

Now we’ve written a bunch of data to the variable. We are going to compose something with our variable (users) and use Create CSV Table as the next component (again with the users variable).

Now we’ve got a CSV to send to me via email. You’ve got some basic email stuff to fill out but the most important part is the attachment. Name it and then insert the dynamic content Create CSV Table.

Save all that and test it.