Tag: Google

From my spreadsheet script editor, I am using a script to send data (emails) to the sheet.

On the sheet I have two tabs:

Sheet1: Receive the new emails sent

Sheet2: Sort the emails on Sheet1 in a reverse order based on the Timestamp column, so all the time on Sheet2 I have the last email sent in cell B2

I added the function below to the script to allow me to send an email to the address located in cell Sheet2, B2

function SendEmail() {
var e = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange("B2").getValues();
var m = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3").getRange("A1").getValues();
var subject = 'Welcome to ..';
MailApp.sendEmail(e, subject, m);

}

The problem is: the SendEmail function only works well when I execute the function manually from the script and want it to run automatically once a new row is sent to the sheet

I tried adding triggers in two ways:

First, as we know, the sheet is preset in 1000 rows by default, so I removed all the blank ones and then, from the activators of the current project, I added the OnChange activator. I thought that when the total number of rows is changed, the trigger will work and execute the SendEmail function (but nothing happened)

Second, I tried to add a new function to the script to make the first attempt, but programmatically as follows:

function onChange(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
ScriptApp.newTrigger('SendEmail')
.forSpreadsheet(ss)
.onChange()
.create();
}

There was no luck either, so any help would be greatly appreciated. Thanks in advance