Excel

Workbooks provide a number of ways in which you can export data to excel to allow for further data manipulation or to be uploaded into another system. If you export the same Report into Excel on a regular basis, you may find it easier to set up an automatic data export from Workbooks directly within Excel. This configuration will allow you to perform a data import in Excel with the same download URL each time, meaning that the data is refreshed and updated on a regular basis.

Below you will find a step by step guide on how to bring Workbooks information into the Excel, once the data is within the Excel it can then be uploaded or imported to any other system as needed.

Step 1 – Create an API Key

You will need to create an API Key that can access the required Reports, to do this go to Start > Configuration > Email & Integration > API Keys, from this view select New API Key.

A new window will appear to configure the API Key, here you will need to fill out the following information: 

Access as User: The User the API Key will be accessing the system as; we recommend using the Automation User as this will be able to access all Reports within the system and will not have issues with logins to the system.

Name: Give the API Key an appropriate name such as Excel so you can easily identify the purpose of the API Key in the future.

 

Once these have both been populated, press the Create button, this will create your API Key as well as a section at the bottom of the page allowing you to give the API Key capabilities. This API Key will need the following Capabilities:

• API Access
• Export Reports
• View Reports

Step 2 – Create the Report URL

You will now need to create a Report URL that will be loaded into Excel. You will need to create a link for each Report view used, meaning that if your Report has five Summary Views you will need to create five URLs. In this instance you may want to sync the Details view of the Report across and build the Summary Views within Excel itself.

To create the URL you will first need to edit the Report you want to send to Excel. Then select the specific view such as Details or Summary, on this view select the Automation tab and press the API Reference button.

This button will open a new tab in your browser that will show you the API Reference of that Report. You will now need to modify the URL of this webpage.

The webpage should provide you with a URL that looks like the below:

https://secure.workbooks.com/data_view/123/data/metadata.html

You will need to remove the /metadata.html and replace it with .csv?api_key=XXX where XXX is the API Key you created for Excel.

For example:

https://secure.workbooks.com/data_view/123/data.csv?api_key=79a30-8db49-b0f20-f5869-541a1-0fe74-1bb9d-873f8

Step 3 – Connect Excel to Workbooks

Within Excel you will need to go to the Data tab and select From Text/CSV, then paste the created URL in the File Name field.

Once you paste the URL, you will then see the Access Web Content Pop-Up, where you will need to select which level of setting you would like, you can apply this on specific Report view level, All Report level or anything with a https://secure.workbooks.com/ at the beginning of the URL link. Applying it across this will mean any link that you paste will automatically have the same access applied:

You will then see a preview of the data you’ll be bringing into Excel. Set the File Origin to 65001: Unicode (UTF-8) and the delimiter to comma, you also need to specify Data Type Detection, you may need Excel to display ALL of the data set or just the first 200 rows. You’ll then need to load the data:

The end result!

Refreshing the data

It is possible to have this report automatically refresh whenever you open the file. To do this, go to Data > Refresh All > Connection Properties > Refresh data when opening file