Atria External API via Excel
This document illustrates how to use Atria's external API platform to retrieve billing invoice data via an Excel document.
- Atria 12.11 or later
Use the following KB as a step by step to receive your API Authorization key. Atria AP
Retrieve End Dates List
Now that you have the token, please run excel and click on the Data Tab, from the ribbon menu select Get Data -> Select From Other Sources and then select From Web.
In the new page click on Advanced and click on URL Parts to enter your external API address:
Now, we need to retrieve all End Dates of billing API. This data will be used as a parameter to receive billing data.
Enter your external API URL to retrieve end Dates:
Note that you have to enter Authorization in the Key field of Http header and input the word Bearer together with your Authorization code that you received previously as shown in the above picture.
Once you click on OK, you can retrieve end Dates as a list.
On the Power Query Editor page which will be opened automatically once you retrieve data from Atria API, under Convert Menu click on parameters Query on the left side of the page and then select RECORD next to the data field
On the next page, you will see endDates and customers Lists. Click on the List next to endDates.
Here we will have data shown in JSON format and to extract data and show it in a table structure, under Transform Menu click on Into Table icon on the top left side of the page:
To Table page will be opened to define the way you want to create your table:
Click on OK. We need to extract columns from the list shown in Column 1, to extract columns click on icon next to Column1 header:
The column select page will be shown. On the Column Select page, select the columns you want to be shown. In this example, we just need to click on OK
Once you click on OK, the parameters table will be shown and we can use it as a parameter to retrieve our required information.
Now that we have end dates we can use this data as a parameter. On the Home tab click on Manage Parameters and then select New Parameter.
In the newly opened page enter endDates as parameter Name, select Text as Type and enter a current value like '2020-10-31' and click on OK.
At the moment, you have a list that includes your end dates and created a parameter that later on can be used to retrieve all data from the API.
Call Atria External API
Now it is time to call the Invoice API. From the page you are in select New Source -> Other Sources -> Web
Click on Advanced and enter your external API address in the following format:
now click on the add part and select parameter:
Again click on Add part and select Text and enter all the information as follows:
Do not forget to click on Http Request header and enter "Authorization" as follows:
Then click on the value part and enter the word bearer with your authorization code that you received previously as follows:
Now, Excel will retrieve the data from the Atria API and you can view data
Click on Record link and then click list of items:
Here you will come up with a list of records inside list columns that each one contains another list of data. In order to extract data click on To Table option on the top left menu and click on the OK in the newly opened page.
Now that the data is converted to a table you can extract data by clicking on the right end side of the Column1 column:
in the opened menu click on OK to view all columns:
From now on, you can view all data by clicking on each extract button on the right end of each column or you can drill down by clicking on the List of the data.
The above data is only for one billing period. In order to get data for all billing periods please click on the newly created query on the left side of the page and then right-click and select Create Function:
On the new page enter a name for your function like 'Get Invoices'. It will automatically pick endDate as a parameter.
On the left side of the page click on the parameters and then click on Add Column tab and select Invoke Custom Function:
Enter the information as follows in the Invoke Custom Function
This will let you to use endDates parameter to send as a parameter to Atria External API and retrieve all invoice data.
Once you click on OK a warning might be shown as follows:
Click on the Continue button and once the following page is shown, click on Ignore Privacy Levels... check box and then click on Save.
The reason you viewed the above warning is associated with accessing two sources of data in your excel page. In our case retrieving data from 2 source in your External API address, thus you can safely ignore it.
Here is a sample page showing extracted data: