List WhatsApp chats waiting for a reply in Google Sheets
A Google Sheets list of WhatsApp chats whose last message is from the customer, with how long they have waited, so managers see gaps without asking.
- Written by
- By WavesFlo team
- Published
- Published
- Reading time
- 3 min read
A chat is waiting when the customer's last message is newer than your last reply. Two MAXIFS formulas per chat find both times, and a filter lists the chats where the customer's is later. Sorted by how long they have waited, that list is the first thing a manager should open each morning.
| Column | Holds | Comes from |
|---|---|---|
| A Event ID | A value that identifies the event | The event's identifier, which stays the same on every retry |
| B Time | A real date-time, not text | The event's own timestamp, converted to your timezone |
| C Chat | One value per conversation | The chat id the event carries; for a direct chat, the customer |
| D Direction | in or out | in for message.received, out for message.sent, set in the workflow |
| E Number | Which of your numbers | The id of the connected number the message arrived on or left from |
| F Type | text, image, voice note and so on | The message type the event carries |
How do you build the list?
- On a tab called Chats, put =UNIQUE(Messages!C2:C) in A2.
- In B2 (last message in): =IF(A2="","",MAXIFS(Messages!$B:$B,Messages!$C:$C,A2,Messages!$D:$D,"in"))
- In C2 (last message out): =IF(A2="","",MAXIFS(Messages!$B:$B,Messages!$C:$C,A2,Messages!$D:$D,"out"))
- In D2 (waiting, minutes): =IF(A2="","",IF(B2>C2,ROUND((NOW()-B2)*1440),""))
- On a Waiting tab: =SORT(FILTER(Chats!A2:D,Chats!D2:D<>""),4,FALSE) puts the longest waits first.
MAXIFS returns 0 when nothing matches, which is what you want here: a chat with no reply has a last-out of 0, so any inbound time is later. NOW() recalculates on its own roughly every minute while the sheet is open.
How should a manager read it?
| Waiting | Likely meaning | Action |
|---|---|---|
| Under your target | Normal flow | Nothing |
| Over your target, in working hours | A chat is being missed | Ask the owner, or move it to someone who is on shift |
| Overnight | Nobody was covering | Check the shift pattern for that number |
| Days | A closed chat that the customer reopened, or an abandoned lead | Reply, or close it on purpose |
Pick your own target and write it at the top of the sheet. The number is yours, not a promise from WavesFlo or anyone else.
What does this list not know?
- A thank-you from the customer makes the chat look waiting. A column for a person to mark Done keeps it honest, and that is a small step to ask of anyone.
- A chat answered on the phone counts as answered, because the sent message is logged.
- Who owns the chat. Ownership lives in WavesFlo: the shared queue and its backlog show chats nobody has taken, and the Assigned to you filter shows each person's list.
If the answer to a waiting chat is routing, not reminders, read chat routing with queues, shifts and caps.
Inside WavesFlo the same waiting chats are visible without a sheet: every number's conversations sit in one inbox, filtered by unread or by the person each is assigned to.
Questions
How do I find unanswered WhatsApp chats?
Compare each chat's last incoming message time with its last outgoing one. If the incoming time is later, the chat is waiting.
Will a customer's thank-you count as waiting?
Yes, unless someone marks the chat done. A sheet cannot tell a question from a thank-you.
Does this need the phone to stay connected?
The log depends on events arriving from the connected number. If a number drops off, WavesFlo sends an account event, which you can log and alert on.