Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Power Query API Authentication

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

Need Help
  • ams1's avatar
    ams1
    3 years ago

    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

9 Replies

Replies have been turned off for this discussion
  • ams1's avatar
    ams1
    Responsive Resident
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        ams1
        Responsive Resident
        3 years ago

        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.