Save Web Data to Excel - Power Automate Desktop Advanced Use Case

you'll solve a real-time Microsoft power automate desktop project with me today we are automating Excel and browsers and I'll make sure you learn all the best practices around RPA development to learn the most open up power automate desktop and do the programming with me if things goes too fast just pause or rewind the video it's designed for that my name is Anna Jensen let's learn some Microsoft power automate desktop let's start by downloading the Excel file for the lesson the full address of this page is in the video description scroll a little bit down to description and then click download the Excel file for the lesson that will take you to my OneDrive now click these three dots and then click download now we have downloaded the file let me just minimize this and open up my downloaded folder I want to create a project folder and I will do that on my desktop it's not recommended to have your data for the robot on your desktop but in the developing phase and especially when I'm about to show you a lot of things it's very convenient we will just need to remember to move it when we move the robot to production not a problem so I right click here and then I say new folder I'll call mine project like this and then I can open it here I will put in the downloaded Excel data like this now it's here let's open it and inspect the data we have seven sheets the first sheet is called an Essence nog it is some basic information about my social media then we have the one company's data that is for a very basic exercise that we did last week you can find it linked to that video in the upper right corner you don't need to go there now you can find watch by this you will learn everything here so we'll start by scraping 10 companies and these are CVR numbers that is vat numbers in Danish for each CVR number I want to extract this information on a website I will show you in a second as the tutorial progresses these challenges will be harder and harder I build in some errors I added some data that will only access by clicking on other elements and finally I will show you the power of a rest API call at the last lesson so hold on just do the exercises with me I'll explain everything and all the best practices and we will start at the two companies 10 data sheet you can see the names down here so I'll close this let's grab the link to the project folder so we can use that in power automate for desktop you can go to that folder and shift on your keyboard right click on your mouse and then choose copy as path you will only see this if you press the shift key on your keyboard at the same time as you press the left button on the mouse so copy as path now I have the path to this folder let me show you my power automate for desktop flows as you can see here below I have two flows I have a vat lookups flow and then I have a demo that lookup status is where our project actions will go and then there's a demo here I will show you all the nice things in little chunks so we don't mess up our full project but anyway let's go to that lookups let's start by taking in the path for the project and using that as a variable so I'll find a set variable out here in actions then I'll drag it in here I will call this project half like this and then I will give the value by control V here in the value and then remember to delete the quotation marks so now this variable whenever I refer to the project path this will give me this value it has the extreme power that let's say I move my project folder I want to move it in production to for example a network drive then I can just change this variable here imagine that I use this path a lot in our flow then it will make be very hard to remember everywhere or I will spend a lot of time doing so so create a variable for the project path now let's open up the Excel sheet so I go up here and then I'll find a launch Excel drag that in I will open up a document that was our data so in this drop down I take and open the following document now the clever thing comes in because because I want to refer to this project path variable up here so I can just write it in here this one will give me the project path but let me show you a much cooler way to refer to variables so if I delete this click this little X here and now it's here I can either click on it select or simply just double click that will be here but the project path that was only to the project folder so I just need to add a backwards last like this data xlsx this will open up my data xlsx in an instance so I can work with it do I want to make it visible not really it can be fun in the beginning to see the robot Works through the rows but it's also a bit unnecessary and heard performance a bit I'll just untick this open as read only no I want to write data back to the sheet scroll a little bit down here you can see a variables produced called Excel instance we can refer to that variable later in the flow whenever we want to refer to this instance that opened up the document but then I can click save we also want a close Excel like this so close Excel and that is very important because in power automate for desktop if you forget to close your Excel sheet power automate for desktop will lock your Excel sheets for editing that's very annoying just start by referring to the Excel instance here that was the one we opened up here and then choose to do not save document we will save the document later but for now let's just leave it here in fact you can actually run the robot now so click run up here and that will just launch and close Excel nothing will really happen but you can see it worked that you typed in your path correctly now let's add some things to it because let me show you the data here we want to make sure that we are on the correct sheet power ultimate for desktop will read the last opened sheet if not specified anything else so that will be this one here or imagine I was here these two sheets look a bit like each other we wanted two companies 10 data for the first exercise so go to the sheet then right click rename Ctrl C to copy this name we always copy things when it comes to names or I'll try to do it because then we will reduce the risk of Eros now let me close this again so in power automate for desktop find a set active Excel worksheet like this remember to move it between the launch and close because actions in power automate are taking sequential that means we first set the variable then we launch set the active sheet and close you can see here that we can activate the worksheet with the name I control V take the two companies data like that now we made our robot robust let's read the data into two companies 10. data so I'll find a read from Excel worksheet then I'll drag that in after the set active Excel worksheet I want to retrieve all the values in the sheet so in this drop down choose all available values from worksheet like this under Advanced then just expand that and then say first line of range contains column names we have headers that was the CVR name address and so forth as the headers so I take this variables produced that is Excel data we will store the data that we read from the Excel sheet in a variable that is a data table available call Excel data I will show you what that is about I click save then I can click run and then we will inspect this Excel data variable so if I go over here to variables in case you don't see it like this or maybe some other pain is open and you can always click this little X up here that will open up the variables these here this shows the the values of the last run of the robot we want to inspect the Excel data so go over here to 10 rows nine columns double click that will open up our data here you can see our data got written in it looks really really much as the Excel sheet but this is a data table you can see that there's not a lot of difference on an Excel sheet and a data table but a data table only exists in the memory of the robot that is it only exists when the robot runs we call that runtime so it will get initialized here in the robot that was where we read from the Excel worksheet and in case we don't do anything to this data table this will be last so we need to remember to save the data and here we want to add the data to these empty cells and then we want to save it now you can ask why do we use data tables and not just write in Excel sheet directly we can write into Excel sheet we saw that last week but it's much faster and much more reliable to use data tables you can also see here that the first row is referred to as the row 0 then 1 2 3 4. that is because in programming we have zero indexed variables that is the first item is always zero then one two three four it's a little bit confusing because in Excel we are used to that the first row is one but don't worry it's straightforward after this exercise you will not think about it so then I can click close now we have read it we already have five actions here and we're going to have a lot more best practice is to use subflows to make maintains easier imagine you or even worse your colleague coming back in six months to update this flow this will take time to understand and there's a risk of Arrow if the colic doesn't understand 100 what's going on so let's create a subflow to make reading this easier There's no practical difference so even if we make a long list or a subflow as we see now the robot will perform the same we start by creating a subflow here called initialization and that can hold the actions that come before the actual web automation so if I go up here to subflow then I can click new subflow I will call this station like this and then I can click save you'll see that this open up an initialization that is a subflow we still have our main that is what our main project is called that is where the robot starts and then we have an empty initialization if for some reason you don't see it but you have created it you can always find it over here in subflow here double click it that will open it the clever thing is that here initialization we will have all the actions that came before our process actually we're running that is performing the searches so if I go to main I want to take the set variable then I hold Ctrl key in click the launch Excel click set active Excel worksheet and read from Excel worksheet these are all actions that is performed before we do the actual searches so I can click X here that will create an arrow that is fine then I can go to initialization Ctrl V that will paste them in now I just need to call this subflow here from the main so if I go over domain and up here I can find a run subflow and drag it in I want to have it in the start and here from this drop down I can choose my subflows I only have one that is initialization and I can click save I can try to run the flow again you can see that we just move inside the initialization perform the actions there those ones were the same as before and then we close Excel nothing has really changed but this main has gotten a little bit easier to look at and imagine we had like 50 actions then it would be a lot easier let's create two more subflows that is the subflows that we want for our process so up here I click new subflow I also want an end process like this that is where all my actions after the initialization comes so if I go to main I can take this close Excel control X then Ctrl V paste it in the process I just need to call this in process as well here from the main Dragon another run subflow and now I can find the end process finally the actual process we will call that process so go to subflows then new subflow call it process that's it we will have it empty for now but we will start filling in in a little while now go to main Brack in this run subflow B3 initialization and in process click the drop down and then take process click save if I click run again then the same process will go on we will have the initialization the process and the end process now we just have it nicely here so we did some of the initialization let's look at the actual case because if I go to the case page scroll down to description then find this link https data CVR viercd this is Danish vat numbers everything is in Danish but we can click English up here that will take us to the English page here I want to do that lookups and let me just find one from the Excel sheet here so I'll open that go to my data I just pick the first one so copy that one over into my browser and then paste it in Ctrl V you can see here here we have some data and these data for example the address and the postal code this matches these empty columns here in Excel so now we can do these searches based on this that number CVR and then we can web extract these information and write it back to excel the first exercise that is these 10 numbers and no built-in traps we can do it very smoothly but we will start adding problems to this case problems that you will see in real life so first let's just launch a new browser I have my course data here in Edge and I really want to use Chrome to separate these because we might have want our robot to close all browser instances so that's why I keep two browsers you could keep one for this video that could be an edge and another one in Chrome or so forth so what we want to do is First grab the web address let me move one back it is this one so Market control C now we have that we go to the vet lookups move inside initialization I want to set another variable with this URL so I'll have a set variable and drag it in below the first I will just call this URL and then I will control V in this address like that I also want to launch a browser and since I want to open all programs here initialization I put it in here and not in the process itself so go over here and then pick launch here you can choose between four different browsers Internet Explorer Firefox Chrome Niche I prefer Chrome or Edge but you can use fireplacs as long as you promise you don't use the Internet Explorer so drag in this chrome like this here we will launch a new instance and then we will go to the URL that we just saved in this variable up here so if I click this x here that will take me to the UL and I can click save we also want to close the browser so in the end process we'll find a close browser yep and drag that one in that will just close the browser instance that we have just opened but because I really want to inspect the data I just right click here and click disable action for now you should do the same so we can inspect right click and then disable that means that you can see it's grayed out until you enable it the robot will run yeah ignore this action let's close our Excel sheet and then run the robot if you for some reason have problem launching Excel it might come up with a workbook retrieval Arrow then click the video up here in the right corner to solve Excel problems that is a common problem here in power domain for desktop that's it we opened up the correct browser so that was Chrome and navigated to here now we want to click the English because we want to be in the English session section sorry so I move over here and that is actually in our process so we'll find a click link on web page so click link on web page and drag that into process we are still working in the browser instance and then if I click this drop down and then I click add UI element and here you can see the UI elements on the web page get red in case you haven't installed the weapons extension you need to do that you can find a video guide to that by clicking the video in the upper right corner but we already did that so then just find the English button then press Ctrl on your keyboard and click it that one we have now created that UI element for the English button and I can click save so that is the only thing we have in process for now we have something in initialization here and in the end process and remember these three processes subflows those ones are controlled from the main but let's just inspect the UI element that we just created so if I go in here I can now see on the UI elements that is this stack of paper so on the web page that is data CVR vehicle we have now created a button called English and I can inspect the address of this button because every element in a browser or in the windows user interface have an address so if I double click it you can see the address here we will work with this address you can see it looks kind of weird if you're not used to seeing it seen this this is a CSS selector I'll show you a lot about customizing that later in this session so for now just click save or cancel we haven't changed anything but we only want to click this English if it's on Danish or in Greenland is that it's called so and in case the English is already clicked we don't want to click it so let's inspect the address of this button and see if there's an attribute we can use so we can check if this one is clicked or not now let me go to My Demo flow let's move to the UI Elements by clicking this stack of paper I will add UI elements I'll add 1 for this English button and here you can see that I actually opened up Edge that will work as well but I'm not interested in that I want the instance instance in Chrome so don't confuse yourself make sure you choose that browser you specified in the initialization so here I create a button for this English so I press Ctrl N I have created one that is when the button was not pushed so and then just click it normally with your mouse now it is gray and that means we have pressed it then press Ctrl n and create another element now we have the English that was when the button was not pushed and the English 2 is when the button was pushed and we don't need to click it again so then I can click done we will inspect these two addresses and see if we can find an attribute that's changed doing these two instances so we have one here the button English and let's just rename it to keep better track of it so I right click and I can say rename here I'll say English not clicked and the other one called click so go down here right click say English click we do this just to keep track of our UI elements and to easily see what's going on always rename your elements as well so now I can open this not click and really let me just this is the address I Mark everything Ctrl C then I open up a notepad and just paste it in here then just one below I need to find the address of the English clicked so here I will just copy all of this and then paste it in here it looks like or it looks like these two selectors are the same so right now these two are the same and those ones cannot be used to see if this element is clicked or not but let me just take the English not clicked let's move to the lower tier here we have a button called button English so here we can see that we can add this to the address then this one will be saying disabled false and similarly I can click save here I can go over to click scroll down to the last part here I have the disable true so these two differs and in case you want to get really giggy let me show you how you can actually check this in your browser if I just open up my browser and maximizes now you can press F12 like this that will open up the developer tools it might open up to the right then you can click these three dots and choose dark to button it's just a matter of preference if you want it over here or as I prefer to have it in the button then click this Arrow here now click the English right now it is clicked but click it and then you can see something here that is the element called button and then we have some attribute type button class something and here you can see the disabled but if I just have the Danish open and then click this Arrow again on the English now you can see we still have this button element but now we don't see the disabled so the disabled is only present when this angle is here is pressed let me show you that again click the arrow here go up here and this disable that corresponds to this disable here in the demo so what we will do the strategy let me just click save or cancel save is fine so what we will do here in the vat lookups we will do a check and we will do a check whether or not this this abled attribute is true or false let's go create that in process so we will normally just have a click link on webpage that is this one but I only want to click that if the English button is not click and that is if this disabled is equal to false right now it is clicked then this disable issue and it's true we don't want to click it but right now it will not be there so let's go create that so what I want to do is that I want to click this x here and then find a get details of element get the value of an element attribute on a web page so usually these names will come to you very very nice sometimes I don't even think about it I just do a small search and then I just drag it in so don't worry you will learn all these actions now I'll drag this in so I want to get the details of the element I want to get them we already created this UI element called button English so I can just click this drop down choose the button English like this then we want to get an attribute name and I want to have that disabled so maybe I can find it here there it is I'm storing it into a variables produced I can store it into attribute value or I can say disabled value and then click enter this is just again the best practice to rename your variables so it makes sense then I can click save so now we get the details and then we can ask is this disabled value true or false let me show you so right now you can see that we have clicked we have not clicked this English and then it means that the disable is equal to false so we only want to create it when the disable is false and now I'm repeating myself a lot but I really hope this will stick this is an important concept to check for elements before just randomly clicking this has the benefit that we will only click the elements when they are there so always perform checks that will improve performance and stability so now I find an if and if ask question that can add be answered but true or false drag it in right after the get details of element on web page here I want to ask is this disabled value so I find it over here in the variables manager like this is that equal to false then I want to click the link on web page let's see that in action so now I click save here then I drag this click linked down here the if let us just see that it works so I will just close this one down to not confuse ourself and then I'll run the flow so here I'll run you can see I ran it from inside the process that doesn't matter it will still run so right now we click the English you saw that we have clicked here but next time we open the browser this will get remembered so we should not click next time let's see if it works and then we can move on so now I just run the robot again very very simple I launch the new Chrome now we shouldn't click and you can see we didn't click it went really fast but we didn't that was because the disabled value for the second run was equal to true and over here in process we said that e only if this disabled value was equal false then we want to click on the link so we created a kind of advanced check here where we got the details of the elements and then we use the value of that attribute to perform an action so congratulations you all ready build some very advanced stuff having the check done we can now perform the searches so we will iterate true let me open up the data again we will iterate through each one of these rows here like this then we will say what what is the number in the CVR column we will perform the lookup on the web page and then we will proceed one step at a time let's just make sure that our robot can do these searches so again pay attention to the column header that's called CVR we will refer to that in a little while let me close here and let's just move in so to iterate to an Excel data table remember we had that over here in the Excel data I open it we had this data table so to do that I'll find a for each a for each take each element in a collection and do something to it I'll paste it just below this end so what value do we want to iterate well we have our data table called Excel data so I can click this little X here take the Excel data here each one of the rows will never be entering through it that is we take first the first row then the second and so forth we can refer to that in the current item variable so let me just show you how that works so now I click save so here we take each row one by one and so if I want to do actions that refer to the data in the Excel sheet I can refer to the Q and item to get the row that we have that works like this so let's just inspect the data before we proceed so here I'll find a message box and now so on the message boxes drag in a display message here I will try to refer to the CVR number that is the Q and item so click this little X here take the current item and over here you will find these square brackets like this single quotation marks start then just say CVR single quotation mark and then we can click save so this one just pops up a message box with the CBR numbers and let me just close the browsers for now and then we can run the robot this is just to inspect how we actually refer to data with our robot so now we've opened it up we don't perform any actions but we just see that we can actually get each one of these cvrs numbers CVR numbers will get 10 so I'll just press enter and jointer and instead of having those ones here in the message box we will just type them into this search field just like who human would do that's it our robot ran so again go to process instead of this display message we will have a populate text field on web page if I move over here I'll find a populate text field on web page and then just paste it in below we'll need a UI element and that is this search field so click this drop down here you can see we have our button English that was the button that we inspected click the add UI element and here with the red border around it's called input text press Ctrl on your keyboard and click with your mouse we have now created another UI element we just repeat this one up here it's nice to get practice so click this x here an item now we need to say this will give us a data row so this will give us the entire row we only need what's in the CVR column so hard brackets start single quotation marks CVR single quotation mark hard bracket and like this then we can click save and now you'll see we refer to the same thing that we did before so I can delete the display message this was only for logging and showing off at least showing off what we have learned let's just go to the UI element again this stack of paper you can see here we have a name called input text search CVR again best practice says that if it doesn't make sense then rename it I will just right click rename and here I'll say CVR search field when I press enter look over here you can see that this UI element over here it automatically updates that's beneficial so if we change it here it will change it everywhere our flow if you had used it multiple times right now we have only used it once so let's close that again so we can now do these searches but we will run into a problem because when we have done a search now I'm just typing in eight random numbers there wasn't a result but when we have done a search then there will be a result down here and this search one will not be visible and just to make a 100 sure that you know what's going on let us just do a search of this here so right now we will look at the flow here so what's happening is that we are doing this search and then this next item we will do another search but there's no search field so we need to navigate to the front page to perform another search here no problem I'll go over here so what I will do here is that I'll find a go to web page here and drag it in just above the public text field I want to navigate to a URL and remember we created a variable for that we click the X here then find the url like this and click save shouldn't we try to run it let me close this Excel sheet down and then run the robot so now we still are needing a lot of actions but we're taking it one step at a time very very easily that's it with power to make now you can see we do the searches we actually not performing the searches but we are typing it in and we're navigating to the page you can maybe you can see it up here you can see that the page reloads every time so that's fine now we just need to click this search button move into process then find a click link on web page here and that's here so after we've done the search we want to click something click this UI element drop down to element over here you can see we have this SVG or a button it will not make a difference this button will get click whether or not we take a alternative one or two I just prefer this press Ctrl and click it now we have created a button so I click save again go over to UI elements open up the button search here we can see button search CBR that is probably a file name so we will leave it there now the exciting thing comes will this work let me close my browser instances so let's click run and test that the robot actually works let's just fast forward over this search and that's it we can now do the searches now we just need to scrape this data but clap yourself on your shoulder you learned a lot now we will scrape the data so what I want to do here is that I want to scrape the address the postal code everything that this Excel sheet specified on the company's 10 data so name address postal code city start date business type advertising status and one thing that I will do because now I only have one screen is that I'll just open up a notepad then I will just write the order of these columns because I will use them in a little while I can if you want you can also have the Excel sheet ohm that's just quite large so for my small screen here I prefer this start date business type at advertising protection so this is just to see what I actually want to search for like this then I can close the Excel sheet so right now I want to do some data scraping and we have an action for that that is extract data from web page so go search for that it's very important that you take the one on the browser and not UI automation then after the search drag that in here you need to open up a web page with the results you want to scrape we are here and we can see that so now you can see my notepad is over here I just drag this wizard up here so we already have the CVR we don't need that but we want the name the name is up here so take this H1 with the red border right click now and then extract element value and choose the text it doesn't matter whether or not you have this result up or some other result up I'll just pick this one here then we can say I also want the address that one is here so right click extract element value and choose that one you can see they will get added over here I also want the postal code but the these I cannot choose I cannot take the postal code it's the postal code and the city no worries I'll just right click and take them all together we'll need to do data manipulation in a little while but that's not a problem either so now we have the postal code and the city I want the start date that one is here so right click again extract element value and take the text I also want the business type here so right click hit that one then I want advertising protection to right click this finally I want the status like this do this so now I have all the elements these will or we'll see work for all our results that is because the addresses that this extract data from web page uses those ones are quite quite generic I can click save now let's try to run it again and inspect the data that gets created here in data from web page so and what happens here is that we're extracting for each one of these rows we are extracting data but and also see that this will get overwritten because this variable only exists when the robot runs but we can inspect it for the last result that's all we're going to need so now I click run again our robot will run and then we'll do 10 searches and we will inspect the result that we will scrape if this video helped please give it a thumbs up and a comment that will help my channel and me a lot and I'll say thank you and I'll promise to do much more of these videos it's also quite scary because this is a longer build a lot of things can go wrong sometimes when I develop ER during my daily developer live things go wrong but it's also nice to see how we can fix it so if something goes wrong in this video I will show the solution to that problem on the Fly hopefully I can solve everything anyway now we ran the robot over here we can see data from web page here we have our data this one was the last result again well that is uh the address and here we have only the city and certainly just postal code and the city in one column we will need to separate those ones later so each time this data from web page get generated with data we will write that into our Excel sheet so after the extract data from web page we will write that in we also one thing that we'll need to do is that we need to keep track of what row we are at here in our Excel sheet because we will use that so this is Row 2 and now it's Excel so the first row is one and here we have our headers so the first row with data is 2 3 4 5 6 and so forth let me close that we will create a variable with that information that is just we say it's 2 in the beginning so in initialization then I will find another set variable always keep your set variable in the beginning of your robot then you can easily see them when you open up the robot and if something needs to be changed for example a UL or a path we can just change it in the beginning instead of looking for it here I'll say row number like this and the value will be 2 so I click save we also want here in the process so for each time we move to the next item we will add one to that row number because that means we have got to the next row so drag in another set variable here in the end here you can click this x again and find the row number and now we will add one to it so say row number like this and then we will say plus oh sorry plus one what I'm doing here plus one percentage sign n like this that's the way we add 1 to it I can click save we're going to use that so find a right to Excel worksheet like this and we will drag it in after this extract data but before we add one to it so what value do we want to write and here we need to talk a little bit about what we're doing so then I can click save the data we want to write that is in data from web page and let me just open that one here you can see that is a data table but we want to we need to refer to the first row that is this one and the way we do that is to go into write Excel worksheet the value to write click this action data from web page inside it right after the page have a hard bracket start then you'll say zero because that's the first row in the data table and hard bracket end the right mode that is unspecified cell and since we have our CBR numbers in the a column we will not touch that then say B the row well we just created this row number variable to keep track of what row that needs to get written so click the X here then take the row number and click save so now you just need in the end process open up the close Excel and choose save document and just click save and it doesn't matter if we close these instances or not it will refer to a correct instance but for a clean view I just click run here so now we are again launching Excel and then we perform 10 searches but now it's exciting to see if we actually able to write this back to excel so and we can really see it before the robot ends so let's just enjoy that our robot actually worked up until this point and I hope you learn a lot remember to subscribe to the channel to get my new power automate and power to make for desktop videos coming up right in the future so I'm not really sure which row we add but we will just stop whenever the robots stop doing these searches and we could of course have only five ad numbers in the browser in the Excel sheet but that's fine now I go to my project open up the data there you go you have now built a robot that could read these cvrs and then you can web script these result and write it back to excel but you can see that we have the postal code and city and these two needs to get separated so the rest of these ones will go into the right columns not a problem let's go create that so now our solution is to take all the data and gather it in power domain for desktop and then at the end write it out here we were writing to the Excel worksheet for each one of the rows let me close this one and let me show you in the process so for each one of the items we were written to the Excel worksheet we did that because it was easy and we could just take the entire row here but since the data doesn't match what we want into Excel we are creating a much more we are actually creating a full customizable solution and this solution is the one I go with production so what we want to do is to create a data table all the results and then we can write things to that and again a data table is a table that only exists when the robot runs that is during runtime it looks like in tally like an Excel sheet except that the first row is just referred to as 0 1 2 and in Excel it is one two three so just one one number below for each one of the rows it's very performance efficient and very very easy to do so instead of this right to Excel worksheet I can delete it but since some of you might want a code I just right click and click disable action if you want the code for this entire project then send me a DM on LinkedIn and I will send that to you so we extracted the data that is data from web page let's see how we can because we have a problem here we need to separate this postal code with the city not a problem so what I want here let us do the postal code first is that I want a get sub text here I'll drag it in right after the extract data web page so the original text that was the data from web page I still refer to the first row as zero but now I want to say I want to have everything that is in a column where the city and the postal code and the CD is so let me just click save here this will give us some errors no problem we'll fix it so what I want to do is that I want to grab whichever is in here that is 0 1 2 so the third the the third column is named is number two sorry that was a little bit confusing I hope you got it so what I'll do here is that I'll have another hard brackets and then say two this will give me the postal code and the City I will say I want to start at the start of the text and then I want four numbers that is because the Danish postal codes are always in a four digit format so I can just do this and then in variables produced click that and rename it to postal code like this and I click save similarly we will do that with the city so you can mark this Ctrl C mark this right track so worksheet control V and now you have two the same ones we will just customize this to take the city so open that one that's the last one we are still looking at this postal code and city so that leave that one the start index that will be of a character position and the character position we want to start at index 5. so here we will say position number five and then we will say we will just go to the end of text that is because we have four digits and then one space so we'll do that remember to change the variable produced sorry like this I just need to edit it like this and then I can click save so now we have store separated these two similarly we can refer to let me show you here we can refer to the start date that's the four column by just changing this 0 to 3. we'll do that in a little while now we will create a data table and then we will add data to it during this process so in initialization that's up here we will create another variable that will be our result variable that is a data table so I'll say set variable like this and I'll drag it in I will call this result data table like this and then we will give it a value so this one is a little bit longer expression it's very easy when you get used to it so let me show you so I'll have a percentage sign in the start and I can add as many spaces as I want those ones will just be ignored this is just to show you to make it a little bit more easier to read then I will have curly brackets in the start and in the end so again you don't have to use these spaces I just needed to so I can show you a little bit better then I want to say I have a headers row so I have this hat here and then I am specifying my headers because right now here initialization I will only add headers to it and then when we add data to it then we will we'll specify what comes in so here I have these headers so uh this hard bracket start and in the end of these curly brackets I'll have a hard bracket end and I will add in the names here so CVR in single quotation marks and then they are comma separated we have the CVR the name address like this name then comma single code is very important that you write it exactly like I do because um then otherwise it will not work so address postal code and city again these um these Expressions will ignore the space so this city and what do we need we need the start date business type business type like this and what do we need next advertising protection let me just copy that that's what I said in the beginning always copy making my own medicine so advertising and then it was status like this so I hope you have written it like here now we created a day table with these headers so then I can click save here we will have a syntax error that's probably because we forgot one character let's see if we can find it and yes it's here we forgot a single quotation mark in business type so power automate for desktop launches if we forget something now it's right so here we are setting this data table variable let's so for each one of the results we will add data to that so find a set variable yeah and drag that one in just uh below the get subtext here I want to write to the result data table variable that I just created so click here go find it here and then and then the value so I want to take whatever is in the result data table like is here and then I want to add a new row to it in this way we keep everything what's in the result data table imagine that we are iterating through it for the second time or further we don't want to override it so then I'll say result data table and then I'll say Plus then I will say hard brackets start and then hard bracket end in here I will have the first item and here I will just type in the CVR number we already got that so I will say q and item like this then in hard brackets single quotation marks I will say CBR then a single quotation mark and a heart and so here we are just typing in one value this will not work but a good way to check sometimes if you want to check that is save it and if it says syntax error then you know you are and not right instead of just writing a long expression which will be very hard to validate so first we want the name so and we know that was the first Occurrence at our data script page so I'll say comma and remember to have it inside these hard brackets so I'll do this then I'll say data from web page and then I want to refer to the first row like this and for the First Column I will do this so 0 0 then I can click save and the syntax is still good so similarly I'll just add the next one that was address so I can just copy this Ctrl C and go out here armor paste it in say number one then I want the postal code and city so I will say postal code and then also a city that will get the variables and then I want the rest of the items let us just see how that was the wrong one so I just want start type start date business type advertising projection advertising protection and status and those ones will be that will this one here from the script results that was one two three so the start date will be in the fourth column so that will refer to that by saying three let me just show you it is easier when we see it so I'll say comma then paste in here then I'll say the third column comma paste in number four and I'll say comma paste in number five comma paste in number six like this then I can click save so now I am writing the result back here and one more thing we'll go to the end process because now before we save we need to write a data table to an Excel worksheet so I'll find a right to Excel worksheet like this and then I'll drag it in up here what value do I want right that was the result data table like this where do I want to write it that is unspecified cell I'll say in the A and then I don't want to write overwriter headers so I'll just say two then I can click save shouldn't we go test the automation again so let's try to and then we'll just enjoy how our robot solve yet another problem so here we are navigating to the first item and the second one and here in the set variable each one of the results will get added to the result data table so one by one is this video helping you please let me know in the comments what you think of it and if you have problems you want solved you can paste it on my Discord the link to the Discord is also in the description below but for now we will solve this nice case this is a nice case because it will teach you a lot will come to a lot of the problems that we're actually seeing in RPA development so let's go inspect the data so here I open up the data and now we have the postal code and city nicely separated so far so good so that was the first one that we're trying to solve we can now separate them and we have a much more flexible solution because we can easily let me just go in the process we can easily add results to this and so that one will be very easy now for the next problem or a problem because I don't want to override the original data so I want to do Dynamic file naming I'll use a date time to actually be able to do Dynamic file naming and do unique thing because each second in your life is different that will have a different time stamp so we will use that that was the timestamp when the robot starts so in the initialization then find an action say get tuned date and time like this and then we will drag it in this will just get the qn system timer and the robot runs so I click save now I want a convert date time to text so I'll just find a convert and then I'll say convert date time to text remember to take it in after the get current date and time so I just want to convert this date time up here that is because this is in the date time format I need to have it in text so I can use it when I save an Excel sheet so click the X here up here then you will say q and date and time so here you can choose a standard format that lets us choose between a lot of format but it's not really suitable for file names so what I want to do is to take the custom and then specify my own format I want to use month days hours minutes and seconds this is how to use custom date and time format with.net there's a link to that in the course description as well let me show you down here but for now uh just believe me this will work so then I can click save so now we have the queue and time then we go to the end process we go to the close Excel now we want to save it to a specific name and then just not override what we have so choose to save document as the first thing we want that is the project path so I will find a project path here then I'll say backward slash now we will use our date so here I'll say X then I will say formatted date time like this underscore and then it will be data Excel SX this is just to make sure I don't overwrite data and I keep a track of when the robot has run so let me be sure to save it and close it and run it again remember to save your robot here and there because there's no auto save in power automate for desktop when you quit power automate for desktop will ask you if you saved but sometimes you just forget it or hard close your computer that will not be optimal to lose all your code now let's run it again and just see that we can actually do Dynamic file naming so we're reading Excel we do 10 searches the only thing we do here that is just to have a better name for our Excel sheet I'll take sip of coffee while this runs and drink a lot of coffee here while I develop and yeah what are you drinking out there coffee Cola water I should drink more water actually but um yeah that's it so now we again we do the searches and hopefully we have solved the dynamic file naming so um here we are um extracting data um and we can actually just move this a bit we can see that we are at row number 11 so this is the last search and hopefully we have did it right so here we have in our project now we have added a timestamp to it that's quite clever and we can even open it and we can now see that the results these ones are totally the same we have just created a new Excel sheet with a dynamic time stamp isn't that clever so let's do some error handling and logging an important concept for Developers so if I just open up the sheet again move to the tree companies Trail data I put into errors you can see that one of them is one two three four five six seven eight and one of them is eight and let me show you why they are errors so if I go to the front page one two three four five six seven eight like this then I search we found zero results and similarly if I type in eight we found one hundred and seventy thousand blah blah blah so these two arrows we want to handle because imagine that you search for it then there will be no thing to scrape like we configure our robot to do that was the name address start date and then our robot will fails it will actually stop so it's very important that we handle these arrows these could be errors from the business side that they filled in wrong numbers or simply just numbers that are expired so we need to um we need to solve for that so and as you saw these gave us two arrows we have one error with zero results and one arrow that is if we have more resolved than one multiple results so let's try to solve for that so we need to verify are we on the correct page then we can start scraving and let me just show you one result we can use this CVR number so this element here will say is that present then we'll start scraping otherwise do this but let's take one step at a time so here I will right click rename copy this one and then just close it so I go back to Power automate for desktop then go to initialization set active worksheet change this to three companies 12 data so now we have the right thing our check will come into the process right after we have done the search but before we scrape find a if web page contains like this and drag this in here so here we will ask for UI element and it's important that you have this result up so we can create this CBR number so here in this UI element I will say add UI element I'll take the CBR number press control and then click and we have this UI element here then I can click save so that one is called bold text CBR number we will just let that be so if that contains the CVR number then we want to extract the data we want to get the subtext I want to get another subtext and we also want to set the variable that is we get all the data but in our sheet let me just open the sheet that we're working in now that is the three carbonstrual data we also have a log column so we need two things we need to fix um let me just close this one here that was right after the status so we need to add a lock to this one here and the way we do this is just to let me just see that I am on the correct thing after the last item in our row say comma like this and then we can write in a message in single quotation marks like this here I'll just say success full like this and similarly I will and then I need to save here but here and I need a single quotation mark in the end again writing these Expressions will give you some errors so now I just need to because I added 1 to this Row in the initialization where I set the variable I also need a log column does a comma single quotation marks I'll say log like this and then I can click save so now I have when everything goes right we are moving in here then we can say well what if we want to fall what if this fails we want an error message so if I go up here I will find an else like this and drag this else inside div this if web page contains so it will say is this present yes then we'll do this else will do something else so here we can say well we want to look for whenever this is with zero results that was whenever we did this will create an instance for that first so we'll just look for this we found zero elements and if that is present then we want to do a log where we tell we found zero results it will look like this so again I will just have a if web page contains like this and I'll drag it in here so I'll need to create a UI element and what I will do here is that I will add this UI element that one is this heading so and then I will press Ctrl then I click click save so here I'll say if if this refund zero was solved then I can make a log and again I will just use either this one up here so I can copy this one here so right click copy move down here right click paste of course I don't need to I shouldn't write all these things I'll keep the CVR but I'll delete this and then you will have two single quotation marks that will be an empty value delete this one as well you'll need to have the same amount so that's why I do it like this and we don't have any data to write here because we haven't scraped anything we found zero results remember and let me just keep the syntax so here two single quotation marks two single quotation marks two single quotation marks like this and instead of successful we will just write fail we found zero results like this and then I can click save so now we have this but we still need to do something whenever we have multiple results but let's just first inspect this element over here so here I'll say heading we found two results and then just rename this and here I will say a heading zero results but something tells me let me open this one here that here you can see that this H2 this selector is the same as um as it will be when we have multiple results there's nothing that says here it's just the H2 header so we need to say something about well what's actually in this header here and let me again open this one here press F12 like this and again this is very important so I really hope you do this then just choose the arrow and click here here you can see if this H2 we can say does that contains we found zero results so we will just add this text to it we go over here and we have this text editor like this so then we say we want to say um is this H2 does that contains and I actually am creating a course in manipulating CSS selectors so that one will be upcoming stay tuned so here I will say contains then a parentheses start quotation marks paste in the refound zero result quotation mark parentheses and that is the selector that we're going to use so then I can click save so now we have this one here but we also want to say if this is not the case if this is not a zero result then we know we have multiple results and in the ideal world you will counter for every exception that you want to look at so it's important that you put it inside there if web contains this second one then just take this one copy it move down here click the end Ctrl V paste it in now open that log message and say fail oh sorry we found multiple results like this and then we can click save now um we are ready to do our error handling and a logging so let's do that I'm just saving it again then I can click run so now we run the robot again and what we added here is is Advanced or Arrow handling and logging so um and here we are doing the exact same searches as before but hopefully we will also update the log column and we will counter the errors so right here you see that here we shouldn't the robot shouldn't do nothing it should do a new search it does it did I actually think it did before as well one two three four five six seven eight we were just busy talking the two of us so it's and actually here we have this eight here so um what we actually doing let us just see why we searched that twice or that was what I saw so let us just make sure that we are not doing anything we don't want um that should be correct so right here and um let's just move all the way to the end and that one was our last item that's it so let's just move to the data and this one was this one here and here we have this four tree companies data no that was status right we have this eight failed we found zero results and failed we found multiple results isn't that pretty we have now you can see these data will not get updated because our robot will not if if it can find a result then it will not run that's it and let me just show you the next case because now it will get even more difficult I added one item and here we actually have a problem and let us just try to sorry let us just go here rename that one and copy the number four that means save it close down this Excel sheet go to my bad lookups take the initialization set active Excel worksheet paste it in now let's just try to run the robot without doing anything and I'll show you what the error is hint there's a difference in how the data looks in these searches so now I'm introducing a company with some other items and because we just scrape here by the number that is we take one item that is the address and the postal code and so forth then if a weird company comes in and then it will because of the numbering it will just scrape like that so we need to create more robust selectors I'll show you what the problem is and how we can create a robust selector so we're doing these searches again let me just fast forward that's it and if I inspect my results and again we have still have this time from a nice format you can see here that one of my items and that is this one here ravisa partner here we have two dates and then suddenly um we have um we have everything that that we have postal code city start date business type advertising and so forth and that is because our usual result look like this but for some reason some of the companies look a little bit different let me just do a search with this weird company you can see we have two dates and the auto generated selectors in power automate just take this and treat this as number probably number two three four five six and then when another date occurs then they will just say oh well that is the fourth element do this so we need some knowledge about selective work let me just delete all of these here so we can combat for that we need to create stable selectors for each one of the UI elements and that is if I go to that lookups and then in the process so instead of this extract data from web page we will get the details of the elements let me show you let me go to the demo here another one is here and let's try to create new items these ones were the English one so not to confuse ourselves I will just completely delete it so now we have an empty flow and what do I want to do well I first I of course need to do everything but then I want to create a more stable selector for let's start with the H1 header and then the address so F12 to open up the developer tools click the arrow here and let's just take the address first so what we want to do here is that we want to use this address as an anchor because we know that if an address comes then the next thing will be the actual address so we want to include that in the selector how automate for desktop didn't do that so if I just go one above here click this you can see we have this address here so what we want to say we want to look for a div that contains address then we want to take the next div that is this div and we can add a class called call 6 call lg9 we probably don't need it but let's just do that so let me open up a new notepad and let's just create a selectors all by once and then we can easily create an Empower domain for desktop so we want a div and we want to say that needs to contains and then we say contains address like this us so I'll say parentheses quotation marks like this then I want to say address quotation marks like this then I want to say I want the next div like this and here I want to say in hard brackets I want to say I want the class to be um this one here copy this one here and then move inside here so here I'll say class where that whenever that the div contains address then take the next div with this class and then let me get it then we will use a get details of elements web page to get it out so just copy this and let's go back to My Demo flow here I'll add a UI element and let's just first let's just take this one here the auto drain in it press Ctrl and pick it click done and I'll be wrong so this one is over here and here first let's just right click and rename it to address like this then double click to open it and here you can see that this is not the selector we want to use so I want this text editor let's delete it Ctrl V this is our selector so what we will do here is to click save and then we can get whatever we want by using a get details of element on web page here the web browser instance let's just refer to this and now I just closed the developer tools for now so I'll be referring to this one here so the web browser instance we need to open up a browser so I just click save here and then I just click launch and then I launch another Chrome now we will just attach to this page down here so I'll attach the running instance by title click the drop down and unit view click save we're just doing this here in the demo flow where we're trying to understand what's going on double click that get details of element on web page we will fill it in and now I can choose the browser the UI element I created it over here the address and here we will just say we just want this own text and the variables produced well I because we want to rename it in the final flow I'll also do it here click save so now when I try to run this flow we will attach to this and then we will get this CVR number attribute the flow has actually ran on in here here you can see we got the address and that was by using this selector here so let's create selectors for each one of the other elements I have it here so this one was we need the name we don't have that one yet then we want the address we have that one here and then we want I can actually just copy it from the list we made over here so we can do this this this this this so now let's just take the postal code city start date business type advertising protection and status and let me just move in here so now I opened up F12 once more and this is actually very nice to have this knowledge you need this as a power automate for desktop developer so now choose the postal code click that one here you can see we have the postal code and if you open up the div just one before you can see just as before we have a diff that contains postal code and city so if we say if we just tune this one here don't let me move in here we end the postal code so here I want to use the postal code and city instead of the address so and this was just to do this now let me do this cut it and then replace it with address like this so it's reasonable to think that we can do it with each one of the other elements and trust me I just did it before you came so right now I will just I will fix this one in a little while but you can inspect your developer tools to see that I'm actually doing it right so here we have the address the postal code and see that these are actually the same so we'll only do it once but for now we'll just have it here then we have this start date so and let us just try to because they look so similar we will just try to do it and then we'll have the start date then we'll have the business type and it's reasonable to think that it's just these ones up here and then we'll have the business type the advertising projection here it's always challenging to do these long builds but I think we both you and I learn a lot from it so uh yeah so that is worth it so now we have the selectors we just need one for the name actually and let us just try to see what we can use here so here we have a H1 and what I want to use is that I want to say the class contains the H2 here so let's just have this one here so we're selective for this one was it here the name that will be uh H1 that is this element H1 then I want to say in hard brackets I'll paste in this class and I will just like this I will just say contains that is a star and again because I don't just want this H2 that should be stable enough I'll do this so now we have selectors for each one of the elements that we want to scrape now we need in this flow we need to get details of element on web page and fix the selector for each one of these elements let's go do that in power automate for desktop so instead of this extract data from web page I'll just right click and disable I'll have and now you can see these ones arrows so we can't enable it if we don't want to look at it but that doesn't really matter so then have a get details of element like this and drag it in here so we need a UI element and we're going to create that because we haven't created that one here or we can just pick one and then just fix it afterwards but I like to just do this so we don't confuse ourselves the first one is the name we don't use these ones here and then we're saving it into a name variable like this so then I can just click save and we are going to fix it over here so and you can see here heading 1 are these are partner APS so I right click here and then I'll say I'll call this name that is the UI element we still need to fix the address so double click it text editor and we have it here so this one is our selector like this and then I can click save similarly we create the other ones and save it into these names blah blah blah so um what we can do is that we can add the UI elements over here or we can do it in the get details of element on web page now I'll just do it here so we can get used to it I'll create a new UI element and this is the postal code city so here I will add a UI element like this and we will save this into postal code City like this because now again we're saving it to the same one so here let us just fix the the name over here so I rename and here I will say postal code city like this and I also need to fix the address so double click and check this text editor I have this postal code so we still need the address actually so that one is the postal code but then instead of using this get subtext to store it here uh to to have the original text I'll delete this and use the postal code city instead like this save it and the get subtext will still work is the same data that we get out so I just do this like this and I click save so now we're using it down here and to use the name that was actually the data from web page we can change that or we can do it all by once so we're deleting it down here and then we just refer to the name variable then I can click save so we need the address actually so let's have another get details on web page choose this drop down add UI element and in case we forget one that doesn't matter we will pick that up when we run the robot we will stall this into address like this over here I'll right click rename address here then we just need to double click again we're doing a lot of repetitions that will make us good so here I will just copy this selector that we created here and I can click save so now we have name address postal code and city we can take the business type and advertising projection so here I will take the business type add UI element [Music] here and I will store that into this business type like this save I will and here you can see that we have div and part cellscape so I will right click rename call this UI lemon business type and double click it text editor you'll get used to this if you hopefully you're doing this with me so then I can copy this paste it in up here now we need another one because we got advertising protection so add a UI element for that and we will call this at advertising protection like this and we click save again go over here right click rename put check protection like this double click to open it text editor and then we will copy this one in here let me click save so now we have the advertising protection what do we need we just need the status so let's have another get details of element on web page add your element and when we're doing this to create our own selectors so just pick one of these we just um we are fixing it so it doesn't really matter change this to status and then click save so now go over here to div normal right click rename and then we can say status open it text editor go into the text document and copy this one over here pull the right click save so now we have all our elements then we can update our set variable here with the lock or the data road that gets added so now we have the name and then we what we want next was the address so instead of this state that our map page 0 1 I'll type in address and remember if I spell wrongly here the variables will not be referred to correct so if we have an error on the variable side it can be because of this so then I'll say business type I can find them here because I create them in order so and the fourth one that will be advertising protection then we will have status so here we just need to delete this and add a start date wherever it fits let's just see where we want to start date let's just open here and we want to start date after the city we didn't create that one that is okay so let's just create it in here so what we're doing here is that we are deleting this one here and right after the city we start writing a start date we haven't created that variable so look it was narrow that's fine I just click save you can see it here so after this postal code city I have this get details of element on web page choose the UI element and then add UI element and let me just minimize this so the start date that could be this one here like this and now it open up the demo so here now we can create the start date variable then we'll say save and when you work with this you can see that there's plenty of possibilities to make an arrow don't worry you can see I often forget things that's perfectly fine just you should just be able to fix it then it doesn't really matter so and the start date we also created a selector for that we just forgot to create it that's fine then we click save so now we have each one of these we can delete the extract data from web page like this we also have this row number we can actually also delete it but for now it's okay it keeps track of the rows let's just see if this works I'll just save it again remember to save your flows so now I will run it and I will but now we are running again and here we're doing the check and then we're doing the searches so let's see that you can actually see that this is a bit slower but stability comes with the price and here we have an arrow so this one business type we can not find and uh we'll see why that is so I will just open up this and that is here so we have the business type and it's probably just because we have missed spilled something here so I'll just have the F12 and then I will and that's the way to solve things because sometimes we just uh so here we have the business type and we have the text here so here we have this you can see the text here might be because we have a space too much but let's just fix it if I go over here that is nice that I have renamed my UI elements well so what I will do here is just paste in this business type like this so now I click run again if your Excel makes a problem again I made a guide on how to force close the Excel hopefully the business type will work now and now we get the details of each one of these one by one and then this business type we should be able to to get that we are not so element would would selector business type the div contains business type um not found well let's just look what that could be again again it's a trial and error it's a bit like Sudoku so I press F12 here and then let's just do it I prefer to have these ones here so here we can say we can see class equals to to call LG 9 and you can see that it's actually a break word as well so what we need to fix is that on the business type that one was here we just need to have a star here so we'll go fix that in power automate for desktop and do this and that's we also created these elements so uh just having asterisk here like this and hopefully we will not have that in advertising protection but to solve for that just a quick fix we saw the other ones we can just have a contains here as well and do we have the status as well sorry over here we can also have an asterisk here now it will work let's try to run the robot and I will Fast Forward here but I will say this method is slower than the previous one because we need to get details of each element separately the previous one we could get all together in a few minutes I will show you an even faster method but let's fast forward to inspect the results that's it let's inspect our data so here if I go to sheet number oh sorry that was four here we have our result and we also have uh everything we only have one date for each and each of the business type and so forth are in right places we also have these two fails but those were the ones that we created before so mission accomplished challenge five that is this one it looks a lot like the previous data set and it is we have these arrows as well but we also had an activity code to scan and where do we find that let me show you here let me just close one so we have one open so in here we have an activity code but we can only see it if we click this plus and that is this one here so we want to scrape that and let me just show you in the demo so I will delete this and then I'll just attach to the unit view again let's try to extract the data from this activity code again we will fix the selector so let's first just use a get details of element on a web page here and drag that in and we need a UI element let's go create that again this is just a demo to see what our strategy strategy will be so if I choose this press Ctrl and click we have a UI element and we just need to jump back to the demo let's just store it in attribute value for now and then just click save if I go to my UI elements that is this one here and so I can see what's actually going on activity code here sdui limit I double click to open and here I can see that it's also looks a bit unstable so we will fix that as the first thing we will do so let's just make a more reliable selector where we actually use this activity code again as an anchor so again I press F12 like this press this little arrow scroll down to my object that is this one here and here we see it so we need this text out and just as before we have a div with the activity code just before so we will say diff should contains activity code and then we will have a div with a class attribute equals call usually you shouldn't use classes because they can change but that's the only one we have here and we still have this anger so let us create that so what we want to do is that we want to create our self here and we can actually see here we have this div class here then we just need to have a diff contains so if I just do delete from here just need to catch it oh I didn't so we have a div here and that diff just as before should contains then a parentheses quotation marks activity code like this here you could also copy paste from here show you you get the right text but that looks fine so now we have a more stable UI element and let's just try to get the data out with the demo flow so I click run so I run the flow and we will see actually I think it already ran we can see it here and in the attribute value we got the right data out but the challenge here is that if this looks like this are we also getting it out so we know we have now you can see here we try to get in there let's see if we get it and we will get a Planck value so our strategy here is to let me just find here is to First click on expanded business information we will do that just as we did before by clicking a link on web page and then choose the UI element so that will look like this here I'll say click oh that one must misspell click link click link on web page and that one should be just before they get details we'll fix that in a few seconds now let's click this drop down add a UI element that will be this button control and click with your mouse now we have it and again it jumps to the incorrect flow here I can click save the button expanded business information that's fine let's just move it up here because now it will look like this when we when we do a search and let me try to run it and we just need to inspect now you can see it will expand and it will probably scrape the correct result there you go now we have it again so this is the strategy that we want to use in our flow first thing that we will do is to just duplicate the data our sheet changes changed we have this five company data so we copy the copy the sheet name and here we are going to write to the activity code column which is just before the log and let me just close it again here go back to our flow so in initialization we are setting the active worksheet can you see this is really nice that we just need to fix this then we are reading the correct worksheet so we just did what we did in the demo we go to the process and since we are going to use um our data that was the activity code that will be right before the log uh just let me show you again so we are aligned yeah let me just so we have it right after the status so let's do that so what we will do here we will have a click link on web page and get that then get the attribute so here I will say click link on web page here and drag that one in we can have it here so the UI element we will create again because we don't have it here so I'll add the UI element and let me just minimize this is this one so Ctrl n and press this button so um sorry and that was the wrong thing we need to do this and then we will need to fix it so the easiest thing to to do here is just to add another UI element and choose this button so that's it now we have it then I can click save so here we click a link and let's just go over here because now you can see it's it's quite quite messy and this one was the wrong that we created down here we delete that yes please we could also go through all these both CVR number heading zero results so I think we use all of these um but now we are using this so we are clicking and then we can get the details again so get details and now you can see my computer lacks a bit but um it should be fine we have it here so we are creating a UI element once more that was actually the one we created before we didn't have to delete it but um it's late though so here I just picked that one by picking Ctrl n and then we have it and again it jumps to demo but we are going over here so we will store that in not attribute value this time but activity code and then click save like this so over here we will just fix the name and UI elements and again if your U elements doesn't show up simply just press these three here and then find it here so right click rename and here we can say Activity code like this so now we are getting the details out we and storing them into a variable call activity code now we just need to add them to our log down here and to add them in the initialization where we create the data table so in initialization up here that was right after the status so single quotation marks activity code single quotation mark and a comma then click save now we have it here we will add it in our log as well and we just created a variable for that so that will be activity active VT code and then the comma successful like that so that is how we handle invisible elements let's try to run the robot so I click run up here and now our robot will run so this time we handled the secret element so to say let's see if that works so here we are scraping it and we're not we're not reached reached it yet and in a few minutes the solution to build wrap now we can see we clicked it and we extracting it and in a few minutes we will get the probably the most efficient solution of today which is rest API calls with Json parsing so stay tuned I will Fast Forward again so um we can quickly see our result from this method and here we get an arrow and you can actually see that was correct because now we have an hour arrow and since our data table has grown once we also need to add it here and here so there's a lot of things to fix you just need to know how to fix these arrows you can see I make a few actually during the project but that's perfectly normal so what I will do here is to fix it in here in this variable I'll just need to add uh space two single quotation marks and a comma and then I can click save Sim oh sorry similarly I'll do it down here so just somewhere add two single quotation marks and a comma like this and then I can click save so now we fixed that we are ready to run again so let's try to run the robot again and see if we have fixed everything and here we are reading it and blah blah blah again I'll just fast forward to the arrow to see a robot handle that correctly so stay tuned here we have one of the arrows let's just see if we're able to do this my pencil let's just see if we are able to handle that that we actually wrote the log it appears so that's great to know how to fix the errors during development run your robot a lot of times like we did here to test if everything works up until so far when you build then you will catch the arrows on the Fly and not build an entire workflow and then discover that something in the beginning didn't work and now you have to build it all over so test it during development a lot just run it and maybe you don't have to run 10 occurrences so I just have maybe two rows in Excel sheet for now it's great because we get a chance to talk to each other and again everything here in power automate for desktop is straight forward we need to know a few best practices but that's that you don't need to be an I.T developer to build your automation here of course it helps but you can easily do with just being a great business user that knows your processes anyway let's fast forward to the end that's it let's inspect our newly created data here so I opened that and in here you can see that we actually also have the activity code of course not for the fails that's the one that didn't exist but for everything else here you can see that so sometimes we get some codes and sometimes we can look it up at scat so far so good so let us just fix that's the last part here let's just fix the company's 10 data this is a very simple sheet but now we are doing the most efficient and method that will not course errors at all because we are doing it away from the user interface that is the browser and we're doing a rest API call let me show you what that is so if I go to the course page that one is here and then just sorry then then we need to scroll a little bit down take the API documentation here we can write in a CBR number this is only in Danish but that's just what we have here and since they are Danish numbers then just pick the first one here I copied that one out and I go to my browser I paste it in here and there you go now we have all the information straight out in a format this format by the way with these curly brackets in the start and end that's a Json that is key value pairs so what we want to do is to do this lookup and then get for example the industry code that was the one we just had the phone or maybe just the address up here here you can see the zip code and city as well so this is very reliable we're just sending in a request we are using this URL up here for and that is I copy this that is for this CBR number and then let me just show you what will happen if I do this then this is the Json this is the same Json here is here it is just a little bit prettier but we are sending these away from power to mate for desktop and then automatically get the result let me show you how fast and reliable it is so we'll first do it in a demo so I will delete all this and then up in actions we will have an invoke web service here and drag that one in here we need a URL that is this one you actually don't have to do anything else here you can just click save then we can run the robot here you go and you can see here the webs it created a variable called Web Service response that should be this one here and here is our Json so now we got all the information we want on this baton number CBR number we just need to know how to get the data out of this Json it's called Json passing we are making it we're creating an object out of it so we can easily refer to for example address and get that one out so right now it's just a text value in a Json format so then we close it so what we will do is that we'll find a convert Json to custom object and move it over here so our Json that is just a web service response from up here that's the input so I'll just click the X here and then I'll say web service response here and the variable is produced that is Json as custom object so I can just save here and then I can click run and here you can see that if I go over here to Json as custom object now I have it in a custom object and what I can do here now I can just start to refer to the jsonness custom object that's the name of this variable and then put in the name let me show you I'll just use a symbol set variable for now for this demo and then we will build it in our flow in a few seconds so here I'll just be calling it new VAR that's fine this is just a demo and over here I'll refer to um Json as custom object we just created over here and if I want for example there let me just inspect the data again that was this one here over here it's just more easy to look at if I want the name out I just need to refer to this name I can also find it in demo over here if I wanted the way to do this is to go in here then hard bracket start single quotation marks and say name single quotation mark hard bracket end click save and if I run the flow again and go over to new VAR we can see that we have the name here isn't that clever and that is really really fast I'll show you in a few seconds and again just to show you that this one was actually right that is the personal Visa and in our solution imagine that we can make this one up here Dynamic right now it's quite static we use we have this CBR number but we will you'll be using the current item with the CVR in our wet lookup for this one this value here and then we can do it dynamically let me show you so we go back to Power automate for desktop our main flow and here let's just talk about what we're going to do first we will need to get the company's 10 data so I will right click rename and copy then I will close down this sheet because now I have what I want so in initialization I'll set the active Excel worksheet to six companies 10 data that is fine that's here in the process now we are not working in the browser anymore so what I will do here and actually in the initialization I will delete or disable the launch new Chrome then we'll get a few errors because all these are browser elements so I will just click so this one don't hold Ctrl n but don't take the for each because we're going to use that but you can pretty much or not pretty much you can just Mark each one of these red elements um and those ones are and again here we don't need to get subtext because um we will just extract the postal code and city um one by one and then I also can delete these ones here we will not do error handling in this but you can do it so at the same time but usually an API doesn't create errors of course we can look up values that doesn't exist and in that way we can handle the errors so if I just right click here and hopefully um we're able to uh just be leaving with everything else so and that looks fine but we have this here let me just be sure that we have deleted everything we need to actually activate the end of the um of the forage that one is here we can also if maybe we should do that actually let me just delete all the web actions so we are not confusing ourselves if you want the code again just write me I will um send it to you so now I'm just deleting it here I can also delete this right to Excel worksheet and we will delete this we'll delete this we will delete this and we will delete this so now we just have the set variable where we set our results and then we have the row number we will not use the row number anymore but I will delete it in in the initialization and here when we're done um I can actually just do it now so now we did it let's just inspect a let's just create the API call here so here again I will have the um invoke web service sorry here and drag that one in up here so that's the first thing we do so we'll have our URL and that one is this URL but I will copy this one go in here so I'll paste in this and because I want to use just as we did before not a static um not a static number I'll delete this and then I'll have two percentages assigned and in here I'll say q and item that one is referring to the Q and item and then I'll say I want to use the CVR from the Q and item because that one is the Q and Excel sheet so I'll do this and then I can click save so now again I just get the same thing and then I'll do just as in our demo flow I will have a convert Json to custom object here drag that one in so the Json that is the web service response from up here so web service response and here we produce a variable called Json as custom object that's it now we can refer to this custom object just as we did in the demo and we can do that here in the set variable so it looks a bit different but it's almost the same so and again I just copied this one out to a new notepad I like to keep my notes there when I build these things because otherwise it's quite messy to look at so what I want to do again for the name here I just say I type in this Json as custom object like this and then I'll have hot brackets and here I'll just refer to the name just as we did before in the demo then like this and like this so now we created the name and let's just see if it's actually the same data that we're going to use so in the last one here those ones here are the one that we're going to use so again I just fix that was this one here so I just type copy the headers and paste them in so I know in what order they should come if this if that's the same I think it is and we have the name and again we just have the address the postal code the city the start date business type start date business type advertising protection status we don't have the activity code for some reason but that's fine you can if it's here you can find it it's actually the industry code if you want to use it but for now it's okay that's not really the point of this and it's just to know how to do things so let's just fix the rest of it so then we'll have the Json as custom object name and again we need to check if that's actually the name in the API the address what is that called in the API that is called address here and yeah in the notepad so that one was called this and then I'll just need using this custom object like this and here I'll have a single quotation mark and like this again so and maybe it's will be more easy just to have this one here so after the address that one that is the postal code and I will fix these addresses in a few seconds the city the start date and the business type and the advertising projection status advertising projection status and activity code we don't use we just have the log and that one will be successful so I'll just delete this so now this is our what we're going to use I'll fix these ones here but while we remember it and let's just click save here it will give us a few errors that's fine in initialization in the set variable we just need to delete this activity code again again you can edit if you want but for Simplicity we will not do it so here we have the address and just we have it a few times so here we have name address then we will after the address we will have the postal code what was is that called here in the API documentation it is called zip code so an API documentation doesn't always exist but when it does you should use it so well and again let us go here so and a long day so ZIP code here and then afterwards we need the city that one is called City up here again it's okay if we make a mistake or two in this expression we can easily fix it and we get some practice so after the city we have the start date and then the business type so the start date here that one is called start date and here so that one is right after the city like this and after the city we have the business type and that one is company desk here what we're doing here again is that we are creating this expression here so then we have the advertising protection status and then we're done so advertising protection um that one I just need to find here that one is protected I will copy this one here paste it in here and finally we need the status of this if it's here so the status was called something will yes no but maybe it's not here and that's just the way it is and this is just actually a free API so it's not the official API and that's might that might be the reason why we can't find the status here we can have the credit status that is null well this is not really that the company code industry code and here we don't have the status that is fine so what we can do and here we can let me just inspect the data we in in the sheet we actually had the status so what we will do here is that will close this one down and then in its expression I will just be writing nothing in it that is two single quotation marks now hopefully I've made the correct expression because this is rather long so I'll go in here and not here I'll go in the process and then in the set variable will paste this one in this should do it I can click save and we don't get an error that's fine so now we are invoking this web service then we are converting it to a custom object this Json and then we use it to refer to these names and let me just save it to be completely sure that we have it here delete everything here and then I will run the flow and now you should pay close attention to how fast this is because we are launching Excel then we are doing each one of these invoked web services you can see that's it that was really really really fast now if I open the data if I go to six companies data there you go we have our um have our data we have the name address postal code city we have this start date which is in two different formats that's just the way it is it comes from the server then we have the business type advertising protection here we have a status and then we have a log so that is very easy to use and API and again this one was not the official API this was just a three one one I found and the benefit of that is that we can test on it for free but if you want all the information then you should look for the official API to that website which use case should I solve next let me know here in the description and I might pick your case well done you completed a project to get even better you should watch and build this project up here with me

Channel: Anders Jensen

Views: 14,640

Keywords: power automate desktop, power automate desktop use case, power automate desktop advanced, anders jensen, tutorial, save web data to excel, rpa, robotic process automation, office365, microsoft power automate desktop, power automate desktop tutorial, power automate desktop web automation, power automate desktop examples, power automate desktop web, power automate desktop web ui elements, power automate desktop excel, excel in power automate desktop

Id: 7R5EjshDoQU

Length: 129min 56sec (7796 seconds)

Published: Wed Sep 21 2022