Stream Data from Google Sheets to a Dashboard

Spreadsheets excel at manipulating data but not displaying it. This is exactly why so many specialized data visualization and BI dashboard companies started popping up. The problem with most of these products is that they are typically expensive and difficult to set up. This tutorial will show you a simple, inexpensive way to create a beautiful, sharable, real-time updating dashboard from your Google Sheets data.

Services We Will Use

Google Sheets: A powerful spreadsheet you can use in your web browser (free).

Initial State: A data streaming and visualization service that you can use to create real-time dashboards in your web browser (free for students, $9.99/mo or $99/yr for everyone else).

How This Will Work

Initial State is a data streaming service, which means you can push time-series data (i.e. data with a timestamp) to its API. In other words, if Temperature is 50 degrees at 5:45 PM, we can easily send that information as a data point to the Initial State API. Once that data is in your account, you can display that data in visualizations and build a custom data dashboard that you can view in your web browser. You just need to get Google Sheets to send data from your spreadsheet to your Initial State account when you want it sent.

Google Sheets has an awesome scripting feature that we can use to build a custom function to do just that. We just need to write a function in Google Script that will send data to the Initial State API. Calling that function in your spreadsheet will be easy and let you send whatever data we want.

Step 1: Create an Initial State Data Bucket

Register for an Initial State account here, if you do not already have one. Once you are registered and logged in, click the +cloud icon at the top of your bucket shelf to create a new data bucket. This will be the destination for your streaming Google Sheets data. To send data into this bucket, you need two keys. One is your private account Access Key that will direct data into your account. The second key is the Bucket Key, which is used to specify which data bucket in your account the data should go into. You can specify whatever Bucket Key name you want in the New Stream Bucket dialog box. Your Access Key will be listed as well. Click the Create button to create this new data bucket. You will see the new data bucket listed in your bucket shelf. You can click on the Settings link under the data bucket name to see the Bucket Key and Access Key. You will need these keys for the next step. Your Initial State data bucket is ready to receive data.

Step 2: Create a Google Script

Create a new Google Sheets spreadsheet. Click on Tools -> Script editor to open the Google Script editor. Copy and paste the following function into your script editor:

function streamData(signalName, value, enable) {
 var accessKey = 'PLACE YOUR ACCESS KEY HERE';
 var bucketKey = 'PLACE YOUR BUCKET KEY HERE';
 if (!signalName) {
   signalName = 'UnknownData';
 }
 if (enable) {
   var url = 'https://groker.init.st/api/events?accessKey=' + accessKey + '&bucketKey=' + bucketKey + '&' + encodeURIComponent(signalName) + '=' + encodeURIComponent(value);
   UrlFetchApp.fetch(url);    
   return signalName + '=' + value;
 }
}

You will need to place your account Initial State Access Key on line 2 and your Initial State Bucket Key on line 3 where directed. Click on File -> Save to save this script and apply all changes (if you don’t save your changes, your spreadsheet cannot use this new function).

Let’s take a look at the function we just created. streamData(signalName, value, enable) requires three input parameters. signalName is the name of the data stream (e.g. Temperature). value is the current value of the data stream (e.g. 50). enable is either TRUE or FALSE and is used to control when we actually send data to our data bucket. Line 8 is the line of code actually calling the Initial State API using URL parameters.

You can test this out by editing a cell on your spreadsheet with the formula: =streamData(“myNumber”, 1, TRUE) and hitting ENTER. If data was sent successfully, the cell should return myNumber=1.

Go back to your Initial State account and click on your new data bucket. You should see that the number 1 was sent to a new data stream named myNumber. Play around with changing the value and signalName and watch your dashboard change.

Step 3: An Example Spreadsheet

This link will take you to an example spreadsheet that uses the streamData function in two different places, one to send the value of myNumber and another to send the value of myString. You will have to copy this example and repeat Step 2 to add your own Google Script function to play around with this spreadsheet. The enable is specified in C2. Simply change this to FALSE to make changes without sending any unwanted data to your data bucket and back to TRUE to enable data streaming.

Notice you can stream numbers, strings, or even emojis to your dashboard. Any time any input variable changes, the streamData function executes and sends data.

Option: Specifying Timestamps (Stream Data From the Past)

The first example simply sends data via URL parameters whenever a data input to our Google Script function changes. The timestamp used for this data point is whatever time the API receives the data. What if you want to specify a timestamp? For example, what if you want to send data from a month ago into your dashboard? The Initial State API (documentation here) allows us to do just that. We just need to write a different Google Script function that takes in the timestamp as an input parameter:

function streamDataTime(signalName, value, timestamp, enable) {
 if (!signalName) {
   signalName = 'UnknownData';
 }
 var headersIS = {
   'X-IS-AccessKey': 'PLACE YOUR ACCESS KEY HERE',
   'X-IS-BucketKey': 'PLACE YOUR BUCKET KEY HERE',
   'Accept-Version': '~0'
 }
 var data = {
   'key': signalName,
   'value': value,
   'iso8601': timestamp
 };
 var options = {
   'method' : 'post',
   'contentType': 'application/json',
   'headers': headersIS,
   'payload' : JSON.stringify(data)
 };
 if (enable) {
   UrlFetchApp.fetch('https://groker.init.st/api/events', options);
   return signalName + '=' + value;
 }
}

The Google Script above adds a timestamp input parameter. This script calls the Initial State API by sending a JSON object via an HTTPS POST. All you have to do is specify your Initial State Access Key and Bucket Key on lines 6 and 7.

The timestamp must be iso8601 formatted (info on iso8601). An example iso8601 timestamp is “2019-01–01T18:00:00-06:00". This timestamp is equivalent to January 1, 2019, 6:00 pm CT. The “-06:00” part of the timestamp specifies the timezone. If you don’t specify your timezone, the time is assumed to be UTC. When you view your data in Initial State in Tiles, your browser’s local time zone will be used to display your data.

This link will take you to an example spreadsheet that uses the streamDataTime function in the F column. You will have to copy this example and repeat Step 2 to add your own Google Script function to play around with this spreadsheet. This example logs the number of website visitors for each day of the month in January.

Streaming this example spreadsheet to an Initial State dashboard can look like the above (you can view this dashboard at https://go.init.st/v8sknuq ). Notice the timeline at the top of the dashboard lines up with the timestamps specified in the Google Sheets data (column D). This dashboard uses a gauge and emojis to add context to Tiles. You can import this exact dashboard layout into your example by following these instructions. You can add a background image to your dashboard to give your data more context.

You could add any number of KPIs to a Google Sheets spreadsheet and stream them to a dashboard using the basic principles in this example. You could set up multiple spreadsheets to send data to a single dashboard. You could set up sensors or other applications to send data to the same dashboard as your Google Sheets spreadsheet and even mathematically combine those different data sources inside your dashboard.