Forem: Arpit Vasani

How to connect Whatsapp with google sheets?

Arpit Vasani — Sat, 05 Mar 2022 08:49:39 +0000

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

Create a blank google sheet or open an existing one
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.
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.

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.

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.

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.

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.

Can we automate code-review? Yes we can

Arpit Vasani — Mon, 23 Sep 2019 11:33:17 +0000

Index

What is code-review?

After you submit a pull request then there will be someone from the senior development team who will review(or maybe test) your code before merging it to development or master branch.

Code review helps us identify the wrongs before it gets to master code base or get published to a production site. It could be programming-wise or business logic wise. For programming related mistakes there are tools using which one can enforce best practices and for the other, we need to have a person who has an understanding about the flow of the system. The last one can't be automated(at the moment).

Why would you want to automate it?

There are lots of reason to automate your tasks. Like following.

More free time on work 😉
It will give you time to focus on more concerning problems
Higher accuracy than human
It's a Reliable system to prevent the buggy code to reach production
...and much more

It's not like we are completely removing human intervention in the code review process. In fact, Technology will play helping hand in this review process for a senior developer(or a code reviewer).

How can you automate it?

In this tutorial, We will be using sonarqube. Sonarcube is code quality checker which can be hosted on-premise it also has an inbuilt web interface to show the bugs and code sniffs. Sonarqube is an on-premise version which you can download and install on a machine on LAN. on top of that there is a community edition of sonarqube which is free and open source. yay!!

Sonarcloud is the cloud version of the same service and it's free for public repo so we are going to use that.

For the demo purposes, I will be using this repo https://github.com/vasani-arpit/sonarcloud-tutorial which has some intentional mistakes.

Steps

Go to sonarcloud and sign in with Github
after authentication is done, click on ➕ on the top and select analyze new project
Next, Import your GitHub repo using "Choose an organization on Github button"
Click on "Bind to an existing SonarCloud organization" and then "Bind Organization" button
Select/check the repo you want to analyze from the list and click on set up
Now go back to your repo and add a file at root level as following and save it as .sonarcloud.properties
```
# Path to sources
sonar.sources=.
```
push your commit. Sonar cloud will automatically pick up your code and start analyzing the repo.
once the analysis is complete you should see something like following
It is a public project. you can see it on https://sonarcloud.io/dashboard?id=vasani-arpit_sonarcloud-tutorial as well.

It says there is one bug let's see what that is.

as you can see if and else block of the code is the same so sonar suggesting me to remove the condition because there is no point in having it. It also tells how much time it will take to resolve this bug. So, Next time when you have an existing project to refactor then pass it through sonarcloud and tell exact efforts needed for reactor so your PM won't ask you every day for the update. 😜

Now, This is something my IDE/compiler won't catch because it only checks for the syntax.

Conclusion

by Partially automating the code-review task you can have better results which human eye can miss. If you like pair-programming then sonarqube/sonarcloud can be your pair-reviewer. 😀

On a serious note, If you are in domain/organization in which you can't put your code on the cloud then you can setup sonarqube (instead of sonarcloud) on your local machine and automate your PR reviews.

How I Created Whatsapp bot to talk with my friends?

Arpit Vasani — Thu, 08 Aug 2019 08:58:46 +0000

Index

🤔 Why?
🤨 How?
- How did I got inspired?
- How did I do it?
- Enough show me the code
- Final results
✌ Conclusion

🤔 Why?

The title seems kinda rude and lazy. Right? But if you think about the situation I am in then you'll understand. Here in India it's important to be socially active than being alive. If you don't reply or send a few "good morning" messages in a few groups, People would think you are actually dead. and taunt you for being busy and not spending time with them.😤

Now, the thing with me and social media is, I want to use it on my time not when they send me a notification. So, there are situations Where I reply to a post after 4-5 days and by that time my reply would not matter, as that moment would have been gone. Let's consider about posting. For example birthdays anniversaries etc... But the thing is I don't want to be that guy who replies after 4-5 days. We all have 24 hours and I try my best to allocate it to all the things and priorities I have at my disposal. I'd get disappointed if someone replies to me after a week so I don't want to be that guy for them.

