Google Sheets Query

1
votes

I'm creating a lottery system for some gaming community I'm a member of using Google Forms and Google Sheets.

I currently have the follow sheet that collects the form responses and highlights any that are still pending processing by a "lottery official". As shown, each record has a a maximum of 10 tickets spread across 10 columns. I need to make a sheets that shows the result of a query that tests whether the date is after a "start date", before a "end date" and one of the ticket number columns equals the "ticket number". I want to do this by having 3 input boxes then doing a query search to show results below it, however I'm unsure of how to use Google Sheet's queries. I have looked at the documentation and it's not very clear on how it structures. Hopefully someone more experience with Google Sheets / Excel could help shed some light.

I have this so far, but am completely unsure how to get it to work =QUERY("FormResponses!A2:P1004", "select C where FormResponses!A > date A:2 AND FormResponses!A < date B:2 AND (FormResponses!F = C:2 OR FormResponses!G = C:2 OR FormResponses!H = C:2 OR FormResponses!I = C:2 OR FormResponses!I = C:2 OR FormResponses!J = C:2 OR FormResponses!K = C:2 OR FormResponses!L = C:2 OR FormResponses!M = C:2 OR FormResponses!N = C:2 OR FormResponses!0 = C:2)")

Further more, I'm not sure if it is checking the cells from the right sheet and how to correct that within the query.

https://i.gyazo.com/1ed95b1fe87bc9104a2dcc5a15b4b0c0.png enter image description here

1
google-sheetsgoogle-query-language

1 Answers

2
votes

This should fix it. On the FormResponse sheet format column A to Date (not Date Time). Then use:

=query(FormResponses!A:P,"select C where A>= date '"&text(A2,"yyyy-MM-dd")&"' and A<= date '"&text(B2,"yyyy-MM-dd")&"' and (F="& C2 &" OR G="& C2 &"OR H="& C2 &" OR I="& C2 &" OR J="& C2 &"OR K="& C2 &" OR L="& C2 &" OR M="& C2 &" OR N="& C2 &" OR O="& C2 &")",1)