Integrating WhatsApp with Google Sheets

TLDR

I used to have a manual job that took hours to complete. Essentially, for some of the parties here in my city, guest lists were prepared. These lists included attendees who were paid, free, or prepaid. However, I was the one responsible for creating this guest list—event promoters would send me the names of the attendees, and I had to add these names to a spreadsheet for the day of the event.

The idea was to somehow automate this task. I wanted to minimize the changes for the promoters, so I didn't intend to shift from the convenience of sending names in a WhatsApp group. However, I also didn't want to spend hours organizing a list (some events had more than a thousand names).

To automate this, I thought of integrating WhatsApp with Google Sheets. I'll be sharing a bit about my experience performing this integration.

Tooling

Given my familiarity with the NodeJS + MongoDB stack, I chose to use it as the development stack for speed.

The complete stack I selected comprised: Typescript, NodeJS, MongoDB, and SWC.

For managing Google Sheets, I utilized the Google-provided API: googleapis

To handle WhatsApp, I needed to simulate a browser using Puppeteer and connect my WhatsApp to it. I used a library that did this for me: whatsapp-web.js

Once equipped with these tools, it was time to get my hands dirty. It's important to note that I started with the bare essentials—the MVP. The goal was to keep things simple initially.

I won't delve into every detail in this post, so feel free to follow the project: wpp-sheets-party-list

Entry point

To run the application, three things were necessary: connecting to my database, authorizing the Google client, and connecting to WhatsApp.

Mongodb

Connecting to my database allowed me to identify and synchronize a WhatsApp group with a spreadsheet. You can find the complete schema at https://github.com/vinibgoulart/wpp-sheets-party-list/blob/main/src/sheets/SheetModel.ts.

Google Sheets

The authorization part with Google was a bit more complex. Various environment variables were necessary.

const client = new google.auth.GoogleAuth({
    scopes: GOOGLE_SCOPES,
    keyFile: "credentials.json",
  });

For our application scope, we used Google Drive and Google Sheets to manipulate files and spreadsheet content. In the credentials, we needed to include all the necessary data to authenticate the Google client.

export const credentials = {
  type: "service_account",
  project_id: config.GOOGLE_PROJECT_ID,
  private_key_id: config.GOOGLE_PRIVATE_KEY_ID,
  private_key: config.GOOGLE_PRIVATE_KEY.split(String.raw`\n`).join("\n"),
  client_email: config.GOOGLE_SERVICE_ACCOUNT_EMAIL,
  client_id: config.GOOGLE_CLIENT_ID,
  auth_uri: config.GOOGLE_AUTH_URI,
  token_uri: config.GOOGLE_TOKEN_URI,
  auth_provider_x509_cert_url: config.GOOGLE_AUTH_PROVIDER,
  client_x509_cert_url: config.GOOGLE_CLIENT_CERT_URL,
  universe_domain: "googleapis.com",
};

WhatsApp Api

The next step was to connect WhatsApp and listen for new events. Firstly, initializing the client and displaying the QR code to connect to WhatsApp.

import { Client, LocalAuth } from "whatsapp-web.js";
import { onReady } from "./onReady";
import { onMessage } from "./onMessage";
import { onAuthFailure } from "./onAuthFailure";
import { onQrCode } from "./onQrCode";

export const client = new Client({
  authStrategy: new LocalAuth(),
  authTimeoutMs: 60 * 1000,
  puppeteer: {
    args: ["--no-sandbox", "--disable-setuid-sandbox"],
  },
});

export const connectClient = () => {
  client.initialize();

  onQrCode();

  onReady();

  onAuthFailure();

  onMessage();
};

A single file encompassed all my events. The first one to be executed was onQrCode, responsible for displaying the QR code for connection.

import { client } from "./client";

import qrcode from "qrcode-terminal";

export const onQrCode = () => {
  client.on("qr", (qr) => {
    qrcode.generate(qr, { small: true });
  });
};

After scanning this QR code on our phone, we received a message that the client was ready to receive messages.

import { client } from "./client";

export const onReady = () => {
  client.on("ready", () => {
    console.log("Client is ready!");
  });
};

Now, we could delve deeper into the sent messages.

The idea is this: create a group with promoters, naming the group after the event. Add the number connected to the application to the group, start the bot, and it would begin listening for new names and sending them to a spreadsheet.

These are the configured messages that the bot accepts:

import { client } from "../client";
import defaultMessage from "./defaultMessage";
import freeMessage from "./freeMessage";
import helpMessage from "./helpMessage";
import howItWorksMessage from "./howItWorksMessage";
import inputExamplesMessage from "./inputExamplesMessage";
import paidMessage from "./paidMessage";
import removeMessage from "./removeMessage";
import sheetsCreateMessage from "./sheetsCreateMessage";
import sheetsDetailMessage from "./sheetsDetailMessage";
import sheetsFinishMessage from "./sheetsFinishMessage";
import startListeningMessage from "./startListeningMessage";
import stopListeningMessage from "./stopListeningMessage";

export const onMessage = () => {
  client.on("message", async (msg) => {
    switch (msg.body) {
      case "!help":
        return helpMessage(msg);

      case "!hiw":
        return howItWorksMessage(msg);

      case "!ie":
        return inputExamplesMessage(msg);

      case "!sc":
        return sheetsCreateMessage(msg);

      case "!sd":
        return sheetsDetailMessage(msg);

      case "!sf":
        return sheetsFinishMessage(msg);

      case "!start":
        return startListeningMessage(msg);

      case "!stop":
        return stopListeningMessage(msg);

      case "!free":
        return freeMessage(msg);

      case "!paid":
        return paidMessage(msg);

      case "!remove":
        return removeMessage(msg);

      default:
        return defaultMessage(msg);
    }
  });
};

Some are help or explanation messages, while others are for interacting with the spreadsheet. The most important commands are:

• !sc: to create a new list
• !sd: to get details of the ongoing list
• !sf: to finish a list
• !stop: to temporarily stop the bot from listening to new names without finishing the list
• !start: to resume the bot's listening process

Additionally, we have commands to manipulate names on the list:

• default: if the bot is running in a group, any message that isn't a command is interpreted as a name and sent to Google Sheets
• !free: to indicate that a sent name should go into the 'free' column
• !paid: to indicate that a sent name should go into the 'prepaid' column

Sheet Create

The !sc command initializes a list. You can drill down into how it works here: https://github.com/vinibgoulart/wpp-sheets-party-list/blob/main/src/wweb/onMessage/sheetsCreateMessage.ts.

With this command, several actions are performed. Firstly, we create a list on Google Sheets, then change the group's description and name to indicate that the list is active. Finally, we send spreadsheet information to the group and start listening for new names.

From now on, all sent names will be directed to the spreadsheet. You can confirm if a name was sent to the spreadsheet by checking if the bot reacted to your message.

In this case, we sent three names to the list, and the bot accepts various name formats.

Apart from sending names to the list, the bot capitalizes names and sorts them alphabetically.

It's also possible to send names to the other two columns, 'free' and 'prepaid,' by replying to a message with either !free or !paid, and the name will go into the respective column.

And that's basically it. You use it to receive names and automatically add them to a spreadsheet. Finally, you can execute the !sd and !sf commands to view the list details or finish it.

You can view the complete code and delve deeper into the project here: https://github.com/vinibgoulart/wpp-sheets-party-list/tree/main

Deploy

To minimize my concerns about the bot, I opted for deployment, which brought new challenges. I chose to deploy on Railway because it's a platform I was already familiar with.

As we're running Puppeteer underneath and simulating a browser, we needed to install Chromium during the deployment preparation.

We easily achieved this with Docker by installing Chromium and defining it in our Dockerfile.

# syntax = docker/dockerfile:1

# Adjust NODE_VERSION as desired
ARG NODE_VERSION=20.3.0
FROM node:${NODE_VERSION}-slim as base

LABEL fly_launch_runtime="Node.js"

# Node.js app lives here
WORKDIR /app

# Set production environment
ENV NODE_ENV="production"

# Install pnpm
ARG PNPM_VERSION=8.6.5
RUN npm install -g pnpm@$PNPM_VERSION


# Throw-away build stage to reduce size of final image
FROM base as build

# Install packages needed to build node modules
RUN apt-get update -qq && \
    apt-get install -y build-essential pkg-config python-is-python3 chromium chromium-sandbox

# Install node modules
COPY --link package.json pnpm-lock.yaml ./
RUN pnpm install --frozen-lockfile --prod=false

# Copy application code
COPY --link . .

# Build application
RUN pnpm run build

# Remove development dependencies
RUN pnpm prune --prod


# Final stage for app image
FROM base

# Install packages needed for deployment
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y chromium chromium-sandbox && \
    rm -rf /var/lib/apt/lists /var/cache/apt/archives

# Copy built application
COPY --from=build /app /app

# Start the server by default, this can be overwritten at runtime
EXPOSE 3001
ENV PUPPETEER_EXECUTABLE_PATH="/usr/bin/chromium"
CMD [ "pnpm", "run", "start" ]

With this Dockerfile setup, we finally managed to deploy to Railway and keep the bot running.

Github: https://github.com/vinibgoulart/wpp-sheets-party-list/tree/main

Photo by Mohamed Nohassi on Unsplash