Connecting to an OAuth API Like PayPal With Power Query

Philip Treacy

August 6, 2020

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:

  1. Make a request to the API with a previously created username and password.
  2. Receive an access token (a code) from the API.
  3. 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.

By submitting your email address you agree that we can email you our Excel newsletter.

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.

Paypal OAuth 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'.

Create an app

Give the app a name

Something descriptive that indicates what the app will be used for.

give the app a name

Record the app credentials

The Secret is initially hidden so click on Show to reveal it. Record the Client ID and Secret somewhere secure.

app credentials

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.

specify app settings

View the newly created app

Your app is set up and ready to be used in Power Query.

view your newly created app

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.

blank power query

Open the Advanced Editor.

open the advanced power query editor

The first part of the query requests an access token from the API.

Insert your own Client ID and Secret.

m code to get oauth token

  • 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.

m code to query the oauth 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.

query transformation steps

When you first run the query you will be asked what credentials to use to make the connection to PayPal's API.

what query credentials to use

Because the query itself contains the credentials and the mechanism for making the connection, just choose Anonymous Access.

use anonymouse 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.

data privacy level

set data privacy level

After the query runs we end up with data like this.

data from paypal api

Close and Load to a table, ready to analyse.

paypal api data in excel table

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.

28 thoughts on “Connecting to an OAuth API Like PayPal With Power Query”

  1. 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?

    Reply
  2. 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.

    Reply
    • 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

      Reply
  3. 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 ?

    Reply
    • 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

      Reply
  4. 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?

    Reply
  5. 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

    Reply
  6. 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

    Reply
    • 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

      Reply
  7. 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!

    Reply
    • 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

      Reply
    • 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

      Reply
      • 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

        Reply
        • 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

          Reply
          • 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

          • 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

  8. 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.

    Reply
    • 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

      Reply
  9. 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?

    Reply
    • 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

      Reply
      • 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.

        Reply
        • 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

          Reply

Leave a Comment

Current ye@r *