• 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|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Connecting to an OAuth API Like PayPal With Power Query|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryConnecting to an OAuth API Like Pay…
sp_PrintTopic sp_TopicIcon
Connecting to an OAuth API Like PayPal With Power Query
Avatar
Toynk Accounting

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
July 16, 2019
sp_UserOfflineSmall Offline
1
May 16, 2021 - 11:13 am
sp_Permalink sp_Print

Hello, I'm able to connect to PayPal as described in this article Connecting to an OAuth API Like PayPal With Power Query by Philip Treacy.  However, I'm limited to loading one page at a time, for a total of 500 rows. How can this query be modified to load into Excel all the pages returned by the API call?

I know it's possible for the query to iterate through the pages as I've done it myself in other use cases but I can't for the life of me figure out how to make it work using the PayPal connection query.

Any help with loading the entire list of records within the specified date range will be greatly appreciated!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
May 16, 2021 - 1:10 pm
sp_Permalink sp_Print sp_EditHistory

Hi Toynk,

You have to use API query parameters: https://developer.paypal.com/d.....parameters

First, query the API to extract the total count of items with the count parameter.

TotalCount= query: api-m.sandbox.paypal.com/v1/invoicing/invoices?count

Knowing the total count, choose your page size , calculate the number of pages:

Pages=Number.Roundup(TotalCount / 50)

and start a loop or a recursive query, increasing the page number, until you reach the last page index:

api-m.sandbox.paypal.com/v1/invoicing/invoices?page=1&page_size=50

api-m.sandbox.paypal.com/v1/invoicing/invoices?page=2&page_size=50

Avatar
Toynk Accounting

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
July 16, 2019
sp_UserOfflineSmall Offline
3
May 18, 2021 - 3:05 am
sp_Permalink sp_Print

Thanks for the reply.  Does not answer my question however.  I need to know how to modify the M code in the example.  I can't figure it out.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
May 18, 2021 - 3:04 pm
sp_Permalink sp_Print sp_EditHistory

Paste the query into a blank new query:

(api_url, path, PageNumber, TotalPages, token)=>
let
JsonData = Json.Document(Web.Contents(api_url,
[RelativePath = path,
Query = [page=PageNumber, page_size=50],
Headers=[#"Accept" = "application/json", #"Authorization" = "bearer " & token]]),1252),
ResponseData=JsonData[transaction_details],
AllData= if PageNumber+1<=TotalPages then
try ResponseData & GetNextPageData(api_url,path,PageNumber+1,TotalPages, token) otherwise ResponseData
else
ResponseData
in
AllData

The name of this query must be: GetNextPageData

The following section from the example query:

data= Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Query = [end_date=endDate, start_date=startDate, fields=fields],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
),
transaction_details = data[transaction_details],

Must be replaced with:

TotalCount= Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Query = [count],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
) ,
TotalPages=Number.RoundUp(TotalCount/50),
transaction_details=GetNextPageData(api_url, path, 1, TotalPages, token),

Most likely, will not work the same for all API providers, each API is unique: some providers will have the next page link in the current response, others will provide pagination meta data in response, like: response[meta][pagination], some API's expect you do to a blind loop until you get an error (they don't provide a count or pagination, so you don't know where you should stop).
If you think that you'll be able to use the code for any API like Paypal as your topic says, my guess is that it will not work. You have to study each API separately, see what elements they provide in the response and adjust the actions according to their documentation.

Avatar
Toynk Accounting

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
July 16, 2019
sp_UserOfflineSmall Offline
5
May 18, 2021 - 10:31 pm
sp_Permalink sp_Print

Thanks again for your assistance!

I'm getting two errors.  First, in the GetNextPageData query I'm getting the following:

An error occurred in the ‘’ query. Expression.Error: The name 'RelativePath' wasn't recognized. Make sure it's spelled correctly.

In the original PayPal query after your modification, I'm getting the following error:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

 

Did I do something wrong that is causing these?

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
May 19, 2021 - 1:40 am
sp_Permalink sp_Print

Copy again the GetNextPageData function from above, was missing an opening [ just before RelativePath.

Copy again the changes indicated, each line should end with a comma.

Avatar
Toynk Accounting

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
July 16, 2019
sp_UserOfflineSmall Offline
7
May 20, 2021 - 1:16 am
sp_Permalink sp_Print

I did as you describe and I'm not getting this error:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

This error is related to:

TotalCount= Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Query = [count],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
) ,
TotalPages=Number.RoundUp(TotalCount/50),
transaction_details=GetNextPageData(api_url, path, 1, TotalPages, token),

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
May 20, 2021 - 1:10 pm
sp_Permalink sp_Print

I don't see errors in the code.

With Advanced Editor open, if you press Show Error, what section of code gets highlighted?

Avatar
Toynk Accounting

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
July 16, 2019
sp_UserOfflineSmall Offline
9
June 19, 2021 - 3:40 pm
sp_Permalink sp_Print

I was able to finally get this to work.  First of all, I owe you many thanks for all your help.  Would not have been able to do this from scratch on my own.  Your help is very much appreciated.  Below are the errors I needed to resolve to get it to work.

As I mentioned in an earlier post, I was getting the following error:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

This error was caused by the step below and specifically, Query = [count] was the culprit. 

TotalCount= Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Query = [count],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
) ,
TotalPages=Number.RoundUp(TotalCount/50),
transaction_details=GetNextPageData(api_url, path, 1, TotalPages, token),

There were also datatype conversion issues with the TotalPages variable and the pagination math in the function.  I reviewed the PayPal API docs and found that the API response includes total_pages so I used it instead of calculating a page total.  So my query now looks like this:

TotalCount= Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Query = [end_date=endDate, start_date=startDate, fields="all", page_size="500"],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
) ,
TotalPages=TotalCount[total_pages],

transaction_details=GetNextPageData(api_url, path, 1, TotalPages, token),

There was also a datatype issue in the function related to the page number so I made the change in bold below.  Those were my only two issues and now it works perfectly.  Thanks again!!

(api_url, path, PageNumber, TotalPages, token)=>
let
JsonData = Json.Document(Web.Contents(api_url,
[RelativePath = path,
Query = [end_date=end_date, start_date=start_date, page=Number.ToText(PageNumber), page_size="500", fields="all"],
Headers=[#"Accept" = "application/json", #"Authorization" = "bearer " & token]]),1252),
ResponseData=JsonData[transaction_details],
AllData= if PageNumber+1<=TotalPages then
try ResponseData & GetNextPageData(api_url,path,PageNumber+1,TotalPages, token) otherwise ResponseData
else
ResponseData
in
AllData

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
June 19, 2021 - 4:18 pm
sp_Permalink sp_Print

great, thanks for feedback! Smile

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Riny van Eekelen
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27250

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

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.