This article will help you prepare your spreadsheet before connecting to Geckoboard using our Spreadsheets data source. Geckoboard supports many types of spreadsheet file formats, from Google Sheets to Excel and OpenOffice Calc.
How Geckoboard connects to your spreadsheet
Your dashboard widgets are tied directly to specific cells, so when data in those cells change, your widgets will update. This means that if you move columns or rows around, whatever ends up in the original location is what will be displayed.
If you only need a subset of the data in your spreadsheet for your metrics, or if you want to run calculations on your metrics but don't want to edit the source spreadsheet, we recommend creating a new sheet and fetching just the required data for Geckoboard from the main sheet.
1. Replicate data and copy it from one sheet to another
As long as the new sheet is linked to the raw data, it will stay updated with any changes you make. This means you can keep your current formatting and continue using your raw data how you always have.
Importing sheet data using Google Sheets
Google Sheets offers built-in import functions that enable it to pull data from a variety of sources into your spreadsheet. ImportRange and ImportData are particularly useful for fetching the data you need from a source spreadsheet or .csv file.
Imports a range of cells from a specified spreadsheet.
Fetches data from a given URL in .csv (comma-separated value) or .tsv (tab-separated value) file format.
Note: Widgets powered by spreadsheets using these functions update refresh approx. every 15 minutes. If this isn't often enough for your needs, you can set your own refresh interval by writing a script.
Importing sheet data using Excel
In Excel, you can import data from an existing workbook and then specify the relationships between your existing data and the new data. Learn how to import data from an existing Excel spreadsheet.
When it comes to importing data from a text (
.csv) file with Excel, you can open it in Excel, or you can import it as an external data range. Learn how to import text files with Excel.
2. Format your Geckoboard-specific sheet
Making sure your imported data uses the correct format for the type of data it holds, as well as laying it out in a clear and consistent manner, helps prepare your sheet for connecting to Geckoboard.
Checking your number formats
Particularly when formatting currencies and percentage values, make sure that these are defined and consistent throughout.
Set a date-time format
If your sheet contains cells with date-time values, Geckoboard should automatically recognize them. However, there could be some locale date formats Geckoboard may not identify (for example, Germany's date format
DD.MM.YYYY isn't supported), which can cause trouble when building your widgets. We recommend either
Tip: You might also find these formulas handy for formatting your date.
Functions for formatting your sheet
Here are some useful functions that can help with formatting your imported data, particularly those that contain data from different sources.
Allows you to combine the contents of two or more cells into a third separate cell.
Edits text within a cell to title case so you don’t have to format each entry manually.
Allows you to split data from a single cell into multiple cells.
Replaces existing text with new text in a string.
Convert row-arranged data into column-arranged and vice versa.
Truncates a number to an integer by removing the fractional part of the number.
3. Reduce potential errors in your Geckoboard-specific sheet
Here are some functions that can help reduce the potential errors that can occur when you perform calculations your spreadsheet can’t handle:
Enables you to protect against errors in your spreadsheet formulas.
In the event of your formulas returning common errors such as
Tip: We recommend either leaving the cells empty, or, for numerical values, leaving a 0.
Converts a provided date string in a known format to a date value.
4. Connect your Geckoboard-specific sheet
With your sheet optimized for Geckoboard, you're all set to turn your data into an easy-to-understand dashboard.