How to Use Google Sheets to Organize Newsletters in Make#

Make allows you to automate manual processes, combining various services.
In this article, we will consider how to use Google Sheets and GREEN-API to automate mailings in WhatsApp.

Table of Contents#

1. Setting up the Green-API application
2. Creating a spreadsheet
3. Setting up the process of sending messages
   • Receiving spreadsheet data
   • Sending messages
4. Setting up the process of sending messages with files
   • Receiving spreadsheet data
   • Sending messages
5. Start and test

Setting Green-API application#

Setting Green-API application is described in detail in the section Integration setup

Creating a spreadsheet#

The first step is to create a spreadsheet that will store all the necessary numbers and messages.

1. Log in or register an account in Google Sheets

2. Create a new empty spreadsheet

   

3. Create 2 columns in the spreadsheet:

   • For a phone or a group number - column A

     • The phone number must be specified in international format without the + sign. At the end, add the postfix @c.us
     • You don't need to generate the group chat number yourself, you can get it using various methods GREEN-API.

   • For the message text - column B
   
   How to format the message text?

   Text formatting occurs by adding symbols before the text and after its end.
   For example, bolding can be done using the symbols *: *bold text* -> bold text.
   You can read more about text formatting methods in the article how to format messages.

Setting up the process of sending messages#

Getting spreadsheet data#

1. Click the + sign, find Google Sheets in the list of applications

   

2. Select the Get Range Values module

   

3. Click on the added module to open its settings

4. Click Create a connection to add a connection to your Google Sheets account

   

5. Select the created spreadsheet in the Spreadsheet ID parameter

   

6. Select a sheet with filled data in the Sheet Name parameter

   

7. Specify the range of values ​​A2:B in the Range field, where the columns are:

   • A - Phone numbers
   • B - Message text
   

8. Click Save to save values

Sending messages#

Sending text messages in Green-API is implemented through the "Send Message" module. It accepts the chat ID (personal or group) and the message text.

1. Click on the + sign, find GREEN-API for WhatsApp in the list of applications

   

2. Add the Send a Message module from the Green-API application

   

3. Click Create a connection to add a connection to the instance

   • Specify the values ​​of your instance idInstance and apiTokenInstance
   

4. Select the column with phone numbers from the spreadsheet. In our case, this is column A

   

5. Select column B with the message text

   

Now you can move on to testing the automation.

Setting up the process of sending messages with files#

To send messages with files, you will need another module - "Send File By Url". It specifies the recipient, link to the file, its name, text message (optional).

Getting spreadsheet data#

1. Create a separate spreadsheet for sending files with the following columns:

   • ChatID - contains phone numbers or groups as in the first step
   • File description/Message text - for the message text that will be sent along with the file. Optional field
   • File link - contains a link to the file to be sent
   • File name - it is necessary to specify the name of the file with its extension, which will be displayed to the user
   

2. Select the created spreadsheet with files in the Google Sheets module

   

3. Specify the range of values ​​A2:D in the Range field, where the columns are:

   • A - Phone numbers
   • B - Description of the file (text message)
   • C - Link to the file
   • D - File name with extension
   

Sending messages#

1. Add the Send a File By URL module from the Green-API application

   

2. Click Create a connection to add a connection to the instance

   • Specify your instance's idInstance and apiTokenInstance

   (The script will receive messages from the number associated with this instance)

   

3. In the Chat Id field, specify the column with phone numbers

   

4. In the Link to outgoing file field, specify the column containing the link to the files

   

5. In the File Name field, specify the column with the file name

   

6. In the File Caption field specify the column with the text message. If you plan to send only files, without signatures to them, you can skip this step

   

How to send a file from Google Drive?

To send a file from Google Drive, you need to modify the link for direct access to the file. To do this, you need to follow these steps:

1. Open Google drive and find the file you want to send

2. Select the file to send, right-click. Then select “Get link” in the menu, set the access menu to “Everyone who has the link” with “Reader” rights. After that, click “Copy link” and “Done”

   

3. Now we need to get the file ID. To do this, paste the resulting link into a text editor

   • Example of a link to a file:

     https://drive.google.com/file/d/13sseEurJDYZxb-ueH2VOpVoYY4U5Kvu1/view?usp=sharing
     

   • The file ID is contained between d/ and /view (for documents - between d/ and /edit). In this case, the file ID will be:

     13sseEurJDYZxb-ueH2VOpVoYY4U5Kvu1
     

4. To create a direct link to the file, insert the file ID at the end of the following link:

   https://drive.google.com/uc?export=download&id=
   

   * The final link should look like this:

   https://drive.google.com/uc?export=download&id=13sseEurJDYZxb-ueH2VOpVoYY4U5Kvu1
   

5. Use the final link in the sendFileByUrl method

How to send files from other file storages is described in the article "How to send a file using the sendFileByUrl method, using an external storage?".

Start and test#

Now that the automation is set up, you can set the conditions under which it will be executed.
Make offers the following options for conditions for starting:

• Immediately - immediate sending; set automatically when configuring the module with an incoming webhook
• At regular intervals - sending at regular intervals (the interval is specified in the Minutes parameter below the selection list)
• Once - once according to the schedule (date and time are specified in the Date parameter below the selection list)
• Every day - every day (time is specified in the Date parameter below the selection list)
• Days of the week - certain days of the week at a specified time (days of the week are selected in the Days parameter, time is specified in the Time parameter below the selection list)
• Days of the month - on certain days of the month at a specified time (dates are selected in the Days parameter, time is specified in the Time parameter below the selection list)
• Specified dates - on specified dates (months are selected in the Months parameter, dates are selected in the Days parameter, time is specified in the Time parameter below the selection list)
• On demand - automation will only run when the module is called manually (by API request or via the "Run Scenario" module)

For testing, we will run automation once, having previously set the message sending interval to 10000 ms via the personal account to reduce the risk of blocking.

Run automation. The automation will complete its execution after all messages are sent to the queue.

This is not the end of the mailing. Messages will be gradually sent from the outgoing message queue.
You can view the remaining message queue using the ShowMessagesQueue method.

This way, you can set up regular mailings of text messages and messages with files using Make.
You can see other examples of using scripts below.

Green-API Application Usage Examples#

How to Make a Chatbot with Artificial Intelligence from OpenAI in WhatsApp

How to Make a Messaging Experience between WhatsApp and Slack

How to Make a Messaging Experience between WhatsApp and Discord