After selecting your data source, you need to decide what visualization you would like to display your data with.
By default, the selected visualization is a Line Chart, but you can change to a Column Chart, Bar Chart, Number Widget, Leaderboard, or Geck-o-meter using the Visualization Selector on the top right side of the Set Up Area.
To prepare you visualization, follow these steps.
- Click in the Set Up Area.
- Click to activate each field and select the cells you want to visualize from the Spreadsheet Data.
If your data is found on a different sheet of your file, you can select a different sheet by clicking the Tab Selector in the bottom left corner of the Spreadsheet Data area.
Tip
If you receive the error message 'Each series must have at least one data point', you need to add a 0 to any selected blank cells.
Depending on what you want to select, this is what you should do:To select Do this A single cell Click the cell, or type in the cell i.e. B3 directly in the field you want to populate with that cell. A range of cells Click the first cell in the range, and then drag to the last cell, or hold down Shift while you press the arrow keys to extend the selection. You can also type in the range i.e. B2:B10 directly in the field you want to populate with that range.
A large range of cells Click the first cell in the range, and then hold down Shift while you click the last cell in the range. You can scroll to make the last cell visible. You can also type in the range i.e. B2:B10 directly in the field you want to populate with that range.
An entire row or column Click the row or column heading. Note
When you select an entire row or column, your widget will pick data automatically as values for new cells become available.
Note
Series names are editable via the Set Up Area. By default, series names come from your selection (first cell), but you can use a different name. To do this, click on the Series field and enter your chosen name.
Use the toggle switch to turn off the default setting. - Use the Live Preview to see how your visualization will look on your dashboard. And you’re done!
Note
It's not possible to make changes to your Google Sheet data within the widget set up screen. Any changes have to be made in Google Sheets before refreshing the Google Sheet widget set up screen to see your changes reflected.
Using pivot tables in Geckoboard
Geckoboard will automatically interpret pivot tables from your Excel spreadsheet. For Google Sheets, the Script Editor provides an effective workaround.
Using a script, you can copy the values from a pivot table into a new sheet as values. These copied values are then used to power your Geckoboard widgets. Using Google Sheets' triggers we can also automate this process.
Copy and paste this script into Google Sheets' Script Editor.
function extractPivotTableValues() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
/* Replace pivot-table with the name of the sheet that contains the original pivot table. If your pivot table is very large you may also need to increase the cell range from A1:Z999 */
var source = ss.getRange('pivot-table!A1:ZZ9999');
/* Create a new sheet for this script to copy your pivot table values into and replace pivot-table-copy with the name of that sheet. */
source.copyTo(ss.getRange('pivot-table-copy!A1'), {contentsOnly: true});
}Finally, don’t forget to set up the auto trigger after adding this script.
If you need, we have a thorough guide explaining how to set up the script and use auto triggers in the "How to Configure Automatic Updates" section in our guide here.
Note: You'll need to use the script provided above for making pivot tables work, and not the example script provided in that linked guide.
Refreshing your spreadsheet or worksheet
If you need to make changes to your source Google Sheet or Excel spreadsheet whilst setting up your widget, click the Reload spreadsheet button to refresh your spreadsheet or worksheet data with any changes you have made.
Once your spreadsheet widget is live on your dashboard, your spreadsheet or worksheet data will auto-refresh.
Refreshing your spreadsheet functions and formulas
If your Google Sheet or Excel spreadsheet contains functions and/or formulas, these will refresh each time your file is updated.
If you are using Google Sheets and need your functions and/or formulas to update more frequently, or automatically, you'll find how to set up automatic updates on any of our advanced tips and tricks.
Spreadsheet widget refresh rate
All of our Spreadsheets widgets automatically refresh every 15 minutes.
If you make changes to your sheet/worksheet in between those refreshes, the latest changes will not immediately be reflected. Instead, the changes will be picked up in the subsequent widget refresh.
You can also force the values to update by editing the widget and clicking Save.
Next steps
Now that you’re familiar with how to import and prepare a visualization from your spreadsheet, read our dedicated guides to creating the different visualizations available.
- Line Chart
Plot multiple series of values and visualize these trends over a period. - Column Chart
Plot a series of data as vertical columns, with the option for individual labels for each column and a goal field, which can be set to highlight values above a specific target. - Bar Chart
Plot a series of data in a horizontal bar chart, with the option for individual labels for each column and a goal field, which can be set to highlight values above a specific target. - Number Widget
Consists of a single number pulled from a specified cell. Enables you to visually compare two figures with 4 comparison visualizations: - Sparkline
Displays a primary metric with a sparkline (a small line chart) below it. - Percentage
Displays a primary metric with a percentage change value below it that compares the primary metric with a secondary number. - Number
Displays a primary metric with a value change below it that is a calculation of the difference between the the primary metric and a secondary number. - Goal
Displays a primary metric with a goal change below it that is a calculation of the difference between the the primary metric and a secondary number. - Leaderboard
Consists of a ranked list of items, each with a label and an optional value. - Geck-O-Meter
A gauge visualization to quickly see a metric in comparison to defined minimum and maximum values. - Text
Displays non-numerical data from a cell, or multiple cells, in a spreadsheet.
