Sourcing Automation with Microsoft PAD - We cracked the code!

Well, hello there. Welcome to Shelly's Alley. It looks like we are ready to go today. Well, this has been, this has been an interesting, exciting, challenging, I don't even know what else to tell you. Road, but I am so happy to tell you that we finally figured it out. So Glenn and I have been, as you might recall, working together on trying to solve the, the problem of parsing LinkedIn data from, um, search results and putting them into a spreadsheet using Microsoft Power Automate Desktop, or PAD as we are fond of calling it. So I'm here to tell you that we finally cracked the code and actually kind of want to kick myself a few times because, um, the solution was just so easy and literally right in front of me. It just— I want to— I'm gonna just kick myself because I didn't come up with this, um, the first time. Anyway, so we have 2 solutions. We have one for Google and one for Bing, and I'll explain why the, the difference or why we ended up switching to Bing in a minute. You know, when I, when I show you what's actually going on. But the second part of this, which is really exciting beyond the actual discovery of how to parse the LinkedIn URLs— see, before we were able to grab first name, last name, title, company, location, things like that, but we weren't actually able to get the HTML href link of that person's profile to parse accurately without some extraneous text that, that would kind of mess things up and make it more complicated. So that's the thing that we solved. But I also added the ability to parse all of that into a spreadsheet and put things into appropriate columns. So when you just click once, it will import all the data into a spreadsheet and basically parse it out or break it up into columns. All of this entirely for free using Microsoft Power Automate Desktop. So today with me I have Glenn Guttmacher. He should be joining us here on screen any moment. He has a little bit of an internet connectivity situation, so he may or may not have video on. Let's see if he's able to come up with video. Oh, there he goes! We've got, we've got video. Hey Glenn, you're live! Howdy, how's it going?

All right, I'm not in my usual place, but I'm happy to join.

Hey, we're happy to have you. Thanks a lot for coming. Couldn't have done this, uh, without you. So Glenn, I'm just about to pull up the— well, first let me share my screen actually. There we go. I'm just about to pull up the Power Automate desktop here to show everybody what we've done. But I wanted to give you a shout-out on one thing because as we were going through this, when we finally solved it and created the parser, you had the idea that you suggested something like— I don't know exactly what the words were that you used, but you said, Why don't we create a prompt to have the user put in information and then the script will execute based on that information? And I thought to myself, wow, that's a really good idea. We were just launching the Chrome browser and then parsing the data, but why not ask the user a couple of questions? So that's how we ended up with this display input dialog. Which is one of the interactivity actions that you see on the left-hand side. And what this does is it asks for the user to enter in— the first one is a job title and the second one is a company name. We can make this do whatever. We can make it be location. You can add location. You can do— you can even have a prompt that has multiple boxes. So you can have a multi-input prompt that has job title, company, location, whatever. We didn't want to get too fancy, just wanted to show you how that works. So this is what's going to happen when I click play. Play here. It's going to ask me to type in some information, then it's going to launch the browser, parse the information, and put it in a spreadsheet. All right, I hope you got your seatbelts on because, um, here we are. Glenn, any, any, any last words? I know you just went on mute, but any famous last words before I hit the button here?

No, I think they're gonna like it. Then we'll explain.

