Retrieve API Data and Store in SharePoint with Power Automate

Introduction

In this article, we will see how to automate data retrieval from an external API using Power Automate. Taking Exchange Rate API as an example, I will demonstrate how we can set up our SharePoint list to be updated consistently with all the latest exchange rates.

Why We Need This Automation?

1. Accurate Data: Make sure SharePoint is always up to date with real-time data.
2. Saves Time: No manual data entry anymore.
3. Scheduled Update: Automation of data updates at predetermined intervals.
4. Boosts Productivity: Reduces repetitive tasks for the team.
5. Custom Data: Only allows the storage of the data points that are needed.

Setup External API

• We will use ExchangeRate-API to get the latest exchange rates.
• Create a User Account to Access the API Key:
  • Visit https://www.exchangerate-api.com/
  • Sign up to get your API key.
• API Endpoint
  • This is what you call to get the latest exchange rates: https://v6.exchangerate-api.com/v6/YOUR-API-KEY/latest/USD.
  • Replace YOUR API KEY with the actual one you received when you signed up. Setup Your SharePoint List.

Create a SharePoint list with desired columns

I am adding the below columns as an example.

1. Base Currency
2. Rupees
3. Euro
4. Kuwaiti Dinar
5. British Pound
6. Swiss Franc

Ensure that the relevant data types are selected for each column to ensure data integrity.

Create Scheduled Flow in Power Automate

Go to https://make.powerautomate.com and log in with your credentials.

Create a scheduled flow

1. Click on "Create" and select "Scheduled Cloud flow".
2. Select your desired recurrence.
   
3. Now, in the flow designer, click on "New step" and search for the "HTTP" action.
   
4. Configure the HTTP action.
   • Method: GET
   • URI: https://v6.exchangerate-api.com/v6/YOUR-API-KEY/latest/USD
   • Replace {YOUR-API-KEY} with your actual API key.
     
5. Run the flow manually to check whether data is coming or not. If the flow runs successfully, then in the Body section of the HTTP trigger, you will see the data.
   
6. Copy the whole “Body” section in a notepad.
7. Click on "New step" and search for the “Parse JSON” action.
   
8. Use the “Body” of the HTTP response as the content in the “Parse JSON”.
   
9. In the “Schema” section, Click on “Use sample payload to generate schema” In that, paste the “Body” of the HTTP response. It will generate a schema for the JSON.
   
10. Now Click on "New step" and search for the "Create item" action from SharePoint.
    
11. Select your SharePoint site and list and select column names we created in the SharePoint list from the dropdown “Advanced Parameters”.
    
12. Using dynamic content, map the parsed JSON response fields to the columns in your SharePoint list. Below is an example-
    • Rupees: Use the exchange rate for INR.
      
13. Now, “Save” the flow and test the flow manually to confirm it’s working correctly.
    
14. Now go to the SharePoint list and check that the records are being inserted correctly.
    
15. Now, test the flow by selecting the “Automatically” option. This will set your flow to run at the scheduled time you set in the recurrence.
16. This way, the latest exchange rates will automatically update in your SharePoint list.