A live manager view of WhatsApp in Google Sheets: volume, replies and gaps
Build a Google Sheets view of WhatsApp that managers can open any time: messages in and out today, chats waiting, and slow replies, with formulas.
- Written by
- By WavesFlo team
- Published
- Published
- Reading time
- 3 min read
A manager's live view is a second tab that reads the message log and answers four questions without asking anyone: how many messages came in today, how many went out, which chats are still waiting for a reply, and how long first replies are taking. The log is filled by a workflow; the view is formulas, so it updates the moment a row arrives and nobody has to chase an agent for it.
| 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 |
What goes on the Today tab?
| Cell | Shows | Formula |
|---|---|---|
| B1 | Start of today | =TODAY() |
| B2 | Messages in today | =COUNTIFS(Messages!D:D,"in",Messages!B:B,">="&B1) |
| B3 | Messages out today | =COUNTIFS(Messages!D:D,"out",Messages!B:B,">="&B1) |
| B4 | Chats active today | =COUNTA(UNIQUE(FILTER(Messages!C:C,Messages!B:B>=B1))) |
| B5 | Out to in ratio | =IFERROR(B3/B2,"") |
B4 returns an error until the first row of the day arrives, so wrap it as IFERROR(…, 0). Keep the sheet's timezone (File, Settings) the same as the one the workflow converts to, or the formulas will compare times that are hours apart.
How do you see the busy hours?
Add a column G on the Messages tab, Hour, with =ARRAYFORMULA(IF(B2:B="","",HOUR(B2:B))). Then a table of inbound messages by hour:
=QUERY(Messages!A:G,"select G, count(A) where D = 'in' and G is not null group by G order by G label G 'Hour', count(A) 'Messages in'",1)Put a column chart beside it. The hours where inbound is high and outbound is low are the hours that need more people.
How do you list chats still waiting?
That takes a Chats tab with the last inbound and last outbound time per chat; the unanswered chats guide builds it. Reference its count of waiting chats from the Today tab so one screen carries all four answers.
What will the view not tell you?
- Which person replied. The log records that a reply left a number, not who wrote it; per-agent figures are in WavesFlo's reports, which respect roles.
- Whether a chat ended well. Stages, win and loss reasons and ticket results are in the reports, not in the message feed.
- Anything before the workflow started. Old chats do not appear.
The columns are filled by a workflow, not by hand: WhatsApp to Google Sheets builds it in n8n, with a Power Automate version, and the webhooks guide covers signature checks, retries and duplicates.
The sheet gives a manager the day at a glance, while WavesFlo's reports carry what it cannot, such as who replied and how each chat ended, under the same roles as the inbox. WavesFlo for large businesses describes that view across every store, branch and team.
Questions
Can a manager see WhatsApp activity in Google Sheets live?
Yes, if a workflow writes a row for every message event. Formulas on a second tab then count messages, chats and waits as rows arrive.
Do the formulas recalculate on their own?
Yes. Google Sheets recalculates formulas when a row is added, so the view is as current as the last event received.
Can a sheet show each agent's workload?
Not from the message events alone, which do not name the person. WavesFlo's reports show load per agent, within each person's role.