• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Connecting to an OAuth API Like PayPal With Power Query

You are here: Home / Power Query / Connecting to an OAuth API Like PayPal With Power Query
Connecting to an OAuth API Like PayPal With Power Query
August 6, 2020 by Philip Treacy

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.
Please enter a valid email address.

Download the Excel Workbook.

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.

Connecting to an OAuth API Like PayPal With Power Query

More Power Query API Posts

getting started with api's in power query

Getting Started with API’s in Power Query

Use Power Query to get data from API's. Understand the authentication process and how to construct queries to get the data you want. Sample workbook.

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power QueryTag: Power Query API
Previous Post:excel pivottable p&LExcel PivotTable Profit and Loss
Next Post:Plan v Actual Excel Dashboardact v plan dashboard

Reader Interactions

Comments

  1. Michel du Preez

    October 2, 2022 at 4:42 pm

    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
    • Mynda Treacy

      October 3, 2022 at 9:11 am

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

    November 27, 2021 at 3:24 am

    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
    • Mynda Treacy

      December 7, 2021 at 9:15 am

      Hi Khyati,

      This post has a suggested solution.

      Mynda

      Reply
  3. gvg

    September 16, 2021 at 9:58 pm

    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.

    Reply
    • Philip Treacy

      September 19, 2021 at 12:25 pm

      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. Mark Goldin

    March 4, 2021 at 3:20 am

    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
    • Philip Treacy

      March 4, 2021 at 5:44 pm

      Hi Mark,

      As long as your web app accepts that POST request then yes.

      Regards

      Phil

      Reply
  5. Eric Brinsfield

    January 14, 2021 at 2:01 am

    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
    • Catalin Bombea

      January 14, 2021 at 11:54 am

      You’re welcome Eric!

      Reply
  6. Julia

    November 10, 2020 at 4:30 pm

    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
    • Philip Treacy

      November 13, 2020 at 1:36 pm

      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. Anthony Vargas-Gobeille

    October 8, 2020 at 4:09 am

    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
    • Philip Treacy

      October 8, 2020 at 3:28 pm

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

    September 2, 2020 at 6:01 am

    There isn’t any query in excel work book

    Reply
    • Philip Treacy

      September 2, 2020 at 9:22 am

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

        September 3, 2020 at 5:42 am

        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
        • Philip Treacy

          September 3, 2020 at 3:01 pm

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

            September 8, 2020 at 2:17 am

            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

            September 8, 2020 at 9:51 am

            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

  9. Matt

    September 1, 2020 at 8:56 am

    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
    • Philip Treacy

      September 3, 2020 at 3:16 pm

      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
  10. Sergio

    August 12, 2020 at 8:24 am

    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
    • Philip Treacy

      August 12, 2020 at 10:56 am

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

        October 30, 2020 at 7:45 am

        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
        • Philip Treacy

          November 3, 2020 at 10:14 pm

          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 Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.