How to Easily Make a Live Dashboard with Google Sheets

Okay, this is a bit of a cheeky title as the blog won’t cover how to make a full dashboard.

But rather how to make a live bar graph connected to Google Sheets.

Image for post
Image for post
Photo by Luke Chesser on Unsplash
Image for post
Image for post

You can take this knowledge and with a bit more learning of front-end development, you can extend it to make a complex organizational dashboard.

Let’s Get Started!

We will be covering three tools that make this simple:

Create a Google Sheet API using API Spreadsheets

Disclaimer: API Spreadsheets is our product. You can also use Google Sheets directly but the process is a lot more cumbersome

Since it’s the Super Bowl this weekend, we are going to create one with the Team and Wins of the Teams that have won the Super Bowl.

Image for post
Image for post

2. Convert this to an API using API Spreadsheets

Go to www.apispreadsheets.com and sign up for an account.

Click on Google Sheets and select your SuperBowlWinners Google Sheet. After you have selected the file you should see the following

Image for post
Image for post

3. Change Data Format & Copy the URL

Due to the Any Chart library we are using to make the graph, we need our data in a certain format.

So we are going to change the Data Format to Matrix and Copy the API URL.

Image for post
Image for post

Keep this URL handy as we will be using it in the next section.

Creating HTML File with Bar Graph Code using Any Charts

This section is paraphrased from the Any Chart website so feel free to read that tutorial for a more in-depth look into this section.

You can also check out the full HTML file at my GitHub repo: https://github.com/asharma327/LiveBarGraphDemo/blob/master/index.html

I will be calling it LiveBarGraphDemo so anywhere you see that in the tutorial change it to what you are calling it

2. Create a file inside the folder called index.html

3. Write the following code in index.html

<!DOCTYPE html>
<html>
<head>
<title>Super Bowl Bar Chart</title>
<script src="https://cdn.anychart.com/releases/8.0.0/js/anychart-base.min.js"></script>
</head>
<body>
<div id="container" style="width: 100%; height: 100%"></div>
<script> <!--chart code --> </script>
</body>
</html>

This code sets up the HTML file and references the Any Chart Javascript library that we will be using for our Bar Graph.

It also has a <div> called container with width and height of 100%. This is where our Bar Graph will appear.

After the <div> there is a <script> tag. This is where our chart code will go.

4. Write the following code in the <script> tags

<!DOCTYPE html>
<html>
<head>
<title>Super Bowl Bar Chart</title>
<script src="https://cdn.anychart.com/releases/8.0.0/js/anychart-base.min.js"></script>
</head>
<body>
<div id="container" style="width: 100%; height: 100%"></div>
<script>
anychart.onDocumentReady(function() { // Fetch Data from the API
fetch('https://api.apispreadsheets.com/data/422/?dataFormat=matrix')
.then((response) => {
return response.json();
})
.then((myJson) => {
// create data variable for the chart
var data = {
header: ["Team", "Wins"],
rows: myJson.data
}
// create the chart
var chart = anychart.column();
// add the data
chart.data(data);
// set the chart title
chart.title("Super Bowl Wins");
// draw
chart.container("container");
chart.draw();
});
});
</script>
</body>
</html>

Let’s walk through the code between the <script>.

  • We first fetch the data from the API that we created in the previous step.
  • Then we create a variable called data that has headers and rows. We manually specify our headers as Team and Wins. The rows are obtained directly from the API
  • We use the Any Chart reference to create a chart, provide it data, give a title, tell where the container is (in this case our <div> called container) and finally draw it

5. Open up the index.html file in your browser

And you should see a bar graph with teams and their super bowl wins

Image for post
Image for post

Deploy Chart on GitHub pages

Now we are going to be deploying this on a live website that can be accessed from anywhere.

And since our data is in Google Sheets, we can change it manually and our chart will update in real-time!

We are going to be using GitHub pages to do this since it’s the easiest. Like the above, I will be heavily paraphrasing from the official GitHub pages tutorial so feel free to check that out for a more in-depth guide

Also, there are other ways to do this however I am going with the version below because of the least code being involved.

Go to www.github.com and create an account

2. Create a New Repository

I call mine LiveBarGraphDemo to keep it consistent and create it with the following settings

Image for post
Image for post

3. Click on upload an existing file, select and commit the index.html file

Image for post
Image for post
Click uploading an existing file to go on to the next screen
Image for post
Image for post
Choose file, give a commit message and click on commit changes

4. Enable GitHub Pages to track your file

Now we are going to tell GitHub pages to deploy our file and repo online.

Click on Settings

Image for post
Image for post

Now scroll down and under Github Pages, change the Source to master branch

Image for post
Image for post

Enjoy your live Bar Graph

Your bar graph will be live at the website

https://your-github-username.github.io/your-respository-name

For example, mine is at

https://asharma327.github.io/LiveBarGraphDemo

Change Values Around

Now the fun part is that you can easily change the values in Google Sheets and see them reflected live on the chart on your website.

This is how the graph looks now:

Image for post
Image for post

I am going to change the New York Jets to have 20 Super Bowl Wins in the Google Sheets

Image for post
Image for post

Now when I view the website, the scale has changed and the Jets bar has the most wins!

Image for post
Image for post

Next Steps

With a bit more fluency in HTML, CSS and Javascript you can create a full live dashboard using Google Sheets as your data source.

Let us know if you have any questions at info@lovespreadsheets.com!

Products and resources that magically simplify hard spreadsheet tasks. Check us out at www.lovespreadsheets.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store