CODEWITHSUNDEEP
javascriptactionscript-3google-sheets
user4059078
user4059078

Reputation:

OnEdit need to send emails from a column

When in column O cell is mentioned "Shipped" I want to send emails in column C (with corresponding row)

Need support to get this script right

function sendEmail(e) {
    var thisSheet = e.source.getActiveSheet();
    if (thisSheet.getName() !== 'CRM' || e.range.columnStart !== 15 || e.range.rowStart == 1 || e.value !== 'Shipped') return;
    var body, headers = thisSheet.getRange(1, 1, 1, 6)
        .getValues()[0],
        thisRow = thisSheet.getRange(e.range.rowStart, 1, 1, 6)
        .getValues()[0],
        recipientsEMail = (thisSheet.getRange.columnStart !==3 || e.range.rowStart ==1)
        .getValues()[0],
        recipients = recipientsEMail,
        subject = "Your Vehicle is shipped " + e.source.getName(),
        body = "",
        i = 0;
    while (i < 6) {
        body += headers[i] +' - ' + thisRow[i] +'\n';
        i++;
    }

    MailApp.sendEmail(recipients, subject, body);
}

My sheet

Upvotes: 0

Views: 141

Answers (1)

JPV
JPV

Reputation: 27262

See if this works:

function sendEmail(e) {
var thisSheet = e.source.getActiveSheet();
if (thisSheet.getName() !== 'CRM' || e.range.columnStart !== 15 || e.range.rowStart == 1 || e.value !== 'Shipped') return;
var body, headers = thisSheet.getRange(1, 1, 1, 15)
    .getValues()[0],
    thisRow = thisSheet.getRange(e.range.rowStart, 1, 1, 15)
    .getValues()[0];
    var recipients = thisRow[2];
    var subject = "Your Vehicle is shipped " + e.source.getName(),
    body = "Dear Customer,\n\nYour vehicle has been shipped from Japan";


MailApp.sendEmail(recipients, subject, body);
}

You didn't say how much columns you need to use to construct the body of the email but that can be easily changed if you increase the number in the while loop.

Upvotes: 2

Related Questions