All Collections
Connect your spreadsheets
Prepare your spreadsheet
Prepare your spreadsheet for connecting to Geckoboard
Prepare your spreadsheet for connecting to Geckoboard

Optimze your spreadsheet data before connecting it to Geckoboard by fetching just the data you need for your metrics.

Updated over a week ago

This article will help you prepare your spreadsheet before connecting to Geckoboard using our Spreadsheets data source. Geckoboard supports many spreadsheet file formats, from Google Sheets to Excel and OpenOffice Calc.

πŸ™ Grappling with bringing your spreadsheet data to life? Don't hesitate to contact our support team. We can build a dashboard with your Google Sheets for you.

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. If you move columns or rows around, whatever ends up in the original location 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.

Grab your cell data and turn it into visualizations in seconds.

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.



Help walkthrough

​IMPORTRANGE(spreadsheet_key, range_string)

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




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.

Edits text within a cell to title case so you don’t have to format each entry manually.


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.

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.

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.

Convert row-arranged data into column-arranged and vice versa.


array_or_range: The array or range whose rows and columns will be swapped.

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:




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.

Converts a provided date string in a known format to a date value. DATEVALUE accepts different input string format.


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.

Did this answer your question?