Missed Call Chaser: text back missed calls and log each job in a sheet

1. 1Choose this guide when you want every missed caller to get your own text as soon as the call ends, with every other text to them waiting for your approval. Start the Twilio paperwork first, because it takes time: upgrade to a paid Twilio account, buy a US local number with Voice and SMS, and register it for A2P 10DLC. That means a brand (Sole Proprietor if you have no EIN, otherwise Low-Volume Standard) and one campaign, with the number in the sender pool of the campaign's Messaging Service. Twilio blocks texts from an unregistered local number to US phones with error 30034.
2. 2In the campaign form, describe the flow as it is: someone calls your business number, nobody answers, and they get one text from you; you answer their replies yourself. Give your fixed text as the sample message, and say people opt out by replying STOP and get help by replying HELP. Twilio says campaign review takes up to 5 business days for standard use cases, so build the workflow while you wait. Prefer a toll-free number? Toll-free numbers need Twilio's toll-free verification instead, which Twilio says takes about 3 to 5 business days; unverified toll-free numbers can't text US phones.
3. 3Write your fixed text once and keep it the same. Start with your business name, say what to reply, end with Reply STOP to opt out, and leave out prices and offers. For example: Hi, this is Alder Plumbing. Sorry we missed your call. Reply with what you need done and your address, and we will get back to you. Reply STOP to opt out. Up to 160 plain characters fit in one billed segment. Put the same text in Your fixed text under Customize below.
4. 4In Google Sheets create a spreadsheet named Missed calls with a tab named Log and this header row: Time, Caller, Event, Job type, Urgency, Address, Preferred time, Missing details, Their text, Suggested reply, Decision, Record ID.
5. 5In n8n create the credentials: Twilio with Auth Type API Key (your Account SID, plus the API Key SID and Secret from Twilio's API keys & tokens page); Google Sheets and Gmail (on n8n Cloud click Sign in with Google; self-hosted n8n needs its own Google Cloud OAuth client); Anthropic (your API key, or Use Gateway credits on n8n Cloud Starter or Pro); and a Basic Auth credential with a user name and a long password of letters and numbers only, which Twilio sends with every webhook request. Self-hosted n8n must be reachable over public HTTPS; behind a reverse proxy, set N8N_WEBHOOK_URL so n8n shows the right Production URLs.
6. 6Create a workflow named Missed calls and in Workflow settings set Timezone to yours, for example America/Chicago. Add a Webhook node named Missed call: HTTP Method POST, Path missed-call, Authentication Basic Auth with your Basic Auth credential, Respond Immediately. Under Options add Response Data <Response/> and a Response Header named Content-Type with the value text/xml. Copy its Production URL.
7. 7After Missed call add a Code node named Check call, Mode Run Once for All Items, Language JavaScript, and paste the code below. Change the first two lines to your Account SID and to your Twilio number and mobile in +1 format. It continues only when the call to your phone ended unanswered, busy, failed or cancelled, and the caller has a US number that is not yours: const ACCOUNT_SID = 'ACxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; const MY_NUMBERS = ['+13125550100', '+13125550199']; const b = $input.first().json.body || {}; const caller = String(b.From || ''); const missed = ['busy', 'no-answer', 'failed', 'canceled'].includes(b.CallStatus); if (b.AccountSid !== ACCOUNT_SID || !missed || !/^\+1\d{10}$/.test(caller) || MY_NUMBERS.includes(caller)) return []; return [{ json: { caller, record_id: b.CallSid, time: $now.toISO() } }];
8. 8After Check call add a Google Sheets node named Caller history: Resource Sheet Within Document, Operation Get Row(s), your Missed calls spreadsheet and Log tab, and a filter where Caller equals {{ $json.caller }}. Leave Return only First Matching Row off so every earlier row comes back, and in the node's Settings turn on Always Output Data. After it add a Code node named Text or skip, Mode Run Once for All Items, and paste the code below. It skips numbers marked Do not text and anyone who got your fixed text in the last 24 hours: const call = $('Check call').first().json; const rows = $input.all().map((i) => i.json).filter((r) => r.Caller); const day = 24 * 60 * 60 * 1000; if (rows.some((r) => r.Event === 'Do not text')) return []; if (rows.some((r) => r.Event === 'Missed call, texted' && Date.now() - new Date(r.Time) < day)) return []; return [{ json: call }];
9. 9After Text or skip add a Twilio node named Fixed text: Resource SMS, Operation Send, From your Twilio number in +1 format, To {{ $json.caller }}, To Whatsapp off, and Message your fixed text typed out in full, the same as Your fixed text below. Never put an expression or model output in this Message. After it add a Google Sheets node named Log missed call: Operation Append Row, the Log tab, Mapping Column Mode Map Each Column Manually, Time {{ $('Text or skip').item.json.time }}, Caller {{ $('Text or skip').item.json.caller }}, Event Missed call, texted, Record ID {{ $('Text or skip').item.json.record_id }}. Under Options set Cell Format to Let n8n format so phone numbers keep their + sign.
10. 10Add a second Webhook node named Text reply with the same settings and Path text-reply, and copy its Production URL. After it add a Code node named Check text, Mode Run Once for All Items, and paste the code below. Put your urgent words in the second line, the same as Urgent words below. Twilio answers START and HELP itself, so the code skips them; STOP and its other opt-out words go on to mark the number Do not text: const ACCOUNT_SID = 'ACxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; const URGENT_WORDS = ['leak', 'burst', 'flood', 'no heat', 'no hot water', 'gas smell', 'smoke', 'sparking']; const STOP_WORDS = ['STOP', 'STOPALL', 'UNSUBSCRIBE', 'CANCEL', 'END', 'QUIT', 'REVOKE', 'OPTOUT']; const SKIP_WORDS = ['START', 'UNSTOP', 'HELP', 'INFO']; const b = $input.first().json.body || {}; const text = String(b.Body || '').trim(); const word = text.toUpperCase().replace(/[^A-Z]/g, ''); if (b.AccountSid !== ACCOUNT_SID || !b.From || SKIP_WORDS.includes(word)) return []; const lower = text.toLowerCase(); return [{ json: { caller: b.From, record_id: b.MessageSid, time: $now.toISO(), text: text.slice(0, 1000), photos: Number(b.NumMedia || 0), opted_out: STOP_WORDS.includes(word), urgent_words_found: URGENT_WORDS.filter((w) => lower.includes(w)), } }];
11. 11After Check text add a Google Sheets node named Reply history with the same settings as Caller history: Get Row(s), Caller equals {{ $json.caller }}, Return only First Matching Row off, Always Output Data on. After it add a Code node named Packet, Mode Run Once for All Items, and paste the code below. It skips a number that sent 10 texts in 24 hours and tells the model about earlier texts and Do not text: const t = $('Check text').first().json; const rows = $input.all().map((i) => i.json).filter((r) => r.Caller); const day = 24 * 60 * 60 * 1000; const replies = rows.filter((r) => r.Event === 'Reply'); if (replies.filter((r) => Date.now() - new Date(r.Time) < day).length >= 10) return []; const missed = rows.filter((r) => r.Event === 'Missed call, texted').map((r) => r.Time).sort().pop() || null; return [{ json: { now: t.time, time_zone: $now.zoneName, caller: t.caller, missed_call_at: missed, earlier_texts: replies.slice(-3).map((r) => r['Their text']), text: t.text, photos: t.photos, urgent_words_found: t.urgent_words_found, do_not_text: t.opted_out || rows.some((r) => r.Event === 'Do not text'), } }];
12. 12After Packet add a Basic LLM Chain named Sort reply. Set Prompt to Define below and Prompt (User Message) to {{ JSON.stringify($json) }}. Connect an Anthropic Chat Model with your Anthropic credential or Gateway credits and an available Claude model, and set Maximum Number of Tokens to 1000. Turn on Require Specific Output Format and connect a Structured Output Parser with Schema Type Define using JSON Schema and the schema below. In Sort reply's Settings set On Error to Continue (using error output), so a model error still logs the text and sends any urgent alert: { "type": "object", "properties": { "job_type": { "type": "string" }, "urgency": { "type": "string", "enum": [ "emergency", "urgent", "normal", "not_a_job" ] }, "address": { "type": "string" }, "preferred_time": { "type": "string" }, "missing_details": { "type": "array", "items": { "type": "string" } }, "summary": { "type": "string" }, "suggested_reply": { "type": "string" }, "opted_out": { "type": "boolean" } }, "required": [ "job_type", "urgency", "address", "preferred_time", "missing_details", "summary", "suggested_reply", "opted_out" ] }
13. 13Add a Code node named Checked reply, Mode Run Once for All Items, connect both outputs of Sort reply to it, and paste the code below. It drops the suggested reply for opt-outs and non-jobs, holds it back when it is too long, mentions a price or reads like a booking, and decides whether to alert you: const t = $('Check text').first().json; const p = $('Packet').first().json; const out = $input.first().json.output; const r = out || {}; const optOut = t.opted_out || r.opted_out === true; const held = []; let reply = optOut || p.do_not_text || r.urgency === 'not_a_job' ? '' : String(r.suggested_reply || '').trim(); if (reply.length > 320) held.push('over 320 characters'); if (/\$\s?\d|\d\s?(dollars|bucks)\b/i.test(reply)) held.push('mentions a price'); if (/\b(booked|confirmed|guaranteed?)\b/i.test(reply)) held.push('reads like a booking or a promise'); if (held.length) reply = ''; const words = t.urgent_words_found; return [{ json: { record_id: t.record_id, time: p.now, caller: p.caller, event: optOut ? 'Do not text' : 'Reply', job_type: r.job_type || '', urgency: r.urgency || (words.length ? 'urgent' : ''), address: r.address || '', preferred_time: r.preferred_time || '', missing: (r.missing_details || []).join('; '), their_text: p.text, summary: r.summary || '', reply, alert: !optOut && (words.length > 0 || ['urgent', 'emergency'].includes(r.urgency)), alert_reason: words.length ? words.join(', ') : r.urgency || '', decision: !out ? 'Model error: read their text' : reply ? 'Waiting for you' : held.length ? 'Held back: ' + held.join(', ') : 'No reply suggested', } }];
14. 14After Checked reply add a Google Sheets node named Log reply: Operation Append Row, the Log tab, Map Each Column Manually, Cell Format Let n8n format. Map Time, Caller, Event, Job type, Urgency, Address, Preferred time, Missing details, Their text, Suggested reply, Decision and Record ID to time, caller, event, job_type, urgency, address, preferred_time, missing, their_text, reply, decision and record_id from Checked reply, for example {{ $('Checked reply').item.json.job_type }}.
15. 15After Log reply add an If node named Urgent? with the condition {{ $('Checked reply').item.json.alert }} (Boolean) is true. On its true output add a Twilio node named Urgent alert: SMS Send from your Twilio number to your own mobile, Message Urgent text from {{ $('Checked reply').item.json.caller }} ({{ $('Checked reply').item.json.alert_reason }}): {{ $('Checked reply').item.json.their_text.slice(0, 300) }}. This alert goes only to you.
16. 16Also connect Log reply to an If node named Reply to approve? with the condition {{ $('Checked reply').item.json.reply }} (String) is not empty. On its true output add a Gmail node named Approve reply: Resource Message, Operation Send and Wait for Response, To your own email address, Subject Approve a text to {{ $('Checked reply').item.json.caller }} and a Message with summary, their_text, urgency, address, preferred_time, missing and reply from Checked reply. Set Response Type Approval, Type of Approval Approve and Disapprove, Approve Button Label Send this text, Disapprove Button Label Don't send, and under Options Limit Wait Time 24 hours.
17. 17After Approve reply add an If node named Approved? with the condition {{ $json.data.approved }} (Boolean) is true. On its true output add a Twilio node named Approved text: SMS Send from your Twilio number, To {{ $('Checked reply').item.json.caller }}, Message {{ $('Checked reply').item.json.reply }}, then a Google Sheets node named Log sent: Operation Update Row, the Log tab, Column to match on Record ID, with Record ID from Checked reply and Decision Sent after your approval. On its false output add the same Sheets node named Log not sent, with Decision Not sent. If you don't answer within 24 hours, nothing is sent.
18. 18Leave Retry On Fail off on Fixed text, Urgent alert, Approved text and Sort reply so a failed run never texts twice. Executions store callers' numbers and texts, so set execution data retention to suit you.
19. 19In the Twilio Console, under TwiML Bins, create a bin named Ring my phone with the TwiML below, using your Basic Auth user name and password, the host and path of the Missed call Production URL, and your mobile number. timeout 15 rings your phone for about 20 seconds, because Twilio adds five. Open your Twilio number: under Voice set A call comes in to TwiML Bin and choose Ring my phone; under Messaging set A message comes in to Webhook with https://USER:PASSWORD@ in front of the Text reply Production URL's host, HTTP POST. Keep the Messaging Service's incoming setting on Defer to sender's webhook, its default. <Response> <Dial timeout="15"> <Number statusCallbackEvent="completed" statusCallback="https://USER:PASSWORD@your-n8n-host/webhook/missed-call">+13125550199</Number> </Dial> </Response>

20. 20Set Your fixed text and Urgent words below to match the Fixed text and Check text nodes, then in Sort reply, under Chat Messages, add a System message and paste the complete setup prompt below.
21. 21Save the workflow. Don't publish it until the sample check below looks right.

3. Check the first run· 4 steps, a fictional sample to try first, and review checksShow