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.

Store the data in a spreadsheet

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

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

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

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

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

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

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.

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

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

Was this article helpful?

Awesome! 👍  Thanks so much for your feedback!