Using Query statement to filter results based on date(Google Sheets)

1
votes

I have been tasked in creating a Productivity tracker at my workplace. I created a google form and am now trying to filter the responses on a different tab than the FormResponses tab to be specific to a date. I'm having trouble writing in the correct syntax to get the results I need.

Here is a copy of the sheet with dummy data I'm working with https://docs.google.com/spreadsheets/d/1zZrGtunB_9FhNIT7RyUczqc8qYGrAPgU-iwXMf_oznE/edit?usp=sharing

Essentially, I'm trying to query the sum of Column K (How many scooters did you deploy?) from tab FormResponses based on a specific date on to Tab ShiftReportTrackingCalculator. I need more Column sums than that but i wanted to start with just one to get a better understanding of the syntax needed to get the query to work properly.

So far ive tried SELECT K WHERE J = date '"&TEXT(DATEVALUE("6/19/2018"),"yyyy-mm-dd")&"'"

and am getting a parsing error. I haven't figured out how to sum the column yet either.

1
google-sheetsgoogle-query-language

1 Answers

1
votes

The query string you generate in ShiftReportTrackingCalculator needs to be 

="SELECT SUM(K) WHERE J = date '"&TEXT(DATEVALUE("2018-06-19"),"yyyy-mm-dd")&"'"

I changed the following things:

  • Turned it into a formula to preparse the date into the query string
  • Changed the format of the input to yyyy-MM-dd (At least for me it complained about the input for parsing, not sure if that's locale/settings related)
  • Calculated the sum of K for the day