Effortlessly Scrape Data from Websites using Power Automate and Power Apps

hi there folks in today's demonstration i'm going to show you how we can scrape data from a website using a power automate cloud flow so in today's example i'm actually going to look at a table of exchange rate data i'll then pull that from the website into a json array which and then in my scenario going to pass to a power app and display in a gallery now of course because the data is in an array you could use it to add data to a table in excel or maybe add some items into a list or indeed save as records to dataverse plenty of options with the data once it's converted to an array if that's something that interests you please make sure you watch on if you haven't already make sure you like and subscribe and without further ado let's jump into the demonstration so here you can see the table in question on our website here we've got three columns of data with the currency and then two different values i'm going to pull this across into a flow which i'm going to build from scratch today now if i start with my flow you can see that i have the manual trigger and i will eventually convert that into the powerapps trigger but for now for testing i'm going to kick things off with a premium connection so we do need to use the http action and that will allow us to access that website via the get and then i can paste the url that was keen to get out there into this parameter here now if i go ahead and save this and test it we'll be able to see that just from this quick action that we've added we're able to get all of that html back into our flow so open that up we can have a look at the body here and here we have all the html so if i jump back onto that website and want to right click and go to view page source i can then do a search for the table tag which is what i'm going to be doing today in power automate so you can see that i have three potential opening table tags there if i jump down onto the second one and maybe start scrolling across a bit hopefully we'll start seeing some data that's uh recognizable so i see at least one of the columns there we've got monet and we've also got h a h um my french is not great so apologize but definitely we're pulling through some of those values we've got the currency for euros and this is the the string of data that we're going to look for in our flow so how do we get that well we're going to have to use compose several times throughout this solution and i'm going to use index of and of course that is going to allow me to return the position of a string so i'm going to look at that body and i want to return that opening tag of table but i just did a search there on the page source and it wasn't the first table it was actually the second table so i'm going to use the expression nth index of which will allow me to find the nth occurrence and in this case if i put in a comma i want to find the second occurrence of that tag now i've highlighted that expression i'm going to copy it and say okay we'll rename this action to the opening tag and then i'm going to create another compose to find the closing tag and that's why i've copied that expression so closing tag if i jump into the expression builder here and paste that in rather than this opening tag i now want to find the closing tag we just got the forward slash in front of it there and if i hit okay we now have the position of the second occurrence of the opening tag and the second occurrence of the closing tag with the next compose we can go and get that sub string and we're going to use the expression substring to do that so in terms of dynamic values we want to check that body for this particular substring and the substring expression looks for a starting index which is our opening tag so we can pick there the opening tag and then we're looking for a length now if the closing tag is at position 500 and the opening tag is at position 400 then the length is going to be the closing tag minus the opening tag so we can use sub open close brackets and we're going to insert the closing tag a comma and then the opening tag and all being well if i hit ok that should update and save so that will get us our table string i'll call that table i'm going to go ahead and save and test and there's one thing to note at this point the closing tag is the position of the beginning of the strings the beginning of that closing tag but i do need to have everything up into the end of that closing tag and if i expand this compose action here we can see that whilst i've got the opening tag if i go all the way to the end i'm missing the closing tag so simple simple step all i need to do is just type in that closing tag and that completes off our table or our html table next step is all about converting it into xml because if we convert it into xml i can convert it a lot easier into json so another compose i'm going to rename that as xml and it's quite simple i type in the expression xml open close brackets and select the output from that compose table and say okay now that it's an xml i can easily convert it into json and i can do that using the json expression so type in json open and close brackets and if i insert that compose xml and hit ok i'm just going to rename this as well so that i know this is my my json array if i go ahead and save and test that we can have a look at the output of both the xml and the json array action now i've got a video on doing this in more detail more complex situations but this is a relatively straightforward situation for converting it into xml and then into json so we can see now we have our json array and of course to expand this you can see that the table tag is now a key for one of the objects and we've got a table header array here with the column names and then the important bit is this table row array because you can see now that we have these repeating objects that have the currency and then these two values that we're looking to select and so for sending this back to powerapps or doing anything with it to be honest if i'm wanting to create a new list item i want to simplify this data quite significantly to be honest so i'm going to copy this object and i'll open it up in notepad plus shortly to have a look at it but the first thing i want to do is to get access to this table row object so i can get access to this full array and if i look at the path i can see that the path will be table and then follow this line down is tr so go back into edit and i'm going to go into compose and then i want to create a new expression and a little tip here i want to get the expression for this action here i'm going to go into the expression tab type in the number one pick that compose json array and then just get rid of that one and then because we looked at that array a minute ago i want to get the table and then the tr object and hopefully if i say okay to that and hit test it should return to me all those objects with the row data so test that and have a look and we can see that we now have if i expand that all of the individual objects containing each of those rows so this is where we can use a select and the select will allow us to then pick out the header we've got here for the currency and then those two values and so a select will let us loop through each of these objects individually and grab those values so if i go back into edit and i'm going to use my select action and if we insert that compose as the input we then need to define our map so we need those three column headers so i'm going to jump back onto the website and just grab these column headers of course i could call them anything that i want to be honest but i'll just go with the values that are on the website for now i'm not even going to try and pronounce it because i cannot speak a word of french we'll grab that last one there and chuck that one in and just tidy this up with a few extra return lines and if i bring across notepad plus and have a look at one of these objects here you can see that first of all we need to get into this td key and then we need to get into this p key in order to get the euro string but it is actually different for the other two objects beneath so if you know how to access objects within an array they're called by integer indexes so this is integer index zero this is one this is two and we want to loop through all of these in our select so what we do here in the expression tab is to type in item question mark and then we need to think about what we want to retrieve from this object so item is going to return everything we need to get into this td key so if i type in now in single quotes td and then we want to get into the first object in the next array so thinking about inter integer indexes that will be zero so i can put in question mark and then in brackets zero and then after that if i put another question mark and jump back onto this notepad plus i want to get into the value p for this particular one here so all i need to do is square brackets single quotes and the letter p so i'm going to copy that and think about the next one so the next one if i paste that in and bring up the notepad if i was to return p i would get everything in this object but i want to get the text so i can copy that key name and i've got to remember also that we've got several objects so i'm no longer an object zero i'm in an object one so if i go back here i need to change this to object one and rather than it just being p it needs to be p text so i can use a forward slash text the other option would be to put another question mark and put the text value in square brackets so if i copy that say okay we'll go into this one here i can paste that in all i need to change here is the value 1 to value 2 because we're now into the last object here 0 1 and just just to demonstrate if i was to remove this text here i could put in the question mark and the hash text so this would be the alternative in single quotes if you're not familiar using the forward slashes so if i say okay and save that and test that that should hopefully get us a nicely repurposed array of data based on those three columns so it's run okay if i go to the select here we go we can see we've got the euros starlings dollars etc so all those values have been nicely pulled through and based on your requirement you can either now save that to excel into a list dataverse etc but for today i'm going to pass this back to my power nap so for the power app the first thing i need to do is i need to update the trigger so if i delete that trigger i can then go and select the powerapps triggers and there's one and two we're gonna go with version two even though it doesn't really matter version two has the improvements for the input parameters we don't need that today but i prefer the version two and then in terms of response whilst there is a respond to power naps or flow that you see here it will only currently allow you to return a string there is a new feature that has come out this week it's not yet reached my tenant sadly that does allow you to parse strings in power apps to create an array when that comes out i'll maybe do a quick video for today's video because we're already in the premium actions i'm going to use the http response which is here it's a premium action and this allows me to respond back to the powerapp with the data from the select so the data is like i mentioned from the select we do need to however create this json schema and the way we do that is from running the flow so i go ahead and test that again and this should run the flow and then we can jump into the select action and copy the output to create our schema so highlighting all of this we could do a ctrl a and ctrl c back into edit i can then go into generate from sample and insert my payload and say done and you'll see that i have a nicely created schema here that now will allow this action to return the data back to powernaps so go ahead and give that a save i also better give it a more meaningful name or i'll never find it to scrape data from web hit the save button i'll jump onto my power app so with the powerapp i'm just going to use a button today to run the flow you could do it via the unvisible property of your screen and i'm going to populate a gallery so if i go and insert my vertical gallery and i need to attach my flow to my power app so if i go into the power out automate button here on the left hand side go to add flow and go and search for this new solution scrape data from web so that's just attaching that flow into my power app and then once that's done if i go to my button i should be able to find that new expression for that flow so pressing that button now will run that flow but i want that the results to be in a collection so i can clear collect i can type in my collection name which can be my scraped data or scrapped data i've called it scraped data i need to put the closing bracket at the end there so that keeps that expression happy and then i need to update my gallery so that it's now using that collection which is created as a result of running my flow so if i put it into play mode and hit the button hopefully it will bring through the data which is fantastic and then if i want to display other fields i can control c and control v in order to create just a duplicate text field which we can see here on the left hand side and then i could change the expression here to return the other value and there we go so we have scraped the data from the website returned it into a json array then we've passed that onto our powerapp into a gallery and that we can now see that data so this will be real time if this website gets an update and these prices change if we were then to trigger that flow it would of course trigger this http action pull the data as it is find the opening closing tag get the table converted xml into json and then we can start building out our new select action to create our nicely repurposed array of data so that marks the end of the demonstration uh plenty to take in there again and some good use of some of the data operations select and item and substring in index of or nth index of if you're looking to find a particular string or occurrence of a string if you haven't already please make sure you like and subscribe and hope to see you again sometime soon thanks very much for watching cheers

Channel: DamoBird365

Views: 19,023

Keywords: Power Automate, scrape data, website, HTML table, XML, JSON, array, Power Apps, collection, Gallery, data collection, automation, business automation, workflow automation, Microsoft, Office, productivity, web scraping, Microsoft Power Automate, data collection automation, Power Apps integration, DamoBird365, Power Automate Tutorial, Power Automate How To, Power Platform Tutorial

Id: 2kvSlh-Tvb4

Length: 17min 35sec (1055 seconds)

Published: Sat Sep 10 2022