DevBlocks Agency
DevBlocks Agency
Google Sheets Round Robin Agents readable from Zapier
🗳

Google Sheets Round Robin Agents readable from Zapier

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:

image

I needed to create a script URL readable from zapier (webhook) that receives a zip code

https://script.google.com/macros/s/ZZZZZ/exec?zip=0800

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:

You can check the source code here: