How to connect Whatsapp with google sheets?

Table Of Contents

• Background
• Problem
• Solution
• Why?
• Limitations
• Conclusion

Background

Few years ago I made a simple solution to my problem which was involving WhatsApp. you can read about it from this post

How I Created Whatsapp bot to talk with my friends?

Arpit Vasani ・ Aug 8 '19

#whatsapp #bot #webautomation #puppeteer

Since making this people have been using this to solve their various personal or business problem. Also, People have been asking for several features one of which being making this messages dynamic as per their google sheet.

So, in this article, I will share how you can connect your WhatsApp account with google sheet and reply to your customers/contact as per the information in the sheet for free. We will not be using any subscription or paid service for this. This guide has been made keeping technical(developers) and non-technical people in mind.

Problem

Using bot.json, Wbot provides keyword-based rules such as if an incoming message has certain keywords, it will respond by an equivalent message written using that rule. There are image-based rules as well.

The problem is, in a business case you might want to serve your customer with unique messages. Something like following

As you can see, this generic message won’t help because the value $240 in this message is dynamic (something which changes customer to customer). We can't set this from bot.json because it applies to everyone, not just one customer.

Solution

Wbot provides a feature called webhook. It can be used in this use case. What is a webhook? It is a simple network request which can be used as an event. What it does is every time WBOT gets a message it will call an URL which we set in webhook property in bot.json file. So, for every message, we will be notified. In this URL, we can write our custom code in any language, and it doesn't have to be code-based. It can be any URL that you have control of what is incoming.

Google has this service called Google app script which lets you code on top of google sheet or any other google product per se. So, We can make our own webhook.

Here is what solution looks like

Steps

1. Create a blank google sheet or open an existing one
2. For this example, we will keep it simple as following column C is something that we will send to the user. You can use any formula you like in there. In the end, whatever text is in this cell will be sent to the user.

3. Next, we are going to create a web app using this data. So go to extensions > AppScript. You should see something like following

   

   I have updated the name of the project for organization and simplicity. It might be an untitled project in your case. You can update it by clicking on it.

4. Select all and remove those two lines of code and put the following in
   

   const SHEET = 'YOUR-SHEET-ID'
   
   function doPost(e) {
   
     //fetching values which we are getting from wbot
     let { text, type, user } = JSON.parse(e.postData.contents)
   
     let customerMobile = user.replace('@c.us', '')
   
     // setting up response with default value
     let response = [{
       "text": '😢 No order found!',
       "type": "message"
     }]
   
     //getting the reference of the sheer
     const sheet = SpreadsheetApp.openById(SHEET).getActiveSheet()
   
     //getting current data from sheet
     const data = sheet.getDataRange().getValues();
   
     //find phone number in sheet
     for (let i = 0; i < data.length; i++) {
       // Logger.log(data[i][0], customerMobile)
       if (data[i][0] == customerMobile) { //[0] because column A
         Logger.log("Match")
         response[0].text = data[i][2]
       }
     }
   
     // Return result of operation
     return ContentService
       .createTextOutput(JSON.stringify(response))
       .setMimeType(ContentService.MimeType.JSON);
   
   }
   
   

   There is this small thing which you need to change in 1st line of code where it says YOUR-SHEET-ID take the URL of the sheet and take the following part from it without /
   
   put/paste this between quotes ' in code.

5. It is time for deployment. Click on Deploy > New Deployment. In Select Type > choose Web app. Setup things as follows
   
   click deploy. You should see something like following
   
   Click on Authorize access. It will open a popup as following.
   
   This will give our code permission to read the sheet whenever needed. Wait for a few seconds while google deploys our code. Once done you should see something like the following.
   
   Copy the URL. We will use it as webhook in bot.json.

6. Open bot.json from wbot and create a new block in bot Array (section) with the following
   

   {
           "contains": ["bill", "order"],
           "exact": [],
           "webhook": "https://script.google.com/macros/s/AKfycby8cwEumo.../exec",
           "response": "okay. Checking",
           "afterSeconds": 3
   
       },
   

   do make sure that the JSON is valid. You can use https://jsonlint.com/ to check that.

7. That's it, Start the wbot and test your changes.

   

Why?

You must be wondering why this solution instead of some third-party integration?

The answer is Freedom.

There are a lot of strings attached when we use third-party tools. As a small business ideally, you should be able to solve your business problem without worrying about subscription, compliance, compatibility, data protection, etc. hassles.

Limitations

Google has some limits on how many times you can call their services during the day. This may vary for account type and region. Do keep an eye on executions from this page https://script.google.com/home/executions

Conclusion

Most of the problems for small businesses can be solved using minimum code or with low code tools without paying huge monthly subscription charges.

Comments

[Peach185]

Hi, this seems amazing so I have tried implementing it by following the tutorial. However I have an issue with calling the webhook on certain keywords instead of having it defined at "appconfig" level. Webhook is working fine on "appconfig" level as I can see execution records from Google App Scripts, but no records of webhook called while I try to call it from certain keywords ("bot" section). Any idea why does this not work?

[Muhd-Naufal]

You're a wizard! I followed your tutorial but once I tried asking about my bill, it responded back with "okay. Checking" and stops there. Any idea what went wrong?

[Arpit Vasani]

Make sure the google sheet's API works. You can test it by sending a request from postman. If that works correctly then this may be an issue with WBOT. Try it out.

[Muhd-Naufal]

Just as you suggested, it's my google sheet's API that doesn't work. I enabled it through my Google Cloud Account but yet it doesn't receive the request from postman.

ibb.co/fxDxP2Q

On the preview screen it also says "Script function not found: doGet". Idk if it's helpful for you.

[allpreegroup]

Same here, it stop at "okay. Checking", i think it's the code in google app script

this line
let { text, type, user } = JSON.parse(e.postData.contents)

somthing to do with e.postData.contents