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.
Download the Workbook With Sample Query
The query in this Excel file can be copied/pasted into the Power BI Desktop Advanced Editor and will work there too.
Enter your email address below to download the workbook with the data and code from this post.
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.
https://developer.paypal.com/docs/api/overview/
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
- Fees
- Tax
- Customer Details
- Product Details
- etc.
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.
Summary
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.
Excel PivotTable Profit and Loss
Monte St. Johns
So I LOVE this post – and yesterday I downloaded your sample file, changed the CLientID and Secret Key to my PayPal App info – then I edited the Start End dates, when prompted set the Credentials to Anonymous as you suggested and I successfully pulled 6 months of data getting the exact content you had in the Query. Today i go back into Excel and begin to modify the Data columns I want and now I get this error “DataSourceError: Web.Contents with the Content option is only supported when connecting anonymously. – Since I already set the Credentials yesterday not sure why but when I get the error it’s at the Token_Response step – and the Edit Settings button is in the PQ editor page – I click the button and get a JSON pop-up window that has Advanced checked and various fields like URL parts, URL Preview, Open file as (JSON is selected) File origing is set to None. BUT now I can NOT modify the query – yet I can run it to retrieve data?
Catalin Bombea
Try setting the Privacy level to Ignore.
Michel du Preez
Good day Phil and Myrna,
I subscribe to your Youtube and website content, and am beginner to writing DAX and M language especially with my current assignment dealing with the Paystack API (not paypal), and have no idea how to apply your recommended solution in advanced editor in Power query. Is there no way we can schedule a call? What is your rate regarding consulting hours? I do need help urgently with this deadline. All the best.
Mynda Treacy
Hi Michel,
We don’t provide consulting services, sorry. However, if you want to reach out via email we can recommend someone who can help you: website at MyOnlineTrainingHub.com
Mynda
Khyati
Hi Philip,
Hope you are doing well,
I tried the tutorial above and it is working fine for me when I use it in Power BI desktop application.
However when I try to publish the report to Power BI service, it gives me authentication issue for the token api for my application.
Do we need to anything specific in the Power query or datasource settings when we try to publish it in the Power BI service ?
Mynda Treacy
Hi Khyati,
This post has a suggested solution.
Mynda
gvg
Hi,
When I register my app in PayPal, I get Sandbox account in the form of sb-……@business.example.com. Where do I use it in PQ? Do I put it in the token_path ? It I do it I get DataSource.Error: The underlying connection was closed: The connection was closed unexpectedly.
Philip Treacy
Hi,
If you’ve created a sandbox account, then registered a new app, you should get a Client ID and Secret created for that App.
It’s this Client ID and Secret that you insert into my Power Query query/code, not the sandbox account name.
Regards
Phil
Mark Goldin
I need to get a token from a web application. Everything is on my local box. POST parameters look like this:
grant_type=password&username=domainname\user&client_id=xxxxxxxxx
Can I use your tutorial for my case?
Philip Treacy
Hi Mark,
As long as your web app accepts that POST request then yes.
Regards
Phil
Eric Brinsfield
Thanks so much for showing this example. I was struggling to get connected to a club membership management site and your article helped me solve my problem.
Thanks
Eric
Catalin Bombea
You’re welcome Eric!
Julia
Hi Phil,
Thanks for sharing the code!
I have a business sandbox account, so i use https://api.sandbox.paypal.com for api_url and plug in my ID and Secret. The rest of code is the same. But it shows the DataSource Error: The operation has timed out. Have you seen this before?
Thanks!
Julia
Philip Treacy
Hi Julia,
Yes, it indicates there’s been no response from the website you are trying to reach.
Are you still getting this problem?
Regards
Phil
Anthony Vargas-Gobeille
Hello,
Thank you for the tutorial with PayPal. I’m currently doing one with the Cloud Firestore of Firebase. I don’t know how to get the access token of Cloud Firestore. I tried multiple ways and searching on forums. However, I didn’t find any answer to my question. I would like to know, if you know how to get the access token of a Cloud Firestore?
I have the URL for the token with oauth2 and I have the client_id et client_secret. I always get an error saying : ”DataSource.Error: Web.Contents failed to get contents from ‘https://oauth2.googleapis.com/token’ (400): Bad Request”
Thank you very much!
Philip Treacy
Hi Anthony,
I’m not familiar with the Cloud Firestore system so would have to read the documentation to figure it out.
It’s really hard to say what the issue is without seeing your query. Can you please start a topic on the forum and post your file. You can remove any sensitive parts like your client_id and client_secret.
Cheers
Phil
Zeeshan
There isn’t any query in excel work book
Philip Treacy
Hi Zeeshan,
Yes there is. You need to go to the Data tab and click on ‘Queries and Connections’ and you’ll see the query named ‘Paypal Transactions’.
Regards
Phil
Zeeshan
Hi Phil, I found the PayPal Transaction query there but I didn’t get what showed in above example or am I doing wrong? Moreover is there any guide to connect PayPal with PowerBI
Philip Treacy
Hi Zeeshan,
You need to open the Advanced Editor and modify the query so that it contains your own ClientID and Secret.
Power BI does not have a connector for PayPal which is why I wrote this, but you can of course use this PQ query inside Power BI.
If you set up your PayPal account/API as described in this blog, and supply your own ClientID and Secret then the query should work for you to retrieve transactions.
If you want to do something else then you will need to read the PayPal API documentation to understand how to change the query to get the data you want.
If you get stuck, please start a topic on the forum and attach your workbook/query there.
If you create a PayPal Sandbox account then you can leave the ClientID and Secret for the Sandbox in your query for me to check if it’s all working correctly.
Regards
Phil
Zeeshan
Hi Phil,
Thanks for your response.
I don’t know what wrong I’m doing but I’m not able to get the query. Could you please email the query so I’ll use it.
Regards,
Zeeshan
Philip Treacy
Hi Zeeshan,
If I emailed you the query it’ll be the same one that is in the workbook you downloaded from this page. The issue seems to be what you are doing with that query and to work out why it’s not working I’ll need to see what you’ve done.
So, can you please start a topic on our forum and attach the workbook you are using that contains the query to Paypal. As I already said, you must set up your own Paypal account and insert your own Client ID and Secret into the query – have you done this?
Regards
Phil
Matt
Is it possible to read any of those values from a sheet? For instance, I work with multiple client systems. I would like to be able to put all their basic info (root URL, client secret, UID, PWD) in a sheet, select the client to run the query for, and have the connection read the appropriate data from that row in the sheet.
Philip Treacy
Hi Matt,
You can put all of that info into a table (or tables) and then read the table into PQ.
You can write separate queries to grab each separate piece of data. Or you can have 1 query and access the different rows.
If you have a table called Data with 1 column and however many rows (the first row is the header):
Data
username
password
secret
and you read the table into PQ, you can access the first row value (username) with Source{0}[Data] , password with Source{1}[Data] etc.
Cheers
Phil
Sergio
Thank you very much for the tutorial. It has been really useful to me. Do you know if you can extract transactions from a period of more than 30 days?
Philip Treacy
Hi Sergio,
The API will return up to 31 days of transaction data. If you need more than that you will need to duplicate the query for every month/period you need and then alter the start and end dates accordingly within the query.
Regards
Phil
Saulius
Hi,
very helpful tutorial, thank you. I was doing some analysis with large csv files from Paypal and now I am thinking of geting data directly by API. And had an idea of automatical process with Power Bi services. But get stuck with Papypal API limitations.
Paypal transaction API limits results to 500 per page (default limit is 100 per page). For 1 month query I receive 34 pages with default 100 rows in the first page. How could I get all rows from all pages to the PQ? When I do query for specific date range I don’t know the total count of pages Paypal API would return.
I can do first query to get the string with count of pages. But how automate process of PQ to take data of all pages incrementaly one by one?
Maybe you have some ideas and coul help? Thank you.
Philip Treacy
Hi Saulius,
I’d write a custom function in Power Query that can be called in a loop to get the data you need.
If you can start a topic on the forum and supply your query showing the fields/parameters you are getting from Paypal, I’ll write the function. You don’t need to include your Paypal API key or other secret info. I just need to know the structure of the URL in the API call.
Regards
Phil