Auto-send WhatsApp message to New Google Sheet Entries
You can combine Botcake and Google Sheet to automatically send a template to the customer's WhatsApp number. Here's how:
Last updated
You can combine Botcake and Google Sheet to automatically send a template to the customer's WhatsApp number. Here's how:
Last updated
This is a very complex integration which involves a lot of steps. Please contact Pancake team if you need help.
Please double check the following conditions to make sure your account is ready for setup:
On your Google Sheet account, create a spreadsheet that you will later use to save your customer's information. You can create as many columns as you need to save the data.
In this example, the customer's information will contain 4 fields: Name, Phone number, Email and Registration date.
The spreadsheet MUST contain a Phone number column to determine the customer's WhatsApp number.
The phone number MUST contain the country code as WhatsApp can send message worldwide.
Example:
Vietnam: 84963848416 - country code 84
India: 919369081825 - country code 91
Indonesia: 6287861820636 - country code 62
Colombia: 573156912137 - country code 57
On Botcake, create custom fields to synchronize the data from Google Sheet.
If you have 4 data columns in Google Sheet, you must create 4 custom fields accordingly:
First go to Botcake's setting section and click on Integrations -> Click the connect button for Google API integration.
You will be redirected to Google Login page. Please login to the correct account (which is containing the Sheet you want to integrate) and provide all the neccessary permissions.
Once done, the interface should look like this. Continue to map the data by clicking the "Connect" Button on the Google Sheet Connect Webhooks.
Choose the correct spreadsheet that you want to get the information form. The Work sheets is the exact name of the sheet where the information is. The look up column is
After clicking OK, you will get a webhook link like this. We will use this on the second step.
In order to trigger events to send data back to Botcake, you will need to use 2 add-on extensions on the Spreadsheet: AppSheet and AppSheet Events.
On the spreadsheet, click on Extensions -> Add-ons -> Get add-ons
Search for AppSheet and AppSheet Events - both provided by Google and install them.
Turn on the event for the Sheet.
Next, click on Extensions -> AppSheet -> Create an app.
Click on automation and Create a bot.
Give the bot a name.
Click on Configure event.
Select the event of "A Customer Data record is created or updated"
Make sure the Data change type is correct. You there are 2 options you can choose:
Adds: The message will be sent everytime a new data is added to the spreadsheet.
Updates: The message will be sent everytime a data is updated.
If you choose both option, the message will be sent for both cases.
Next, click on "Add a step"
Give this step a name: Call webhook
Remember the URL created on step 1? It's time to use it!
On the setting section, select "Call a webhook" and paste the webhook URL in the URL box.
After that, please don't forget to save the app.
Next, click on the Data section and check the KEY and REQUIRE? box for Phone Number field. Make sure Phone number is the ONLY KEY in the list.
Since the data are already mapped within the app you created:
You can not change the columns' name and criterias. Any changes in the columns will result in disconnection of the data, and you will need to recreate the app again.
If you add other columns to the sheet, it will not affect the app's operation, but the data will not be synchronized. If you want to synchronize it, recreate the app.
The most important column is Phone number. If you change the name of this column, ALL DATA WILL NOT BE SYNCHRONIZED.
Lastly, go to the Deploy section and click on Move app to Deployed state.
From now on, when you have a lead on the Google Sheet, it will automatically be synchronized to Botcake as "Google Sheet leads". The information will be saved according to the user custom fields that you have set up in section 1.
Go back to Botcake and create a new flow. This flow will contain the template that you want to send to the client.
For the variables in the template, please select the correct Custom Fields that you have created in Section 1.2. The information synchronized from Google Sheet are stored here.
After selecting the fields, it should look like this.
Click Ok and Save the Flow.
Next, go to the "Rules" section in Automation, and Create a new Rule.
Set the trigger to "New Subscriber"
If you only want to send the message to the leads from Google Sheet, please add the "Source" condition and filter for GOOGLE_SHEET.
After choosing the filter by source, the Botcake view should look like this. Next, click on Action
Select the "Start a Flow" option.
Select the flow you prepared.
Double check the condition and the action, and save.
Go back to the Rules section, and Turn on the rule you just created.
After everything is done, simply insert a new data to the Google Sheet, and watch the magic happen!
