Creating a simple self-containing Dashboard with an API (JS, Tailwind, HTML)
Last week at work I found myself in a situation where I needed to create a dashboard with ‘real-time’ timeseries data from the Danish Electricity grid. The data is used in a continuous analysis of the electricity market, by me and my colleagues, thus having fresh data is important and can provide an edge when operating in the power markets.
Why I need an API solution?
Previously, I had sourced the data from an FTP server, and then having a Python script hacking together an HTML file with styling and all. This was run on a cloud machine using (windows) Task Scheduler to have the script build a new HTML-file every five minutes with the freshest data. However, the data was not current enough, and using Task Scheduler on a cloud machine could not always be relied on.
Thus I had to look for an alternative solution. Luckily for me Energinet who is the Danish Transmission Service Operator (TSO), have recently released their API, freely available for the public. The different datasets they provide an API call for is found here: https://www.energidataservice.dk/search
I had never used an API before and have only been dabbling a little bit in web development. Thus, I wanted to keep this as simple as possible by not using any backend server, and making the dashboard self-containing.
What is needed to create a useable dashboard with real-time data?
- API to query data from
- HTML template to show the data in
- JS script to query the data and export it into the HTML template
API to query data from
Firstly, you need to figure out what data to get and where that data is available at. In my case I need some data about the Power System in Denmark, which can be found here Power System Right Now.
The data in the image above is the timeseries data with one minute frequency, that I want to source. It is possible to read the API documentation and see query examples here API Guides. The API can be queried in two ways. As I am familiar with SQL, this is the query type I will go with.
Query example (first 5 results)
https://api.energidataservice.dk/datastore_search?resource_id=powersystemrightnow&limit=5Query example (via SQL statement)
https://api.energidataservice.dk/datastore_search_sql?sql=SELECT * from “powersystemrightnow” LIMIT 5
I need the newest data from today. Thus my SQL query looks as such:
SELECT *
FROM “powersystemrightnow”
WHERE “Minutes1DK” > timestamp’2022–02–05 00:00:00'
AND “Minutes1DK” < timestamp’2022–02–05 23:59:59'
ORDER BY “Minutes1DK” DESC
According to the API documentation, I need to put “quotations” around the table you are querying, and use the timestamp declaration when querying. You can use Postman to easier test and figure out how to write the queries. Additionally, the browser will format spaces and special characters. A space is converted to %20 and a quotation mark is converted to %22. This means that my SQL query above looks like the one below when queried in the browser.
Now we have our query, next up we need a HTML template which can be used to show the data in.
If you have not tried working with json structures before, this would be a good thing to read a thing or two about. It is not too complicated, and it is good to know about when working with APIs.
This is how the object is structured from the API in this example. Try to use the console in your browser to look at how you access the specific data you need from the API query.
HTML template to show the data in
We got the data query down, now we need somewhere to show that data. For this I create an HTML shell, where I can make JavaScript paste in the fresh data in a table format.
I will not use CSS for the styling, instead I will use pure Tailwind. If you have not yet heard of Tailwind, then I would recommend you to look it up. It is basically a framework, which makes it easy to style HTML elements in a highly customizable and fast way. All styling with Tailwind is done in the classes of the HTML elements, which is why the HTML can look a bit messy.
I have incorporated three scripts one for Axios, Tailwind and App.js where my own JS code is found. The scripts for Axios and Tailwind is sourced from content delivery networks (CDN) meaning you do not need to download the scripts locally.
<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0"><title>Energinet</title><script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
<script src="https://cdn.tailwindcss.com"></script></head>
<body class="bg-green-100/80"><header class="px-4 py-8 bg-green-900 space-y-2"><h1 class="text-4xl text-white font-sans">Energinet</h1><p class="text-white">Data sourced:
<a href="https://www.energidataservice.dk/tso-electricity/powersystemrightnow" class="underline text-red-200">Power System Right Now</a></p><label for="dateSelect" class="text-white">Chose date:</label>
<input type="date" id="dateSelect" class="border-2 border-slate-200 rounded-lg"></header><div class="flex flex-col my-8"><table class="table-auto"><thead class="bg-gray-100 dark:bg-gray-700">
<tr id="tableHeaders"></tr>
</thead><tbody id="tableRows">
</tbody></table>
</div><script src="app.js"></script>
</body>
</html>
The picture above is what the HTML template looks like without my custom JavaScript running. Even though, it is not visible there is a table on this page, or at lest in the HTML markup. This table has been made with specifically placed id’s in two elements; namely tableHeaders and tableRows.
The tableHeaders id is placed in a <tr> element inside the <thead> element, and is used to append <th>elements with header names on to. The tableRows id is placed in the <tbody> element, and is used to first append a <tr> element as a row, and then append the <td> element with the individual values on to. All of this is happening in JavaScript, however it would not be possible to do without some sort of clever HTML markup.
As of normal HTML markup, there is not much to be said. I have an H1, a link to the original data source and a date picker, which is used in the JavaScript to select the date you want to se data from.
JS script to query the data and export it into the HTML template
The JavaScript section of this example consists of five functions, some minor and some essential. Here is a list of the functions in the order of appearance in the JavaScript file.
smallDate → A function to add a zero in front of the month (int) if this is smaller than 10. This is a way to get around new Date().getMonth() in JavaScript returning an int, and when this is used for SQL we need to have two digits for all months (meaning March is 03 instead of 3). Additionally, the function actually returns 0 for January, so one needs to add 1 to the output for it to make sense in the real world.
const smallDate = (datePart) => {
if (datePart < 10) {
return `0${datePart}`;}
return `${datePart}`;}deleteChild → This function is used later on to clear the table, whenever we input new data when changing the date and sourcing new data.
// This clears the table before adding new data.
function deleteChild(parent) {
let child = parent.lastElementChild;
while (child) {
parent.removeChild(child);
child = parent.lastElementChild;
}
}loadEnerginet → This function queries the API, and outputs the data in an object. Here the url for the API is queried through and async function using Axios. In the query url I have made it such that the query will take dateSelect.value as an input. If you do not know about promises in JavaScript, it might make sense for you to look a bit more into this. However, you could also just try this way of doing it out with an API of your choice (it might work fine with a bit of tweaking).
const loadEnerginet = async () => {
try {
const query = `https://api.energidataservice.dk/datastore_search_sql?sql=SELECT * from "powersystemrightnow" WHERE "Minutes1DK" > timestamp'${dateSelect.value} 00:00:00' AND "Minutes1DK" < timestamp'${dateSelect.value} 23:59:59' ORDER BY "Minutes1DK" DESC` const res = await axios.get(query)
console.log(res)
return res.data.result;
} catch (e) {
console.log(e);
}
}
loadTable → This function calls the loadEnerginet function and transforms the data into the table. It might be easier to copy and paste the code into VS-code and have it format the code, as the formatting is a bit difficult on Medium.
The code loads the data in, and starts of by creating a list of headers, evading some of the colums using simple string match (.startsWith). Then a <th> element is created where the header text is appended to. The <th> is then appended to the <tr> with the id tableHeaders.
Next up the table rows are created in kind of the same way. The data object is looped through using the .entries() method, which returns an Index and Value for each row in the data object.
For each Value (row) in the data object a <tr> is created, the list of headers is looped through and a <td> is created and appended with the value of the data object at the given row with the given header.
In the end of the row loop, the <tr> is appended to the <tbody> with the tableBody id.
The headers and rows are all styled with Tailwind, as can be seen in their classes.
const loadTable = async () => {// Loading the data from the API
const tableData = await loadEnerginet();
console.log(tableData)// Remove earlier table data
deleteChild(tableHeaders)// Create the table headers:
// Storing the table headers element
const tableHeaders = document.querySelector('#tableHeaders')// Creating a headers list
const headerList = []for (let header of tableData.fields) {if (!header.id.startsWith('_') & !header.id.endsWith('UTC')) {// Creating a new element and appending the headers to it
const newHeader = document.createElement('th');newHeader.innerText = header.id.replace('_', ' ').replace(/([a-z])([A-Z])/g, '$1 $2');
newHeader.classList.add('py-3', 'px-6', 'text-xs', 'font-medium', 'tracking-wider', 'text-left', 'text-gray-700', 'uppercase', 'dark:text-gray-400')tableHeaders.append(newHeader)
// Saving the header name in the headers list
headerList.push(header.id)
}
}// Storing the table rows
const tableRows = document.querySelector('#tableRows')// Remove earlier table data
deleteChild(tableRows)
for (let [keys, values] of tableData.records.entries()) {// Creating a new table row
const newRow = document.createElement('tr');
newRow.classList.add('border-b', 'odd:bg-white', 'even:bg-gray-50', 'odd:dark:bg-gray-800', 'even:dark:bg-gray-700', 'dark:border-gray-600', 'hover:bg-green-200')
for (let header of headerList) {
const rowValue = document.createElement('td');
if (header.startsWith('Minutes')) {
rowValue.innerText = values[header].slice(11, values[header].length - 3);
} else {
rowValue.innerText = Math.round(values[header])
}
rowValue.classList.add('py-4', 'px-6', 'text-sm', 'font-medium', 'text-gray-900', 'whitespace-nowrap', 'dark:text-white')
newRow.append(rowValue);
}
tableRows.append(newRow)
}
}
Besides functions I also create a date constant, add an event listener to the date picker and runs the loadTable() function once to initiate the table.
The entire JS script can be seen below. As mentioned earlier, it will be easier to read, if you copy and paste it into VS Code or another IDE, and have that format it, as the formatting in Medium is kind of messy.
Thank you for reading, and good luck with your own projects!
const dateObj = new Date()const smallDate = (datePart) => {
if (datePart < 10) {
return `0${datePart}`;
}
return `${datePart}`;
}let date = `${dateObj.getFullYear()}-${smallDate(dateObj.getMonth() + 1)}-${smallDate(dateObj.getDate())}`const dateSelect = document.querySelector('#dateSelect')dateSelect.value = date
// This clears the table before adding new data.
function deleteChild(parent) {
let child = parent.lastElementChild;
while (child) {
parent.removeChild(child);
child = parent.lastElementChild;
}
}const loadEnerginet = async () => {
try {
const query = `https://api.energidataservice.dk/datastore_search_sql?sql=SELECT * from "powersystemrightnow" WHERE "Minutes1DK" > timestamp'${dateSelect.value} 00:00:00' AND "Minutes1DK" < timestamp'${dateSelect.value} 23:59:59' ORDER BY "Minutes1DK" DESC`
const res = await axios.get(query)
console.log(res)
return res.data.result;
} catch (e) {
console.log(e);
}
}const loadTable = async () => {// Loading the data from the API
const tableData = await loadEnerginet();
console.log(tableData)// Create the table headers:
// Storing the table headers element
const tableHeaders = document.querySelector('#tableHeaders')// Remove earlier table data
deleteChild(tableHeaders)// Creating a headers list
const headerList = []
for (let header of tableData.fields) {
if (!header.id.startsWith('_') & !header.id.endsWith('UTC')) {// Creating a new element and appending the headers to it
const newHeader = document.createElement('th');
newHeader.innerText = header.id.replace('_', ' ').replace(/([a-z])([A-Z])/g, '$1 $2');;
newHeader.classList.add('py-3', 'px-6', 'text-xs', 'font-medium', 'tracking-wider', 'text-left', 'text-gray-700', 'uppercase', 'dark:text-gray-400')
tableHeaders.append(newHeader)
// Saving the header name in the headers list
headerList.push(header.id)
}
}// Storing the table rows
const tableRows = document.querySelector('#tableRows')// Remove earlier table data
deleteChild(tableRows)
for (let [keys, values] of tableData.records.entries()) {// Creating a new table row
const newRow = document.createElement('tr');newRow.classList.add('border-b', 'odd:bg-white', 'even:bg-gray-50', 'odd:dark:bg-gray-800', 'even:dark:bg-gray-700', 'dark:border-gray-600', 'hover:bg-green-200')for (let header of headerList) {
const rowValue = document.createElement('td');if (header.startsWith('Minutes')) {
rowValue.innerText = values[header].slice(11, values[header].length - 3);
} else {
rowValue.innerText = Math.round(values[header])
}
rowValue.classList.add('py-4', 'px-6', 'text-sm', 'font-medium', 'text-gray-900', 'whitespace-nowrap', 'dark:text-white')
newRow.append(rowValue);
}
tableRows.append(newRow)
}
}dateSelect.addEventListener('change', loadTable)
loadTable()
