Calculate spreadsheet data before connecting to Geckoboard | Geckoboard Help Center
Calculate spreadsheet data before connecting to Geckoboard
Learn how to use the QUERY function in Google Sheets to complete calculations or aggregations of your based on certain time periods.
April 14, 2025
The Spreadsheets data source is designed to display data rather than calculate it. This means any calculations and aggregations in your spreadsheet need to be completed before you bring the data into Geckoboard to visualize it.
In this guide we'll cover a number of ways you can use the Query function to make calculations or aggregations of your data within a Google Sheet. To learn more about the Query function in Google Sheets, we recommend this guide.
We'll use data from this example spreadsheet, which contains ready-made calculations. Make a copy of the sheet, then use ImportRange to add your own sales data, or feel free to follow along with your own sheet.
Use ImportRange to add your own sales data
Use ImportRange to extract sales data for calculating in new sheet
Calculate totals by date units
In our experience, spreadsheets typically include three common columns: dates, numbers, and strings. Strings can represent anything from a sold item category to the salesperson responsible for selling it.
In our example spreadsheet, we've built Query functions to generate totals for several date units, which we'll explain below and provide an example query for. Here are the quick links to each section:
- Today
- Today grouped by string
- Yesterday
- Past 7 days
- Previous 7 days
- So far this week
- So far last week
- This month
- Last month
- Months grouped by string
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")
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")
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")
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 'Total Last 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")
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")