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 this mock 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 the Create 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 a Widget 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 the Datasource manager. From here, you can add a new datasource or edit an existing datasource. At the bottom of the page, you'll find Query 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 the Text 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 the Table 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 to
default
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.
-
Column type: You need to select the data type for every column. By default, it's set to
Example: Suppose we have created a new column in the table for the Name of customers then we will set its column type as string
, column name as Customer Name
, and Key as Name
. The Key should be the same as the column name in Google Sheet.
- Creating a Compose Message area by using the Container component. We will be putting Text Area and Button 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 on add datasource
- From the dialog window, choose Google Sheets
- The next dialog box will ask you to authorize Google Sheets to grant either
Read-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 on Connect 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 message
Auth successful, you can close this tab now.
- close the tab and then click on the Save 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 the Query Editor, and click on the
+
icon to create a new query - Select the datasource as Google Sheets from the dropdown.
- From the Operation dropdown, select Read data from the spreadsheet
- Enter the Spreadsheet ID and the cells Range 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 the Preview 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 the Table data box the data in the table will be automatically updated.
- Click on the Table to open the inspect panel on the right sidebar.
- We will want the table to reference the data fetched from the query, so in the Table 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 a Loading 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 the message from the Text area and the Phone Number of the selected customer in the table. We'll be using these two properties in WhatsApp'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 new On-click event handler.
- Click on the On-click event handler and change its Action property to Open webpage.
- In the URL field, Enter WhatsApp's API and replace phonenumberfromtable with
{{components.customerInfo.selectedRow['Phone Number']}}
and messagefromtextarea 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 the Deploy button on the top-right corner. On the dialog box:
- Click on the +Version button to create a version of the app
- Click on the Save button to save the progress and then click on the Deploy 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 to make the application public and shareable through the link in the box
- You can also edit the URL according to your choice Voila — You just built a WhatsApp CRM app. You can now go ahead and beautify it with ToolJet's built-in component styling properties. 🎉
Top comments (0)