Okay, this is a bit of a cheeky title as this guide won’t cover how to make a full dashboard. But rather how to make a live bar graph connected to Google Sheets.

This means you will have a bar graph on a website which will update live when you change values in your Google Sheet

You can check out what the final result will look like.

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:

1. Create a Google Sheet API using API Spreadsheets
2. Creating an HTML file with the bar graph code using Any Chart and connecting to the Google Sheets Data
3. Making the HTML file live by deploying it on GitHub using GitHub Pages

---

Create a Google Sheet API using API Spreadsheets

1. Create a Google Sheet in your Google Account

We are going to create one with the Team and Wins of the Teams that have won the Super Bowl 2019.

2. Convert this to an API

Sign Up for an account on API Spreadsheets

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

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.

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

1. Make an empty folder and call it anything you want

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

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 between the <script> tags

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

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.

1. Create a GitHub account if you don't have one

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

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

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

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

---

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:

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

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

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.

If you have any further questions, feel free to email us at info@lovespreadsheets.com! We can’t wait to see what you build.

Checkout our other tutorials!

Go Up