Question:
How can I embed InLoox 11 data live in Excel?
Answer:
With the InLoox OData API, you can integrate data from the InLoox database directly into Excel as a data source using Microsoft Power Query.
The following guide will help you add an example query of the project list to an Excel document:
1. Create Personal Access Token
In the My Profile section, go to the Personal Access Tokens (PAT) tab. Here, you can generate a personalized access token for authorizing data access from applications to your InLoox account. The token acts as a personal login and allows access only to the data you can see in InLoox.
Click New Token, enter a meaningful name in the dialog, and select the account whose data you want to access.
Save the token in a secure location, as you will need it later. If you lose the token, simply create a new one.
IMPORTANT A Personal Access Token is comparable to a login and password. If it is shared, it can be used with the InLoox API to gain read and write(!) access to data in InLoox.
We strongly recommend creating a new user with the "Read-Only" access type specifically for retrieving data via a PAT token. Additionally, configure permissions for this user to ensure access is granted only to the necessary data.
2. Connect the InLoox OData data source to Power Query in Excel
Power Query is a technology embedded in Excel and other Microsoft products that allows you to import external data sources into Excel tables.
In Excel, navigate to the Data tab and open the Get Data dropdown menu. First, select From Other Sources and then Blank Query.
This opens the Power Query Editor in a new window.
Now, open the Advanced Editor:
Enter the following query, replacing [YOUR_PAT_TOKEN] with the Personal Access Token (PAT) you created earlier:
The query in detail:
let
headers = [#"x-api-key" = "[YOUR_PAT_TOKEN]"],
Source = OData.Feed("https://app.inloox.com/api/odata/Project", headers)
in
Source
Explanation of the query:
- YOUR_PAT_TOKEN: Your Personal Access Token
- OData.Feed: The URL of your InLoox account, including the OData API path and an OData API object. In this example, the list of all projects.
- If you are using InLoox in the OnPrem version on your own server, you must enter the corresponding URL defined by your organization, e.g. „https://inloox.mycompany.com/api/odata/Project“.
Typically, you will now be prompted to specify how your connection to the InLoox API should be established.
Click Edit Credentials:
Then select Anonymous with the level set to https://app.inloox.com// or the corresponding URL for your InLoox OnPrem server:
After confirming by clicking Connect, a preview of your data will be loaded:
On the top left, click Close & Load to exit the Power Query Editor and import the queried data into a new Excel table.
IMPORTANT Depending on the size of your InLoox database, this query may load a very large amount of data! Ideally, you should apply filters to limit your query and retrieve only the data relevant to your use case. For more information on filtering data in Power Query, refer to Microsoft’s documentation: Filter data (Power Query) – Microsoft Support.
Now you can analyze your InLoox data directly in Excel - enjoy!