1

I'm a recovering engineer who is trying to remember how to do things like this in Googlesheet query language and I've completed just about everything I need. There is one more query to do and I'm stuck.

I know how to split up timestamps, pivot on date info from it, how to find contains results and count occurrences but I'm stumped on this one. How do you group by a substring? I've tried Left, regex extract, and about anything else I can think of but no luck.

I have a google spreadsheet with 5 pages, each with 4 columns. Each page is created from a form that a user is entering data into. I've added a date filter in B1 and B2 on my results sheet that works fine, too.

Here is an example of a query I'd like to see work.

=query({User1!A1:F; User2!A1:F; User3!A1:F; User4!A1:F; User5!A1:F}, "SELECT Col4, COUNT(Col4) Where Col1>= datetime '"&TEXT(B1,"yyyy-mm-dd HH:mm:ss")&"' AND Col1 <= datetime '"&TEXT(B2+1,"yyyy-mm-dd HH:mm:ss")&"' AND (Col4 is not null AND Col4 = 'Submits') group by **Left(Col2,4) AND** Col4 pivot month(Col1)+1, Day(Col1), Year(Col1)",1)

That bold bit seems to be the problem area. The rest of this works.

Here are the contents of the fields I'm working within each of the pages:

  • Col 1 - Timestamp
  • Col 2 - Opportunity # String that I want to use the left 4 chars as a group by - strings look like O347-183XXXX so I want to use just O347 in this case
  • Col 3 - Opportunity Name (not needed in result)
  • Col 4 - Strings that I want to count the occurrence of one item (7 different strings total) so I want the result to be a count the occurrence of 'submits' for example.

I want to output a table with a group by Col2 Substring on the left column, Col4 count values for each substring, and pivot by date.

It ought to look like this when I get the results

I've seen some things here and other places that lead me partway there but I just hope there is an easy way to do this.

Dhia Djobbi
  • 1,176
  • 2
  • 15
  • 35
  • Well a simple brute force way to do it might be to add an ARRAYFORMULA column to your form's response sheet, that calculates the sub-string for you, and you just use that column in your query, in place of the full string values. But I found a similar question on SO - searched for "query substring tag[google-sheets]" that seems to have a more elegant answer. Does this help? https://stackoverflow.com/questions/57364636/how-to-use-a-sql-query-in-google-sheets-to-group-by-a-substring – kirkg13 Jul 25 '20 at 00:25
  • Thanks for the response. I did do the brute force with ARRAYFORMULA and it works fine. I also looked at the link you quoted and had seen it before. I'm not the guy maintaining this long term and I felt that might be over the head of the guy who is so opted for more obvious solution. I appreciate you taking time to respond. –  Jul 26 '20 at 15:13

0 Answers0