Read Excel data and download files from website | Dynamic selectors | Power Automate Desktop

hello there it's gracella here in today's video we're going to be using power automate desktop to read some data from an Excel file and perform actions based on the data we are reading from the file for this use case we're going to be working with this Excel file it has just three columns invoice number status and an email what we want to do with this is to just work with the invoices that are awaiting payment and then download the file from our accounting system to have it in our computer so what we want to replicate in this automation is just logging in and then going to the business stab and then invoices and here in this invoices list what we want to do is just find all the invoices that we have in the Excel file that are awaiting payment so for example this one and then go to the search section then enter or invoice number then hit on search and this will this will give us the result we just need to open it and then we are going to click on print PDF some of the files we are going to be printing will show us this popup so we will also set up our automate to recognize this popup only when the popup is present and once we have that we will move forward to just the next invoice and to do that let's just open for animate and I have already created my blank flow so for that let's just sign out from the system and let's start in the login screen so let's use the recorder and once we start the recorder let's indicate power automate that we want to start a new instance of our browser and we are going to be starting in the Lo page so let's update that here as well and then let's start recording then we are here just signing in then we click on the login button that will take us to the next screen where we just click on the business on the business Tab and something really important is that we need to make sure that when we are doing or creating animations with the browser that this browser icon is showing up whenever we are hovering over our elements because that means that the extension is working properly if you see this icon that looks like a window over a browser element that might mean that your extension is not working properly so in our case you can see that I have here the right icon showing that means that the extension and power automate is recognizing correctly these items as a browser item so the next thing we want to do is just click on the invoices option and then we are going to search and then let's get one of these invoice numbers and move to the side of the screen and then record and then just go here and input the invoice number make sure that part automate recognizes that we are making an input there and then finally hit on search then we're going to click on the first result and go here and then we are going to be clicking on the print PDF then we are going to be clicking on this option that says Mark as sent and now after that you'll notice that their invoice will be just downloaded I have set up here my browser to just download things instead of asking me first so you can see now that it's downloaded here in the folder that I have indicated that I want things to be saved on and here we we could do several things we could send through email or we could for example upload to SharePoint if you want to know more about how to upload things from your computer to SharePoint using power aromate you can have a look at the video in this in the description of this video because there we go deeper on that topic the focus of this video is just performing actions Based on data that we have on an Excel file so for this animation specifically we're going to leave it here just downloading the actual invoice into our computer so now that we have recorded the whole process let's hit here on done here we're going to update this flow a little bit just to make it a little bit better and better at handling any error or any delay that happens so in this case once we log in we want to wait for this button to be available because it may be that the web page doesn't load immediately so what we're going to do is just go here to this browser Automation and then we're going to be using this option that's called wait for web page content so we're going to put it right before the button business and what we want to do is just wait for this button to be available in in the web page so we know that we that power automate can now click it so now let's just look for this business button then hit on select and then we are sure that once we reach this stage of the process we have successfully logged in and all the pages have loaded into this accounting system we are using as example then something else that we can do instead of clicking on business and then invoices is just going directly to this web page so that way we are going to be saving two clicks that could go wrong at some point just because so I'm just going to copy this and then I will use the option go to web page I'm going to do that right after we log in and we make sure that we are logged in by waiting for the content and then we are going to be clicking on the URL here we are going to be just inputting the uh invoices list URL and then hit on save that will help us save these two clicks so I'm just going to delete them then I am just going to add another wait for web page content right before our search so we also make sure that we are waiting for the content to be available and for everything to have loaded properly so let's do the search here and then select then we are entering the section where we actually input the text that is coming from the Excel file so we're going to update that soon now we also have this last item that we know is not going to always show up because if I click again on one of these items that I already opened and I try to click print you're going to see that it prints it directly it does doesn't check first if I want to mark it as sent so we only want to click on this if the actual element is showing up so for that we are going to be using um this same section in the browser animation that is called if web page contains so if the web page contains this element the one that says Mark asent then we want to click it if not we want to skip it so I'm just going to paste it and put it here in the middle I'm going to add a little delay here in between so we can just wait and make sure that the element is not just taking forever to load so I'm just going to wait 3 seconds and after that if the element is not present we can just skip it and now that we have generated the base actions based on the recorder let's just make sure that it's working properly and then we will move forward to the Excel part okay so here it seems like there are a couple of clicks that should not be there so let's remove these two and then we have the search and then we actually click on the row that we're interested in so let's run it once more just to make sure everything is working properly until we reach to the till the end of the automation okay so now we are ready to start using Dynamic data that is coming from our Excel file so we're going to work on that now so what we want to do is we want the login part to just happen once because we just need to log in once and then we read the list of invoices so right after we see that we have logged in properly when we see the business button there we're going to add some Excel actions so I will just open the Excel section and I'm going to use the launch Excel action so I'm just going to put it here right after step seven and first is asking us if we're going to be doing this with a blank document or open an existing document our document is in the C folder and then invoices list so I'm just going to open it then we can do several things what we're going to be doing first is just reading from the Excel worksheet and then here we can we can indicate part animate which kind of data we want to read do we want to read everything from the spreadsheet do we want to read just one a single do we want to read just a single cell so in this case we are going to be using all available values from worksheet and whatever we select here will depend a lot on the type of file we're working with as you can see here I only have a couple of columns and a couple of rows but if you have for example the type of file that you know will have some hidden column at the very end or something like that or some hidden Row in between it might be that this option will not be the best in this case I know that this is for example a system generated report so I know that only the first three columns will always be populated I just don't know how many rows they will be populated but if I know that there will be something here at the end or something here below the actual data that I want to work with probably you don't want to use this option because what this will do is just select everything that has a value or everything that has a used Range in Excel in the Excel file you're working with so if you don't if you know that that could be an issue you could also you could also use either values of name cells if you know your Excel file contain name named ranges or you can use as well values from a range of celles and here it will ask you the start column and the start row and the end column and the end row so what you could do with that one is reading the first free column and the first free row if you use that specific action it will calculate what's the first free column that it finds and the first free row so in this case it will find that column D is my first free column and row 10 is my first my first free row so in this this case I could use this calculation in the next step to just know which data to read so for example if I select the option values from a range of cells I could just use a start Row one start column one well in this case a start row two and we can use the end column which is going to be the column that we are working with here in column D and then column the and the end row which we're going to be 10 and this will be dynamic from the from this action so that's one of the other ways that you can do this my case it's very simple so I'm just going to use this one and this is going to be saved in the variable that is called Excel data I'm just going to rename these two invoices so it's a more a friendlier name so I can remember later now that we are reading this we know that we have through we have to go through each of the rows that we have here so what we need to do is to do op we need to perform a loop over this set of rows to just find the items that are awaiting payment so we can download them into our computer so for that we what we are going to do is use this action that is called loops and we are going to be using this for each action so we're going to grab it and just put it right after reading or Excel data it will ask us what do you want to Loop what do you want what's the value you want to to iterate and in this case we want to iterate the invoices that's the table that we are grabbing from Excel files and now we are going to store this in this variable that is in this case is called current item I'm going to rename it to current invoice so now we have that and let's hit on Save what we want to do is to perform all these actions here for each of those rows so what we are going to do is we are going to use the control key in or keyboard to select all of the ones all of the actions that we know that we want to perform by each of the items that we have in or Excel file and just put them in the middle of this Loop in the middle of this for each that way for every single row that we find here this is going to happen and we are going to be searching for this invoice number now remember that we only want to do this is if the status is awaiting payment so what we can do here is just a conditional so we know that when this status is present in the Excel file in the current invoice that we are checking we want to perform all these actions so I am going to put my if in the middle of my for each and I am going to use the current invoice item and this is in column number two column status so what we are going to be doing is using these square brackets and then writing the number one one this is because columns are read in power animate using indexes and index zero will always be column number one index one will be column number two and so on so if you have column number three or four you have just to um remove one from that number and that's what what you would have to use here to find the index for that specific column and we want to know if the status which is in index one that's column number two it's equal to awaiting payment if that's the case then we want to perform all these actions so we are going to select all of them you can use you can do this by using the shift keyboard and selecting the first and last one and just move them inside the awaiting payment okay now we have that now we also want to make this value Dynamic instead of always writing this invoice number 1042 we want to just grab whatever invoice number is gr here so in this case we want to do a similar thing to what we were doing with the payment I'm just going to copy this code that has the one and I am going just going to write a reference instead of a reference to that same variable which is the current invoice but just column number one which is index number zero so I am just going to remove this 1042 and placed my variable which is the current invoice that means my current line but in this case I want to bring index zero which is the first column that I find so I'm just is going to hit on Save and that way bar automate will be searching instead of just gring the same value all over again it will just uh gr the value that is coming from this column whenever the status is awaiting payment now something else that is really important when we are working with web pages and downloading data and finding data in a table is that usually the elements in our table will have IDs or will have a for or a way for the web page to know which are the specific rows and the unique IDs these rows have so in this case you see that we are clicking on this specific data cell that has this specific value so it might be that power automate will not always find what's the specific row if we are looking for a different invoice number so if I search for example for invoice 1039 it may be that power automate won't be able to recognize it to recognize this new invoice number using the setup that we recorded for the invoice number 1042 that might be the case but that but it might also be the case that per automate will be able to recognize it and that depends a lot on the HTML structure on the website code in this case we know that for automate recognize this specific row as this name so I go to this section that is called UI elements and then here I just look for for the actual element that we're looking for in this case it is stable data cell o RC 1042 when I double click I see here that power automate is recognizing this specific item using the ID of this table detail and the ID that it's looking for is X gen 44 if I go here to the browser and I search again my 1042 invoice and then I see the table here with the result and then I right click on it and then I click on the inspect button you will see that this is the specific ID that this is looking for now let's see what happens if I search for invoice 1039 let's do this and if I right click on it you see that it still recognizes my table detail my table cell as extension gen 44 so that means that part that this website is really not assigning these IDs based on the actual invoice number but probably based on position and all of them might start with X gen 44 so that makes that makes us think that for animate will be able to recognize regardless of the invoice we're looking for because it seems like they like they always start with Gen 44 so let's look for another one just to make sure and if I go here and then I hear I do an inspect you see that it's always going to start with Gen 44 it seems like the first result will always get this ID but how we can do this if we know that the ID will not be the same and maybe it will variable it will be variable depending on the item there's not really a single way to achieve this but one way we can do it is just grabbing this specific item using the text that it has so in this case we want to select the table detail tag this DD based for example on the text that it has so what we can do is just just uh make sure that we have the right the right element selected and then click here on the text editor once I click on the text editor I'm going to remove everything that is after the TD and I'm going to write a column these two dots and then we're going to write contains in these contains I'm just going to pass the very same value that we were working on here which is the current invoice and then index number zero that way what we want to do is to search instead of using the ID of the element that for now we have seen that it seems that it will work like it will recognize the same ID always but we are just making it variable so we make sure that it will always work based on the invoice number and not the ID because sometimes the ID will be variable depending on the web page so we're just looking at what would be the option if we wanted to just select the invoice based on the actual number or some actual text or ID we have have so in this case we want this DD to contains to actually contain the text that we have in our current invoice in index number zero so now let's hit on Save and let's test from start to end and see if our changes are working properly and you see that now it's searching for the invoice and then found the actual row that we're looking for and remember that we did this dynamically so we're not using the X gen 44 we're not trusting that it will always assign the same ID we are doing it that dynamically and it's selecting the invoice number from the row based on the actual number that we're looking for and yeah now we see that we have the mark sent in the next invoice so it's actually clicking on it the first time because it's present in the web page and now it's doing the same thing for all the invoices that we have have here and something important to mention about the changes We performed on this specific item to search this table to search for the specific invoice is that power automate uses jQuery selectors so in this case I knew that for an element to be recognized by the text we are just using the column then the keyword contains and then the actual text that we want to look for but there are many many ways in which we could set up a power atom aate workflow to recognize elements in our web page and the way we can do that is just performing a search in Google for example just use the word jQuery selector and then whatever selector you want to use in this case I want this to contain certain text and this will give me exactly what's the format that I need to use in power automate and you can see that this selector is what will give me the is what will help power automate identify what's the element it needs to click on and what's the element it needs to find if you also for example want to search something based on the name you can also search you can also perform a Google Search and you're going to find some documentation on how this can be achieved and pretty much what you just need to do is to identify what's the thing that makes unique the element that you want to click on in this case we knew that this table only had the invoice number once and that's the that's the way power automate understood that we could just click on this specific item and what you can always do to identify the best way to to set up your power automate workflows is just right clicking on the element and then inspecting the element and just see and just have a look at the HTML code that you have in in the web page and that will help you define what's type of jQuery selector that you want to use in this case I thought that the best option was just using the TD or the table detail tag and just combining that with the this specific invoice number that we were looking for and but this is not the only way we could do this there are many ways we could achieve something similar and that will depend on the type of code your web page has and many other things but you know that you can always search for jQuery selector and then that will give you an idea of what you would have to use here in the selector editor that we have in our adate and I hope that this was useful for you see you next time

Channel: Power GI

Views: 111

Keywords:

Id: UUMjipBf63I

Length: 24min 33sec (1473 seconds)

Published: Mon Jun 17 2024