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:

1. Preparation

Please double check the following conditions to make sure your account is ready for setup:

1.1. Google Sheet

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

1.2. Botcake

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:

2. Setting up the platforms:

Step 1: Integrate your account Google Account to Botcake and create a webhook URL

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.

Step 2: Configure the Google Sheet

1. Install AppSheet and AppSheet Events Add-ons

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.

2. Create a webhook bot

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.

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.

Lastly, go to the Deploy section and click on Move app to Deployed state,

Step 3: Create a flow and a rule in Botcake

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.