Power Automate Desktop: How to Read Excel, Do an Online Search, and Write the Result Back to Excel

read excel data do an online search with microsoft power automate desktop scrape web results and write it back to excel it's so easy to do with power automate desktop let me show you how i have my data in an excel book called data this sheet is sheet 1 and we have our company and ceo columns each row is a company and i want to find a corresponding ceo with a google search and then i want to scrape the data and write it back here so for example the microsoft ceo i want to have it written in here the major ceo here and so on let's create it in power automate desktop and i'll show you how easy it is so i click new flow in power automate desktop and here i can call it something i'll say co web scrape it's very important that you open up power automate desktop yourself and do the operations with me in that way you will learn the most first we will create a simplification of the use case that is we will just create a static run to find one of the ceos without the excel i think and then we will add in the excel and make it dynamic so we can solve the whole case we would do it step by step and i'll hold your hand so don't worry just click and type everything i do first i want to launch a browser so i find a launch microsoft edge and drag it in that's it here i can choose to launch a new instance or i could choose to attach to a running instance i will launch a new instance because this is the beginning of my robot i have nothing initialized so i have no browsers open i'll choose to launch a new instance then i want a url i can either choose to create a variable or just hard code in the address i will hard code in the address for now but it's often good practice to make variables i go to my microsoft edge take the google here because that's the address where i want to go go back to power automatic desktop and paste it in here the other settings we won't touch i just click save now i can run my automation you have built a robot now that can launch a browser let's try to click run up here you'll see that we open up microsoft edge fine this is working so we will take on the next step and the next step is to send some searches in here now you can see we have two instances open so always whenever we build this robot we will be doing the runs a lot because we want to test our robot but make sure you just close them down while we do it we will need one because we will need it open because we need this field that is where we want to type something in so if i go back to power automate desktop to type something in search for a populate text field and here you can choose between two form filling that is for applications web form filling that is for yeah web form you guessed it drag it in here we need to refer to a web browser instance and you can see here that there's a browser in percentages sign the percentages that is just stating that it is a variable and it's called browser and this variable was created automatically up here in the launch new microsoft edge so whenever power automate desktop recognize that you are opening this browser it creates a variable for you that is fine then we need a ui element that is a ui element is simply just one element in either an application or a field in the web page and all the things you can see in your windows that one all those things have addresses which you can target here we just click this drop down you can click add ui element this one pops up here you can navigate around the page but we will have it here but you can also do the things here nothing will get saved unless you press ctrl left so we want to specify where do we want to send our text to we want to send it to this search field so if i press ctrl down i left mouse click you can see that we now have it over here i click done we have a place to put in our text because we want to make it static i want to just search for the microsoft ceo first before we create the full solution so then i just type in microsoft theo now i can click save and one thing you might want to see if i open it again i can double click to open the activities this emulate emulate typing this is almost the go to practice it's default it's a lot faster than normal typing which will replicate what a human will do but sometimes we have to disable it to have normal timing but for now it's very good i just click save we can test our automation and as i said you saw before that we needed this window to be open because we needed to target the search field but now we can close it because our automate let me close the other one as well because our automation will open a new window so if i click run i will launch the edge and i'll do the search that's fine i just need to actually do the search either click the button the search button or press enter so go back to power automate desktop i just want to send an interclick to do so find send keys here and drag it in what do i want to send i can send in all sorts of keys i can just type something in i can even type type in the short for the enter that is return in curly brackets or let me show you an easier thing click the drop down here with insert special keys miscellaneous and then find the enter that's it we created this one automatically now click save so now we're doing our search and let us go back and close this because when i click run the reason why we do all this that is just to test our automation so we don't have to make 20 steps and realize that what we did was wrong now we are here and what we want to work with is this text so this also makes sure that we are at the place where we want to be so if i go back to power to make desktop now i want to web scrape the satya nadella that's this one here so back to power to make desktop to do so i'll just find and extract data and i'm sure a lot of you came here to see how you do web scraping this is how you do it so i find it extract data from web page again on the web data extraction and drag it in here i'm in the browser and now i just need to open up the browser where i want to scrape the data from well that is why we keep them open and we make sure that we are navigating to the right place this one opens there outside the window of this video that's it now i can scrape all the data i want all the data i want on this webpage is actually possible to scrape what do i want to scrape i want to know the ceo of the microsoft well he's here satya nadella so i just can just hover over here i can right click then extract element value and here i can see some things i can scrape i can scrape the text or for example the url attached to it all and some other attributes i want to just have the text satia nadella you can now see that we have lines dotted lines around it that is fine satya nadella he's here i click finish this one will produce a variable with that name in and it will store it into the data from web page if i click save here let's do a simple log message i'll choose a message box i'll search for a display message that's what was a little misspelled i'll search for display message and drag it in usually i use the display message for log messages or to pause my robot working in my robots while i work at them so in the development phase usually i can choose a title i will just have the results if not that not that important we won't use this either just just to see that our robot works to get the variable click the x here and what do what variable do we want we want this data from web page from up here so click here double click the data from web page it automatically gets in here you can see it is available because of the percentages sign then i click save now we created an automation that do a google search and open the browser do a google search scrape the results and present the data that was almost half of our task today if i close down the browser i run our automation again we will just see that this works it will take a few seconds and outside the window this message box pop up great we are done with the first assignment now we got the intuition we will add in the excel module and we will make sure that it is dynamic let me close this one let me repeat what we're actually doing so i want this excel sheet is stored at my desktop in a folder and i want to read it and i want to do google searches on all these company names and find the corresponding ceos from google and make sure to write it in here to do so let me go back to power automate desktop first thing we want we want to launch excel so if i find a launch excel this is just to work in excel so here i drag in this launch excel in the start do i want to launch a blank document or do i want to open up a document well here i can either choose to select a file by the path selector or i can just find it at my desktop mine was here this is a desktop folder it's called data and i can shift right click this is a good trick shift right click that opens up the copiers path so click here minimize this again and just paste it in here ctrl v remember to move the quotation marks in the start at the end of course you could also use it here that's it do we want to have the instance visible we could choose to have the excel open or not while we work usually i will just have it closed i don't need it to be visible so i just untick this per default then i click save now we open it an important thing is to close it again by the end of our automation otherwise it will get locked it might not seem very important but it is trust me always at a close excel otherwise you will pull your hair out of your head whenever your excel sheet got locked so here i will close the excel instance that was the available that got produced up here do i want to save or not well that's fine we want to save to make sure that we have every data we want then i click save so now we open and close it we want to read the data before we can work with it so to do so let me show you the excel sheet and to do so we need to find what range this is in and we will first we will find the first three column that is this one and the first three row because we will use that one so the first three column this one the first three row is this one we can find it with power to make desktop and to do so find a get first free column row that is this one drag it in beneath the launch excel here this one will just produce two variables we don't have to write it with any more info it will read from the excel instance variable that's it we have found the first three column in row now we will use those two things to read our excel sheet into a data table and to do so we will find a read from excel worksheet that is here drag it in because now we will take advantage of the first three column and row we will read from the excel instance what do we want to retrieve we want to retrieve the value of a single cell no we want a range we want the values from the range of cells then we want the start column we want the start row now we need an n column and row and we actually got the end column in row almost by our first three column because the end column that is one before the first three column and the end row that is one before the first free row so to do so just click this variable here that is the column that will be the first three column click this variable again first free row now we just need to subtract one from these two to do so inside the percentages same minus one inside here same minus sorry that was half minus one that's it we can click save but before we do so always check the advanced here because if i go here i can say first line of range contains column names it does it it contains this company and ceo that that is that are headers if we don't specified here we will have uh we'll have an arrow in our workflow it will not perform the action that we want it will have skewed data so if i click save here now we have read our excel we can start by iterating through each row of the excel sheet to do so find a for each here that is a loop and where do we want to place it place it just beneath the launch new microsoft edge this for each will run over our data table one by one until the end so what value do we want to iterate well we want to iterate the output variable that we got from up here excel data that was from the read from excel worksheet so if i click here excel data double click it it produces a variable called q an item and this q an item that would be whenever we iterate through this one will be the q and item this row then this one then this one this one this one and this one so a lot of q and items that's fine i click save here we have our for each and start and end the best practice is always to go to the end of so whenever we make these transactions we have eight transactions here the eight google searches we want to make sure that we have the same place for each iteration so we know that we can perform the actions without an error so we want to be at the front page of google to do so i will find a go to web page that is here i drag it i want to be in the browser that's fine i want to navigate to a url i will just type in the google here we could have used available if you wanted but it's fine for now that's it so we navigate to google then we want to do the search that is the public text on web page so if i move it up here here we make a static microsoft ceo so this one will just give us seven microsoft co searches we want to change that so it will do searches from the excel sheet let me double click here so instead of the microsoft i'll delete that now i'll refer to my current item that was my cue and row so i click here then i'll say cue an item that is this one but i want to refer to this one will just refer to everything here i want to refer to the company column that was the that was where i want to do the search so to refer to a column because i know i'm the current item that is each one of these rows i just wanted to refer to the company column and to do so so inside here in the current item make some hard brackets start and i always like to make the end as well then inside it i want to make a single quotation mark and i want to make another one and inside that so now we have everything around it in place we can write whatever inside i just run to write company this one targets the company column of the cue and item and then it says ceo that's fine we save it then we want to do the return key well that was what we did before as well we also want to extract the data so let me drag that up that's fine instead of displaying the message we want to write it to excel and to do so i'll just find a write to excel worksheet here and drag it in here in the end now a little tricky part comes but it's not that tricky so just make sure you pay close attention we will write to the excel instance what value do we want to write we want to write a data from web page which we just extracted so if we go over here we can take the data from web page then we want to write a specific cell the column well we want to write into the second column that is this one here but we need an integer as well that is whole numbers for the row and we don't really know so we will create a variable that will tell us so if i just click save here this one will give us an arrow that is fine we will fix it in a few seconds so here i'll set a variable just before the for each i'll set a variable that holds the start value two and then we'll increase it by one whenever we have done an iteration of our for each so here i will say set variable and drag it in here so it will be called new var that's perfectly fine feel free to rename it if you want and that one will have the start value of two that will be our row number two because we have headers in our excel so this one it will say two and then in the end of this loop here we will have an increased variable that adds one to this so if i go up here i'll have an increase variable drag it in in the end the variable name that was the new bar so click the x here then find a new bar in the end and increase by one that's fine now we can use this variable here in the write to excel worksheet so we don't have an arrow that is this one here click the x find a new var double click it and we will use that so and we can because we write we are writing to this excel worksheet here we can choose to delete this display message to do so just right click click delete it's outside the window but it's all the way to the button that's here we have now created our automation let's just save it to make sure that we actually have it if something breaks and we are saving our flow and then we can run it then we can run the automation we click run power to make desktop will make seven iterations that will be our seven google searches and here you can see that we are navigating to the google.com to make sure that we're doing the google search from a blank google page which is important it's very important when you build the robots also when you work in applications always make sure that for each iteration you do go to the front page here we go we're done if we go to power automate desktop we can see we're done here i can drag in the data here i can open it and you can see we scrape the data what happens if i add a row with a non-existing ceo so if i go here and i can say insert then let me just delete these ones here then i can just say company on a sensor org like this and i choose to save it so if i run it now we will have an arrow here because unless an org won't ex will not exist as in ceo let me show you how to counter that so go to power automate desktop and while this will give an error because that company doesn't exist at google our whole automation will stop and to have prevent that we will have an unblock aero this is a very important concept of power to make desktop and rpa development overall what do we do when our robot fails well this is not a fail in the system our hardware our browser is working fine sorry our software but we will will have an error in the data that's the business exception and that is perfectly fine so if i drag this unblock arrow and drag it just below the send keys here we can give it a name i'll just say ceo don't doesn't exist call it whatever you want this is just a name to make it easy visible what it's doing so this block here whenever something happens it will go to the end of the block that is the exception handling mode that is fine i click save now i have this unblock arrow so where can i have the block where i can have the error that is in the extract data from web page i drag this one in and i will also have this write to excel worksheet because if we have an arrow here i want to jump over this so i'll just drag this one in so now i can run it this one will get handled by my arrowhead i will make a lot more error handling just make sure you subscribe to the channel to get all the new automated desktop videos that will come in the next month and going forward so now i can run it and let me show you what will happen because if i click start here we will now have eight iterations we will also open up an ascension org which is by the way my handle on twitter instagram linkedin whatever just go you can go search for it and follow me there now you can see it this one doesn't exist but our robot will run it doesn't scrape anything because it can't find a ceo but it is not giving us an arrow that is fine here we go we can inspect our data so if i open up the data that's it you can see here now we just jumped over on ascension.org and didn't write anything we'd also write an error message if you have any questions just type them in below so what you want to do now is to take the full power automate course that will teach you everything about power automate you can click in the video in the middle to get to it

Channel: Anders Jensen

Views: 48,722

Keywords: power automate desktop, power automate desktop tutorial, power automate desktop web automation, power automate desktop examples, power automate desktop ui automation, power automate desktop ui elements, anders jensen, power automate desktop read excel, power automate desktop scrape, power automate desktop web scraping, power automate desktop search online, power automate desktop read excel search online, power automate desktop save to excel

Id: 1WezrCQlpBY

Length: 22min 25sec (1345 seconds)

Published: Tue Dec 28 2021