Send email per column if specific data in row cell

Question

I'm trying to create a script on my example Google Sheet, which, on a spreadsheet edit, will send an email containing the data in the applicable column cells of Rows 2 and 3 whenever a cell in Row 7 has the words 'Send email' added to it, which will then add 'Email sent' to the same cell.

For example, if I choose 'Send email' in Cell E7, the email should immediately send, with a message to the extent of "Your data is 'Cell E2' and 'Cell E3'", and then Cell E7 should change to 'Email sent'.

I've had some success with doing this for rows, so I've tried to tweak my script to work for columns, but I have not yet been successful. This is my current script:

var EmailSent = 'Email sent';

    function sendEmail() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet9');
  var dataRange = sheet.getDataRange();
  var lastRow = dataRange.getLastRow();
  var lastCol = dataRange.getLastColumn();
  var data = sheet.getRange(2, 1, lastRow, lastCol);
  for (var i = 0; i < data.length; ++i) {
    var col = data[i];
    var emailAddress = "fake@email.com";
    var message = "Email details here." + row[-1] + row[-1] ;
    var emailSent = col[6];;
    if (emailSent == "Send email") {
      var subject = 'Message from Sheet9.';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startCol + i, 7).setValue(EmailSent);
      SpreadsheetApp.flush();
    }
  }
}

How would I best accomplish my goal?

Answer 1

I believe your goal is as follows.

• When you run the script, you want to send emails by checking row 7.
• When the email is sent, you want to include the values from rows 2 and 3 for each column.

In this case, how about the following modification?

Modified script:

function sendEmail() {
  var EmailSent = 'Email sent';

  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet9');
  var dataRange = sheet.getDataRange();
  var lastRow = dataRange.getLastRow();
  var lastCol = dataRange.getLastColumn();

  // I modified the below script.
  const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : ""; // Ref: https://stackoverflow.com/a/53678158
  var data = sheet.getRange(2, 1, lastRow - 1, lastCol).getDisplayValues();
  var [, ...tValues] = data[0].map((_, c) => data.map(r => r[c]));
  var ranges = tValues.reduce((ar, c, i) => {
    if (c[5] == "Send email") {
      var emailAddress = "fake@email.com";
      var subject = 'Message from Sheet9.';
      var message = `Your data is '${c[0]}' and '${c[1]}'`; // or var message = "Email details here." + c[0] + c[1]; of your current script.
      MailApp.sendEmail(emailAddress, subject, message);
      ar.push(`${columnIndexToLetter_(i + 1)}7`);
    }
    return ar;
  }, []);
  if (ranges.length == 0) return;
  sheet.getRangeList(ranges).setValue(EmailSent);
}

• When this script is run, the values are checked in every column. And, when row 7 is "Send email", an email is sent by including the values of rows 2 and 3. And, row 7 is modified to "Email sent".

Note:

• This modified script is for your provided Spreadsheet. If you change the Spreadsheet, this modified script might not be able to be used. Please be careful about this.

References:

• map()
• reduce()

Added:

About your following new question,

Using this method, if I add Sheet10 to list names in column A with corresponding email addresses in Column B, and would like variable emailAddress to be determined based on the name in Row 6 of Sheet9, would you have a suggestion on how to best accomplish this?

In this case, how about the following sample script?

Sample script:

function sendEmail() {
  var EmailSent = 'Email sent';

  var ss = SpreadsheetApp.getActive();
  var sheet10 = ss.getSheetByName("Sheet10");
  var obj = sheet10.getRange("A2:B" + sheet10.getLastRow()).getDisplayValues().reduce((o, [a, b]) => (o[a] = b, o), {});
  var sheet = ss.getSheetByName('Sheet9');
  var dataRange = sheet.getDataRange();
  var lastRow = dataRange.getLastRow();
  var lastCol = dataRange.getLastColumn();
  const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : ""; // Ref: https://stackoverflow.com/a/53678158
  var data = sheet.getRange(2, 1, lastRow - 1, lastCol).getDisplayValues();
  var [, ...tValues] = data[0].map((_, c) => data.map(r => r[c]));
  var ranges = tValues.reduce((ar, c, i) => {
    if (c[5] == "Send email") {
      var emailAddress = obj[c[4]];
      if (emailAddress) {
        var subject = 'Issue Assignment';
        var message = `Please investigate ${c[0]}: ${c[1]} and solve.`; // or var message = "Email details here." + c[0] + c[1]; of your current script.
        MailApp.sendEmail(emailAddress, subject, message);
        ar.push(`${columnIndexToLetter_(i + 1)}7`);
      } else {
        throw new Error("Email address was not found.");
      }
    }
    return ar;
  }, []);
  if (ranges.length == 0) return;
  sheet.getRangeList(ranges).setValue(EmailSent);
}

• When this script is run, the above goal is achieved.

Note:

• This modified script is for your provided Spreadsheet. If you change the Spreadsheet, this modified script might not be able to be used. Please be careful about this.