ActivePieces & Google Sheets for AI chatbot with data access

ActivePieces & Google Sheets for AI chatbot with data access

Your website chatbot can not only answer questions about your business or online store, but it can also perform simple tasks, for example:
  • Add an event in Google Calendar
  • Create a new contact in Hubspot CRM
  • Collect survey answers in an Airtable database
 
Want to learn how to take your chatbot beyond just Q&A? Read along: this guide will take you through the necessary steps towards making your chatbot even cooler!
 
notion image
 

Introduction

In this guide, we will create a chatbot that searches Google Sheet for order status and returns it to the user. If you want to follow along, make sure you have the accounts set up on ActivePieces and Google Sheets.
 
💡
This guide explains a specific example of integrating with one of services (”Pieces”) available on ActivePieces, Google Sheets - but the same steps apply to any integration.

Preparation

Prepare the Google Sheet database

Let’s get our “order database” ready for some searching! Go ahead to your Google Sheets account and create a new sheet.
 
💡
Feel free to skip this step if you already have a sheet you want to use.
 
Add some data for the chatbot to search; you can copy our demo sheet for the purpose of this guide.
notion image
 

Create a Flow in the ActivePieces dashboard

ActivePieces works by connecting two or more apps (”Pieces”): the Trigger app and the Action app. We will use 'Webhook' as the trigger piece, 'Google Sheets' as the first action piece and 'HTTP' as the last piece.
 
  1. Begin by navigating to your ActivePieces dashboard and creating a New Flow.
  1. Select Webhook as the Trigger.
  1. Copy the provided Webhook URL. Append /sync to it (eg. https://activepieces.com/.../sync) because we’ll want the webhook to return data back to our chatbot.
 
💡
Currently ActivePieces returns an error if the webhook is called with /sync while the flow is in draft state. You can remove the appendix for testing and bring it back after testing is completed.
 
notion image
 

Add a Webhook as a new Action for your AI chatbot

The following steps will be performed in the Chatwith dashboard. We will define the values used in this new Action and provide instructions to the chatbot.

Adding Webhook as a new Action

From the Chatwith dashboard, click on the 'Actions' button in the left sidebar.
In the window that appears, click 'Install' on the 'Webhook' Action card.
notion image
 
A popup will open. Here, we will enter the Webhook URL and define the values that our chatbot should collect and send to the webhook. We will also send a first test payload to establish a connection between the services.
When choosing values, consider what the triggered service accepts. For example:
  • Use first_name and email when adding a new newsletter subscriber to Mailchimp.
  • Use phone_number and full_name when creating a Hubspot contact.
  • Use answer1, answer2, and so on when asking survey questions and adding them to Google Sheets.
 
For our chatbot, we want to find orders by name, so let’s define the payload as such:
notion image
 
Click 'Send test payload' and you’ll see the payload show up in ActivePieces. We’re almost there!
 
notion image
 
Before returning to ActivePieces and completing the flow, let's include instructions for the chatbot on when and how to use this new Action:
If the visitor asks for order status, then you will use the provided Webhook Action to search for the order by customer name. If they did not provide their name upfront, you must ask them for the name first. Never attempt searching the database with partial or dummy information.
 
notion image
 
 

Finishing the Flow in ActivePieces

Now that we have established and tested the connection between Chatwith and ActivePieces, we are ready to complete and enable the Flow.
 
Let's return to the ActivePieces dashboard and proceed with the following steps:
  1. Select Google Sheets as the Action app.
  1. Choose 'Find Rows' as the event action.
  1. When prompted, authorize ActivePieces to connect with Google Sheets and access the necessary resources.
  1. Map the value received from Chatwith through the Trigger to the Action. Make sure to choose only the name field in the “Data to Insert” picker.
  1. Add 'HTTP' piece and choose 'Return Response' as action. Choose 'Find Rows' (the outcome of the previous step) as the Response value.
  1. Save, test, and publish the new Flow.
 
Choosing ‘Search Value’ used for querying the Google Sheet. Make sure to select just the name field inside of the body object passed from your Webhook Trigger.
Choosing ‘Search Value’ used for querying the Google Sheet. Make sure to select just the name field inside of the body object passed from your Webhook Trigger.
 
 
Adding the last step of the flow that will return the search result back to our chatbot.
Adding the last step of the flow that will return the search result back to our chatbot.
 

Updating the System Prompt

To ensure that our chatbot is aware of its new role, we need to update the System Prompt. Click this link or follow these steps:
  1. Select 'Settings' in the navigation sidebar.
  1. Locate the 'System Prompt' field.
  1. Update the field with the following instructions:
Your task is to provide order status when asked. If the visitor asks for order status you will use the enabled Action to search the database and return information you found.
Make sure to save the changes once you have finished updating the System Prompt.

Testing

We are ready to see how our upgraded chatbot performs! Head on to the 'Preview' section in the dashboard and try asking the chatbot for order status:
notion image
 
Congratulations! Your chatbot has a new superpower now 🎉
 

Troubleshooting

What if something doesn’t work?
  • Open the ActivePieces dashboard and check if the Flow is published and does not show any warnings. For example, one of your services used in the ActivePieces could become disconnected and require you to log in again.
  • If the chatbot does not return any data (but you expected it to), make sure you added the /sync appendix to the Webhook URL.
  • Put on your Prompt Engineer's hat and make sure the System Prompt and Action Instructions are filled in and clear. Look for signs of the chatbot not being aware of the actions at all or using the actions with incorrect details.
  • If all else fails, try starting over: delete the Flow in ActivePieces, remove the Action in Chatwith, and create the connection from scratch.
  • Reach out to our team at support@chatwith.tools. Our team will be more than happy to assist you with setting up your chatbot.