Connecting Power Automate Desktop to REST APIs with the HTTP Actions: Sending Requests, Parsing JSON Responses, and Writing Results to Excel

Introduction

Imagine you're a data analyst at a logistics company. Every morning, someone manually opens a browser, navigates to a shipping carrier's tracking portal, copies delivery status information for dozens of shipments, and pastes it into an Excel spreadsheet. It's tedious, error-prone, and completely unnecessary — because that carrier offers a REST API that can return all of that data in seconds with a simple request. The bottleneck isn't the data. It's the bridge between the API and the spreadsheet.

Power Automate Desktop has a built-in set of HTTP actions that let you make real web API calls directly from your desktop flow — no coding environment required, no extra middleware, no cloud connector fees. You can query a live API, extract the values you care about from the JSON response, and write them straight into Excel, all within a single automated flow. This lesson shows you exactly how to do that, from your first HTTP action to a fully populated spreadsheet.

By the end of this lesson, you'll be able to build a desktop flow that queries a public REST API, handles the JSON response intelligently, extracts specific fields from nested data structures, and writes the results into a formatted Excel file row by row.

What you'll learn:

• How REST APIs work and what makes an HTTP request valid
• How to configure the Invoke web service action in Power Automate Desktop
• How to parse JSON responses using the Convert JSON to custom object action
• How to navigate nested JSON structures and loop through arrays of results
• How to write extracted API data into Excel row by row

---

Prerequisites

Before diving in, you should have Power Automate Desktop installed and be comfortable with the basics of building flows — if you haven't done that yet, start with Getting Started with Power Automate Desktop: Installing, Recording, and Running Your First Desktop Flow.

You should also understand how variables work in desktop flows. If terms like "list," "data table," or "custom object" feel unfamiliar, spend a few minutes with Variables, Lists, and Data Tables in Power Automate Desktop: A Complete Practitioner's Guide before continuing.

For the Excel portion, you'll need Microsoft Excel installed on your machine. We'll be writing data programmatically, not recording clicks, so familiarity with Automating Excel with Power Automate Desktop: Reading, Writing, and Running Macros will help you follow along smoothly.

---

What Is a REST API, and Why Does It Matter Here?

A REST API (Representational State Transfer Application Programming Interface) is a standardized way for two computer systems to exchange data over the internet using HTTP — the same protocol your browser uses to load web pages.

Think of it like a restaurant menu. The restaurant (the API server) has a fixed set of dishes it can prepare (endpoints). You (the client) place an order (make an HTTP request) by specifying what you want. The kitchen prepares it and returns your meal (the response), typically formatted as JSON.

JSON (JavaScript Object Notation) is a text-based format for representing structured data. It looks like this:

{
  "city": "Chicago",
  "temperature": 72,
  "conditions": "Partly Cloudy",
  "wind": {
    "speed": 14,
    "direction": "NW"
  },
  "forecast": [
    { "day": "Monday", "high": 75 },
    { "day": "Tuesday", "high": 68 }
  ]
}

Notice the structure: key-value pairs at the top level, a nested object for wind data, and an array (a list) for the forecast. Real APIs return responses exactly like this. Your job in a desktop flow is to reach into this structure and pull out the values you need.

The most common HTTP method you'll use is GET — it retrieves data without changing anything on the server. You'll also occasionally use POST, which sends data to the server (like submitting a form or creating a record). We'll focus on GET for this lesson since it covers the majority of data-retrieval scenarios.

---

Understanding the HTTP Actions in Power Automate Desktop

Power Automate Desktop provides HTTP actions in the HTTP action group, found in the left-hand Actions panel. The primary action you'll use is called Invoke web service. Here's what it does and what each setting means:

• URL: The full address of the API endpoint, including any query parameters
• Method: GET, POST, PUT, DELETE, etc. — what kind of operation you're performing
• Accept: Tells the server what format you want back (usually application/json)
• Content type: Only relevant for POST/PUT requests where you're sending a body
• Custom headers: Additional key-value pairs the API requires (often where API keys go)
• Request body: The payload you send with POST requests
• Connection timeout: How long to wait before giving up (in seconds)
• Response: Where the action stores the raw response text — this is an output variable you name

The action stores the raw HTTP response as a text variable. It doesn't automatically interpret the JSON — that's a separate step.

---

Setting Up Your First HTTP Request

Let's build a flow that fetches current weather data for three cities and writes it to Excel. We'll use the Open-Meteo API (open-meteo.com), which is completely free and requires no authentication.

The API endpoint for current weather looks like this:

https://api.open-meteo.com/v1/forecast?latitude=41.85&longitude=-87.65&current_weather=true

Breaking that down:

• https://api.open-meteo.com/v1/forecast — the base endpoint
• latitude=41.85&longitude=-87.65 — query parameters specifying Chicago's coordinates
• current_weather=true — asking for current conditions specifically

Step 1: Add the Invoke Web Service Action

Open Power Automate Desktop and create a new flow. In the Actions panel on the left, search for "Invoke web service" or navigate to HTTP → Invoke web service. Drag the action onto the canvas.

In the action's configuration dialog:

• Set URL to: https://api.open-meteo.com/v1/forecast?latitude=41.85&longitude=-87.65&current_weather=true
• Set Method to: GET
• Set Accept to: application/json
• Leave Headers, Credentials, and Request body empty for this public API
• Set Connection timeout to 30
• Note the Response output variable — rename it to WeatherResponseText for clarity

Click Save. Run the flow up to this action (use the Run button or step through with F10), then inspect the WeatherResponseText variable in the Variables panel on the right side of the designer. You should see raw JSON text like:

{
  "latitude": 41.875,
  "longitude": -87.625,
  "current_weather": {
    "temperature": 68.5,
    "windspeed": 12.4,
    "weathercode": 3,
    "time": "2024-05-15T14:00"
  }
}

This confirms your request succeeded. You have live data. Now you need to reach into it.

---

Parsing the JSON Response

Raw text isn't useful on its own. You need to convert it into a structure that Power Automate Desktop can navigate. That's what the Convert JSON to custom object action does.

Step 2: Convert JSON to a Custom Object

Search for "Convert JSON to custom object" in the Actions panel and add it after your Invoke web service action. Configure it:

• JSON: Set this to your WeatherResponseText variable (click the variable icon or type %WeatherResponseText%)
• Custom object: Name the output variable WeatherData

After this action runs, WeatherData is no longer a blob of text — it's a navigable custom object (sometimes called a JSON object or parsed object) with properties you can access using dot notation.

Step 3: Extract Values with Dot Notation

To get the temperature, you reference: %WeatherData['current_weather']['temperature']%

Notice the bracket notation. Power Automate Desktop uses ['property_name'] to navigate into nested objects. Some versions of the product also support dot notation (%WeatherData.current_weather.temperature%) — test both and use whichever your version accepts.

Add a Set variable action and assign:

• Variable name: CurrentTemperature
• Value: %WeatherData['current_weather']['temperature']%

Add another Set variable action for wind speed:

• Variable name: CurrentWindspeed
• Value: %WeatherData['current_weather']['windspeed']%

Run the flow again and verify these variables populate with the right numbers. If you see the number correctly, you've successfully parsed a JSON response.

---

Handling JSON Arrays: Looping Through Multiple Results

Real-world APIs rarely return a single value. More often, you get an array of records — a list of orders, employees, products, or in our case, hourly weather forecasts. Let's extend the request to include hourly temperature data.

Modify your API URL to:

https://api.open-meteo.com/v1/forecast?latitude=41.85&longitude=-87.65&hourly=temperature_2m&forecast_days=1

This returns a JSON structure where hourly.temperature_2m is an array of 24 temperature values (one per hour). After running the updated request and converting the JSON, the structure looks like:

{
  "hourly": {
    "time": ["2024-05-15T00:00", "2024-05-15T01:00", ...],
    "temperature_2m": [58.2, 57.1, 56.4, 55.8, ...]
  }
}

To loop through the temperature array and write each value to Excel, you need a For each loop.

Step 4: Loop Through the Array

After converting JSON, access the temperature array with: %WeatherData['hourly']['temperature_2m']%

Add a For each action:

