Sourcing Automation with Microsoft PAD - We cracked the code!

Hello everybody, welcome to Shelly's Alley. Today we are continuing the conversation around Microsoft Power Automate Desktop. Also known as PAD. I am basically really infatuated with this tool. So, um, I keep finding all kinds of fun things, and not just fun but very practical things to do with it. So I am just going to keep doing it until somebody else stops. Um, otherwise, why not, right? So last time we talked a little bit about scraping and then parsing information from a search result on Bing and Google. And I said that it might be worth exploring the possibility of then taking that scraped information and uploading it to SeekOut or some other, you know, tool like that to then parse— not parse, sorry, append the contact information and then bring back the results. So I'm going to try and do that today, but before I do that, there were two quick things that I wanted to show you that were new things that I, you know, new automations that I worked on over the last couple of days. So let me start by sharing my screen here. There we go, screen shared. And all right, the first of those that I wanted to show you is let's say that you have a folder with maybe a bunch of resumes in it. So here we have actually 3 folders, and inside of these folders are the resumes of the people that have been submitted or presented. This could be a folder of applicants or whatever. You know, if you have a SharePoint drive that can be mapped— this is actually a SharePoint drive, it doesn't have to be a drive on your computer, but if this works with anything. So you have a list of files in here, a whole bunch of files, and you want to extract the names of those files so that you can track them or whatever. You can do a lot of things with this. You can actually go into each one of these documents, for example, and look for a pattern using regular expressions that identifies email addresses and extract the email addresses from all these documents. You can go in and parse the information into a spreadsheet. There's a lot you can do, but today I just want to show you a quick thing that I actually needed to do, which is I needed to get the names of all of the files in these 3 folders because there's a lot of them. Um, there's at least 50 in each one or something like that. It's a lot of work for me to grab that manually, so I'll show you what I did. I created a new automation called Extract File Names from OneDrive. Now, OneDrive and SharePoint, um, Google Drive, if it's mapped to your computer, it doesn't matter where it is. It's a folder on your computer, even if it's a folder that's a cloud folder mapped to your computer, as long as you can point your machine to that direction. The first thing that I did is I told pad to grab all of the files in a folder, and it's not actually storing all of the files in a variable is what I meant to say, not a folder. It's actually just grabbing the information and putting that into the, into the variable. So the first variable that's produced here is files1, and the second one is files2 because I've repeated the function for the second folder. Now I could do this, I could tell the, uh, system to actually look at subfolders. And instead of looking at this folder and the other folder, I could tell it to look at all of the subfolders. But that's only if all of the subfolders have the same kind of information. And although that may be the case, that is a second solution to the same problem. In this case, what I'm doing is I made one command to get the files in folder Gary and one command to get the files in folder Aaron. If you wanted to do both, you would simply remove this and include subfolders, and everything under the parent folder, which would be all three of these, would be parsed. So that's the only reason why there's two of them. You only really need one, or if you have different folders in different places and you can't have subfolders, then you can do that. And then what I did is I stored those two variables and then told Pad to write something to a file. In this case, the file is just called names.txt, so it's going to create a text file. This could be a rich text file, it could be a PowerPoint, it could be an Excel, whatever you want. I just made it as a txt primarily because I'm going to then grab it from there, put it into a spreadsheet where it's going to parse out all the names and everything. But you already know how to do that. So let's take a look at what this is going to do. It's going to go into this folder grab the files, store them here, go into this folder, grab the files, store them here, go into this folder, create a file named names.txt, which is what I told it to do, and it's going to append the data from files1, files2 into that sheet. Let's go ahead and see if this works. I'm gonna run it now. Now sometimes with, um, this kind of stuff, you feels like nothing happened because the file doesn't automatically populate. Um, that may be the case. So I'm going to back up and just show you what happens when I write to file with one and then the other. So I'm going to paste it. I think maybe, um, what may be happening is the file1 and file2 are not appending. It may be erroring out. So troubleshooting a little bit here because this might happen to you. So now I've just separated, so it's going to do read this right here, read that right here. Now let's try it again. Now it looks like it did something right. Okay, so it's supposed to be writing a file to this folder right here. Let's see if it worked. Sure enough, there it is. Ta-da! And all of the names of all of them from both folders, the Aaron folder and the Gary folder. In fact, actually, you know what, I think it did work the first time. It did it twice. Check it out. I'm going to delete this file here and reverse my changes to see if that actually did work. So by reverse by changes, what I mean is I'm just going to go into the write— I'm going to delete this one— and going to the write text to file and add files1 back in here. So it'll be files1 comma files2. And I think actually it did work last time, so I'm going to go ahead and run it again. So there's no, there's no names file here right now. I'm going to hit play. It looks like it did it, and there it is. Look at that, it did work the first time. Maybe I should have just been patient, it happened so quickly. Boom! So there's your list, super easy to grab the information out of here. If you're going to go into a spreadsheet where you're just going to put the information in there, you can paste it as text, or you could use the, the write to Excel. You don't have to write to file that's text, you can write to Excel. And even when you write to Excel, you can automatically remove patterns. Like, for example, you can say, all right, get rid of everything that has this so that all of that information is removed, since basically it's just the folder location and we don't really need that. You could have a formula that does that, and then another formula that creates a column for Gary and a column for Aaron. Piece of cake. Super easy automation. I mean, I love it. I'm literally using this all the time. So my problem was, just to reiterate, bunch of file names and I had to write them all down so that I can go do something else with it in another, in another document. It would have been a terrible, tedious task to go into the folder. Imagine if I had to do that, go into the folder, then grab each one of these file names one at a time. I mean, that's just, there's nuts, right? So that's the one that I played around with most recently that I wanted to show you. There's also The other one, which is the actuarial directory. Now, this is going to work for just about any directory. Um, the idea here is you find a directory that you have a login for, whether you have a, you know, membership, it's password protected, whatever it is, but it has essentially a list of people in it, and you can extract or grab the information from it. So first thing I need to do is search the directory so that I can qualify what I'm looking for. Let's say that I want to focus in, of course, United States, and I also want to add some other criteria, like perhaps job title would be senior actuary. There we go, it's even got that as a choice. And in what industry? Um, let's say I want insurance. And let's say that's fine. We'll start with that and see what happens. Okay, there we go, 58 results. So here's the directory. Now there's actually a nifty little choice on this website, but this might not always happen. You might only have a view like that, which are what they call card views. Doesn't matter whether it's a card view or a list, either way. The point is you're going to click through to one of them, and now we have the first profile. But notice that right up here it says next. So we actually have a pagination link that we can use just like last time. So what I'm going to do is I'm going to run the robot and then I'm going to go back and share with you what it actually did. Um, so you can see how easy— literally it took me like 5 minutes to create this— how easy it is to create this for yourself. So I opened my preconfigured flow instructions basically. And look how easy it is, just 2. So it's going to go to Chrome and find the foreground window. In this case, it's actually going to pretty much go to whatever browser window you point it to. Now I could also have this actually open, but then I would have to have it come into the fields and put in the search. I already did all that and I already know what I want, so I'm just going to go directly to this and then it's going to extract. I'll show you that in a minute. So watch as this goes. There's 58 of them. Before I run this, I'm actually going to set a limit here. So let's do— let's do 30. Now let's do 20 so that it doesn't take up too much time. So it's only going to do the first 20. All right, here we go. So now the robot is running and you can see it parsing in the background. You see it kind of running through its thing. And in the foreground here, it's clicking on next, grabbing the information, going to the next one, grabbing information, going to the next one. And what I've told it to do is get the name, title, some other information, uh, email address, phone number, and, um, I think one other thing. I forgot what it was. But you see how fast this is? It's, it's practically doing like 2, or I wouldn't even say almost 3 per second. Uh, right now it's at 36 seconds. 40 seconds. Looks like it's done. So 45 seconds roughly, and it produced the file with those leads. So 20 leads, let's call it 45 seconds, roughly about 2 leads per second. Looks like something like that. Hopefully my math's not terribly wrong. Now I did predetermine these fields. So I'll show you what they are, but this basically is the name, the title, the company, the address, city, state, zip, email address, phone number, and then some designations. That was the other one, their, their certifications. Basically it puts that all into Excel. So how do I do it? Super easy. Just like before, all you have to do is tell Power Automate Desktop what data you wanted to grab. And you can see here what data it's grabbing. So it's grabbing the name, skipping that, then the title, company, address. In fact, I broke it up into the street address, the city name, and the zip so that when it parses, I don't end up with like a giant chunk of an address. One thing I forgot to do is I didn't add the country, and, uh, that's because I chose United States, so they're all going to be United States. If you're doing this where you're going to want that, obviously you want to capture that. Then the designations. I also didn't grab some of the other stuff down here, but basically if I wanted to grab any of that, I could just simply right-click on it. Remember how we did that? And Extract Element Value. And this will be the industry, so you'll know to label that as industry. And you can do the, um, area of practice. And there you have it. So that's it. If you want to sort graphical information, you can do that. You can store the map or the image. For example, if you want to have this— the little headshot images, although most of them aren't really present anyway. So that's it. It's working because of the pagination here. I'm going to go ahead and cancel that and show you exactly what happens. Actually, that's it. I already showed you what happens. I showed you the fields. So that's all that it's doing. Pretty cool, huh? It's super fast. Didn't take me very long to, to actually create it. Once you have the page, right-click, right-click, right-click, and that's it. Don't forget the two things that are going to trip you up. Number one, starting a new window. So I'm using attach to a running instance, which means the Chrome window that I have now, and I chose Chrome from the actions on the left-hand side. You choose the browser, you have to tell it which browser, it doesn't know until you tell it. It's got all 4 major browsers here. So I choose Chrome and I say attach to running instance and use the foreground window. The other choice would be to actually pick a title, which would look for the, the title of the tab, in this case the Actuarial Directory, or by URL where you would specifically put in this address. The reason I don't want to do that is because if I do that, then I still have to add the, um, other search criteria. So I just went with use foreground window. Do these two things until you got kind of master it, then you can switch to running some of the other automation. There was a suggestion in the groups about, uh, running this pervasively as a, as a pervasive search. I'll tell you what that means in just a minute. So I know how to do that, and I want to show you in case you wanted to do that. And then, um, once you launch the new Chrome and append to the browser, the next thing is to extract the data that you want, and that's it. What I did not do is manipulate the Excel or anything else. So the end of this process is an Excel file that has no name and hasn't been saved. If you wanted to run this as a pervasive search, you would schedule a wait period. Let's say, for example, um, I don't know, you would wait, uh, days or hours. Let's say you want to run this every day at 10 in the morning. So you would choose the parameters and say run this every day, or wait 24 hours, then run it, wait another 24 hours, then run it, and it'll run pervasively. And let's say that you want it to append. So it's going to run it and try to find new information and append. Here's the trick though. Once it creates this Excel instance, you have to have another command if you want this to be saved that actually manipulates the Excel file. So you want it to save the Excel. That's what you're going to do here. So it's going to grab the Excel instance and save the document. You can even specify where you want it to save and even give it a name. You can also give it a variable that's incrementable by a certain amount. So for example, You can have a document path, that's the folder whether you want the file stored, your project folder. And the file name will have a variable in it that's identified in the flow that perhaps is the date. So you can say, uh, get today's date as the command, store that as date. Then over here you would have document folder, document file name, and append the date or prepend the date so that it's got May 27th, May 28th, May 29th, and so on. You could do that so you would have different files, or you can simply continue appending to the same file. And instead of doing that where you save different files, you save the date as one of the cells in the spreadsheet. So now you have one spreadsheet with all of the dates generated, and that's how you turn this into a pervasive search. It will be constantly running every time on command, whether it's once a day or whatever you tell it. it'll execute, do the new search results, and bring back and append them to the spreadsheet and bring them back. That wouldn't be as useful for the directory because this is a pretty static directory, but where that really does come in handy is for that LinkedIn search, the one that we talked about where we created a search that brings up content from Bing and does a scrape off of that. So in that case, it would be— waiting for this to load here— it would be the entire workflow here. Um, by the way, sidebar, I'm going to tell you in, in just a minute how to do this, but sidebar, Glenn sent me an email with instructions, uh, 3 simple instructions on how to do the append to loop without actually having to create a formula to parse the columns. Instead of using this loop formula, we use Write to Excel with a different set of variables and put things into a table. So it's actually much simpler. And what that saves you is you don't have to calculate how many rows there are. It just simply adds a new row every time. So it basically adds to an empty row rather than counting how many, then, you know, adding to the etc., right? So There's an easier way to do it. Since we already solved it, I'm not going to do it again. But what we would do here is we would add the wait here and say, okay, wait for a particular time period, then run this, parse all the information and append it to the spreadsheet, which it would normally do, and then save it. So the only thing we did not do here is we did not save the file. So let's go ahead and save. This Excel spreadsheet as— we will call it— actually, you know what, I'm just going to choose the, the file that it created last time. Well, hold on a second. Before I do that, let's go ahead and run this once, and then it'll create a file, and then I can save that. But That way I'm not having to go back and forth. So I'll run it, it'll make a file, I'll use that file and then add to it. So let's go ahead and run it. If you remember what it does, it's going to prompt me for a couple of pieces of information. The first one of those, if you can't read the screen, is simply job title. Oops, where'd it go? Sorry, I was checking on something and I got distracted. There it is. Okay, job title. Let's go ahead and put in here transfer pricing. And for next question is company. Let's say Grant Thornton. I hope I spelled that correctly. I don't know why I chose a difficult spelling. And then the last one, the third one— these are all prompts that we created last time— is the target city, which could be whatever, you know, you can put in. Atlanta, you can do Atlanta, Georgia, whatever. It's going to take whatever input you give it. Let's go ahead and do something like, um, Austin, which is nice and simple. When I say simple, I mean, uh, if I was to do like Rochester, I probably would need to put Rochester, Minnesota, or Rochester, New York, because there's so many Rochesters or Springfield or whatever. But Austin, you know, is— there's just the one Austin as far as I'm aware. So hit OK. It's going to grab a new window, put in transfer pricing and Grant Thornton, and begin parsing the information and putting it into— oh, look at that, same thing happened last time. It, um, it forgot to save my settings where I changed it to, uh, 50 rows at a time. And I am logged in, so it's— I gotta admit, a little frustrating that it keeps doing this. I don't know why it keeps doing this. So what you have to do is you have to go into your settings, and you shouldn't have to do this over and over again, but in More Over here. Oh, it did save it. Show this many results on each page. Okay, so why did it not— hmm, why did it only do 3? 1, 2, 3, 4. Didn't show me any of the others. Is that because there's only 4 people? Well, that's actually possible. Maybe what I should do is use Uh, maybe KPMG or something. Let's see how that works. Okay, that gets me 6 results. All right, uh, Deloitte. Why did you pick such an obscure skill set, Shelly? All right, let's do Deloitte. Scrapping the project because it apparently did not get very good results, and I'm going to start over once again. Transfer pricing. Then Deloitte, then Austin. And it's going to do it now again. Hopefully this time it'll get 20-something— 21. There we go. And they're all on one page. Good. Now I actually have something to show you. The other one only had 4 results, so this might happen pretty quickly. Let's take a look. It's trying to extract the data. Once it does that, then you'll see it running through the loop. And there it is. Only got 20, so there must be one that didn't have the necessary information, but that's okay. So we have this file now. Let's go ahead and save it. I'm just going to save it to my, um, my desktop or somewhere where it's really obvious. So, you know, because this is something you're going to be checking. So again, the idea is you're going to be saving this or running this every day and saving additional, you know, data on it. So we'll call this transfer pricing. I'm going to call it Transfer Pricing LinkedIn Scrape, and it's right on my desktop, nice and simple. I have saved it, so I can now choose it. When I save the Excel file. Now the other thing you can do, by the way, is you can actually open the Excel file and then write to it and save it. You can, you can actually grab a specific file if you want, but for now I'm just going to go ahead and append to this one here. Save document as document path. Desktop. There we go. Where'd it go? Trans— I know I put it in here somewhere. Oh, there it is. Okay, found it. And there we go. So the next time this runs, it's going to save to that file. Now, the only thing that's missing is to add the wait. And of course, I can't show you that because it would have to wait 24 hours. But you would basically take the wait process here, put it up here, and then choose a variable for the parameter. You can, you know, set a variable if you want based on the date, or you can just simply give it an instruction. This basically is seconds. So basically multiply times, uh, 60, and again times 60, that would be— and then so 60 seconds in a minute, 60 minutes in an hour, 24 hours a day. So 60, 60, 24— I'm trying to do the math in my head and it's not working. I'm just going to use a calculator. 60 times 60 times 24, 86,400 seconds. If you wanted this to run every 24 hours, you would do 86,400 seconds. So it'll wait a whole day, run the whole thing again. And this is what's called an unattended robotic process. Unattended because it's going to run at a specific time. The thing is, with Power Desktop Automate, you have to have Power Desktop Automate open, which is fine. Um, just don't close it out of your system right now. I'll show you what I'm talking about. I'm going to close the robot, but I'm not going to actually close Power Automate Desktop. It's still running. This is what I'm talking about right here. You want to have this application continue to run, otherwise it won't, uh, step— the, the process won't start. This Power Automate Desktop has to be running, so just minimize it instead of closing it. If you close it and it's not live in your system tray, uh, the process will not run. You'll see if it's a continuous process, you'll see here it says running, and it's just basically waiting for the seconds to expire and that'll run again. So that is how you do it. Um, the only other thing I wanted to show you is putting it on a SeekOut Drive. We don't have a whole lot of time, but I'm going to see if we can get part of the way there. So let's open our Bing LinkedIn with user input. Oh, wait a minute, I just realized something. Well, sorry, I just realized it might ask me to log in, but it may also may not ask me to login. So, all right, we're going to open this, uh, flow or pad workflow, and I'm going to add another step over here, which is to take this spreadsheet that it creates and put it over here. So the next step after it's finished the loop and created the spreadsheet, the next step is to tell it to go over here. So you're going to launch another browser window You can, um, sorry, start a new one, a new tab, or whatever you want, but you still have to choose the Launch Chrome even though you already quote-unquote launched Chrome. So you can do Attach to a Running Instance and, uh, By URL. In this case, because it's going to be an automated one, it's actually going to go to your dashboard, and we'll call this Browser seekout. Maybe if you have other variables, you might want to think about how you name them and come up with a naming convention. But right now it's going to go— oh, where'd it go? Didn't I just put it in here? Oh, did I put it in the loop? Yeah, I did. I accidentally dropped it in the loop. This is supposed to go after the loop. All right, once the loop is finished, then go and launch a SeekOut page. Okay. So it's going to go over here and it's going to launch. What we need to do then is go to a project, and I should have done this in advance, but I didn't. I'm going to call this upload. So there is a project called upload. You can actually direct it to here. I'm going to test that and see how that works. The only question I have about it is it might ask me to log in, but you actually can program login. There's a way to do that. So let's see if this will accept that variable. Oh, it will. Okay, let me run from here and see if I can run from here. What I'm doing is I'm just testing that little segment to make sure that it actually is able to open Sorry, I don't know if it actually did it, so let me try that again. Run from here. What it should do is open up a tab or add a tab and go to— aha! Fail to assume control. Okay, was I not pointing? I wasn't pointing, wasn't it? That's what happened. Okay, there we go. Now it should do it. I had to tell it which one is the active one. Nope, still didn't do it. Okay, that's all right. Rather than trying to troubleshoot, this is a good time to go ahead and launch a new instance and go directly there. So rather than trying to goof around with, um, appending or attaching or whatever, it's literally going to just launch a new instance. So let's test it and see. This is one of the nice things I like about— you can test any of the subroutines independently without having to run the whole thing. Okay, so it opened and it went there. Now is the million-dollar question: is it going to ask me to log in? It didn't. Okay, great. So now what I want to do— well, I want to upload candidates. So that's going to be the next thing. I can have a web automation that clicks on that link, which is click link on page. And the web browser instance will be SeekOut, this new one that we created. And the UI element will be whatever we choose right here. So it says hover until it's framed, then press Control, left-click. And that should add it. There it is. Done. So now I have opened a new window. And added. Wait, why didn't it add it? Huh. Try that again. Add new UI element. Control. Left-click. It's added. Okay, I'm going to assume it's there. For some reason, it doesn't seem to be. Saving it. So that's as far as— oh yeah, it didn't save it. What the heck? Okay, so it's asking for the UI element. I'm telling it which one it is, and it wasn't put— now it's there. All right, you know, I didn't do anything different. I don't know why, like The first 2 times it was blank, and then the 3rd time it just appeared, but whatever. I probably wasn't doing something right. So now I'm going to save that and— okay, now let's test it from opening a new window. I'm going to close this one and test from here, or run from here. It should open SeekOut to a project called Upload and click the button to upload. Let's see if it does both of those. Look at that. Now I can tell it to click another button. So simply click link on web again. I could have just copied it. But it's still the same browser. And now I'm going to do a new UI element, which is— I'm going to look for that code to appear. Okay, it appeared. There you go. I guess that code wasn't appearing before. All right, it's now going to click the button that says Upload. Now when I click this manually, what it does is it opens up a window and I then have to navigate. So what I'm going to have to do next is I'm going to have to tell Power Automate Desktop to go to a particular file, uh, name basically, which is where this file is stored. Paste that in here and tell the— tell PAD to paste the URL here, then click the Open button, then click the Upload button, and then we're done. So, uh, first things first, I need to find the path for that. Where did I save it? Oh, I didn't save it, did I? Ha, I saved it last time, but I didn't save it this time. So let's go ahead and save it. Save Excel. Save document as, and we'll give it a document path. I'm just going to put it in the default documents folder and call it upload .xlsx. So that's where it's going to go. Now that I know where it's going to be, I can then tell PAD to browse to that when the, when the window comes up. So that's the step that we're on right now is I clicked on upload file and now the robot is here but it doesn't know what to do. So now I have to tell it to interact with a window UI. Let's see, system window, select one of these here. I know we're running way over. We're almost 10 minutes over. Shoot. Okay, you know what, I'm going to finish this next week, but we're right at the point where basically we're going to add an action that selects the window, then it's going to paste that URL right there, and then it's going to click Open, which will then— oh, I had— I haven't created the file, of course. So I paste the URL in here, that's the next element, then I'm going to click Open. I'm actually going to click Cancel now, which will then put it in here. Then I have to go back and activate this step again, which will upload the file, and then it's there. Now you can have another element that says 'Hey, I finished,' which could be something that sends you a text or an email or pops up an alert on the window or something to let you know that a project has been uploaded. You can have that set as an environmental variable that stores on this— not on the flow, but on the environment. And that variable perhaps could be a yes or no variable that says, 'Is there a new file today?' If so, then yes, sets it to yes. And then that yes Triggers a wait that's waiting on the variable becoming a yes. And then it runs a second flow that goes to the project and downloads the file and saves it to a folder. How cool is that? Think about it. All right. So I'm going to stop there because that could take us another 10 minutes to figure it out. And once we do have that though, essentially you start by giving it company name, job title, location, and it programs a pervasive search that runs every day and saves things to a folder, puts them into SeekOut. Appends the information, sends you an email or a notification, downloads the information, and saves it to a file, perhaps overwriting the file that you had before or putting it in a new file. It's a sourcer that's sourcing for you every 24 hours. Pretty cool, huh? All right, everybody, have a great weekend and see you on Shelly's Alley next Friday. Bye-bye.