My previous post on using Power Query to access API's looked at API's that required either basic or no authorization.
This post looks at API's that implement OAuth authorization, and as an example I'll be connecting to PayPal.
The following query/process works in both Excel and Power BI Desktop.
What is OAuth?
OAuth is a set of rules that describe how an application (like Power Query) can get information from a website (the API).
The mechanism by which this is implemented is:
- Make a request to the API with a previously created username and password.
- Receive an access token (a code) from the API.
- Use this access token to get data from the API.
A Word on Security
To create the username and password from Step 1, usually means logging in to the website you want to access data from and creating them.
When you do this you should expect to be able to grant the username a certain level of privileges. Never grant more than is needed.
If you create a username that is only going to be used for reporting, then just grant Read permissions.
If you need to use the API to write or change data, create a different username/password and grant the necessary rights to that account.
The username and password you create will be stored in your Excel file so be aware of this and don't give access to anyone who should not have it.
Getting Started with the PayPal API
PayPal provide a live and a test API. The test API is known as the Sandbox and lets you play around without having to worry that you are going to mess up your live account.
The PayPal documentation refers to an App. What they mean by this is the program/application being used to access the API, so in our case that's Power Query.
The process of logging in to PayPal and creating an app is what creates the account (username and password) that is used in Power Query to initially access the API and create the access token. Refer back to Steps 1 and 2 above.
The following screenshot decribes the process.
Follow the instructions on this page to log into the PayPal Developer Dashboard.
Create an app
Click on 'Sandbox' or 'Live' then click on 'Create app'.
Give the app a name
Something descriptive that indicates what the app will be used for.
Record the app credentials
The Secret is initially hidden so click on Show to reveal it. Record the Client ID and Secret somewhere secure.
Specify app settings
Set the app settings to only allow what you want it to do then click Save. I'm just after transaction data.
View the newly created app
Your app is set up and ready to be used in Power Query.
What Data Are We Getting From the API?
I'm going to get the account transactions which is documented here https://developer.paypal.com/docs/api/transaction-search/v1/
This provides information like:
- Transaction Amount
- Transaction Currency
- Transaction Date
- Customer Details
- Product Details
In addition to standard data like this, there will also be data that is specific to your PayPal setup. For instance if you are shipping physical products then there will be shipping details included in the data the API sends you.
Or if you are using a shopping cart on a website, the API may return data like order ID's specific to that cart.
Setting up the API Connection in Power Query
Start with a Blank Query. I'm using Excel in Office 365.
Open the Advanced Editor.
The first part of the query requests an access token from the API.
Insert your own Client ID and Secret.
- api_url is the root URL for the API. All requests to the API start with this.
- token_path is the resource path where we send the request for an access token.
- The ClientID and Secret are the values generated earlier when you created your app in the PayPal dashboard.
The second part of the query uses the access token we just generated to get data from the API.
- The root URL for this part of the query is the same but the path is different because I'm retrieving transaction data.
- endDate and startDate specify the period that we want data for.
- fields = "all" tells the API to return all data associated with each transaction.
The remainder of the query consists of various transformation steps. You can create your own transformations to suit your needs.
When you first run the query you will be asked what credentials to use to make the connection to PayPal's API.
Because the query itself contains the credentials and the mechanism for making the connection, just choose Anonymous Access.
If prompted to set the Data Privacy Level, I'd recommend you use either Organizational or Private. What you choose will depend on your situation. NOTE: The following couple of screenshots are from Power BI Desktop.
After the query runs we end up with data like this.
Close and Load to a table, ready to analyse.
Please note that the API returns at maximum a month's worth of transaction data. So if you want to get data for other months, which you naturally will, then you will need to duplicate this query and specify a different startDate and endDate for each month.
Querying Other PayPal Resources
This query returns transaction data. If you want to get something else then you need to check through the API documentation and alter the 2nd part of the query accordingly.
The downloadable file provides a template query that you can use to query an OAuth API.
To get the data from another API you will need to read that API's documentation to:
- Understand how to set up/create an app/account to get your Client ID and Secret. Different API's may use different terms for these things.
- Understand how to set up the initial request for the access token.
- Understand how to structure the query to get the data you want.
If you are having issues connecting to an API, drop in to our forum and start a new topic. I'll be happy to help where I can.