Forum Discussion
Power Query API Authentication
Hi Anonymous
Yeah, we had the wrong Content-Type - give below a try:
let url = "https://www.space-track.org/ajaxauth/login", identity = "change_this", password = "also_change_this", query = "https://www.space-track.org/basicspacedata/query/class/boxscore", headers = [#"Content-Type" = "application/x-www-form-urlencoded"], // this was the problem response = Web.Contents(url, [ Headers = headers, Content = Text.ToBinary( "identity=" & identity & "&password=" & password & "&query=" & query ) ]), jsonResponse = Json.Document(response) in jsonResponseI actually tested it 😊
Please mark this as ANSWER if it helped.
P.S.: I saw there are some rate limiting guardrails in place on their side -> don't press the refresh button too often
Thanks for helping.
When I say it didn't work, see screenshot.
There is some documentation that references cURL, here it is:
How to return data through the API using cURL (recommended)
Step 1 (optional): If using a proxy server, set a local environment variable where "proxyhost" is the URL of your proxy and "portnumber" is the proxy's port number.
$ export https_proxy=http://proxyhost:portnumber
Step 2: obtain a session cookie good for ~2 hours (replace myusername and mY_S3cr3t_pA55w0rd! with your space-track.org username & password). If you are using a proxy from Step 1, append the command below with --proxy-anyauth -U:
$ curl -c cookies.txt -b cookies.txt https://www.space-track.org/ajaxauth/login -d 'identity=myusername&password=mY_S3cr3t_pA55w0rd!'
Step 3: use the cookie to connect to https://www.space-track.org/ and return the data in a RESTful query like these examples. Please help us control our bandwidth costs by using '--limit-rate 100K' in your cURL scripts. You can construct your own API query using URL-safe characters if you need and substitute it for the one below:
$ curl --limit-rate 100K --cookie cookies.txt https://www.space-track.org/basicspacedata/query/class/boxscore
Step 4 (optional): direct the API query results into a file. Using the curl command from Step 2, redirect the query’s output into a file (boxscore.json is only an example):
$ curl --limit-rate 100K --cookie cookies.txt https://www.space-track.org/basicspacedata/query/class/boxscore > boxscore.json
Optional Method: You can use a single command to both log in and query the database by including the query in the POST request of the login command. A file named 'login' is created containing the data
$ curl https://www.space-track.org/ajaxauth/login -d 'identity=myusername&password=mY_S3cr3t_pA55w0rd!&query=https://www.space-track.org/basicspacedata/query/class/boxscore'
How to logout and close a session
Logout by sending a https://www.space-track.org/ajaxauth/logout request (examples below)
Responsive ResidentHi Anonymous
Based on the:
Optional Method: You can use a single command to both log in and query the database by including the query in the POST request of the login command. A file named 'login' is created containing the data
$ curl https://www.space-track.org/ajaxauth/login -d 'identity=myusername&password=mY_S3cr3t_pA55w0rd!&query=https://www.space-track.org/basicspacedata/query/class/boxscore'
The PowerQuery code would be:
let
url = "https://www.space-track.org/ajaxauth/login",
identity = "myusername",
password = "mY_S3cr3t_pA55w0rd!",
query = "https://www.space-track.org/basicspacedata/query/class/boxscore",
headers = [#"Content-Type" = "application/json"],
response = Web.Contents(url, [
Headers = headers,
Content = Text.ToBinary(
"identity=" & identity & "&password=" & password & "&query=" & query
)
]),
jsonResponse = Json.Document(response)
in
jsonResponse
Give it a try and tell us if it worked.
Please don't forget to mark as ANSWER this reply if it helped.
thanks for helping, it looks like I must be doing something wrong as it is saying this
- ams1
Hi Anonymous ,
Please paste in PowerQuery exactly the code i've sent AFTER replacing identity and password (and maybe query) AND choose anonymous when prompted for credentials.
I think you're trying to use some API key stored in credentials manager - that does not work.
Please mark the relevant replies as answer if they helped.