Google Sheets tutorial: How to use QUERY and COUNT to get number of messages in a Gmail conversation (thread)

When you set up backup, save, or export email messages into Google Sheets you might want to improve your Google spreadsheet so it also has a number of messages in a Gmail conversation.

This can be easily done using a very powerful Google Sheets QUERY formula.

To do that just add the following query as the last column of the spreadsheet:

=QUERY(A2:B, 
       "select A, count(B) where B != '' group by A 
        label A 'Thread Id', count(B) 'Number of emails messages in a thread'", 0)

Explanation

The first parameter of the formula is A2:B. This will select all cells from A and B columns starting with the second row (the first rows is a header). The thread Ids are in column A while message ids are in column B.

The second parameter is the actual query:

  1. The clause group by A tells the query to group everything by value of A.
  2. The select count(B) tells the query to display number of messages which have the same thread Id
  3. The label part of the query tells the query which text to display in headers

Example

The example Google Sheet which the above is here

Tagged: