Hey there! Let's get that issue sorted out—you're just a small tweak away from pulling the B column value into your email body correctly.

The Problem with Your Current Code #

Your var values line is causing the issue because row is just a number (the active row number), not a Range object you can call methods on. Plus, the syntax getcell()[0,2] isn't valid in Google Apps Script. That's why that line is failing to fetch the B column value.

Corrected Code #

Here's the fixed version with key changes explained:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = sheet.getActiveCell().getValue().toString();
  
  // Fix: Fetch the value from column B (column index 2, since Sheets uses 1-based indexing)
  var bColumnValue = sheet.getRange(row, 2).getValue().toString();
  
  var recipients = "kel...@trafficbuilders.us";
  var message = '';
  
  if(cellvalue === '✔'){
    // Insert the B column value into your message and subject
    message = 'All Items have been received for ' + bColumnValue + '.';
    var subject = 'All Items have been received for ' + bColumnValue;
    var body = message + ' Visit ' + ss.getUrl() + ' to view the changes';
    
    MailApp.sendEmail(recipients, subject, body);
  }
}

Key Changes Breakdown #

• Proper B Column Retrieval: Instead of trying to use row.getcell(), we use sheet.getRange(row, 2) where:
  • row is the active row number
  • 2 is the index for column B (Google Sheets uses 1-based numbering for columns)
• Injecting the Value: We added bColumnValue to both the email message and subject, so the recipient sees the relevant value from column B clearly.
• Clearer Naming: Renamed values to bColumnValue to make the code easier to read and maintain later.

Bonus: Optimized Version Using the Event Object e #

For better reliability (especially if multiple edits happen fast), it's smarter to use the built-in event object e instead of relying on getActive* methods. This version also adds a check to only run when column F is edited:

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var cellvalue = e.value?.toString() || '';
  
  // Only trigger if the edit is in column F (index 6) and the value is "✔"
  if(range.getColumn() === 6 && cellvalue === '✔'){
    var bColumnValue = sheet.getRange(row, 2).getValue().toString();
    var recipients = "kel...@trafficbuilders.us";
    var message = 'All Items have been received for ' + bColumnValue + '.';
    var subject = 'All Items have been received for ' + bColumnValue;
    var body = message + ' Visit ' + sheet.getParent().getUrl() + ' to view the changes';
    
    MailApp.sendEmail(recipients, subject, body);
  }
}

内容的提问来源于stack exchange，提问作者Kelly Elsmore