Using Google Sheets for mailings

Collecting email addresses with the help of a chat-bot on Salebot.ai 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
sheet.getRange("a3:a").clearContent()
}

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

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:

^[a-z0-9][a-z0-9_.-]@([a-z0-9]+.)[a-z0-9][a-z0-9-]+.([a-z]{2,6})$

^[-\w.]+@([A-z0-9][-A-z0-9]+.)+[A-z]{2,4}$

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!