Power Query API Authentication

[Anonymous]

I want to connect to data at https://www.space-track.org/.

It requires an API connection.

I can't seem to figure it out in the normal authentication way or via PowerQuery.

I tried this and didn't work:

let

url = "https://www.space-track.org/ajaxauth/login",
headers = [#"Content-Type" = "application/json"],
postData = Json.FromValue([username="<user_name>",
password= "<password>"]),
response = Web.Contents(
url,
[
ApiKeyName="api_key",
Headers = headers,
Content = postData
]
),
jsonResponse = Json.Document(response)
in
jsonResponse

Signing up and getting a username is free.

Any ideas?

thanks

[ams1]

Hi @Anonymous 

IMO the best requirement for an API connection is a working cURL command with the API (ex. extracted from Postman etc.). Can you provide this working cURL command?

If you cannot provide the cURL (ex. haven't worked with it before), please provide some screenshots with the API documentation - I see you need to create an account to see that.

Finally: when you say it "didn't work" -> what do you mean by that?

[Anonymous]

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)

[ams1]

Hi @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.

[Anonymous]

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.

[Anonymous]

Thanks @ams1 

Choosing anonymous got rid of that error, now I have a new one 😪

[ams1 · Accepted solution]

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
    jsonResponse

I 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

[Anonymous]

You're Amazing, thanks ams1!!