Setting Up Excel Integration with OData

Created by Mark Shepherd-Smith, Modified on Tue, 06 Jun 2023 at 02:03 PM by Lachlan Robbie

Docketbook's integration with Excel provides a powerful reporting tool where you can extract live data from your company's Docketbook, apply formulas, lookups, charting etc to add real insight to the docket data you're collecting.

This article describes the process of setting up the Excel integration, which uses a secret key to overcome issues with Excel storing passwords in clear text. The process, shown in the video below, consists of:

Generate a Security Key using THIS LINK

Authenticate your copy of Excel using the security key

Establish Excel privacy levels

Extract the data.

Getting Access

To get started, you'll need to generate a secret to use instead of your normal password. In order to do this, click the button below and you will be prompted for a username and password. Use the same credentials that you would otherwise use to login to Docketbook:

Generate a Secret

Once you login, you will see a screen like this that contains your secret

Copy and paste the secret (the string between the quotes) into a safe location - We will use it later.

Getting a Template

A basic template that extracts all the docket data in a structured format is available in a template for customers on the Business Plan. If you're interested in getting a copy, create a ticket we can help you get started.

On a security point, this template contains data connections and macros, both of which Excel is going to present warnings about. We use macros to trigger data to reload as other data changes (as shown in the video) and the data connections to talk to Docketbook. These warnings are shown because macros can be potentially harmful if constructed as such. If you have concerns or your organisation requires it, it is best to verify with a trusted professional that our macros are harmless or otherwise contact us directly.

Starting with the Template

The video below illustrates the process most users will encounter if they're starting with our template.

The steps that the video works through are:

Open the downloaded template file and hit the "Enable Editing" button

Once in editing mode, hit the "Enable Content" button

Go into the "Data" tab and open the "Queries & Connections" panel

Starting with the "Organisations" query, right click and select "Refresh"

Because this is the first time with this query in this workbook, you should receive a prompt like this. When you do, select the "Basic" option on the left. Your username is your Docketbook username and the password is the secret that we created earlier.

Once thats done, you should see your organisations listed under the "Orgs" worksheet and also selectable under the drop-down in the "Settings" worksheet.

Select your organisation from the drop down list, which will trigger another query to fetch the related organisation groups. Another pop-up will likely appear the first time. When presented, we select "Organisational" from the drop-down. Your organisation may want you to use another option, so it is best to check with them

Once the query is complete, you should be able to select your organisation group from the drop down as well as see the results in the "Org Groups" worksheet.

Select one of your groups and the list of dockets within that org group should be fetched and populated into the "Dockets" worksheet.

Once you've got docket data being loaded into the "Dockets" worksheet, you can perform any analytical work you would otherwise do. Each column on the Dockets tab is also filterable.

Where To From Here?

You can see how we write our queries by opening the Data -> Queries & Connections panel, right clicking on a particular query and clicking "Edit". With the "Query Editor" window open, select the "Advanced Editor" option along the top. This will show you the query steps involved in obtaining data from Docketbook and were required, how we extract parameters from the worksheet to make it interactive.

At the moment, we only support Docket and Checklist querying based on organisation, organisation group and date range. A date range is mandatory and the absence of one will result in an empty response. In the future, we plan to support additional query parameters for Dockets and expose an even wider array of our data.

General Tips

Try to keep any queries you run confined to a particular date range. Our API's won't prevent you from fetching every docket you've got, but its going to be a very long running query and generally slow down Excel.