An example of how to use the FourJaw API in Microsoft Excel.
Please ensure you have completed the first guide to gain an API key and access the interactive API docs before beginning this guide.
Introduction
This article details a step-by-step guide for pulling utilisation data from the FourJaw API into Excel, and refreshing it dynamically.
A finished version of the Excel spreadsheet created during this guide can be downloaded here.
You're welcome to follow the steps on your own, or can watch the video walkthrough below.
Step 1: Build the URL
Open a new Microsoft Excel Document and enter the following data into the cells as shown. This gives us a structure to build up our URL for the API request.
In Cell B3, we will enter the following formula that will build up our URL by concatenating data from column B5 to B14.
=CONCAT("https://", B5, ".fourjaw.app/rest/api/v0.1/analytics/utilisation?asset_id=", B6, "&start_timestamp=", B7, "&end_timestamp=", B8, "&grouping=", B9, "&shifts=", B10, "&sort=", B11, "&order=", B12, "&page_size=", B13, "&page=", B14)
If you hit enter, you'll see B3 now has something resembling a query string, but it is missing a lot of information. This makes sense as cells B5 to B14 are empty!
Step 2: Enter Your Parameters
Now we will enter values in the cells B5 to B14 for each of the parameters as follows:
Subdomain: The subdomain of your FourJaw dashboard. For example, if your dashboard is found at "https://fjmanufacturing.fourjaw.app" then your subdomain is simply "fjmanufacturing", without the quotes. Every FourJaw customer has a different subdomain. he test site I am using for this demo is on a subdomain of "staging10".
Asset ID: The ID of an asset you're interested in. You can find asset IDs by following this tutorial. In this case, I'm going to use the top level organisation ID: "657871d62d2a73c177275c1c".
Start Timestamp: The start timestamp for the range of utilisation data we want to get, formatted as an ISO 8601 string. These take the format of YYYY-MM-DDTHH:MM:SSZ where T and Z are just left as letters and the rest are replaced by dates/times, for example: 2024-09-01T08:00:00Z for 8am on the 1st of September 2024.
End Timestamp: The same behaviour as Start Timestamp, but must be a date after the Start Timestamp. Here we'll use 2024-10-01T08:00:00Z - the 1st October 2024 at 8 am.
Grouping: Choose from hour, day, week, month or none. There are some restrictions to what you can ask for. For example, getting hourly data for a year is forbidden as it's a lot to ask for at once. The API docs for each endpoint contain detailed guidance on what is/isn't allowed.
Shifts: Allows you to filter for in-shift time and ignore other times outside of shift hours (such as a Sunday evening).
Sort: Choose "ascending"
Order: Choose "timestamp"
Page Size: The number of results returned in one go. Pick 100 to begin with, but beware when using the API that this value may truncate/chop off some of the data if set too small.
Page: For iterating through multiple pages of data. In this case, we'll just use the first page. Choose "1".
Step 3: Create the Table
When entering the data in the previous step, you should have seen the URL in cell B3 update until all the correct values are specified.
Finally, you should enter a valid API Key into cell B2.
Now we will turn the top 3 rows for columns A and B into a table. This is important to enable dynamic updating of the query from within the spreadsheet.
Highlight the range A1 : B3 and then under the "Insert" menu in the top navigation, click "Table".
Make sure to tick the box saying "my table has headers".
You'll now see a coloured formatting applied to the table, indicating it has succeeded.
Step 4: Import the Table into Power Query
All this setup makes it a quick next step to request data from the API.
First we will click one of the cells within the table...
Click "Data" in the top bar and select "From Table/Range".
You'll see the Power Query editor open with the API Key and URL from within the table. I would recommend renaming it using the text input on the righthand side. I will name my table "QueryTable".
Once you have changed the name, click the little arrow on the "Close & Load" button, then select "Close & Load to..."
In the following dialogue, choose "Only Create Connection". If you don't do this, Excel will randomly create another sheet and insert the table again - not the end of the world but a little annoying.
It seems like a lot of work to get to this point, but the benefit is, you can now change any of your query parameters and have that reflect in an API request.
Step 5: Making the API Request
Now we are ready for the API request. Go to the "Data" tab and under "Get Data" navigate to "From Other Sources" > "Blank Query".
In the Power Query editor that pops up, use the text input on the right to rename the query as "UtilisationData" and then put the following formula in the text box at the top...
= Json.Document(Web.Contents(QueryTable{1}[Value], [Headers=[#"X-API-KEY"=QueryTable{0}[Value]]]))
This formula will pull the URL and API Key from the table we created before, and formulate them into a web request. You'll see in the formula that it specifically references the table by the name I gave it "QueryTable". If your table has a different name, you'll need to make sure it matches.
At this point you'll see a yellow warning across the top. This happens because we have one query (the table with our API and URL) feeding another query (our web request). This is perfectly secure in this case as we are in full control of the inputs. However, Excel prevents this behaviour out of caution.
To solve this, first save the query by clicking the arrow under "Close & Load" and select "Close & Load To...". In the resulting popup, click "Only Create Connection" like we did before.
Under "Data" > "Get Data" select "Query Options".
In the popup that appears, look under "Current Workbook" > "Privacy" and select "Ignore the Privacy Levels and potentially improve performance". Press OK.
We can now open up the Power Query editor under the "Data" tab, select "Get Data" and "Launch Power Query Editor".
To run the API request, click on the "UtilisationData" query on the right and then "Refresh Preview".
If you get a result back looking like this, you've had a successful request to the API and have got a response - congratulations!
If you hit a yellow message with an error from the API, it's likely there is a mistake in your request. Please check the API key, asset ID and other parameters match up with the documentation. If you're really struggling, put the exact parameters you have in your spreadsheet into the interactive docs and adjust the request until it works.
Step 6: Extracting the Results
In the Power Query editor, we can now extract the results back into our spreadsheet.
Right click the cell saying "items" and select "Drill Down".
You'll see this adds an extra data processing step on the righthand side. Here we are effectively telling Power Query how to interpret the API response.
Click "To Table" in the top left.
Use the default values for the table by pressing "OK".
The last step is to click the expansion symbol at the top of the resulting column. This will extract the timestamp and utilisation data.
Finally we've got our table of utilisation data!
Personally, I like to rename the column titles to something a bit more human-friendly. You can do this by right clicking the column headers and choosing "rename".
We're now ready to put this table back into our spreadsheet. Click "Close & Load" in the top left.
Step 7: Load to Spreadsheet
In the main spreadsheet, select a cell where you want the utilisation data table to go. I will choose cell E5. Then, under the "Data" tab, choose "Queries & Connections".
In the righthand pane that appears, right click the "UtilisationData" query, and click "Load To".
In the resulting popup, make sure you have selected to load to a "Table" in the existing worksheet. It will automatically enter the currently selected cell, E5.
Click "OK" and watch the data load!
You will now see your utilisation data appear in the spreadsheet!
Step 8: Dynamically Change the Data
You might be thinking it has been a long tutorial, now we'll see what this has all been working towards - dynamically updating the data!
In the query parameters on the left, you can change the request by modifying the data in the cells. Why not change the "Grouping" from "week" to "month".
Once you've made a change, simply click "Refresh All" under the data tab and the API will be sent a new request with different parameters. You should see your table of data update.
Step 9: Leverage Excel Functionality
You can now leverage any of the normal Excel functionality with the utilisation data pulled from the API. Feel free to build spreadsheets for scheduling, staffing, cost calculations and much more off the back of this API data.
As a small example, we can insert a plot for the data. This makes it especially visual when dynamically updating the parameters with the API.
Congratulations! You're ready to use the API dynamically in Excel. A similar pattern can be followed for other types of data that you can get from the API.
Feel free to get in touch if you have any questions or comments.