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.

Function	Description	Help walkthrough
ImportRange `IMPORTRANGE(spreadsheet_key, range_string)`	Imports a range of cells from a specified spreadsheet.	Use ImportRange to extract data needed for Geckoboard to a new spreadsheet
ImportData `IMPORTDATA(url)`	Fetches data from a given URL in .csv (comma-separated value) or .tsv (tab-separated value) file format.	Use ImportData to display online data in Geckoboard

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 (.txt or .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 MM/DD/YYYY or DD/MM/YYYY.

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.

Function	Description	Syntax
CONCATENATE	Allows you to combine the contents of two or more cells into a third separate cell.	`CONCATENATE(string1, [string2, ...])` `string1`: The initial string. `string2`: [Optional] Additional strings to append in sequence.
PROPER	Edits text within a cell to title case so you don’t have to format each entry manually.	`PROPER(text_to_be_capitalized)` `text_to_be_capitalized`: The text which will be returned with the first letter of each word in uppercase and all other letters in lowercase.
SPLIT	Allows you to split data from a single cell into multiple cells.	`SPLIT(text, delimiter)` `text`: The text to divide. `delimiter`: The character or characters to use to split `text`. `split_by_each`: [Optional – `TRUE` by default] Whether or not to divide `text` around each character contained in `delimiter`. `remove_empty_text`: [Optional - `TRUE` by default] Whether or not to remove empty text messages from the split results. The default behaviour is to treat consecutive delimiters as one (if `TRUE`). If `FALSE`, empty cells values are added between consecutive delimiters.
SUBSTITUTE	Replaces existing text with new text in a string.	`SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])` `text_to_search`: The text within which to search and replace. `search_for`: The string to search for within `text_to_search`. `replace_with`: The string that will replace `search_for`. `occurrence_number`: [Optional] The instance of `search_for` within `text_to_search` to replace with `replace_with`.
TRANSPOSE	Convert row-arranged data into column-arranged and vice versa.	`TRANSPOSE(array_or_range)` `array_or_range`: The array or range whose rows and columns will be swapped.
TRUNC	Truncates a number to an integer by removing the fractional part of the number.	`TRUNC(value, [places])` `value`: The value to be truncated. `places`: [Optional - `0` by default] The number of significant digits to the right of the decimal point to retain.

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:

Function	Description	Syntax
IFERROR	Enables you to protect against errors in your spreadsheet formulas. In the event of your formulas returning common errors such as `#NAME?`, `#VALUE!`, `#DIV/0!`, and `#REF!`, `IFERROR` will provide a fallback and return a value you specify. Tip: We recommend either leaving the cells empty, or, for numerical values, leaving a 0.	`IFERROR(value, [value_if_error])` `value`: The value to return if value itself is not an error. `value_if_error`: [Optional – blank by default] The value the function returns if value is an error.
DATEVALUE	Converts a provided date string in a known format to a date value. `DATEVALUE` accepts different input string format.	`DATEVALUE(date_string)` `date_string`: The string representing the date

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.

Import data into your spreadsheet before connecting to Geckoboard

Use ImportData to display online data in Geckoboard

Display data from pivot tables in Geckoboard

Spreadsheet import error: 'This worksheet is too large to import'

Calculate spreadsheet data before connecting to Geckoboard