Archive every finished LiveChat conversation in Google Sheets

Build me a deterministic code workflow that archives every finished LiveChat conversation into Google Sheets, so we keep a searchable record that outlives LiveChat's own reporting retention. Trigger it with a webhook on LiveChat's chat_deactivated event, which fires whenever a chat is closed.

When the webhook fires, take the chat id from the payload and call LiveChat Get Chat to fetch the finished conversation, including its thread, the events in it, the users on it, and the thread properties. Everything the archive row needs comes straight out of that response, so there is no summarizing or judgement anywhere in this workflow. If Get Chat does not come back with the thread I need, fall back to LiveChat List Threads for that chat id and use the most recent closed thread.

Then append exactly one row to the archive tab of my spreadsheet with Google Sheets Append Values, in this column order: chat id, date, time, agent name, group, customer name, customer email, chat duration, tags, satisfaction rating, transcript. Date and time come from the first event in the thread, rendered in my local timezone as separate columns. Chat duration is the elapsed time between the first and last event in the thread, formatted as minutes and seconds. Agent name and group come from the agent user on the chat and the chat's group assignment. Customer name and email come from the customer user on the chat. Tags are the thread tags joined by commas, left empty when there are none. The satisfaction rating comes from the chat's rating property, written as good, bad, or not rated.

The transcript column holds the whole conversation as plain text. Walk the thread events in order, keep only the message events, and write each one on its own line prefixed by who said it, for example "Agent Maria: Hi, how can I help?" then "Customer: My order never arrived." Use the author's display name where LiveChat provides one and fall back to their role when it does not. No HTML and no JSON, just readable lines separated by newlines so the cell stays searchable with a normal spreadsheet find.

Always append, never update and never overwrite. Append Values adds rows after the last row of the detected table, which is exactly the behaviour I want here: the archive keeps growing and no history is ever lost. Write the header row once if the sheet is empty, then only ever add rows underneath it.

Add one simple check at the end. If the satisfaction rating is bad, also post into my support leads channel with Slack Send a Message, carrying the customer name and email, the agent who handled the chat, the chat id, the duration and the full transcript, so somebody can follow up the same day. If the rating is good or the chat was never rated, skip the Slack step and finish after the spreadsheet row is written.

Make the spreadsheet id, the tab name and the Slack channel configurable inputs. If a field is missing from the chat payload, write an empty cell rather than failing the run, and never let a failed Slack post stop the archive row from being written.