• Value to iterate: %WeatherData['hourly']['temperature_2m']%
• Store value into: HourlyTemp

Inside the loop, you now have HourlyTemp representing each temperature value as the loop progresses through the 24-item list. You also need the corresponding time — access it in parallel using an index variable, but for simplicity in this lesson, we'll use a row counter.

Before the loop, add a Set variable action: RowIndex = 2 (row 2, since row 1 will be our header).

Inside the loop, write HourlyTemp to Excel and then increment RowIndex by 1 each iteration.

---

Writing Results to Excel

Now comes the payoff — taking all those parsed values and writing them into a structured spreadsheet. If you want a deeper understanding of Excel automation in desktop flows, check out Working with Excel Ranges, Sheets, and Named Cells in Power Automate Desktop: Reading Tables, Writing Data, and Switching Worksheets Without Macros for a thorough walkthrough of the Excel action group.

Step 5: Launch and Prepare Excel

Before the HTTP request actions, add:

1. Launch Excel action — either open a specific file path or launch a blank workbook. Set the output variable to ExcelInstance.

2. Write to Excel worksheet action — write the header "Hour" to column 1, row 1, and "Temperature (°C)" to column 2, row 1. Do this twice, changing the column number each time.

Configuration for the first header write:

• Excel instance: %ExcelInstance%
• Value to write: Hour
• Write mode: On specified cell
• Column: 1
• Row: 1

Repeat for the second header:

• Value to write: Temperature (°C)
• Column: 2
• Row: 1

Step 6: Write Data Inside the Loop

You also need the time array. Add a second variable before the loop: access %WeatherData['hourly']['time']% and store it as TimeArray.

Inside the For each loop (which is iterating over the temperature array), you need a way to also grab the matching time value. Since both arrays have the same length and are in the same order, use a counter. Before the loop, initialize LoopCounter = 0.

Inside the loop, write:

Write to Excel worksheet (for time):

• Value to write: %TimeArray[LoopCounter]%
• Column: 1
• Row: %RowIndex%

Write to Excel worksheet (for temperature):

• Value to write: %HourlyTemp%
• Column: 2
• Row: %RowIndex%

Then add two Increase variable actions:

• Increase RowIndex by 1
• Increase LoopCounter by 1

Step 7: Save the Workbook

After the loop, add a Save Excel action:

• Excel instance: %ExcelInstance%
• Save mode: Save document as
• Document path: A specific file path like C:\Reports\WeatherData.xlsx

Then add Close Excel to cleanly release the file handle.

---

Making POST Requests with a Request Body

Not every API interaction is a GET request. If you need to send data to an API — submitting a form, creating a record in a CRM, or triggering a calculation — you'll use POST with a request body.

The body is typically formatted as JSON text. In Power Automate Desktop, you build the JSON string as a text variable before passing it to the action.

For example, if an internal API accepts a POST request to look up a customer:

URL: https://api.yourcompany.com/customers/lookup
Method: POST
Content-Type: application/json
Body: {"customer_id": "CUS-10492", "include_history": true}

In your flow, set a variable:

RequestBody = {"customer_id": "CUS-10492", "include_history": true}

Then in the Invoke web service action:

• Method: POST
• Content type: application/json
• Request body: %RequestBody%

If the customer ID changes each run (pulled from a list or entered by a user), you build the JSON dynamically using string concatenation:

RequestBody = {"customer_id": "%CustomerID%", "include_history": true}

---

Adding Headers and API Keys

Most production APIs require authentication. The most common method is an API key passed in a request header. In the Invoke web service action, there's a Custom headers field where you add headers as key-value pairs, one per line, formatted as HeaderName: Value.

For example, to include an API key for a weather service:

X-API-Key: your_api_key_here

If you're calling the GitHub API or Jira API with a bearer token:

Authorization: Bearer your_token_here

Never hardcode sensitive keys directly in the action field. Store them in a sensitive variable or retrieve them from a secure source. The lesson on Handling Credentials Securely in Desktop Flows: Sensitive Variables and Azure Key Vault covers exactly how to manage API keys and tokens without exposing them in your flow.

---

