Can I perform calculations on my spreadsheet data in Geckoboard?

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.

Calculation functions

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.

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.

Error handling functions

Reduce the errors that can occur when you try to do a calculation that 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

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.

Tip

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.

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

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

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

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

    Kirby

    USA
  • Heather Customer Success Avatar

    Heather

    USA