How to Stream Your Google Sheets Data to a Real-Time DashboardInitial StateBlockedUnblockFollowFollowingFeb 20Spreadsheets excel at (see what I did there …) manipulating data in all kinds of crazy ways.
However, they tend to be a rather boring tool for displaying / sharing KPIs and other key summary data.
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 useGoogle Sheets (info): A powerful spreadsheet you can use in your web browser (free).
Initial State (info): 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 workInitial 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 our account, we can display that data in visualizations and build a cool data dashboard that we can view in our web browser.
We just need to get Google Sheets to send data from our spreadsheet to our Initial State account when we 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 our spreadsheet will be a piece of cake and let us send whatever data we want.
Step 1: Create an Initial State data bucketRegister for an Initial State account at https://iot.
app.
initialstate.
com if you do not already have one (this will start a 14-day trial, no credit card required).
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 our streamed 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 data should go into.
You can specify whatever Bucket Key you want in the New Stream Bucket dialog box.
Your Access Key will also be listed.
You can set the name of this data bucket in the Name field.
Click the Create button to create this new data bucket.
You will see the new data bucket listed in your 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: A Google ScriptCreate 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:You will need to place your account Access Key (from Step 1) on line 2 and your 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 (more info).
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 Spreadsheethttps://docs.
google.
com/spreadsheets/d/13331i9R090aKtm8LMcvLzz7_Zn9CXp_KMrZxNyCCRxc/edit?usp=sharingThe link above 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.
Google Sheets streaming in action: change cell data, dashboard updates in real-timeNotice you can stream numbers, strings, or even emojis to your dashboard.
Any time any input variable changes, the streamData function executes and sends data.
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: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 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-15T18: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, your browser’s local time zone will be used to display your data.
https://docs.
google.
com/spreadsheets/d/1wcdQg-jtG4n27xJxcHfMx-QF1dBc1o1PMdgAuV0A0Fk/edit?usp=sharingThe link above 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.
https://go.
init.
st/v8sknuqStreaming 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 (more info) and emojis to add context to tiles (more info).
You can import this exact dashboard layout into your example by following the instructions here.
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 (more info).
.