Prepare your spreadsheet for Geckoboard

This article will help you prepare your spreadsheet for connecting it to Geckoboard using our Spreadsheets data source.

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.How to get data from several sources into a single spreadsheet that connects to Geckoboard

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.

Syntax: IMPORTDATA(url)

How to use the IMPORTDATA function to display your data.

ImportFeed

Imports an RSS or ATOM XML feed.

Syntax: IMPORTFEED(url, query, headers, num_items)

ImportHTML

Imports data from a table or list within an .html page.

Syntax: IMPORTHTML(url, query, index)

How to use the IMPORTHTML function to display your data.

ImportRange

Imports a range of cells from a specified spreadsheet.

Syntax: IMPORTRANGE(spreadsheet_key, range_string)

How to use the IMPORTRANGE function to display data in Geckoboard.

ImportXML

Imports data from any of various structured data types including .xml, .html, .csv, .tsv, and RSS and ATOM XML feeds.

Syntax: IMPORTXML(url, xpath_query)

How to use the IMPORTXML function to display your data.

How to use the IMPORTXML function to create a weather widget.

GoogleFinance

Fetches current or historical market data from Google Finance.

Syntax: GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])

How to use the GOOGLEFINANCE function to display your data.

Scripts and third-party add-ons for Google Sheets

Sync data from APIs using JSON

Thanks to third-party developers, you can sync data from other platform's APIs into a Google Sheet.

Import JSON data to a Google Sheet.

Refresh your data daily with Supermetrics paid service

Supermetrics enables you to schedule an automatic daily refresh of your data on Google Sheets.

Automatically refresh your Google Sheets data daily with Supermetrics.

Import your Xero financial data with Blink Reports paid service

If you use Xero for your online accounting, Blink Reports is an add-on that can automatically import your financial data to Google Sheets.

Importing with Excel

In Excel, you can import data from several data sources:

Import data from an Access database

Export data from an Access database in a file format that can be read by Excel.

How to import data to Excel from an Access database.

Import data from a spreadsheet

Import data an existing workbook, then specify the relationships between your existing data and the new data.

How to import data from an existing Excel spreadsheet.

Import data using copy and paste

Import data from a table you've find on a web page, or any other source from which you can copy and paste into Excel. Use press Ctrl + T to format the pasted data as a table.

How to import data using copy and paste.

Import text files with Excel

There are two ways to import 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.

How to 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.

To make sure your dates-time values are formatted accordingly, please follow the steps below for Google Sheets:

  1. Select the entire row or column which the values are located.
  2. Go to Format
  3. Select Number
  4. Select More Formats
  5. Choose a data format from the list of options. We recommend choosing: MM/DD/YYYY OR DD/MM/YYYY.

Functions for formatting your spreadsheet

Here are some useful functions that can help with formatting your dataset, particularly those that contain data from different sources.

CONCATENATE

Allows you combine the contents of two or more cells into a third separate cell.

Syntax: CONCATENATE(string1, [string2, ...])

string1
The initial string.
string2
[Optional] Additional strings to append in sequence.

Further reading

PROPER

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

Syntax: 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.

How to use the PROPER function

SPLIT

Allows you to split data from a single cell into multiple cells.

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

Further reading

SUBSTITUTE

Replaces existing text with new text in a string.

Syntax: 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.

How to use the SUBSTITUTE function

TRANSPOSE

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

Syntax: TRANSPOSE(array_or_range)

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

How to use the TRANSPOSE function

TRUNC

Truncates a number to an integer by removing the fractional part of the number.

Syntax: 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.

How to use the TRUNC function

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.

How to use the ARRAYFORMULA function.

COUNTIF

Count the number of cells that meet a certain criteria.

Syntax: COUNTIF(range, criterion)

range
The range that is tested against criterion.
criterion
The pattern or test to apply to range.

How to use the COUNTIF function.

REGEXEXTRACT Google Sheets only

Extracts matching substrings according to a regular expression. Useful when extracting specific terms from a set of data on your spreadsheet.

Syntax: REGEXEXTRACT(text, regular_expression)

text
The input text.
regular_expression
The first part of text that matches this expression will be returned.

How to use the REGEXEXTRACT function.

TODAY and MINUS

Can be combined to calculate the time difference between a date in the future and today. Use in conjunction with a script to create a countdown widget for your dashboard.

Syntax: TODAY() and MINUS(value1, value2)

How to use the TODAY and MINUS functions to create a countdown timer.

Step 4 – Reduce potential errors in your spreadsheet

Here are some functions that can help reduce the potential errors that can occur when you perform calculations your spreadsheet can’t handle:

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.

Syntax: 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.

Further reading

DATEVALUE

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

Syntax: DATEVALUE(date_string)

date_string
The string representing the date

Further reading

Next step – connect your spreadsheet

With your spreadsheet prepared, you're all set to connect it to Geckoboard.


I'm using Google Sheets

Connect your Google Sheet to Geckoboard


I'm using Excel

Connect your Excel spreadsheet to Geckoboard

Was this article helpful?

Awesome! 👍 Thanks so much for your feedback!

Sorry about that! Start a conversation now.

We're ready to help, 24 hours a day, 5 days a week

Get support
  • Fernanda Customer Success Avatar

    Megan

    USA
  • Hariharan Customer Success Avatar

    Hariharan

    India
  • Fernanda Customer Success Avatar

    Yasmin

    Spain
  • Luis Customer Success Avatar

    Luis

    UK
  • Richard Customer Success Avatar

    Richard

    UK
  • Fernanda Customer Success Avatar

    Fernanda

    Brazil
  • Heather Customer Success Avatar

    Heather

    USA