If you have multiple Applixure environments under one Applixure account - such as when operating as managed service provider overseeing customers environments - you may want to have list of those environments outside the Web UI for reporting purposes.
By following instructions in this article you can get the list of the environments into Microsoft Excel workbook by using Excel's built-in Power Query feature connecting to Applixure's Connect API.
Prerequisites
In order to access list of all environment from the Connect API, you will need to first generate new API keypair (username+password) at the Applixure account level.
Please note that to access API keys at the account level, your Applixure user account needs to have full account administrator -permissions associated with it as the "Integrations and API access" section at the account level is restricted.
Create new API keypair - or use existing keypair - and take note of the username and password generated for that API keypair. You will need to use these to retrieve data into Excel.
Creating Power Query connection in Excel
To create Power Query data connection, you have to use Microsoft Excel on Windows as macOS version of the Excel does not support Power Query connection creation or editing at the time of this writing.
First, switch into Data -tab in Microsoft Excel and you should see button called "New Query" in the ribbon. Pressing New Query, select From Other Sources -> From Web -option to initiate connection to web services:
Alternatively, if your Excel shows "Get Data" button instead, press Get Data -> From Other Sources -> From Web:
Next you will need to define connection to Applixure's Connect API endpoint providing list of available environments. Enter "https://connect.applixure.com/v1/environments" as URL in the dialog:
In order to connect to the Applixure's API, you must now supply the API credentials created earlier to Excel. Select "Basic" as authentication type and supply API keypair username to "User name" field and API keypair password to "Password" field:
If connection is successful, you should now have Power Query Editor open and first returned line in the main windows should state that the API call was successful ("ApiCallSucceeded = TRUE"). Next, you must transform JSON data returned from the Applixure Connect API into tabular data more suitable to be viewed in Excel.
Transform JSON into tabular format
In the editor, select the line where the first column says "Environments", this is sub-object in the JSON data graph listing all environments available to the account's API key. Click on text "List" on the second column:
This will expand list into separate records. Press "To Table" in the ribbon menu to convert records into table, accepting default options for table conversion:
Next we must expand each individual record for each environment into set of columns. This can be done from the expand to columns -button on the upper right corner of the column shown on the window:
From the opened expand settings menu, uncheck "Use original column name as prefix" -setting:
You can also omit some columns you don't want to see in the final Excel table for environments, such as the environment's id, by unchecking it in the list before pressing OK. After expand operation, now the main window in Power Query Editor should show all environments retrieved from API as table:
If you want, you can sort the table on the "Name" column or any other column, and this sort ordering will be subsequently be remembered for the table even when refreshed later on as it will be part of the data transformation from JSON to table:
Once you are satisfied for table's display, you can close the Power Query Editor by pressing "Close & Load" -button on the ribbon menu to return back to Excel worksheet:
Now you will have automatically loaded and transformed table of all Applixure environments placed on the worksheet:
As the table is coming from data source, you can at any later time refresh it to update any possible changes in the Applixure environments data - such as new environments being created, some being closed and agents count being changed over time - by pressing "Refresh" button on the Design ribbon manu when the table is selected:
This will re-establish connection to Applixure Connect API, fetch current data from the environments endpoint and run transformation to the data to update the table:
0 Comments