Send email when any value is input into first column Google Script

Question

I'm looking to send an email to the recipient (clientEmail) when data is added to the first column of that specific row. The data in the first column would be a mix of numbers and letters. I've tried different methods using the following code but can never get it to send only when the value in the first column contains a value.

var EMAIL_DRAFTED = "EMAIL DRAFTED";

function draftMyEmails() {
var sheet = SpreadsheetApp.getActiveSheet(); // Use data from the active 
sheet
var startRow = 2;                            // First row of data to process
var numRows = sheet.getLastRow() - 1;        // Number of rows to process
var lastColumn = sheet.getLastColumn();      // Last column
var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn) // Fetch the data range of the active sheet
var data = dataRange.getValues();            // Fetch values for each row in the range

// Work through each row in the spreadsheet
for (var i = 0; i < data.length; ++i) {
var row = data[i];  
// Assign each row a variable
var clientName = row[1];                // Col B: Client name
var clientEmail = row[2];               // Col C: Client email
var message1 = row[3];                       // Col D: First part of message
var message2 = row[4];                   // Col E: Second part of message
var emailStatus = row[lastColumn - 1];  // Col F: Email Status

// Prevent from drafing duplicates and from drafting emails without a recipient
if (emailStatus !== EMAIL_DRAFTED && clientEmail) {  

  // Build the email message
  var emailBody =  '<p>Hi ' + clientName + ',<p>';
      emailBody += '<p>' + message1 + ', your requested data, ' + message2 + ', is ready.<p>';


  //Send the emaiil
  MailApp.sendEmail(
    clientEmail,            // Recipient
    'Here is your data',  // Subject
    '',                     // Body (plain text)
    {
    htmlBody: emailBody    // Options: Body (HTML)
    }
  );

  sheet.getRange(startRow + i, lastColumn).setValue(EMAIL_DRAFTED); // Update the last column with "EMAIL_DRAFTED"
  SpreadsheetApp.flush(); // Make sure the last cell is updated right away
}
}
}

Columns are 0 indexed when using `getValues()` on a range, your `row[1]` is actually returning column B rather than column A like you're expecting. — ross, May 24 '19 at 10:40
Apologies, hadn't updated the comments from the previous script. I don't have anything in the script that looks for column A to find if there is a value yet as this is the part I'm struggling with. — pm2588, May 24 '19 at 11:09

ross · Accepted Answer · 2019-05-24T11:59:47.600

Start off by changing your for loop, know the difference between ++i and i++, in this case you'd want to use the latter. See: difference between ++i and i++.

for (var i = 0; i < data.length; i++) {

All you need to do after that is add a check in your if statement for the column in question. Note: you could define this separately like you've done for the other variables. I'll provide 2 examples and you can pick which you'd prefer to use, both will function the same.

//option 1
if (emailStatus !== EMAIL_DRAFTED && clientEmail && row[0]) { 

//option 2
var checkData = row[0];
if (emailStatus !== EMAIL_DRAFTED && clientEmail && checkData) {

In the end your code should look something like this:

var EMAIL_DRAFTED = "EMAIL DRAFTED";

function draftMyEmails() {
  var sheet = SpreadsheetApp.getActiveSheet(); // Use data from the active sheet
  var startRow = 2;                            // First row of data to process
  var numRows = sheet.getLastRow() - 1;        // Number of rows to process
  var lastColumn = sheet.getLastColumn();      // Last column
  var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn); // Fetch the data range of the active sheet
  var data = dataRange.getValues();            // Fetch values for each row in the range

  // Work through each row in the spreadsheet
  for (var i = 0; i < data.length; i++) {
    var row = data[i];  
    // Assign each row a variable
    var clientName = row[1];                // Col B: Client name
    var clientEmail = row[2];               // Col C: Client email
    var message1 = row[3];                  // Col D: First part of message
    var message2 = row[4];                  // Col E: Second part of message
    var emailStatus = row[lastColumn - 1];  // Col F: Email Status

    // Prevent from drafing duplicates and from drafting emails without a recipient
    if (emailStatus !== EMAIL_DRAFTED && clientEmail && row[0]) { 

      // Build the email message
      var emailBody =  '<p>Hi ' + clientName + ',<p>';
      emailBody += '<p>' + message1 + ', your requested data, ' + message2 + ', is ready.<p>';


      //Send the emaiil
      MailApp.sendEmail(
        clientEmail,            // Recipient
        'Here is your data',    // Subject
        '',                     // Body (plain text)
        {
          htmlBody: emailBody   // Options: Body (HTML)
        }
      );

      sheet.getRange(startRow + i, lastColumn).setValue(EMAIL_DRAFTED); // Update the last column with "EMAIL_DRAFTED"
      SpreadsheetApp.flush(); // Make sure the last cell is updated right away
    }
  }
}

Send email when any value is input into first column Google Script

1 Answers1