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.
If you still need to work on your data for a metric, don’t worry: calculations can be made when you're preparing your spreadsheet. This article lists some key spreadsheet functions you may find useful.
Functions can be used to create formulas that manipulate data and calculate strings and numbers.
ARRAYFORMULA Google Sheets only
Perform multiple actions on one or more datasets. Useful when you're using a lot multiple formulas on a large data set.
- 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)
Error handling functions
Reduce the errors that can occur when you try to do a calculation that 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
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:
Read more about effective error handling and other useful functions in our blog post 3 Google Sheets functions that will make your life easier.
Data formatting functions
Good formatting and the combining of data from several places will help with preparing your spreadsheet for connecting to Geckoboard.
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.
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.
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.