Querying a Google sheet with Asterisk dialplan

FreePBX Tips and Tricks
1

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
2

I would go with a full on NPA-NXX and there are API’s out that will give you LAT and LON for them but because of ‘number portability’ that probably means squat , where you to have a zip-code then using least squares , I use

between the two. You have the zip-code of the the store, gleaning the current zip-code of the caller is a simple dialplan QA thingy

3

Agree, the practicality of this specific application is weak, but the concept of doing a live query of a spreadsheet could be useful. You have a clean Gui to create/edit the data, easy to share between multiple users with granular permissions etc. Things like one time usage PINS, on-call rotations etc.

You’re spoiled by geography. My postal code has non-numeric chars.

4

But surely the postal-code has an identifiable lat/lon It works for me from my crib at HA4-6JN

based on

there are lots of things like

I’m sure you can find locale specific versions though

edit:

I would accept that HA4-6JN is not DTMF FRIENDLY , here is where I would go to a STT solution (I use VOSK Offline Speech Recognition API )

5

Hi @lgaetz,

You can fetch the data from your json response with the bash and python on your shell:

exten => s,n,Set(data=${SHELL(curl -s 'https://sheet.best/api/sheets/<somerandomcharacters>' | tr -d '[]' | python -c "import sys, json\; print json.load(sys.stdin)['store']" | tr -d '\n')})    ; query spreadsheet

Thank you,

Daniel Friedman
Trixton LTD.

6

if your python is python3 then the print statement needs parantheses.

If you care to install jq then perhaps cleaner and quicker would be as a SHELL argument.

curl -s https://<whatever>  | jq  -jr '.store'
7

Question: If my number is out Detroit (Southern MI) but I now live in Traverse City (Northern MI). They are roughly 6+ hours apart.

How does this know I am in Traverse City looking to connect to a store in Traverse City?

8

My suggested solution might resolve your pain, there are 46 zip-codes for Michigan, 4 for Traverse City that pgeocode can resolve. (Especially if you have more than one location in Detroit where 48219 is 15 miles from 48215)

9

I am not sure. I grew up in the Detroit area, the surrounding cities where right on top of each other. I mean in one spot Lincoln Park and Allen Park are divided by a street and a literal park. Half way through the park you leave one city and entire another.

On top of that, yes, Detroit has multiple postal codes. However, in Canada, when I lived in Windsor which had 10% the population and size of Detroit there where DOZENS of postal codes. My neighbor on the other side of the street had a different postal code and so did other surrounding houses.

Where I live now has multiple postal codes and two of the same store is in opposite sides of the city. Two different postal codes. None of which would be the postal code I would call from.

In the grand scheme, asking for a postal code will be great as long as the person knows the postal code they are in is great. People wo are travelling, even locally, calling and being asked to enter a postal code is going to be problematic as people will need to get it first.

With the fact I can look up Best Buy on my phone and I am given 5 options of the closest ones with their address and individual store number why do I need a postal code to enter to reach the store I want?

Gonna be honest if I call Biz A and I have to restart the call because I didnt know a current postal code I am in and had to look it up. If I called Biz B instead and I got Bob answering the phone, I am going to Biz B because it was less hassle.

10

That’s up to you. PBX 20th century version

'If you know your zip code, please dial it now, dial anykey to go to our head office ’

21st century version with STT so no buttons to press or websites to consult or Alexas to query.

‘Please tell us what city and state or zip code you calling from’

This works even if you are Canadian where K1A as quicker and more precise than ‘Ottowa Ontario’

But thats not really the gist of my argument , either way the shortest Haversine distance between caller and Service provider’s various locations is easy to compute and provides value added service.

Ever having to dial through an IVR would likely have me calling another ‘Service Provider/Restaurant’ also.

(As little as possible button pushing for me.)

11

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.