Hands-On Exercise

Build a complete flow using the following steps. This exercise uses the free Open-Meteo API and writes results to a new Excel file.

Goal: Fetch the next 7 days of maximum daily temperatures for New York City and write each day's date and temperature into Excel.

API endpoint:

https://api.open-meteo.com/v1/forecast?latitude=40.71&longitude=-74.01&daily=temperature_2m_max&forecast_days=7&timezone=America/New_York

Expected JSON structure:

{
  "daily": {
    "time": ["2024-05-15", "2024-05-16", ...],
    "temperature_2m_max": [72.1, 68.4, 75.2, ...]
  }
}

Your flow should:

1. Launch a blank Excel workbook
2. Write headers "Date" and "Max Temp (°C)" in row 1
3. Call the API with the Invoke web service action
4. Convert the response JSON to a custom object
5. Loop through both arrays simultaneously using a counter
6. Write each date and temperature to the corresponding Excel row
7. Save the file to C:\Reports\NYCWeeklyForecast.xlsx
8. Close Excel cleanly

Challenge extension: Add a third column "Condition" and hardcode "Forecast" for every row. Then modify the API URL to also request weathercode in the daily data and display the numeric weather code in that column instead.

---

Common Mistakes & Troubleshooting

The response variable is empty after the action runs. Check that the URL is reachable from your machine. Paste it in a browser — if the browser returns JSON, the problem is in the action configuration. If the browser fails too, it's a network or firewall issue. Some corporate networks block outbound HTTP calls to non-whitelisted domains.

Property lookups return "null" or the variable shows as empty. The property name is case-sensitive. If the API returns "Temperature" with a capital T but you're accessing ['temperature'] with a lowercase t, you'll get nothing. Use a JSON formatter to verify exact property names and nesting depth.

The JSON conversion action throws an error about invalid JSON. The API may have returned an error response (like a 401 Unauthorized or 404 Not Found) in HTML format rather than JSON. Add a condition after the Invoke web service action to check if the response text starts with { or [ before attempting conversion. For more robust error handling, see Error Handling in Desktop Flows: On Block Error, Retry Policies, and Recovery Screenshots.

The loop counter gets out of sync with the row index. If you initialize LoopCounter at 0 but RowIndex at 2, make sure both are incremented by exactly 1 inside the loop — and that the increments come after the write actions, not before. A common mistake is placing one increment outside the loop, which causes it to only fire once.

Excel rows are overwritten instead of advancing. This happens when RowIndex is not being incremented or is being reset at the top of each loop iteration. Check that your Set variable action for RowIndex is a true increment, not a re-assignment of the starting value.

API rate limits are being hit during testing. Most free APIs limit how many requests you can make per minute or day. If you're running the flow repeatedly while debugging, you may hit that limit. Add a 2-second Wait action between API calls if you're calling the same endpoint multiple times in a loop.

---

Summary & Next Steps

You've now built a complete pipeline from live API data to a formatted Excel spreadsheet, entirely within Power Automate Desktop. Here's what you accomplished:

• Configured the Invoke web service action to make real HTTP GET and POST requests
• Used Convert JSON to custom object to transform raw text into navigable data
• Applied bracket notation to extract specific values from nested JSON structures
• Looped through JSON arrays using For each with a parallel counter variable
• Written structured results into Excel row by row with proper headers

This pattern — fetch, parse, loop, write — is the foundation of dozens of real-world automation scenarios: pulling sales data from a CRM API, checking inventory levels from a warehouse system, retrieving financial market data, or synchronizing records between legacy systems and modern services.

From here, there are several natural directions to explore. If your automation needs to run on a schedule without anyone present, learn about Attended vs Unattended RPA: Choosing a Run Mode and Configuring Machines in Power Automate. If you want to combine API calls with other data sources — like pulling from a database and an API in the same flow — look at Automating Multi-Application Workflows in Power Automate Desktop: Transferring Data Between Windows Apps, Web Browsers, and Excel in a Single Desktop Flow. And if you find yourself repeating the same API call logic in multiple flows, Subflows and Reusable Logic in Power Automate Desktop will show you how to package it once and call it everywhere.