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.
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.
Widgets powered by spreadsheets using these functions update refresh approx. every 15 minutes.
Fetches data from a given URL in .csv (comma-separated value) or .tsv (tab-separated value) file format.
Imports an RSS or ATOM XML feed.
IMPORTFEED(url, query, headers, num_items)
Imports data from a table or list within an .html page.
IMPORTHTML(url, query, index)
Imports a range of cells from a specified spreadsheet.
Imports data from any of various structured data types including .xml, .html, .csv, .tsv, and RSS and ATOM XML feeds.
Fetches current or historical market data from Google Finance.
GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])
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.
Refresh your data daily with Supermetrics paid service
Supermetrics enables you to schedule an automatic daily refresh of your data on Google Sheets.
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.
Import data from a spreadsheet
Import data an existing workbook, then specify the relationships between your existing data and the new data.
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.
Import text files with Excel
There are two ways to import data from a text (
.csv) file with Excel: you can open it in Excel, or you can import it as an external data range.
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:
- Select the entire row or column which the values are located.
- Go to Format
- Select Number
- Select More Formats
- Choose a data format from the list of options. We recommend choosing:
You might also find these formulas handy for formatting your date.
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.
Allows you combine the contents of two or more cells into a third separate cell.
CONCATENATE(string1, [string2, ...])
- The initial string.
- [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.
- 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.
- The text to divide.
- The character or characters to use to split
- [Optional –
TRUEby default] Whether or not to divide
textaround each character contained in
- [Optional -
TRUEby default] Whether or not to remove empty text messages from the split results. The default behavior is to treat consecutive delimiters as one (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])
- The text within which to search and replace.
- The string to search for within
- The string that will replace
- [Optional] The instance of
text_to_searchto replace with
Convert row-arranged data into column-arranged and vice versa.
- 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.
- The value to be truncated.
- [Optional -
0by default] The number of significant digits to the right of the decimal point to retain.
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.
- 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.
Count the number of cells that meet a certain criteria.
- The range that is tested against criterion.
- The pattern or test to apply to range.
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.
- The input text.
- The first part of text that matches this expression will be returned.
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.
TODAY() and MINUS(value1, value2)
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:
Enables you to protect against errors in your spreadsheet formulas.
In the event of your formulas returning common errors such as
IFERROR will provide a fallback and return a value you specify.
We recommend either leaving the cells empty, or, for numerical values, leaving a 0.
- The value to return if value itself is not an 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
- The string representing the date