• 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

Get Access Token with Power Query for Cloud Firestore|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Get Access Token with Power Query for Cloud Firestore|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 QueryGet Access Token with Power Query f…
sp_PrintTopic sp_TopicIcon
Get Access Token with Power Query for Cloud Firestore
Avatar
Anthony Vargas-Gobeille

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
October 13, 2020
sp_UserOfflineSmall Offline
1
October 13, 2020 - 11:01 pm
sp_Permalink sp_Print sp_EditHistory

Hi,

 

I'm trying to do a power query with Cloud Firestore of Firebase. I'm trying to connect to get the access_token of my database. the only examples I found are for Realtime database Firebase. They are not working for me. I tried with an example for PayPal and Twitter but without success.

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”. The error is at the Token_Response

//URI's
api_uri = "https://xxxxxxxx.firebaseio.com/TableIWant/IDofTheDocumentIwant/SubTableIWant",
token_uri ="https://oauth2.googleapis.com/token",
authorize_uri = "https://accounts.google.com/o/oauth2/auth",

//App details
client_id = "XXXXXXXXXXXXXXXXXXX",
client_secret ="YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY",

EncodedCredentials = "Basic "& Binary.ToText(Text.ToBinary(client_id & ":" & client_secret), BinaryEncoding.Base64),

Token_Response = Web.Contents(token_uri,
[
Headers=[#"Authorization"=EncodedCredentials, #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content=Text.ToBinary("grant_type=client_credentials")
]
),
token_json = Json.Document(Token_Response),
token = token_json[access_token],

Source = Json.Document(Web.Contents(api_uri,
[
Headers=[Authorization="Bearer "& token],
ManualStatusHandling={400}
]
)
)

 

If someone can help with it would be really nice.

Thank you!

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

Hi Anthony,

You have to study the documentation google provides for Firestore: https://firebase.google.com/do.....e-rest-api

Their doc say: Use a Google Identity OAuth 2.0 token and a service account to authenticate requests from your application, such as requests for database administration.

To get a service account, you have to go to https://console.developers.google.com, create an application (that needs to be approved by google team, you will submit it for verification)
They also provide documentation: https://cloud.google.com/iam/d.....e-accounts

google.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage google.jpg (29 KB)
Avatar
Anthony Vargas-Gobeille

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
October 13, 2020
sp_UserOfflineSmall Offline
3
October 14, 2020 - 11:46 pm
sp_Permalink sp_Print

Thank you Catalin for the useful links. I will look into it and tell you if I succeeded. 

One question, do I have to create an application for a power query on excel? I won't be using it in an application. I'm retrieving the data to make a pivot table.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
October 15, 2020 - 12:34 am
sp_Permalink sp_Print

Hi Anthony,

It does not matter if the intended purpose is a personal application or a global project. You do not offer google a guarantee that you will not use it to rob banks for example, or other purposes than what you declare.

It's a standard in the industry, all major players are using these applications as an interface for services.
Microsoft has Azure Active Directory for Identity, if you want to use their identity services you have to ... create an app on Azure.

To Connect to another site using Sign in with Facebook for example, that site had to create an app on facebook, in order to get the permission to login their users with their facebook account.

And the list is endless: Amazon, Apple, WhatsApp and so on.

When you go to developers console to IAM Service Accounts, if there is no project/application , you will only see this: "To view this page, select a project".

so just create a new project, go to Service Accounts (IAM) and create a service account, it's easy to create the keys.

 

 
Avatar
Anthony Vargas-Gobeille

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
October 13, 2020
sp_UserOfflineSmall Offline
5
October 15, 2020 - 3:57 am
sp_Permalink sp_Print

Hello again Catalin,

I follow the instructions you gave me and followed the steps Google give for the Service Account and how to access the token. However, my query doesn't seems to work. I still get an HTML answer after the Web.Content. I should receive a JSON file with the access token. Do you know what could cause this error?

Thank you!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
October 15, 2020 - 2:47 pm
sp_Permalink sp_Print

Hi Anthony,
I am not using Firestore, never needed to connect to one, you have to follow the process they describe to access data, can't help more unfortunately.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Chandler Davis
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:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
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.