Prepare your spreadsheet for Geckoboard

Geckoboard supports many types of spreadsheet file formats, from Google Sheets to Excel and OpenOffice Calc.

Step 1 – Get data into your spreadsheet

If you haven't already, you need to get your data into a spreadsheet from wherever it’s located.

When it comes to importing data from other sources and reshaping it to fit your needs, Google Sheets and Excel are particularly useful for creating a new sheet specifically to power your dashboard widgets.

I'm using Google SheetsI'm using Excel

Importing with Google Sheets

Google Sheets has built-in powerful import functions that can be particularly useful when working with large and complex spreadsheets, as you'll usually only require a smaller sample of numbers for your dashboard.

In addition, there are a number of helpful services, add-ons, and third-party tools that specialize in getting data into Google Sheets.

Google Sheets import functions

Google Sheets offers built-in import functions that enable it to pull data from a variety of sources into your spreadsheet. Using these functions, you can easily scrape data from web pages, feeds, and files.

ImportData

Fetches data from a given URL in .csv (comma-separated value) or .tsv (tab-separated value) file format.

Import text files with Excel

Third-party add-ons for Excel

Report Builder for Adobe Analytics paid service

Report Builder is an Excel plugin that enables you to import your Adobe Analytics data into Excel.

Step 2 - Format your spreadsheet

Making sure that your data has the correct format for the type of data it holds, as well as laying it out in a clear and consistent manner, will help with preparing your spreadsheet for connecting to Geckoboard.

Check 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 spreadsheets contain cells with date-time values, Geckoboard should automatically recognize them. However, there could be some locale date formats which Geckoboard may not identify, which can cause trouble when building your widgets. For example, Germany's date format dd.mm.yyyy isn't supported.

SPLIT

[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 behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty cells values are added between consecutive delimiters.

Step 3 – Perform calculations in your spreadsheet

Geckoboard is designed for displaying your data and, for this reason, doesn’t support any sort of modification or calculation of it. This means any changes to your data must be done outside of Geckoboard first.

Here are some useful functions that can help with making calculations in large datasets:

ARRAYFORMULA Google Sheets only

Perform multiple actions on one or more datasets. Useful when you're using a lot multiple formulas on a large dataset.

Syntax:ARRAYFORMULA(array_formula)

array_formula

A range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell.