Create visualizations from your spreadsheets

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.

Set_up_visualization.jpg

To prepare you visualization, follow these steps.

  1. Click in the Set Up Area.
  2. Click to activate each field and select the cells you want to visualize from the Spreadsheet Data.

    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.

    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.

    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.

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

Though Geckoboard isn’t currently able to interpret pivot tables in 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 the script.

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.

Reload_spreadsheet.jpg

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 10 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.
Was this article helpful?
👍

Thank you for your feedback!