How to connect Whatsapp with google sheets?
Tags: whatsapp, googlesheets, integration, howto
Published: 2022-03-05 08:49:39

---
title: How to connect Whatsapp with google sheets?
published: true
description: Whatsapp is a remarkable messaging app but lack of integration can be a bummer for your business. Let's solve that. shall we?
tags: WhatsApp,googlesheets,integration, howto
series: improving Whatsapp
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/96aol51mdbmk1faiq87h.png
 
---

## Table Of Contents
  * [Background](#background)
  * [Problem](#problem)
  * [Solution](#solution)
  * [Why?](#why)
  * [Limitations](#limitations)
  * [Conclusion](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](https://dev.to/arpitvasani/how-i-created-whatsapp-bot-to-talk-with-my-friends-h92)
{% embed https://dev.to/arpitvasani/how-i-created-whatsapp-bot-to-talk-with-my-friends-h92 %} 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 ![whatsapp screenshot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zt7zkj80gkbt1mnardm4.png) 

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
![webhooks to the rescue meme with baby yoda grogu](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2ccc2f1uubphcfun5m8q.jpg)

 
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](https://www.google.com/script/start/) 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
![solution diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h941kkwq7lpyxulur3wi.png)
 

### Steps

1. Create a blank google sheet or open an existing one
2. For this example, we will keep it simple as following 
    ![sample google sheet image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4vrs2udau2nydzod6z1o.png)
    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

    ![google app script screenshot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8zaa9kxzg20asxpt8pim.png)

    *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

    ```javascript 
    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 `/`
    ![screenshot of google sheets url](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/08yem1y8gic83uwas47l.png)
    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
    ![Google app script new deployment page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xp4ao0upsijgc9lfxpni.png)
    click deploy. You should see something like following 
    ![Google app script authorize screen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ry1s38jcsiss66pnehiv.png)
    Click on Authorize access. It will open a popup as following. 
    ![Authorization window](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w6j7olsztxy15qw4lun3.png)
     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.
    ![deployment successful](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bt0b8kbta6hpj9x3vnru.png)
    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
    ```json
    {
            "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.  

    ![man throwing confetti gif](https://i.imgur.com/OnIGlte.gif)
 
 
 
## 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.