Cool. All right, I'm so excited. I'm also so nervous because it's probably not going to work. You know how that is. All right, here we go. Run. I am now hands-free and a prompt comes up, probably really tiny on your screen here. I'll move it around so you can see what I'm talking about. And it says enter job title, no quotes. So I'm going to put in here construction manager and hit okay. Then it says, ah, we need a company name. All right, so I'm going to do CH2M as in CH2M Hill. I know that they've been acquired, so they're no longer CH2M Hill, but there's still people out there with the profile. So look, it went to a browser window and it put in the search criteria and then basically executed the search. And now you can see in the background— oops, sorry about that, I meant to expand this here— so you can see that the prompt basically came in here and added my Construction Manager and CH Schwinn Hill criteria to the, uh, to the search. Now here's where things get a little bit interesting. The first part of this is I wanted to show you why, um, uh, it's failed to assume control of Chrome. That's okay, we can, we can fix that in a second here. I think I know why. That's because of this. And probably also because of this, which is what happened last time. So let me just go ahead and close out of all of that. All right, so why did we switch to Bing? I'll show you side by side here by opening up another tab and going to Google, and then hopefully I can fit these both on the screen. Yeah, I'm sure I can. So there's one, there's the other. Okay, I'm going to take the exact same search and do it on Google. Probably should zoom out a little bit here so you have a better view and maybe zoom in a little bit here. Now, obviously, you'll notice a couple of things. One is that the results are organized or ordered or prioritized differently. There, there are a few other differences as well, but the big one is that the information that you see on Google is kind of structured a little differently. What we were struggling with was trying to extract the HTML for the URL. Well, on Bing, as you can see, it's actually not a struggle because it's literally right there. So advantage one is on Bing, the address of that person's profile is very clearly displayed right there in front of you. Um, hey, Glenn, check it out. It did the thing again where it's only producing the first 8 results. Huh, maybe it's a good time to show everybody how to change that. So sometimes you'll, you'll get only a few results. You can go into your, your Bing settings, and this is something that Glenn actually showed me the other day. Um, and it says under results, show this many results on each page. Now Glenn, I changed this last time, but I think it's because I'm on a different browser now. So, um, you change this to 50 Save it, and now when we come back, it's actually going to show you a whole page of 50 results, which is what we want because we want to parse all that information anyway. On Google, it does have the city and the title and the company, but it doesn't have all this other stuff that you can see here. So it's actually a little bit easier to parse occupation, company, location. It's easier, right? So that was the one reason that we switched over. But then we went back and we found that on Google you can actually still get it. So let me stop this real quick so I can show them. Well, actually, I think— I don't think I have to stop anything, but I can close this out. Actually, no, I don't even need to do that. Let's go to web and add a new Come on, website, where are you? Oh, there it is. Web Automation. Launch a new Chrome. I'm just going to add it to the end here, um, as almost like a little, you know, sidebar so that I can show you what I'm talking about. So I'm going to attach the running instance, use the foreground window, and save. Foreground window is going to be this one that I'm looking at right now. Basically, that's, that's what that means right there. And then I'm going to add the extract data from a web page right here, which brings up that interface. Gotta make sure we tell it browser 2, just because we have this double thing here. And we gotta make sure we tell the spreadsheet, although I'm not really going to execute. So Let me show you where that information is. When we go over to the window, it pulls up the recorder, the little device that starts to remember the things that you clicked on. And right here, there's a— Glen, what did you call it? It was the A tag?

It's— yeah, the A tag has an href value. It's part of what they call CSS selectors.

Ah, okay, right. So in the CSS selectors inside of the structure of the page— oops, didn't mean to do that. It is— darn it. Try that again.

And basically any web page that has a formal structure in how it was created, most content management systems do create pages in a very consistent way. That means you have an easy way to target different elements on the page.

Right, so the CSS, the Cascading Style Sheets, is kind of a— the HTML, sorry, the HTML structure of the page. So I'm going to zoom in a little bit here. It's probably too small. So here we go. I'm going to right-click on, say for example, the first row here which shows the name and title and company, and then the second one here which shows the location. And, um, here's— we can extract the title and we're going to extract the company. But then we got stuck because we couldn't pull out the HTML reference to the a tag until I was goofing around and I did this almost accidentally. Ah, I still can't even do it. It's so well tucked away. There's a longer way to do this, and I'll show you in just a second, but I want to show you what I accidentally did to discover this. Come on. Man, it's really tricky. There it is. I had it. Oh, for a second. Just for a second. Darn it.

You said you came in from the right?

Yeah, I did. And then I basically just hovered over— let me try another one, actually. Yeah, let me do this. We did that, then we did All right, perfect. Now I'm going to come in from the right and— ah, almost, almost, almost. Oh, it's so annoying. Okay, all right, I'm just going to show you the long way around. If you highlight the heading result there and you right-click and select Parent Element, it selects this A tag, which you can basically not really select manually. Look, I keep trying to hover over it and it's, it's really tough to select. But by right-clicking and select parent element, it's right there. So I'm going to do that one more time. Right-click, select parent element, and there it is. And now there it is, the href that we've been looking for all along.

So for anyone who's used to using web scrapers like DataMiner, this should not be unfamiliar because you often have to do that parent selector and fumble around until you get the right element chosen.

Yeah. And we were trying to do that, but we kept basically going above it and under it and we couldn't figure it out. So finally, when we got it, then everything comes into place and now we can extract all of the information, including the hyperlink to the actual profile itself without any extraneous content. So there you have it. This can now be done with Google as well as with Bing. But let me cancel this and delete that one and go back to our original idea here, which is to run the whole thing off of Bing and do the automation. So there we go. Play prompt. Construction manager, enter. C-H-2-M, enter. Launches the Chrome, goes to Bing, puts in the criteria, starts running the search, starts parsing the information. Then I want to show you one last thing.

So you might want to call out why is it going through that loop so many times.

Exactly, that's what I was— thank you, Glenn. So as Glenn pointed out, it's a loop because we are manipulating the data as it's being added into the spreadsheet. And on Bing, we selected 50 results, so this should run up until 50. Um, actually got to 51. I'm not sure exactly why, but it's going to run 50 times because there are 50 results. We can change that by telling it to only get, you know, the first X amount, or we can also say get all of them. Um, that's a, that's a setting that we can use when we actually start parsing the data. I didn't want to have it grab everything because of 442 results, that would be, let's see, It took, what, about a minute or so? So that would be one— that would be almost 10 minutes. Not quite. It would take a little bit longer to get all 400. Now it'd be worth it. Might even be less than 10 minutes. It would be worth it, but I don't want to sit here and, and do that. So I'll show you how we can change that. For now though, you see how it ran and then it went through the loop and it started adding all that information. So it put it all into a spreadsheet. And this is the spreadsheet that it created. Here we have the title, which includes all the information that we needed— name, company, title, etc. Then there's the URL, the address of the actual profile. Then there's just the name, just the company— I'm sorry, I meant the title and then just the company. I will point out that we saw a couple of errors and those errors occur occasionally when the title doesn't have all of the information because it kind of got cut off. This is something that we haven't had time to refine yet, but we can easily refine because we'll be able to parse all of that information from underneath the results down here. All we're doing right now is we're parsing that first line. So as you can see, we'll scroll down here a little bit and you see that one right there. It kind of gets cut off here and there's some that get cut off even earlier, like that one here, Lon Elrich. So let's see where Lon shows up here. There he is. That one's barely made it. Which one did not? Derek. Okay, let's go down to Derek. So most of them really did come through. There's Derek. That was really cut off. Doesn't even have CH2M Hill. However, we could still have, we still have gotten some of that information another way. So we didn't quite do that. But it is putting everything, almost everything that we need into here. So how do we do this? A couple of things. First, the loop, which is going to grab the text from over here and put a formula over here, and it's going to do that row by row for these 3 columns. And then second, the formula. The formula is a little tricky. On the first one, it's basically a find statement that's going to go to the left. Glenn, you want to explain this one real quick? It's going to the left, counting up until the hyphen, and then extracting that. Is that right?

Yeah, you're basically grabbing the leftmost portion of the cell up to that point.

Yeah, basically from here to here. Then the next one is a little trickier because it's not left, it's now the middle. You want to Give us a quick explanation on this one.

Sure. So for those of you who know Excel formulas, the MID formula is a great way when you're trying to grab something in the middle of a cell and you need to be able to specify the start point and the end point. And so that's what this formula is doing. It's saying the start point in A1 is going to be that dash space which we know is where the job title appears to be starting, plus we have to add 3 to that just to make sure it starts after the space, dash, space. So that'll start with the letter C, and then we're going to take it all the way up to the point where the next one is, um, and it— that is a little bit complicated of a formula to explain.

It skips the first one on the, on the, on the other statement. It skips the first one and goes to the second one, basically. Right. Yeah, that's why there's find and find.

There probably are a few ways to achieve the same thing, but basically we're just trying to find the stuff between those two space-dash segments.

Yeah, so basically it's like, okay, the first time around, start with the middle and that's where you're going to go from here to the right. And then the second time around, start with the second instance so that we know the starting point is here and the ending point is here. Therefore, it grabs Construction Manager. Now you see a substitute here. The reason for that substitute is because I want to replace the hyphens and other extraneous content so it doesn't, you know, put in any commas and etc. So that's why it's a little bit cleaner. And then the one on the right is basically just a substitution. What this is doing is it's saying find C1 over here and get rid of it, find D1 over here and get rid of it, and also get rid of the hyphens and the spaces, and also get rid of anything that's after the pipe. So basically what's left is CH2M or CH2M-Hill. So that's it. It's not that sophisticated. We can put these three formulas up on the Facebook chat after the session. And then the loop. The loop is pretty simple as well. Basically, all the loop does is it has a start point and an end point. Here's where it got a little bit interesting because we have to tell the loop to loop as many times as there are rows in the spreadsheet. Otherwise, a machine being what a machine is, if you tell it to do something for a certain amount of times and that certain amount of times is an infinite number, it could just sit there and get stuck in what's called an infinite loop and basically just go over and over and over and add as many rows as it can add until it reaches the maximum amount of rows for your version of Office, which could be a million rows, and then it crashes and closes Excel or whatever. So we wanted to basically count— sorry, go ahead.

Is there a reason we had to bring in Apple's headquarters address into this discussion?

Where's that?

One infinite loop.

Oh, wow. Nice. One infinite loop. Yeah, there you go. So good one, Glenn. Always, always funny. Always funny. So we basically had to ask— Power Automate Desktop had to ask the Excel spreadsheet, hey, which is the first free row? That's this command. And that's literally a command that you can grab right out of the actions. We didn't program this. We didn't make it up. This is literally built in. The command is just get the first free row on a column. So it goes in here and it scans all the way down and it says, hey, the first free row is 51. Great. So then the formula says 51 minus 1 would be 50 because the first free row is 51, which means that there are 50 rows that have data in it. So it tells the loop to loop 50 times. Um, I think the reason it went to 51 is because we forgot to add that minus 1, Glenn. I just realized that one. That's why I got that, um, extra row there. That— see the last one, that's like a blank one. That explains that. So that's why it loops 51 times. So it says count the rows, great, now do this whole 3-formula thing that we showed you 50 times. So the only other thing that we could manipulate here is when we go to extract from data, I'm telling it to grab only the first 5. But if I told it to grab all available, then it's going to go through all of the pages, all 50 results for all of the— I don't know how many it is, uh, 8 pages that it would do it for. And at about a minute each, it would take about roughly about 8 minutes, but you would have 442 results. So that's it. That's the whole thing. To recap, this is display input, and when you ask it to display, it comes up and it says, hey, what do you want it to ask? I wanted to ask, enter the job title, and then it stores that as a variable which we named job title. Very aptly, I would say. Then the second one comes up and it says Okay, we want to ask another question. What is the company name? And we want to store that again very aptly as a company variable. Now we have two variables. You can see on the right-hand side here what are the variables. You don't need to get too fancy about variables and all that if you're not much of a programmer. Basically think of a variable as like the memory that the computer stores. So the first one is it asks the job title and it stores that in memory. Then it asks the company and it stores that in memory. Later on, when it builds the search string, this is the part that uses those two. It's— the search string is going to Bing, starting the query, and then where it says in title, it puts in the job title variable. And when it says in title a second time, it puts in the company variable. Now I'm going to save you a lot of time. Glenn showed me this, and, um, I was starting to get frustrated, so I called them up and I said, Glenn, I can't get this darn thing to work. I was putting in the variable into HTML code, which means I was using the percent 24, the quotation marks and all that. Glenn said, you don't need that. You can actually just write it the way you would normally write it. So that's why you can see here, even though this is a URL, there's a space Normally I would not want that because if I put that space into a search in the URL in a browser, it would error out. It doesn't like it, but this actually translates it for you so you don't need to get the space and you can use regular quotation marks. You don't have to use the character for that. So once I figured that out, thank you, Glenn, we just put in the job title variable and the company variable. And boom, it works. So I'm going to show you real quick how we can add something to this. We'll add another display input. So you're seeing us building this out right now. So here's display input dialog, which is under the message boxes actions, and this will be the third one, which will be input dialog. Let's call it location. And then the message is, what city are— so we'll say, enter the name of a city, or enter the name of the target city. And then we create a variable here, we'll just call it location. Its default is to have user input, but I want to call it location. You don't need a default value because If there is none, then it should just go ahead and remain blank. Then you won't— you don't need to change this, but it shows you single line. This is where you could change it to multi-line if you wanted to have, you know, 3 things or whatever in it. But that's all we're going to do is single line, enter the name of the target city, and we're going to hit save. Now there's a third one over here, so we're going to need to add that location to our URL. And quite simply, we're just going to do location right here. You can add it right from the list of variables. It is percent— I didn't capitalize it right— location percent. Good. So basically, the percent and percent are the beginning and end of the statement that tells that tells the automator that you are entering in a variable as opposed to you're putting in some text. So it's just going to simply add whatever city you put into, into that and save. So now when we run this search, it's going to ask me 3 questions, put all 3 of those into the browser, and then do the rest of the operation. And I think— did I change this to get the first free row? Oh right, this is the one we needed to do -1, right? So it'd be on the loop index from first free row to -1. This is where you would put -1, right?

Yes.

Yeah. I don't want to mess with it right now. It's still going to— it's going to do 51, but that's okay. Normally you would add -1 here so it goes to 50 instead of 51. And did I tell it to go as many? All available. Okay. So this one might take a little bit longer. Maybe I want to switch that back. No, that's fine. I'll leave it all available.

You could just enter a more narrow search to get fewer pages.

That's exactly what I was thinking. So let's try something else. Glenn, you got any ideas?

Well, I suppose if you did a verification engineer, that's kind of narrow. And any title, any company, or, um, well, anyone that does hardware chips is going to have that kind of person.

Uh, Motorola? Maybe?

Sure.

Let's see. So verification engineer, Motorola Phoenix. Too, too narrow.

I'll try Cisco. Sorry, Intel maybe.

Intel. Okay. And Phoenix still?

I think they have a hub there.

