Using Google Sheets for mailings
Collecting email addresses with the help of a chat-bot on and sending a commercial offer with the script from Google Sheets. Detailed instructions.

How to create mailings with the help of Google Sheets

How it works: the bot collects new addresses and pastes them one by one in the first column (A3:A in the “Sending” sheet). The Google-script is launched when the time is set or when you change the sheet (depending on what the trigger is), it creates a mailing with new addresses and moves them into the “Log” sheet with a timestamp.
function SendMail() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName("Sending")
var email = sheet.getRange("a3:a").getValues()
var subj = sheet.getRange("c2").getValue()
var body = sheet.getRange("d2").getValue()
var arr = []
MailApp.sendEmail(email, subj, body)
arr = [[new Date(), email]]
//write sent addresses into Log
ss.getSheetByName("Log").getRange(ss.getSheetByName("Log").getLastRow()+1, 1, 1, 2).setValues(arr)
//clear the Sending sheet
The configuration consists of 4 parts: setting up the sheet, activating the script, configuring the chat-bot and setting up a trigger

How to configure the sheet

Create a copy of the sheet with the code: File -> Make a copy
Notice how it says that the scripts will also be copied - make sure they are when copying the sheet. Press “Make a copy”
In the sheet “Sending” you change only the B2 and C2 columns
About the sheet: the bot pastes the addresses in the first column (A3:A in the “Sending” sheet). You need to change only the subject and the message columns, nothing else
Now you need to check the access to your sheet, it has to be available for editing to anyone with the link, like on the screenshot below.

How to activate the script

In your sheets Extensions -> Apps Script
Choose SendMail
Press “Run”
Press “Review permissions”
Choose the account from which you want to send the mailing
Press “Advanced”
Press “Go to (the name of the script) (unsafe)”
Press “Allow”
Now you need to make sure that the script was activated by checking for the security alert email in your account

How to configure the chat-bot

Let’s take as an example the following schema consisting of three blocks
1) The first green block “Primary condition check” is launching the dialogue with the bot. Here we type the phrase that will launch it in the conditions. To make sure the settings are saved in the blocks, don’t forget to press “Save” after adding.
2) Now create a block below and edit the connection. Flip the tab “User enters data” and paste the name of the variable (client_mail as in the example). Then put a regular expression in the condition to check the email. At this point, it’s important to check the accuracy of the inserted data, otherwise the script will freeze a lot. You can use either of the following expressions:
^[-\w.][email protected]([A-z0-9][-A-z0-9]+.)+[A-z]{2,4}$
More on regular expressions here
3) The next block is the one that transfers the data into the sheet. Configure everything as it is in the screenshot (the description of the fields is below)
  • Choose the POST-json request type.
  • Paste the function URL into the “URL request” tab.
  • Paste the following line into the “JSON parameters” tab {"id": "(your id parameter)", "mapping":{"a":"#{client_mail}"}, "list_name": "Sending"} Paste your id parameter from the link to your sheet. You can find it in the link to it, after d/

How to set up a trigger

Before creating a trigger check that the bot writes down the data into the sheet and that the function works correctly.
If everything went by smoothly, open Extensions -> Apps Script -> SendMail and then Triggers on the menu on the left.
Create a new Trigger using the button in the bottom right corner of the screen
Set it up like in the screenshot. If you’re configuring it Time-driven, then know that the timeframe has to be at least 30 minutes. You can choose your own values but remember that there are certain quotas. Then press “Save”
Now there’s a trigger in the list. Configuration is complete!
Copy link
On this page
How to create mailings with the help of Google Sheets
How to configure the sheet
How to activate the script
How to configure the chat-bot
How to set up a trigger