Log Twilio SIP Call to Google Sheet and send to SIP Trunk FreePBX

Question

We configure our phone numbers in Twilio to handle Voice Calls with a SIP Trunk that goes to our FreePBX server in our office.

My goal is to log all calls to a Google Sheet when they are completed. I have tried to set this up two ways:

1. Configure the Twilio number with: Webhook. Point the incoming call to a Google Apps Script webhook URL, the script logs the call, then return TwiML message to forward the call to my cell phone. This works to log the call to the Google Sheet, but then we cannot use our office FreePBX phone system to take the call since it is configured with Webhook rather than SIP Trunk (and forwarded to my cell rather than handled by the FreePBX server).

Here is that Google Apps Script code:

function doGet(args) {
  var spreadsheetID = 'xxxxxxxxxxxxxxxxxxxx';


  // Create a date object for the current date and time.
  var now = new Date();
  now = Utilities.formatDate(now, 'America/Chicago', 'yyyy-MM-dd\'T\'HH:mm:ss');

  //Incoming parameters from Twilio.  Documentation here: https://www.twilio.com/docs/voice/twiml
  var callStatus = args.parameter.CallStatus;
  var callerName = args.parameter.CallerName;
  var fromNumber = args.parameter.From;
  var toNumber = args.parameter.To;
  var callDirection = args.parameter.Direction;

  SpreadsheetApp.openById(spreadsheetID).appendRow([now, callStatus, callerName, fromNumber, toNumber, callDirection]);


  var text = ContentService.createTextOutput("<Response><Dial>+14055991234</Dial></Response>").setMimeType(ContentService.MimeType.XML);
  return text;

}

Again, this logs the call, but does not subsequently handle it with a Twilio SIP Trunk.

2. A Zapier trigger event for Twilio "New Call". This provides me a Webhook URL and the following instructions:

"Set up your Callback URL! Log into Twilio and visit your incoming numbers page, edit the number you want to trigger on, and paste the below URL into Call Status Changes. Click save!"

In order to place the Webhook URL into the Call Status Changes box in Twilio, the number must be configured in Twilio with Webhooks, but we are configuring with SIP Trunk. So I don't know how to use a SIP Trunk with our Twilio numbers and also use a Webhook for the Call Status Changes so I can log the calls to a Google Sheet.

Any guidance is appreciated.

Answer 1

Twilio Phone Numbers that are setup with Elastic SIP Trunking do not also trigger webhooks.

One way around this could be to turn on Voice Insights for your SIP trunk and use Event Streams to trigger a webhook. There are various different events available but I believe the gateway events (which are triggered by call progress events) would be the right ones to listen for and react on.

Alternatively, you could look to add something into your FreePBX installation that could trigger the update to the Google Sheet once your call is finished.