In this article, you’ll learn how to create an app that lets you send WhatsApp messages to the contacts stored in a Google Sheet.

What is a CRM?

A CRM app is designed to gather all the customer information in the central place to provide a 360º view of all your customer data. Details like the customer's name, email, phone number, and communication preferences, etc, are made available to help teams to reach out to customers for the right reasons and through the right channels. A lot of companies and businesses store their customers' or employees' data in Google Sheets, and this app can be utilized to send personalized WhatsApp messages in no time.

Overview

Let's start with a high-level view of this app. We already have a Google Sheet with mock data resembling the typical customer data: Id, names, emails, phone number, etc.

On ToolJet, we will add the Google Sheet as the datasource and then create a query that will allow us to pull the Google sheet data into our ToolJet app. We can then start adding additional functionality for taking action on the selected row in the table. This will allow for quickly composing the message from the text box and sending the message via a “Send Message” button that will link to WhatsApp's click to chat API. The API lets you open a chat on Whatsapp web directly with 1-click.

Prerequisites

• Google Sheet: Use thismock data
• ToolJet (https://github.com/ToolJet/ToolJet): It is a free and open-source, low-code platform that allows you to quickly build tools.Sign-in here
• WhatsApp's click to chat API endpoint:https://api.whatsapp.com/send?phone=phonenumber&text=message

Building the UI

Let's start with building the app UI first. Log in to ToolJet and then from the dashboard click on theCreate new application button to create a new app. From there, you will be redirected to the visual app editor once the app has been created. The name of the app can be changed by clicking on the app name from the top left of the app builder.

On the right sidebar, you'll find aWidget manager that has a list of built-in widgets and components. You can drag and drop to start laying out your user interface. On the left sidebar, you'll see theDatasource manager. From here, you can add a new datasource or edit an existing datasource. At the bottom of the page, you'll findQuery Editor that will be used to build the query for the datasource that we will connect. For this app, we will be using Google Sheet as our datasource and will be building a query for it—but we’ll get to that further down in the post.

Let's start building the UI by:

• Using theText component from the widget manager to give our app a Title. Drag the component to the canvas and click on it to open the inspect panel on the right sidebar. From inspect panel, you can edit the properties of the component like Text, component name and also style it by changing its color.

Tip: You can also use the image component instead of the Text component as the Title of our app.

• Using theTable component for our customer data. The table component comes pre-placed and pre-populated with mock data. We will drag and drop the table component from the widget manager to the canvas and then click on it to edit its properties from the inspect panel. From inspect panel, let's add columns like Id, Name, Email, Phone Number, country, etc.For every column, we need to specify:
  • Column type: You need to select the data type for every column. By default, it's set todefault but we will be choosingstring for every column since most of our data from Google Sheet is a string.
  • Column name: This is the name of the column that we want to display in UI, so we can name it accordingly.
  • Key: Key property is the accessor key used to get data from a single element of the table of objects. Here you can think of it as the name of the column that is there in the Google Sheet.

Example: Suppose we have created a new column in the table for the Name of customers then we will set itscolumn type asstring,column name asCustomer Name, andKey asName. The Key should be the same as the column name in Google Sheet.

• Creating aCompose Message area by using theContainer component. We will be puttingText Area andButton components inside this container. Text Area component will be used for message body and Button component will use WhatsApp's click to chat API to open Chat in a new window.

Now that we have finished the UI, let’s connect the datasource and build the query to retrieve the data in the table.

Adding the Datasource

ToolJet supports several datasources including Airtable, Elasticsearch, Cloud Firestore, REST API, GRaphQL, and many more. For WhatsApp CRM we will be using Google Sheet as the datasource. So, let's see how to do it:

• On the left sidebar, click on the add or edit datasources button and then click onadd datasource
• From the dialog window, chooseGoogle Sheets
• The next dialog box will ask you to authorize Google Sheets to grant eitherRead-only orRead and write permission; ChooseRead-only since we are only going to retrieve the data and will not update any data on Google Sheet, then click onConnect to Google Sheet button
• It will open up a new window where you'll need to sign in to your Google account to authorize the ToolJet app
• Once authorized, you'll see a messageAuth successful, you can close this tab now. - close the tab and then click on theSave datasource button in the dialog box

So, now that we have connected the datasource to our app, let's see how we can build a query to and update the data on the table.

Building the query

A query is a request for data or information from the datasource. We will build a query that will request the data from Google Sheets. Let's build a query for this:

• Go to theQuery Editor, and click on the + icon to create a new query
• Select the datasource as Google Sheets from the dropdown.
• From theOperation dropdown, selectRead data from the spreadsheet
• Enter theSpreadsheet ID and the cellsRange from the Google Sheet

💡 You'll find the Spreadsheet ID in the URL of your Google Sheet.
Let say the URL of Google Sheet is:
https://docs.google.com/spreadsheets/d/1gIfeXMmgJAKrWmm_MtivOj68zZM_H9D8qR5mzVvzo6c/edit?usp=sharing
In this URL,1gIfeXMmgJAKrWmm_MtivOj68zZM_H9D8qR5mzVvzo6c is the Spreadsheet ID

• Click on thePreview button at the top right of the Query Editor and It'll fetch all the data from the sheet in JSON format
• Click on Create button to save and create the query

Linking the Google Sheets data to the table

Now that we have built a query that retrieves the data from the Google Sheets, let's start referencing it in the table.

• Our columns are already added so as soon as we enter the property in theTable data box the data in the table will be automatically updated.
• Click on theTable to open the inspect panel on the right sidebar.
• We will want the table to reference the data fetched from the query, so in theTable data property, we will enter{{queries.googlesheets1.data}} - heregooglesheets1 is the query that we built, and the data objects fetched is stored indata.
• Next, we will add aLoading state for the table. Loading state shows a loading skeleton for the table.isLoading property to get the status of the query, so we'll enter{{queries.googlesheets1.isLoading}}.

💡 ToolJet will auto-suggest your queries and let you simply select the one you want.

Sending Message

Now, all we need to do is to get themessage from the Text area and thePhone Number of the selected customer in the table. We'll be using these two properties inWhatsApp's click to chat API:

https://api.whatsapp.com/send?phone=**phonenumberfromtable**&text=**messagefromtextarea**

• Click on the Send Message button to open the inspect panel on the right sidebar.
• On Inspect Panel, go to the Events section and add a newOn-click event handler.
• Click on the On-click event handler and change itsAction property to Open webpage.
• In the URL field, Enter WhatsApp's API and replacephonenumberfromtable with{{components.customerInfo.selectedRow['Phone Number']}} andmessagefromtextarea with{{components.textarea.value}} . The final URL should look like:

https://api.whatsapp.com/send?phone={{components.customerInfo.selectedRow['Phone Number']}}&text={{components.textarea.value}}

Making app live

Now we have reached the final step that is making our app live and shareable. To do this just click on theDeploy button on the top-right corner. On the dialog box:

• Click on the+Version button to create a version of the app
• Click on theSave button to save the progress and then click on theDeploy button to deploy on the server

Making app shareable:

• Click on the Share button on the top-right
• In the dialog box, click on the toggle switch tomake the application public andshareable through the link in the box
• You can also edit the URL according to your choiceVoila — You just built a WhatsApp CRM app. You can now go ahead and beautify it with ToolJet's built-in component styling properties. 🎉