Introduction: Update a Real Time Dashboard With Google Sheets
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
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 the video above 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.
2. You can name your bucket anything you wish. Mine is named “Google Sheet Testing,” because well… that’s what I’m doing!
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: Create 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
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”
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)
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.
