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!
Hi Anthony,
You have to study the documentation google provides for Firestore: https://firebase.google.com/docs/firestore/use-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/docs/creating-managing-service-accounts
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.
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.
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!
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.
