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.
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
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.
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
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:
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:
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:
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.