Video

Step-by-step Instructions

Part 1: Populate Your Google Sheets Company Data from a Scraper

  • Create a list of vendors, contractors, service providers, etc.
  • Use a scraper tool, such as “Outscraper”, to gather data from Google Maps.
  • Use the tool to add additional data like email addresses, social media handles, and phone numbers.
  • Make a separate tab in your Google Sheets for better data management.
  • Sort the data according to reviews and ratings to highlight top quality companies.
  • Keep the information current by finding a way to continually update ratings and reviews.
  • The plan is to use this data for a TypeBot that assists users in finding a service provider or getting quotes.
  • Check for service availability by determining if there is a service provider available with respect to a specific ZIP code.
  • Fall back to the response “we don’t have service in your area yet” if the ZIP code is not covered.
  • To get started with TypeBot, import all the data you’ve gathered into it using the Google Sheets API.

Part 2: Connecting Google Sheets API to Typebot

  • Connect the Google Sheets API to Typebot.
  • Use Google Cloud Console to create a new project and enable Google Sheets API.
  • Create new credentials, which include a service account and API key.
  • Share the new Google Sheet you created with the service account email you got.
  • Replace placeholders in the URL of the Google Sheet with the spreadsheet ID, sheet name, and your API key.
  • Test your connection by adding dummy data to the spreadsheet and checking if it reflects in Typebot in real time.
  • The connection is now setup and you can proceed to set up logic and conditional checks to extract ZIP code - information and find top service providers.

Part 3: Setting up the Logic and Conditionals for Typebot

  • Implement a logic flow for Typebot using Google Sheets.
  • If an unknown ZIP code is entered, ask the user to add it and send them a notification once it’s added.
  • Give the users an option to choose whether they want to be notified after a ZIP code is added.
  • If a ZIP code with a service provider is provided, ask the user questions regarding the project.
  • Display the top-rated companies based on the ZIP code and let the user select which companies they want to receive a quote from.
  • Use JSON formatting and variable manipulation to verify and access data from the Google Sheets API.
  • Look at different methods for sending emails to the selected service providers.
  • Give an option of showing a list of service providers that’ll be contacted on the user’s behalf before sending an email.
  • Let the user fill in project details, select vendors, and send email requests for quotes.

Overview of the Angi HomeAdvisor project

Creating a list of contractors, vendors, or service providers

Picture this. You need to find a list of contractors or service providers, but it’s a pain to look through each one and collect data manually. In our recent podcast, I shared how I used Outscraper to scrape data from Google Maps. Using this magnificent tool, I was able to gather a list and append handy additional data like email addresses and social media handles. All this saved in Google Sheets, making the data easy to manage!

Filtering and sorting the data

Now we’ve got our data but guess what? It’s not enough just to collect it; it’s gotta be sorted! So what we did next was sorting the data by reviews and ratings. Why, you ask? Well, we all want the best, don’t we? By sorting it this way, we can quickly find our top dogs, the quality companies everyone wants to work with.

Using the data for Typebot

Imagine that once we harness all this data, we can neatly fit it into a Typebot. This bot would help users find these top-tier companies or get quotes from them. Cool, right? We had to check if service was available for specific ZIP codes, though. If not, we simply responded with “Sorry, we don’t have service in your area yet”.

Setting up Google Sheets API using Google Cloud Console

Our buddy Google comes to rescue once again! We went to the Google Cloud Console to set up the Google Sheets API. Created a new project, enabled the API, the whole nine yards, and voila! Now we can import our Google Sheet data into Typebot.

Connecting Google Sheets API to Typebot

Doing this was actually a lot easier than it sounds. All we did was share the Google Sheet with our new API service account and replace placeholders in the URL with the spreadsheet ID, sheet name, and API key. We even checked if the connection was successful by putting some made-up data and seeing if it updates in Typebot.

Setting up logic and conditionals in Typebot

The trick with implementing logic and conditionals in Typebot is to make sure it can look up ZIP code information and find the best vendors or contractors. For our ZIP codes with no service provider, we added those tricky areas to our Google Sheets and let Typebot alert the user about it!

Parsing JSON and converting data types

I know this part sounds technical, but the gist is we used some JSON and variable manipulation to verify data from the Google Sheets API. It’s like asking our software, “Hey, can you make sure this data checks out?”

Sending email notifications

Finally, we’re onto my favorite part: emailing the chosen service providers! Users fill out their project details, select vendors, and Typebot can send email requests for quotes. They even get the option to see the list of service providers that will be contacted on their behalf.