The requirement of this project was:
We need a custom script to look up a spreadsheet that has a list of people in it, then filter those people by an area code that is provided. Once it finds a group of people in that area code it needs to pick one in a round robin style and return all of the details in the sheet about that person and send that info via webhook so we can use it in zapier.
The information was in this format:
I needed to create a script URL readable from zapier (webhook) that receives a zip code
As you can see at the end, you have the zip value, that can change in zapier.
The script runs with the next process
- Filter all the agents from the first sheet (Agents) based on the zip passed on the parameter of the URL.
- The script checks the EnquiryLog a sheet with the same name, to filter the first list to avoid any agent from that list with the same zip code.
- Then randomly select an agent from the filtered list and returns it to the response in JSON format.
- The agent is added to the EnquiryLog sheet.
- If the zip code already sent all the agents for that sheet code a cycle number is added to restart the cycle and track the cycle number.
You can see an example spreadsheet with agents here:
Example Sheet Round Robin Agents
Agents Agency,Name,Phone,Email,Zip Ray White Broadbeach,Matthew Carney,(09) 4846 1332,email@example.com,4414,4124,5211,4111,2215 Ray White Broadbeach,Perry Gallegos,(06) 6598 1005,firstname.lastname@example.org,4414,4124,5211,4111,2215 Ray White Broadbeach,Justine M...
You can check the source code here: