Google Sheet X Make.com

How To
1

I am trying to automate a VAPI automation.

I am trying to automate a task where a Google Sheet is searched and it sends back the zip codes that are already taken and also sends back the zip codes that are still available. Also, it lists all the zip codes within a 20 mile radius of the unavailable zip codes and saves them somewhere so that when it calls the lead during the appointment, it remembers which zip codes are available, unavailable, and remembers the list of zip codes within a 20 mile radius.

Here is how it works

  • lead is interested and books appointment (triggers the automation)
  • zip codes that lead provided on call are checked and compared to another sheet that basically has all occupied zip codes. If a zip code chosen by prospect is already on that sheet, it is voided because it is unavailable.
  • If all zip codes are available, they get automatically put into Chat GPT which gives the total population. If total population is over 50k, the prospect gets called up for the appointment.
  • If some or all zip codes are unavailable, Chat GPT gives a list of all zip codes within a 20 mile radius of the unavailable zip codes. Then, those zip codes are compared to a google sheet with unavailable zip codes and ONLY the available zip codes within that 20 mile radius get remembered. Then, AI voice agent gives the prospect a call and asks prospect for the amount of zip codes needed compared to how much of the original zip codes were available and how many were taken. The prospect says the zip codes and the AI compares those zip codes to the list of zip codes within a 20 mile radius that are not taken that it saved in its memory earlier.
  • If no zip codes are available within a 20 mile radius after it gets compared to the sheet with taken zip codes, a different AI agent calls the prospect

Thanks in advance :slightly_smiling_face:

2

Hi @Anton_K ,

You can achieve this by iterating the array of zip codes and for each one use Google Sheet : search rows module, using condition ‘Lead zip code’ is equal to ‘Column zip code’. Finally, the results filtered will correspond to ‘Available zip codes’ and you can use a text aggregator to join available zips as a text. You can use this aggregated text afterwards to ask chatgpt whatever you need to ask.

  1. Process of filtering 50k pop condition : do we go through all of the results, or take the first zip-code that satisfies the condition? I’d advise to include population number in your google sheet database directly to optimize the process.