SSO Authentication in Microsoft Power Query

From Zansha Wiki
Jump to: navigation, search
EVEOnlineLogo.png This article is a guide!

Introduction

This guide will explain how to query the ESI API in Microsoft Excel and Microsoft Power BI using SSO authentication required for certain ESI calls. Up until now, solutions existed for Google Spreadsheets, but none as of yet for Power Query, which is a very powerful tool to combine data from different sources. This guide requires some preparation, and it is not as easy to start with as using one of the custom functions created for Google Docs by Steve Ronuken from Fuzzwork. Take some time to follow the steps in this guide.

Preparation

Attention Alpha clones!
Omega clone state.png A developer license is required to complete this guide. This is available to accounts that have had a successful payment at least once.

In order to start querying ESI using SSO authentication, there are a number of preparations that need to be made. This guide requires the use of the Chrome app Postman, as well as a new version of Microsoft Excel 2016 or Microsoft Power BI Desktop installed. It also requires a developers license. "Developer registration requires that you have paid at least once with one of our valid payment methods other than PLEX as well as verified your email address." [1]

Software required

Summary

In short, the following steps are required to query ESI using SSO.

  1. Create an application on the EVE: Developers portal
  2. Get a refresh token using Postman
  3. Create a query to obtain a fresh access code
  4. Create an ESI call query

Guide

1. Create an application on EVE: Developers

Error creating thumbnail: Unable to save thumbnail to destination
The /markets/structures/{structure_id}/ endpoint

To get started, you must determine the ESI call you would like to make. ESI has a various endpoints, many of which do not require any authentication. An overview of the various endpoints can be found on Swagger. In this guide, we will try to discover market orders on a non-public citadel market. This requires authentication to verify that the account we are using has access to this market. In the Swagger interface, you are able to identify which endpoints require authentication by looking for the red exclamation mark icon, as displayed in the picture. Hovering over this icon shows the scope required for this call. For our example, we require two scopes.

  1. esi-universe.read_structures.v1 (required to verify which structures the character has access to)
  2. esi-markets.structure_markets.v1 (requires to verify if the character has access to market data in a structure market)

For querying different endpoints, check which scopes are required. Also, please note that endpoints can be updated. This could break your spreadsheets or you may miss out on updates. Read more information on this here.

Next, browse to the Developer Registration portal, log in using SSO and accept the developer license. Now, you can create a new application. Give this application a name, a brief description, select Authentication & API Access as connection type, select the two scopes listed above as permissions and enter https://www.getpostman.com/oauth2/callback as callback url. Finally, confirm the details and create the application, then click View Application. You will go back to this window later to copy the Client ID, Secret Key, and Scopes.

2. Obtain a refresh token with Postman

Install the Chrome app Postman and launch the app. You can launch the app from the store, or use an app launcher. This app will require you to register or log in using a Google account. Once launched, the app will appear in a separate Chrome window. In the main app window, click the Authorization tab and select OAuth2.0 from the Type dropdown menu, then click Get New Access Token. Fill in all the fields as follows:

  • Name - Name your access token in order to easily identify which scopes you are using
  • Auth URL - https://login.eveonline.com/oauth/authorize
  • Access Token URL - https://login.eveonline.com/oauth/token
  • Client ID - Your Client ID generated by the EVE Developers app created in Step 1
  • Client Secret - Your Secret Key generated by the EVE Developers app created in Step 1
  • Scope - Copy and paste the scopes listed in the EVE Developers app created in Step 1
  • Grant Type - Authorization Code
  • Request access token locally - Make sure this box is ticked

Next, hit Request Token. This will prompt you to sign in using the EVE SSO method. Sign in with your character and authorize the EVE application you made earlier to use the scopes listed. Afterwards, the SSO window will close, and a new token will have been generated and is viewable in Postman. In the list of Existing Tokens, click the name of your new access token and check the right panel. This panel will show you an access_token, the token_type, the expires_in time in seconds, and a refresh_token when you scroll down. Copy this refresh token for later use.

3. Create a query to obtain an access code

In a blank Excel 2016 workbook, open up the Data ribbon and click on Get Data, select From Other Sources, and finally select Blank Query. The location of these buttons and menus may differ depending on your Excel version. Once the Query Editor opens, click on Advanced Editor in the Home ribbon. Copy and paste the following code into the advanced editor, overwriting any existing code already present.

let
    body = Text.ToBinary("grant_type=refresh_token&refresh_token=NdCJDjSpcr0HDrZEMLweXysRH_Vn4rYph9wXbT7vNFK20WeFiOEnGFC15XBRk6GJ0"),
    actualUrl = "https://login.eveonline.com/oauth/token",   
    options =[ 
        Headers =[
            #"Content-type"="application/x-www-form-urlencoded",
            #"User-Agent"="YourNameHere",
            #"Authorization"="Basic ZDE2OGNiNDJjMTQzNGQxY2JhYjMxOTBiYThiYzg3ZWQ6SXpyMGdqaEZHdDU5cGZDZDVhdTdnamU4SGxYRWkxb2IxaXgxUUoyOA=="
                ],

        Content=body

],
 
    result = Web.Contents(actualUrl, options),
    #"Imported JSON" = Json.Document(result,65001),
    access_token = #"Imported JSON"[access_token]
in
    access_token

You will need to change the two tokens listed in this code snippet.

  • The refresh token NdCJDjSpcr0HDrZEMLweXysRH_Vn4rYph9wXbT7vNFK20WeFiOEnGFC15XBRk6GJ0 should be replaced by your own refresh code generated in Postman in Step 2.
  • The Basic token ZDE2OGNiNDJjMTQzNGQxY2JhYjMxOTBiYThiYzg3ZWQ6SXpyMGdqaEZHdDU5cGZDZDVhdTdnamU4SGxYRWkxb2IxaXgxUUoyOA== is a Base64 encoded combination of your Client ID and Secret Key generated in Step 1. You can encode this manually by going to Base64Encode, and copy pasting in your information in this way: clientid:secretkey Please note that the colon punctuation mark is required. Encode this combination as UTF-8 and copy the result into the advanced editor after the word Basic in the #"Authorization" header.

Additionally, change the text string for the header User-Agent to ensure CCP can contact you in case something is amiss.

Next, click Done and the query will retrieve an access code. This access code is valid for 20 minutes. Give your query a name in the right-side panel called Query Settings, or in the left side panel called Queries. In the Home ribbon in the Query Editor, select Close & Load. This will load the access token into a cell, as a table, with the query name as the column header name. In the Data ribbon, you can now click Refresh All, which will generated a new access code, valid for another 20 minutes.

4. Create an ESI call query

Return to Swagger and navigate to the /markets/structures/{structure_id}/ endpoint. Scroll down to Parameters and fill in the following fields:

Error creating thumbnail: Unable to save thumbnail to destination
A list of records converted to a table
  • datasource - tranquility (default)
  • page - The desired page, 1 by default
  • structure_id - The id of the structure you wish to query, for this example we will use 1023729674815
  • token - The access token generated by the query from Step 3, note that this is valid for only 20 minutes
  • user_agent - Add your name here so CCP can contact you if anything is amiss

Next click Try it out! at the bottom of the endpoint window. Various results will show below. Select the Request URL and copy this. This includes all the parameters entered above.

In Excel, open up the Data ribbon and click on Get Data, select From Other Sources, and finally select From Web. You will be prompted for a URL, paste the Request URL here and click OK. This will load the query editor, which for this endpoint will display a list of records. For any endpoint, manipulate the data to how you want it to appear in your Excel workbook. You can convert a list of records to a table by right clicking on the header and clicking To Table.


Next, click the two arrows pointing away from each other in the column header, an example is pictured on the right. This expands the table to all columns available in this endpoint. You are able to select all columns you wish to show. Make sure to untick the Use original column name as prefix to keep the column names readable. In a more structure process, you can leave this enabled. After selecting the desired columns, the data will appear. You can now proceed to further manipulate the data using the Transform ribbon, renaming columns, adding custom columns, or any of the other steps available in the query editor.

Error creating thumbnail: Unable to save thumbnail to destination
An example of data from the /markets/structures/{structure_id}/ endpoint

When you are done manipulating the data, open the Advanced Editor in the Home ribbon. Without any additional changes, your code will appear similar to this.

let
    Source = Json.Document(Web.Contents("https://esi.tech.ccp.is/latest/markets/structures/1023729674815/?datasource=tranquility&page=1&token=LrSNYJQrAnge4uwu2_2Ag3WJmB_Zdh5F3Ro853BmJVkjRctxlY8CjiQFKzplR3ECGfZau17daySKKvyUdfetQg2")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"order_id", "type_id", "location_id", "volume_total", "volume_remain", "min_volume", "price", "is_buy_order", "duration", "issued", "range"}, {"order_id", "type_id", "location_id", "volume_total", "volume_remain", "min_volume", "price", "is_buy_order", "duration", "issued", "range"})
in
    #"Expanded Column1"

In order to avoid making this query unusable after 20 minutes, we have to provide it with a new access code. To do this, we will transform this data query into a function. To do this, add the following to the top of the code in the Advanced Editor.

(AccessCode) => 

This create a parameter function. You can change the name of this parameter. Next, replace the full access code used in the URL with the following code:

"& (AccessCode) &"

The full function should now look like this.

(AccessCode) =>

let
    Source = Json.Document(Web.Contents("https://esi.tech.ccp.is/latest/markets/structures/1023729674815/?datasource=tranquility&page=1&token="& (AccessCode) &"")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"order_id", "type_id", "location_id", "volume_total", "volume_remain", "min_volume", "price", "is_buy_order", "duration", "issued", "range"}, {"order_id", "type_id", "location_id", "volume_total", "volume_remain", "min_volume", "price", "is_buy_order", "duration", "issued", "range"})
in
    #"Expanded Column1"

Next, click done. Then, give your query/function a name in the right-side panel called Query Settings, or in the left side panel called Queries. It is important to remember the name of this function. In this example, the function will be called MarketData. In the Home ribbon in the Query Editor, select Close & Load. This will close the query editor. This function will not load any data into the workbook, because it requires a parameter first.

Navigate to the table in the Excel workbook where the Access Code generated by the query in Step 3 is located. Highlight this cell, then navigate to the Data ribbon and click Get Data, select From Other Sources, and finally select From Table/Range. This may also be available in the Data ribbon itself. This will open the query editor and show the table loaded from the workbook. In the query editor, and navigate to the Add Column ribbon. Depending on the version of Excel 2016, you may have an option Invoke Custom Function. Use this option to invoke the MarketData function. If this option is not available, click Custom Column instead. In the window that shows up, add the following code:

=MarketData([Code]) 

You can replace MarketData by the name of your function, and Code by the name of your first query used to obtain the access code. In both instances, a new column will be added to the table with the access code. Expand this table as before. You can delete the column with the access code. You now have your data, and can load this into your Excel workbook using Close & Load. Remember to give this new query a unique name.

You can refresh all data immediately by going to the Data ribbon, and clicking on Refresh All. This will automatically generate a new access code, invoke the endpoint function, and update your workbook data.

Error creating thumbnail: Unable to save thumbnail to destination
An example of filtered and manipulated data

Further Data Manipulation and Merging

Security

Please keep in mind that these methods expose refresh tokens, encoded client IDs and secret keys, and access codes to anyone with access to this workbook. Be careful with sharing your work!

Background

ESI

Introduction to ESI

OAuth2.0 Authentication Flow

SSO OAuth2.0 Authentication Flow

Power Query

Credits

  • Scrin Entajuu (@scrin on Tweetfleet Slack)
  • iamkurt (on Brave Slack)
  • Jean LaCreux (on Brave Slack)
  • Hargara Odunen
  • Steve Ronuken (@fuzzysteve on Tweetfleet Slack)

For helping me out with various things, including writing this guide for Google Docs on which this guide is largely based;

https://www.fuzzwork.co.uk/2017/03/14/using-esi-google-sheets/

  • LSky NLX (@lskynlx on Tweetfleet Slack)

I wrote this!

  • Others

Sources

  1. Developer Registration