So, I have come up with my programming skills to solve/automate this scenario which will save my screen time.🤩

🤨 How?

How did I got inspired?

I was looking to do an automation on the web and just like Google's top result, Selenium popped up in my mind but, as a JavaScript enthusiast, I choose JS as my ally. As an matter of fact, I found few modules in npm for Selenium but wasn't really impressed by that😐.

While searching for that I stumbled upon this video from google IO 2018 and then this one which got me thinking like

How I did it?

First, I learned about a few tricks from this post

Creating a WhatsApp bot with Puppeteer-Sharp

Darío Kondratiuk ・ Apr 12 '19

#csharp #puppeteersharp

then, I realized that relying on DOM is not a good idea🤨. What if classes or selectors I have used got updated in the page🤔? So I decided not to use DOM. (But I ended up using it any way for other feature🤷‍♂️. Will write about it in another post. Stay Tuned.......😎)

Just like a production issue solved before the deadline😏, I found one repo which gives programmable access to the WhatsApp which works the same way as I want it.

Enough show me the code

Here are the overall steps and gist. I have added a link to the repo as well.

First I created a puppeteer browser window and opened Whatsapp like this

Then, I created a json file to manage messages and it's equivalent reply and also to add some configs.

Next, I added a function inside inject.js which would be called every time I get a new message

Final results

With the few garnishing with libraries like Ora, Cli-progress and qrcode-terminal on the top

I got this

Here is the full code

vasani-arpit / WBOT

A simple Web based BOT for WhatsApp™ in NodeJS 😜. Working as of 📅 Feb 4th, 2024

WBOT

A simple Nodejs BOT for whatsapp web

Preview • Features • Downloads • How to? • Technologies Used • Why? • Goals • deploy • FAQ

🔍 Preview

Quick preview

⚡ Features

🎨 Highly customizable json
💯 Totally Free for personal use
🔒 Complete Privacy. Your data stays with you always
💻 Download media files automatically
👥 Multiple instances

⬇ Downloads ⬇

macOS	Windows	Linux
Download Latest Release	Download Latest Release	Download Latest Release

Supported Platforms

Following platforms are supported by Wbot:

macOS: The minimum version supported is macOS 10.9.

Windows: Windows 7 and later are supported.

Linux:

Ubuntu 12.04 and later
Fedora 21
Debian 8

How to start the BOT?

STEPS

After downloading, extract the zip file and open the bot.json file and in the third line change the value of "headless" from true to false. Now, navigate to the location of the WBOT folder in your terminal…

View on GitHub

✌ Conclusion

This is what technology is all about. Identify the problem and fix the problem with the tools/tech. Isn't it?
There are so many possibilities with the combination of the tools like Google Puppeteer and NodeJS. We just need to be self aware enough to look for solution to the problem we have in day to day life.

!! Reward me with your comments below🎁..... You are awesome !!

Thanks starline for the background vector

P.S: This is the first time I am sharing/writing over the internet. Feel free to point out mistakes so that I can improve that for my next post.

Forem: Arpit Vasani

How to connect Whatsapp with google sheets?

Table Of Contents

Background

How I Created Whatsapp bot to talk with my friends?

Arpit Vasani ・ Aug 8 '19

Problem

Solution

Steps

Why?

Limitations

Conclusion

Can we automate code-review? Yes we can

Index

What is code-review?

Why would you want to automate it?

How can you automate it?

Steps

Conclusion

How I Created Whatsapp bot to talk with my friends?

Index

🤔 Why?

🤨 How?

How did I got inspired?

How I did it?

Creating a WhatsApp bot with Puppeteer-Sharp

Darío Kondratiuk ・ Apr 12 '19

Enough show me the code

Final results

vasani-arpit / WBOT

A simple Web based BOT for WhatsApp™ in NodeJS 😜. Working as of 📅 Feb 4th, 2024

WBOT

🔍 Preview

Quick preview

⚡ Features

⬇ Downloads ⬇

Supported Platforms

How to start the BOT?

STEPS

✌ Conclusion