Last weekend, I got myself in a rabbit hole trying to figure out how I might use Dynamic Routes to query a Google sheet as a database to branch call flow. I spent a bit of my Sunday and got a working solution, but in the end it’s not all that practical. I shall share here on the off chance that someone can adapt it to something useful.
The original goal came from a recent sales request that involved branching call flow based on inbound CID area code (NANP numbers only) to the call center/store that was geographically closest to the caller. This type of request comes up from time to time, and I always think that Dynamic Routes could be the solution, but to do so directly means creating a dynroute with over 300 destinations, one for each valid area code. I think this would be technically possible, but a PITA to create and maintain. This customer indicated they have a spreadsheet with a full list of area codes and a matching destination. In a recent Open Source Lounge, @cosmicwombat demonstrated some integrations that used https://sheet.best/ and it’s been on my list of things to try since the recent addition of the JSON_DECODE function in Asterisk. This is what I ended up with.
Step 1 - Create google sheet as data source
I started by creating a google sheet, only two columns with simple column headings. Col A heading is “code” and is a list of all area codes and column B is the corresponding location name where calls will be routed. Heading names and location names are all arbitrary. The first few rows looks like this:
|code|store|
|200|store1|
|201|store2|
|202|store3|
|203|store4|
|204|store5|
|205|store1|
|206|store2|
|207|store3|
|208|store4|
|209|store5|
|920|store3|
Step 2 - Link to https://sheet.best/
Sign into sheet.best using the same google account that created the google sheet. There is a free tier you can use for testing. Create a new connection and link it to the previously created google sheet. There are a few steps to this and I didn’t document my process, so just follow an internet tutorial for this part.
Step 3 - Determine the Connection URL
Once the connection has been created, pull up the details to get the connection URL. Mine takes this form:
https://sheet.best/api/sheets/<somerandomcharacters>
Step 4 - Add some dialplan to extensions_custom.conf
[get-location-from-ac]
exten => s,1,Noop(Entering user defined context get-location-from-ac in extensions_custom.conf)
exten => s,n,Set(areacode=${CALLERID(number):-10:3}) ; get ac from CID
exten => s,n,Set(data=${CURL(https://sheet.best/api/sheets/<somerandomcharacters>/code/${areacode})}) ; query spreadsheet
exten => s,n,Set(data=${STRREPLACE(data,[)}) ; data retruned is in JSON but neesd to be cleaned up for JSON_DECODE
exten => s,n,Set(data=${STRREPLACE(data,])})
exten => s,n,set(dest=${JSON_DECODE(data,store)}) ; the value in col B corresponding to the area code is now stored in channel var ${dest}
exten => s,n,Return
Step 5 - Create a Custom Destination
In FreePBX, Admin → Custom Destinations, create a new dest with a target of:
get-location-from-ac,s,1
Enable “Return” and select the next destination for the call flow.
Once the call passes thru the custom destination, you have a channel variable ${dest} that can be used in Dynamic Routes to branch a call. Here’s a snippet from the full log:
-- Executing [s@get-location-from-ac:1] NoOp("PJSIP/fpbx-2-1NFCJ9KBReX5-0000004d", "Entering user defined context get-location-from-ac in extensions_custom.conf") in new stack
-- Executing [s@get-location-from-ac:2] Set("PJSIP/fpbx-2-1NFCJ9KBReX5-0000004d", "areacode=920") in new stack
-- Executing [s@get-location-from-ac:3] Set("PJSIP/fpbx-2-1NFCJ9KBReX5-0000004d", "data=[{"code":"920","store":"store3"}]") in new stack
-- Executing [s@get-location-from-ac:4] Set("PJSIP/fpbx-2-1NFCJ9KBReX5-0000004d", "data={"code":"920","store":"store3"}]") in new stack
-- Executing [s@get-location-from-ac:5] Set("PJSIP/fpbx-2-1NFCJ9KBReX5-0000004d", "data={"code":"920","store":"store3"}") in new stack
-- Executing [s@get-location-from-ac:6] Set("PJSIP/fpbx-2-1NFCJ9KBReX5-0000004d", "dest=store3") in new stack
-- Executing [s@get-location-from-ac:7] Return("PJSIP/fpbx-2-1NFCJ9KBReX5-0000004d", "") in new stack
-- Executing [dest-7@customdests:3] NoOp("PJSIP/fpbx-2-1NFCJ9KBReX5-0000004d", "Returned from Custom Destination Area Code Destination") in new stack