Create Dynamic Visualizations using Spreadsheets

Using Google Sheets or Excel to host and manipulate your data? You can keep track of your KPIs in real time using Geckoboard. 

Updating spreadsheets can often be a very manual process but keeping your Geckoboard Spreadsheets widgets up-to-date isn't. You can set up your Spreadsheets widgets in such a way that they'll automatically update as you add more rows of data to your Google Sheet or Excel workbook. You don't need to manually edit the widget configuration as you add more rows. 

 

An example scenario

A perfect example of this is creating a Line Chart from a spreadsheet in which a new row of data is periodically added.

We'll break this example scenario down into a series of steps:

 

Store the data in a spreadsheet

At the beginning of May, my company's sales figures for January to April are finalized. I'm storing our sales figures in a Google Sheet and I want to display this in a Line Chart on my Geckoboard dashboard so that the rest of my organization can see how our sales are progressing.

Store_data.png

Back to top

 

Set up the Spreadsheets widget in Geckoboard

In Geckoboard, I'll create a new Spreadsheets widget connected to my Google Sheet.
(If you haven't used our Spreadsheets integration before, click here to view an article that covers how to do this.)

I'm not going to select just the cells that my data is in (e.g. X-Axis would be A1:A5, and the respective series would be B1:B5, C1:C5, D1:D5, E1:E5). If I did this it would mean that when I add more data to my Google Sheet, I'd have to come back into the widget configuration and select a new range for my X-Axis and each of my series.

Select_just_cells_data_is_in.png

Instead, I will select whole columns so that, as I add more data to my spreadsheet, this new data will be included in the selection range of my Line Chart. 

To select a whole column, click the column header in the Spreadsheet Data area. I'll set  the X-Axis to A:A, and the respective series as B:B, C:C, D:D, E:E.

Select_whole_columns.png

Back to top

 

The result: a dynamic Line Chart

This Line Chart will plot the values found in the specified ranges and ignore all the blank rows. At the end of April, the Line Chart looks like this:

Dynamic_line_chart_result.png

Back to top

 

Add more data to the spreadsheet

As the months pass, I update my Google Sheet with the finalized sales figures for that month. At the end of September, my spreadsheet looks like this:

Add_more_data.png

Back to top

TIP: As you can see, I've prepopulated all the months of the year in my spreadsheet. These extra dates (Oct, Nov, Dec) won't show in the X-Axis of my Line Chart until there's corresponding data in one of the Series columns. Prepopulating dates in your spreadsheet can be a great time-saving technique.

Back to top

 

The dynamic Line Chart automatically updates

At the end of September, without needing to update anything in my Line Chart configuration, my Line Chart automatically updates to reflect the additional rows of sales data:  

Dynamic_line_chart_automatic_update.png

Back to top

 

Use this with other visualizations

You can use this technique in other Spreadsheet visualizations such as the Number widget and Geck-o-meter work particularly well.

As above, set your selection to the whole column (e.g. B:B in this example) and as you update your spreadsheet throughout the year, your widget will automatically update.Use_with_other_visualizations.png

Back to top

 

Frequently Asked Questions 

Why do my X-Axis dates not display properly in my chart?

The most common issue is that the X-Axis dates are being calculated by a formula e.g. =today() or coming from another sheet =Sales!A1. Unfortunately our Spreadsheets integration cannot display these dates due to a limitation of the Google Sheets API; when the data comes through the API into our Spreadsheets integration, the information that it is a date is lost.

The easiest solution is to create another column with manually entered dates that Geckoboard will use to populate your charts.

The GIF below shows that the dates in Column A are coming from another Google Sheet. Our Spreadsheets integration is not able to display these dates in the X-Axis of the chart.

FAQ_X-axis_dates_don%27t_display.gif

To fix this, copy the dates from Column A and then go to Edit > Paste Special > Paste Values Only to paste them into another column. To format these as dates, go to Format > Number > Date. Alternatively, you can manually enter the dates.

In Geckoboard, set your Spreadsheets widget to use this new column of dates as the X-Axis of your chart (as shown in the GIF below).

FAQ_X-Axis_dates_display_answer.gif

How often will the data in my Spreadsheets widgets refresh?

Your Spreadsheets widget will automatically refresh every 10 minutes.

If you make changes to your sheet/worksheet in between those refreshes, the latest changes will not immediately be reflected; however, the changes will be picked up in the subsequent widget refresh.

Can I force the data in my Spreadsheets widgets to refresh?

You can force the values to update by editing the widget and clicking Save

Back to top

Was this article helpful?
👍

Thank you for your feedback!