0

screenshot example

Admittedly I have very limited experience and would appreciate any help that can be given. I created a google sheet to collect forms responses. I need another tab to share daily data. I created a query to pull in the data I need, but lack the ability to filter for "today."

My current query: =QUERY('DoNotUse HISTORICAL DATA'!A:E)

My date format is: 1/15/2022 20:08:01

Nothing I have found will work without any errors. Any ideas?

Thank you.

Link to copy: https://docs.google.com/spreadsheets/d/116o_QQDWGvYkHLX2o5OdRX4X8CdhRVTQNJXYikVre94/edit?usp=sharing

Todd Schuler
  • 3
  • 3
  • It would be much easier to help if you could provide a link to the sheet (or to a copy of it). You seem to have other things going on in the sheet that may be important to consider. For instance, you seem to have placed report data in the same sheet as your form-intake data; and that is not a good idea for many reasons. – Erik Tyler Jan 16 '22 at 02:19
  • I created a copy after adding a filter from the response below. Is there a way to do it without having to manually enter a "today" date for the filter. Again, appreciate all the help from everyone. Link: https://docs.google.com/spreadsheets/d/116o_QQDWGvYkHLX2o5OdRX4X8CdhRVTQNJXYikVre94/edit?usp=sharing – Todd Schuler Jan 16 '22 at 02:27
  • See my reply post and proposed solution below. – Erik Tyler Jan 16 '22 at 03:25
  • I also recommend seeing how a PivotTable helps monitor activity. I have posted another response. – Mike Steelson Jan 16 '22 at 15:20

3 Answers3

1

Assuming that today or another date is in B1, and the form responses are in sheet called Réponses au formulaire 1

try

=query('Réponses au formulaire 1'!A:E,"select * where A>=DATE'"&TEXT(B1;"yyyy-MM-dd")&"' and A<DATE'"&TEXT(B1+1;"yyyy-MM-dd")&"' ",1)

enter image description here

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Thank you so much for taking the time to help. I linked a picture of the spreadsheet to the original post. Would I just change b1 to a2? – Todd Schuler Jan 16 '22 at 02:03
  • I strongly advise you not to include today in the formula, in order to be able to use it for example at the beginning of the day on the data relating to the previous day. In addition, you would like to know for example on monday what happened last weekend. Another solution could also be to create a pivot table to retrieve the statistics over a long period (day, week, month). – Mike Steelson Jan 16 '22 at 08:22
0

In the "Use - Daily Query" sheet, delete everything from Columns A to E (including the headers). Also delete G1:H2.

Then place the following formula in A1:

=ArrayFormula({'DoNotUse HISTORICAL DATA'!A1:E1;FILTER('DoNotUse HISTORICAL DATA'!A2:E,INT('DoNotUse HISTORICAL DATA'!A2:A)=TODAY())})

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
0

You can probably completely change the way you look at the situation

First, compile the data

={"Date","Type","Location";QUERY(ARRAYFORMULA(split(flatten({'DoNotUse HISTORICAL DATA'!A2:A&"|"&'DoNotUse HISTORICAL DATA'!B2:E&"|"&'DoNotUse HISTORICAL DATA'!B1:E1}),"|",,false)),"select * where Col2 is not null")}

Then perform a pivot table and group dates by year/month/day

enter image description here

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20