Okay. 49 results. That works. So verification engineer Intel Phoenix. All right, I'm going to close this out and hit run. So first things first, it's going to ask me, hey, what job title? Okay, I want verification, verification engineer. Then it's going to come back and say, what company? Intel. And then it's going to come back and say, what location? Phoenix, just as before, opens a window, puts those things into the, into the URL of the search. Then it parses the information. Now it's starting to count the loops. You can see it behind the scenes here as it's running through the 51 times. It's probably going to grab a couple of extra things because there's only 49 results. I'm glad I didn't say all of the, uh, all of them because this could take a little bit longer than 8 minutes. It's about 58, 59. Oh, look at that, 59 seconds exactly. Wow. And it opened up a spreadsheet and here we have the information. Yay! All of it is right there. So, um, I promise I'm going to work on refining the ones that have the little cutoff there. That's easy to fix. If you wanted to manually fix this right now, basically you could just— you know that they're, you know, going to be all at Intel, so you could just replace that. But this is definitely fixable, not, not a, not a difficult error to overcome. And the other thing that I wanted to do, which I haven't done yet Glen and I will probably try this out, is to take this spreadsheet because we now have an active spreadsheet that exists. It's actually called Book 1, Sheet 1, which is a default for any— whenever you are working with Excel in Power Automate Desktop and you're creating a brand new Excel, it will create a Book 1 and it will always call the first sheet Sheet 1. That's the standard. So you can always invoke it using those, those variables. If you're opening a spreadsheet that already exists, then you'll need to get the name of it and the sheet name that you gave it. But for now, it's just Book 1, Sheet 1. So what we would do next is we would take this and instruct— after the loop ends, instruct the page to go to, let's say for example, SeekOut and log in. So let's, you know, you would assume because it's a new instance, you would assume login. So you would tell it to click on the login button, and then it would come up with an interface. Right now it's just remembering me from a cookie, but you would have it put in your login and password, and then you'd be logged in. Then you would tell it to go to a predetermined project. Perhaps you call it upload project or something like that. And once you create the project You'll be able to use that variable. So you'll say, okay, go to SeekOut, log in, put in my credentials, then go to the upload project and then click on upload candidates and click this button and browse to the temporary place for book one and upload it. So it literally would upload book one to your project. Hit upload and then wait. Now at that point, you actually have to wait because SeekOut does take a minute to load it up. When you upload, I don't know if you've done that before, Glenn, but when you upload a file, it's not instant. They will crawl all of the URLs. So it takes sometimes, you know, I've come back like a day later and it's still adding more email addresses and things like that.

Well, it shouldn't be that crazy. I would say about a minute per record, maybe a a little, yeah, about that.

Yeah. Right. No, that's usually what I'm just saying. It's sometimes it has been, I, you know, I uploaded it like at the end of the day and I come back the next day and it's still adding. Obviously that was a really big project, but a minute per record sounds good. So if you've got 50, it's going to take 50 minutes max, hopefully. So that would be the next step, literally upload. And then, you know, you would get an email when it's ready. I think it sends you an email, doesn't it?

Yes, you would get notified, right?

So you can even have, um, I mean, I'm just getting really fancy. You can even have Automate Desktop listen for that email and take action. Once the email arrives, you can then say click on the link on the email, go to the page, and hit export all, and you would be able to download it all. That's how advanced you can get with this, and it's really all just point and click. So that's it for today's show. I, um, I hope you really enjoyed this. We will post the formulas, which are a little tricky Please take liberties with the formulas. You can do the same thing with anything else that you can think of to parse that information. There are many other ways aside from, you know, FIND and LEFT and MIDDLE that you could parse that information. I tried to use the, um, FILLXML with an array, which worked, but when it added the array to the formula inside of Excel, because it was a brand new Excel file, it didn't read it as an array. And so there— so it failed. So we, you know, ended up doing this other one, but you can probably solve that if you want. And, uh, yeah, let us know what other ideas. We have a couple of requests from the audience on things to automate, so we'll be queuing those up in future episodes. By the way, if you want to appear as a guest, send me a message and we'll bring you into the Zoom meeting so you can basically walk through a project with us. We can— we'd be happy to help you put something together and troubleshoot it with you. So send me a message to the Facebook The sourcing method group.

And very cheap when you do those sessions.

That's right. Thank you, Glenn, so much. I really could not have done this without you. Seriously, man, this, this was a, you know, you and I worked closely together on this project and I think it's a, it's a labor of love, but it's also definitely a collaboration effort. So appreciate that.

I think what's great about these is each use case we do is applicable to lots of other examples in our own work. So we're also benefiting.

That's true. Yeah, I actually just used it for a list of actuaries today, right before this. And anyone, if you're interested in hiring Glenn, he is available. He just finished up work at his, uh, current employer, so he is available for sourcing leadership and technology positions. So please reach out to him directly. Thank you. Take care, everybody. Bye-bye.