How to Use API to get Hawkin Dynamics Data into Microsoft Excel

Guide for piping data into Microsoft Excel via the Hawkin API

Check the GitHub repository for the most up to date information and scripts

https://github.com/HawkinDynamics/power-query

Select Data Source 

  1. Find and Select ‘Get Data’ button in the ‘Data’ tab of the window ribbon.
  2. Find and select ‘Blank Query’ from the options to open the query window.

Open Script Editor 

  •  Select ‘Advanced Editor’ in the ‘Query’ section at the top of the window, in the ‘Home’ tab, to open the advanced editor window.

Replace Script and Key 

  • Copy and paste the desired script into the script editor.
  • Scripts can be downloaded here: SCRIPTS

  • Replace the “SecretKey” variable in the new script (on line 5) with your unique integration key. Then press the ‘Done’ button.

Review 

  • You might be prompted to answer some specific questions about security. The options of
Public, Organizational, and Private are in reference to file sharing and the other users that will have access to the file. If you will not be sharing the file, Private is the most secure. If you plan to share the file within your organization, obviously Organizational will apply. But if you plan to let anyone outside of your organization access this file, you should select Public.

You might also be asked to which level of the URL this security setting should be applied. Selecting the base ‘https://cloud.hawkindynamics.com/api’ would work best, as it will apply to all queries above it (token and organization). 

A screenshot of a computer

Description automatically generated

Repeat Steps 

This sequence shows the steps you need to create a data call for an individual query. It is recommended that that you use separate queries for each test type, and for other organizational specific identifiers such as teams, groups, and athletes. You can repeat these same steps for each of the queries by the appropriate script.  

Notes 

Site URL 

The scripts being used in this example are written for organizations in the Americas. Ensure that you have entered/replaced the correct URL in the script if you are outside of the Americas. Check your cloud site URL to verify the URL prefix used for your organization. 

Data formatting 

Ensure to check the format of the columns after running the scripts. Particularly the columns including numerical values. After running the scripts, the values in those columns are not assigned a format and will assume as text. Be sure to check the returned data tables and assign the appropriate formats.