***Update 12/01/20*** I was asked if there was a way to do this same type of Query but look for specific text in a cell that may have more than just what you are looking for… So here’s the scenario: A Google Form is being used for reflection after PD. It is a single Form being used across the entire district where there were multiple sessions and a variety of presenters. Instead of combing through the Sheet’s data to find a session you did or using a filter which might affect another viewer of the Sheet; is there a way to pull the data for a specific presenter into its own tab, keeping in mind that sometimes a presenter might be co-facilitating a session with another presenter? I did some digging… here’s what I found: the gist is using the term “matches” and inserting the characters “.*” in front and behind the specific text you’re looking for. It would look like this:
=QUERY(IMPORTRANGE(“Google sheet ID“,”Google tab name!column letter:column letter“),”Select * Where Col# matches ‘.*text.*‘”)
The other day I received just that, a challenge.
Basically, our Kindergarten program is half-day; the teachers have an AM & PM class. They wanted a Form to collect information but depending on a selected answer, they wanted the information to go into one sheet versus another.
At first, I thought this was simple. Input an “IF” function to designate whether the information in a particular row should go to an AM or PM sheet. The problem with this course of action, data which doesn’t meet the “IF” condition would leave a row blank. Example for an AM sheet:
Timestamp | AM or PM? | Name: | Score: | Notes: |
05/26/16 08:13 | AM | Tommy | 21 | Vowel-Consonant-Vowel trouble |
05/27/16 09:12 | AM | Timmy | 14 | Decoding issues |
So I had to search and search and search. I went to a variety of Google Forms forums, YouTube tutorials, and the Google spreadsheets function list. After a little while and no definitive answer, I started to try things out on my own. Eventually, I discovered a solution!
=QUERY(IMPORTRANGE(“Google sheet ID“,”Google tab name!column letter:column letter“),”Select * Where Col# = ‘text‘”)
Below are some resources (Tutorial video, embedded Form, and Sheet)