How to Use Google Sheets to Organize Newsletters in Make#

Make allows you to automate manual processes, combining various services. We have already described the process of creating automation for messaging with WhatsApp and Discord, as well as with the Slack service. In this article, we will consider how to use Google Sheets and GREEN-API to automate mailings in WhatsApp.

WhatsApp may block the number when sending mass messages. To reduce the risk of blocking, set a delay in sending messages from the queue for 10-15 seconds during the broadcast. To do this, use the Set Settings method with the delaySendMessagesMilliseconds parameter (see examples).

Content#

1. Creating a spreadsheet and setting up the Google Sheets module

2. Setting up the process of sending messages with data from the spreadsheet

3. Setting up the process of sending messages with files

4. Launch and testing

1. Creating a spreadsheet and setting up the Google Sheets module#

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

1. Go to Google Sheets

   • Log in to your Google account if you haven't already

2. Create a new empty spreadsheet
   

   

3. Create 2 columns in the spreadsheet:

   • For a phone number or group - column A
     • The phone number must be entered in international format without the + sign. At the end we add postfix @c.us
     • You don't need to create a group chat number yourself, you can get it using various methods GREEN-API.
   • For the message text - column B
     
     
   How to format message text?

   Text formatting occurs by adding symbols before and after the text.
   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.

4. After creating and formatting the spreadsheet, we proceed to obtaining data from it. Let's find the application from "Google Sheets"
   

   
   

5. Add a module "Get Range Value" that retrieves values from a spreadsheet based on a range
   
   
6. Add a Google account to the module to have access to spreadsheets from "Google Sheets"
   
   
7. Select the created spreadsheet from the drop-down list. In our case, this is the "Phones" spreadsheet
   
   
8. Select the sheet that you created earlier for sending messages. By default, this is "Sheet1"
   
   
9. In the Range field, you must specify the range of cells with data for the mailing, excluding the column names. In the created spreadsheet "Phones" such a range will be A2:B
   
10. Let's run the automation once to test its functionality. You can do this by clicking on the purple start button in the lower left corner of the screen
    
11. In the window that appears next to the "Google Sheets" module, we should see data from the spreadsheet
    

After we have learned how to get data from Google Sheets, we can start setting up sending messages.

2. Setting up the process of sending messages with data from the spreadsheet#

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

1. Create a "Send Message" module from the "GREEN-API" application
   
2. Select the column with phone numbers from the spreadsheet. In our case, this is column A
3. Select column B with the message text
   

Now you can move on to testing the automation.

3. 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).

1. Let's create a separate spreadsheet for sending files with the following columns:
   • Chat ID - contains phone numbers or groups as in 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 using links to files in the Google Sheets module 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 file
   • D - File name with extension
4. Create a "Send File By Url" module from the "GREEN-API" application
   
5. In the Chat Id field, specify the column with phone numbers
   
6. In the field Link to outgoing file we specify the column containing the link to the files
   
7. In the Filename field, specify the column with the file name
   

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

   How to add text message to only some files?

   In this case, do not skip this step. In the column for text messages, specify the description only in those lines where the necessary messages are located. Empty cells will be ignored, and there will be no text message.

   

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 do the following 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 “Everyone who has the link” in the access menu with “Reader” rights. Then click “Copy link” and “Done”
   
3. Now we need to get the file ID. To do this, paste the received link into a text editor
   • Example of a file link:
     

     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 a file, you need to 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 resulting link in the sendFileByUrl method

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

4. Launch and testing#

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

• Immediately - is set automatically when configuring a module with an incoming webhook
• At regular intervals
• Once
• Every day
• Days of the week
• Days of the month
• Specified dates
• On demand - automation will start only when the module is called manually (by API request or via the "Run Scenario" module)

For testing, we will launch automation once, having previously set the message sending interval to 10,000 ms through the console to reduce the risk of blocking.

This way you can set up regular text and file message mailings using Make. You can see other examples of scenario usage below.

Examples of using the GREEN-API application#

How to make messaging between WhatsApp and Slack

How to make messaging between WhatsApp and Discord