Update Your Real Time Dashboard with Google Sheets

sheets-dashboard

People have been using spreadsheets to hold and understand data for years. Once that data grows to hundreds or thousands of cells it can be hard to grasp quickly and easily. So, what if we had a Google sheet that automatically updated your dashboard as it populated with new information?

Well, now you can! Using a custom script in Google Sheets, we’re able to turn your cells of hard to read data into beautiful visualizations in Initial State. This is so easy, you’re going to wonder why you haven’t done this before.

Project Difficulty:
Probably the easiest thing you’ll do today

What you’ll need:

1. An Initial State account
2. A Gmail account for Google Sheets
3. A spreadsheet that you want to turn into visualizations

Step 1: Initial State

Sign Up

Signing up for Initial State is easy! Just go to initialstate.com/register to create your account. Type in your email, password, click register and you’re done!

You can watch this video if you’d like a walk through of how to sign up:

Create a home for your Google Sheets data

This is where you’ll create a location for your data to post.

1. Click the cloud icon in the top right of your bucket menu to create a new https bucket.

Jamie example key covered

2. You can name your bucket anything you wish. Mine is named “Google Sheet Testing,” because well… that’s what I’m doing!

screen-shot-2016-10-26-at-11-30-02-am

3. Click Configure Endpoint Keys
4. Optionally, to make it easier to recognize my API endpoint key, I changed my bucket key to SheetTest.
5. Below that, you can change your access key if you wish. Using whichever one pops up is fine.
6. Click create

Once you’ve gone through those steps, you’ll see you’ve created a new bucket ready to fill up with some data.

Step 2: Get a Gmail Account

Most people have a Gmail account, but if for some reason you don’t then have no fear. It’s free, and super easy to sign up and use. It’s so easy that… I’m just going to tell you to Google this step if you can’t figure it out on your own.

Step 3: Turn Your Spreadsheet into a Live Dashboard

Here’s the fun part!

1. Open the sheet that you want to create a dashboard with
2. Go to Tools on the top menu
3. Click Script Editor

screen-shot-2016-11-01-at-9-47-10-am

4. Copy the script below and paste it into the script editor

function InitialState(value, signalName) {
if (!signalName) {
signalName = 'UnknownData';
}
//if (value == null || value !== value) { //If I want to have it send Invalid if data is null, undefined or not a number
//value = 'Invalid';
//}
if (value != null && value === value) { //If I want it to just not send a value if it is null, undefined or not a number
var url = 'https://groker.initialstate.com/api/events?accessKey=INSERT_ACCESS_KEY_HERE&bucketKey=INSERT_BUCKET_KEY_HERE&' + encodeURIComponent(signalName) + '=' + encodeURIComponent(value);

UrlFetchApp.fetch(url);
}

return value;
}

5. Edit script by inserting your access key where it says, “INSERT_ACCESS_KEY_HERE”
6. Edit script by inserting your bucket key where it says, “INSERT_BUCKET_KEY_HERE”

screen-shot-2016-11-01-at-9-47-52-am

Note:

I wanted to make sure that if for some reason the spreadsheet came up with an error or a number that is null, undefined or not a number that it wouldn’t send any data and make my dashboard look strange. So I used the line:

if (value != null && value === value) { //If I want it to just not send a value if it is null, undefined or not a number

If you want it to send the word “invalid” to your dashboard if it has a number that is null, undefined or not a number then you can use the couple lines that I commented out below. To do this, just erase the slashes before if, value, and the closing bracket and comment out (with forward slashes) the other lines above.

//if (value == null || value !== value) { //If I want to have it send Invalid if data is null, undefined or not a number
//value = 'Invalid';
//}

7. Go back to your spreadsheet and use your function. Here I’ve just created a test sheet with data from total views on one of my personal websites. You can see that I created a daily, monthly and total view column. For the sake of this example, I am just calculating monthly and total views. I put this formula in my total views cell:

Calculating total views and sending to Initial State:

=InitialState(SUM(B:B),D1)

screen-shot-2016-11-01-at-9-53-29-am

Further explanation:

You’ll see that I’m sending the value of the SUM of all the cells in column B to Initial State, and it’s going use the signal name (and land in the tile named) ‘Total Views’ which is indicated by the D1 cell in the formula. This is mapped out in our custom script at the very beginning:

function InitialState(value, signalName)

Conclusion:

Now you can take ANY data from your spreadsheets and automatically create a dashboard with them! This is great to keep track of data from business uses, personal budgeting, or anything else you want to keep track of in an automatically populated data visualization.

2 comments On Update Your Real Time Dashboard with Google Sheets

Leave a reply:

Your email address will not be published.

Site Footer