Ctrlk

How to use Google Sheets for mailings

Here you can find the detailed instructions of how to collect email addresses with the help of a chatbot on Mavibot.ai and send a commercial offer with the script from Google Sheets.

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 configuring a trigger

How to configure a 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 "Sending" sheet 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 chatbot

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}$

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.

    https://store.salebot.ai/function/gsheets

  • 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 configure 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!

Other valuable links:

Gmail sending limits in Google Workspace

More info on Gmail Service

Quotas for Google Services

Last updated