Extract Live Web Data into Structured Records with AI Prompts

Extracting data locked inside a public website into your automated workflows has always demanded tricky workarounds—screen scraping, third-party connectors, or repetitive copy-paste routines. The HTTP action combined with AI Prompts changes the game. Instead of wrestling with brittle HTML parsing expressions, you let a large language model read the page content and return exactly the fields you need in clean JSON.

This approach works best on sites where the text is present in the initial HTML payload. Pages that rely heavily on JavaScript to build content after the initial load require a different solution—Power Automate Desktop’s browser automation is typically the right choice for those scenarios.

The Scenario: Automating Daily Data Collection

Blue Ridge Environmental Services needed to capture daily air quality readings published by a state environmental agency. Every morning the agency posts an updated page listing monitoring stations, pollutant types, AQI values, and health advisories. Blue Ridge wanted this data in a SharePoint list so their BI dashboards and alerting systems could consume it automatically.

Instead of hiring someone to type in the numbers, they built a scheduled cloud flow that:

1. Fetches the page HTML
2. Strips away the markup
3. Asks an AI model to extract the station records
4. Writes each record to a SharePoint list

Let’s walk through the exact steps.

Step 1: Fetch the Page HTML

Create a new scheduled cloud flow (or instant cloud flow for testing). Add the HTTP action.

• Method: GET
• URI: https://airquality.example.org/daily-report
• Headers: Accept: text/html

The HTTP action returns the full HTML source of the page. In the next step we strip away all the tags.

Step 2: Convert HTML to Plain Text

Add the HTML to text data operation (found under Data Operations in the action picker). Connect the Body output of the HTTP action into the Content input field.

The action removes all tags and returns a block of plain text with whitespace separating the formerly distinct elements.

Step 3: Build the AI Prompt

This is the heart of the solution. From the Power Automate left-hand menu go to AI Prompts → Build your own prompt.

Name the prompt something descriptive, for example Extract Air Quality Station Data. Use the following instructions. A precise prompt reduces hallucinations and keeps the output structured.

You are a data extraction assistant.
Analyse the text from a daily air quality report.

For each monitoring station, extract the following fields:
- StationID: the unique identifier of the station.
- Location: the city or county where the station is located.
- Pollutant: the primary pollutant measured (e.g. PM2.5, Ozone, NO2).
- AQIValue: the Air Quality Index integer value. Return only the number.
- HealthAlert: the health advisory label (e.g. Good, Moderate, Unhealthy for Sensitive Groups).
- ReportDate: the date of the report. Return in YYYY-MM-DD format.

Output only a valid JSON array of objects matching this schema:
[
{
  "StationID": "string",
  "Location": "string",
  "Pollutant": "string",
  "AQIValue": number,
  "HealthAlert": "string",
  "ReportDate": "string"
}
]

If a value is missing or unclear, use null.

Report text:
{{PagePlainText}}

Create an input variable called PagePlainText. You will pass the output of the HTML to text action into this variable when you use the prompt in your flow.

Test the prompt with a sample web page. When it returns the expected JSON array, press Save custom prompt and close the editor.

Step 4: Process the AI Output in Your Flow

Go back to your cloud flow. The custom prompt now appears as an action (usually under the AI Builder or AI Prompts category).

1. Wire the AI Prompt action.

   • Input PagePlainText: output of the HTML to text action.

2. Add a Parse JSON action to validate the structure and make the fields available as dynamic content. Use this schema:

{
"type": "array",
"items": {
  "type": "object",
  "properties": {
    "StationID": { "type": "string" },
    "Location": { "type": "string" },
    "Pollutant": { "type": "string" },
    "AQIValue": { "type": "number" },
    "HealthAlert": { "type": "string" },
    "ReportDate": { "type": "string" }
  },
  "required": ["StationID", "AQIValue", "ReportDate"]
}
}

1. Wrap an Apply to each loop around the Parse JSON output.

2. Inside the loop add a SharePoint – Create item action (or Dataverse – Add a new row). Map the dynamic fields to your target columns.

Step 5: Schedule and Run

Set the trigger to Recurrence to run daily, weekly, or on whatever cadence matches the source page’s update schedule. Run the flow manually the first time to confirm everything connects.

Common Pitfalls and How to Avoid Them

• Client-side rendering. If the HTML to text output is mostly empty or contains JavaScript code blocks, the site generates its content in the browser. Cloud flows cannot execute that JavaScript. Fall back to Power Automate Desktop or search for an API or RSS feed.
• Token limits. A very long web page can exceed the model’s context window. Truncate the text to a specific section, or switch to a model with a higher token ceiling (like GPT-4o).
• Inconsistent AI output. The same prompt can produce slightly different JSON from run to run. Add a few-shot example inside the prompt text, and always run the output through Parse JSON before writing to your destination so the flow fails cleanly if the schema doesn’t match.
• Cost management. Each AI Prompt invocation consumes AI Builder credits. Monitor your consumption, especially if the flow runs on a high-frequency schedule or against very large pages.

Final Recommendation

The combination of an HTTP request, an HTML to text conversion, and a custom AI Prompt is one of the most versatile patterns in the Power Automate ecosystem. It replaces brittle string-based parsing with an intelligent extraction step that adapts to small changes in the source page much more gracefully than a group of compose and filter actions ever could.

Start with a small page containing a handful of data rows. Perfect your prompt until the LLM returns perfectly valid JSON on every test. Once you have that foundation, scale the pattern to any server-rendered public website that your business reads manually today.

References

• Matthew Devaney, How To Extract Data From Web Page Using Power Automate
• Microsoft Learn, AI Prompts overview
• Microsoft Learn, HTTP connector reference