With Microsoft Excel 2019 or later, you can easily create an API query to Keyline. This lets you create custom reports and process the data as you wish. Follow this short tutorial, which demonstrates the connection using customer invoices.
Create an API permission in Keyline
Find the right URL for your query
To do this, follow this link and put together your URL.
The URL always consists of https://app.keyline-mis.com/api/v2 and a path to the API endpoint you need. In our example, this is:
The complete URL we need is:
https://app.keyline-mis.com/api/v2/accounting/customer_invoices
If you want to retrieve more than the 200 most recent invoice records, you will need to extend your header later. You can also find this information in our API
Set up your connection in Microsoft Excel:
Go to the “Data” tab.
Select the “From Web” data source.
In the URL field, enter the URL to the endpoint you put together in the previous step.
In the headers, enter Name as “Authorization”.
In the corresponding Value field, enter:
“Bearer [Your API key]”,
replacing [Your API key] with your actual API key from Keyline.
Excel then establishes a secure connection to your Keyline endpoint and retrieves the data you need.
If you like, I can also add a short step-by-step guide for Power Query.
Click OK
The Power Query Editor
Your query is now loaded in the Excel Power Query Editor. Click
“To Table” and confirm with OK without entering anything else.
Now expand your table by clicking the field with the two arrows, selecting the entries you need and confirming with OK.
If any columns contain the term “Record”, click the button with the two arrows to insert additional columns.
On the right-hand side of the screen, you will find a list of your steps (point 3).
Here, simply select a previous step to return to the previous list and make changes.
The Power Query Editor offers many more features — feel free to experiment.
No data is written back to Keyline, so you are working safely in your local environment.
Load your list into a normal workbook:
Once you are happy with the import layout of your list, click “Close & Load”.
Your data is now available in Excel for further processing or analysis as you wish.
Do you need more support, or are you short of time to tackle this or another process?
We are happy to help you outside the scope of standard Keyline support to build your workflow,
or, if needed, put you in contact with experienced and skilled service providers who can support you.