All Collections
Calculate spreadsheet data before connecting to Geckoboard
Calculate spreadsheet data before connecting to Geckoboard

Using Google Sheets' query functions you can make all your spreadsheet calculations and aggregations before connecting to Geckoboard.

Updated over a week ago

Geckoboard’s Google Sheets’ integration is designed for displaying data, rather than calculating it. This means all calculations and aggregations in your spreadsheet need to be completed outside of Geckoboard.

# Calculate totals by date units

In our experience, everyday spreadsheets tend to include – among other things – three columns containing dates, numbers and strings. Strings can represent anything from a sold item category to the salesperson responsible for selling it.

## Today

This query sums the numbers in column B, where a date in column A equals today's date. It then generates a heading of 'Total Today'. The query is wrapped in an `IFERROR` function to provide a fallback when no dates match today's date.

`=IFERROR(QUERY(A2:C,"select sum(B) where A = date '"&text(TODAY(),"yyyy-mm-dd")&"' LABEL sum (B) 'Total Today'"),"Total Today")`

## Today grouped by string

This query groups today's sales and uses a pivot clause to output the column titles.

`=IFERROR(QUERY(A1:C,"select C, sum(B) where A = date '"&text(TODAY(),"yyyy-mm-dd")&"' group by C pivot A label C 'Total On'"),"Total On")`

## Visualize today's sales by teammate

Use a leaderboard to track today's total sales by teammate.

## Yesterday

This query sums the numbers in column B where a date in column A equals today's date minus 1 day. It then generates a heading of 'Total Yesterday'. The query is wrapped in an `IFERROR` function to provide a fallback when no dates match yesterday's date.

`=IFERROR(QUERY(A2:C,"select sum(B) where A = date '"&text(TODAY()-1,"yyyy-mm-dd")&"' LABEL sum (B) 'Total Yesterday'"),"Total Yesterday")`

### Visualize today's and yesterday's sales

Use the comparison visualizations to compare today's and yesterday sales and work towards a daily target.

## Past 7 days

This query sums the numbers in column B where a date in column A is less than or equal to today's date and greater than today's date minus 7 days. It then generates a heading of 'Total Past 7 Days'. The query is wrapped in an `IFERROR` function to provide a fallback when no dates match.

`=IFERROR(QUERY(A2:C,"select sum(B) where A <= date '"&text(TODAY(),"yyyy-mm-dd")&"' and A > date '"&text(TODAY()-7,"yyyy-mm-dd")&"' LABEL sum (B) 'Total Past 7 Days'"),"Total Past 7 Days")`

## Previous 7 days

This query sums the numbers in column B where a date in column A is less than or equal to today's date 7 days ago and greater than today's date minus 14 days. It then generates a heading of 'Total Previous 7 days'. The query is wrapped in an `IFERROR` function to provide a fallback when no dates match.

`=IFERROR(QUERY(A2:C,"select sum(B) where A <= date '"&text(TODAY()-7,"yyyy-mm-dd")&"' and A > date '"&text(TODAY()-14,"yyyy-mm-dd")&"' LABEL sum (B) 'Total Previous 7 Days'"),"Total Previous 7 Days")`

## Visualize sales from past 7 days

Use the comparison visualizations to compare sales from the past 7 days with the previous 7 days, and work towards a target for this period.

## So far this week

This query sums the numbers in column B where a date in column A matches today's date and the week it falls in. It then generates a heading of 'Total This Week'. The query is wrapped in an `IFERROR` function to provide a fallback when no dates match.

`=IFERROR(QUERY(A2:C,"select sum(B) where A > date '"&text(floor(today() - 1, 7),"yyyy-mm-dd")&"' LABEL sum (B) 'Total This Week'"),"Total This Week")`

## So far last week

This query sums the numbers in column B where a date in column A matches today's date and the previous week it falls in. It then generates a heading of 'Total So Far Last Week'. The query is wrapped in an `IFERROR` function to provide a fallback when no dates match.

`=IFERROR(QUERY(A2:C,"select sum(B) where A > date '"&text(floor(today() - 8, 15),"yyyy-mm-dd")&"' LABEL sum (B) 'Total So Far Last Week'"),"Total So Far Last Week")`

## Visualize sales for the week so far

Use the comparison visualizations to compare sales for the week so far with the previous week, and work towards a target for this period.

## This month

This query sums the numbers in column B where a date in column A matches today's date in its month and year. It then generates a heading of 'Total This Month'. The query is wrapped in an `IFERROR` function to provide a fallback when no dates match.

`=IFERROR(query(A1:C,"select SUM(B) where month(A)=month(date'"&text(today(),"YYYY-MM-DD")&"') and Year(A)=Year(date'"&text(today(),"YYYY-MM-DD")&"') label SUM(B) 'Total This Month'"),"Total This Month")`

## Last month

This query sums the numbers in column B where a date in column A matches today's date in its month minus 1 month, and also matches the year. It then generates a heading of 'This month'. The query is wrapped in an `IFERROR` function to provide a fallback when no dates match.

`=IFERROR(QUERY(A1:C,"select SUM(B) where month(A)=month(date'"&text(today(),"YYYY-MM-DD")&"')-1 and Year(A)=Year(date'"&text(today(),"YYYY-MM-DD")&"') label SUM(B) 'Total Last Month'"),"Total Last Month")`

## Visualize sales for the month so far

Use the comparison visualizations to work towards a target for this month.

## Months grouped by string

This query uses an array formula to output the sum numbers in column B grouped by the month number.

`=IFERROR(QUERY( ARRAYFORMULA({TEXT(A:A,"01-MM-YYYY"),B:B}), "select Col1,sum(Col2) where Col2 is not null group by Col1 " ),"Date")`

## Visualize sales totals by month

Use a column chart to track the total sales for each month.