Effortlessly Scrape Data from Websites using Power Automate and Power Apps

Summary

In this tutorial, the presenter demonstrates how to scrape exchange rate data from a website using Power Automate cloud flow. They extract table data, convert it into a JSON array, and showcase how to utilize this data in various applications like Power Apps, Excel, or Dataverse. The process involves using HTTP actions, data manipulation expressions like substring and nth index of, and concludes with displaying the scraped data in a Power App gallery. The video is a practical guide for those interested in web data extraction and integration.

Takeaways

• 🌐 The demonstration focuses on scraping data from a website using Power Automate cloud flow.
• 📊 The example data is a table of exchange rate information which is extracted and converted into a JSON array.
• 🔄 The JSON array can be utilized for various purposes such as updating Excel tables, adding items to a list, or saving records to Dataverse.
• 🔧 The process involves using HTTP action to access the website and retrieve HTML content.
• 🔍 The script uses 'indexOf' and 'nthIndexOf' to locate the specific table containing the exchange rate data within the HTML.
• 📖 The data is extracted as a substring between the opening and closing tags of the table.
• 🔄 The extracted HTML table is converted to XML and then to JSON for easier manipulation and use.
• 🛠️ The JSON data is simplified using 'select' and 'compose' actions to extract only the necessary information.
• 📱 The final step involves passing the JSON array to a Power App where it can be displayed in a gallery or used for other purposes.
• 🔗 The demonstration also covers updating the Power Automate flow to respond to a Power App trigger and creating a JSON schema for the response data.

Q & A

What is the main focus of the demonstration in the video script?

The main focus of the demonstration is to show how to scrape data from a website using a Power Automate cloud flow, specifically targeting a table of exchange rate data.

What is the final format of the scraped data in the demonstration?

The final format of the scraped data is a JSON array, which can be used for various purposes such as adding data to a table in Excel, a list, or saving as records to Dataverse.

How does the presenter plan to use the scraped data in their scenario?

In the scenario presented, the scraped data is planned to be passed to a Power App and displayed in a gallery.

What is the first step the presenter takes in building the flow for scraping data?

The first step in building the flow is to use a manual trigger and an HTTP action to access the website via a GET request and retrieve the HTML content.

Why does the presenter use the 'nth index of' expression in the flow?

The presenter uses the 'nth index of' expression to find the second occurrence of the opening table tag in the HTML content, as the data of interest is within the second table on the webpage.

How does the presenter extract the table data from the HTML content?

The presenter extracts the table data by finding the positions of the opening and closing table tags, then using the 'substring' expression to get the data between these tags.

What conversion does the presenter perform on the extracted table data?

The presenter converts the extracted table data into XML first and then into a JSON array for easier manipulation and use in Power Apps.

What Power Automate expression is used to simplify the JSON array data into a more usable format?

The presenter uses the 'select' action in Power Automate to simplify the JSON array data by looping through each object and extracting the desired values based on column headers.

How does the presenter update the Power Automate flow to respond back to the Power App?

The presenter updates the flow to use an HTTP response action to send the data back to the Power App, and creates a JSON schema based on the output of the 'select' action.

What is the method used in the Power App to trigger the flow and display the scraped data?

In the Power App, the presenter uses a button to trigger the flow and populates a gallery with the data by using a collection that is created as a result of running the flow.