Need script code for sending an email based on Google Sheets cell value

Question

I'm trying to write a script that will generate an email to a specific person whenever a cell on a Google Sheets spreadsheet contains that person's name. It's being built as an assignment tracker. Basically, there are five people that could get assignments. When that name is entered into the cell for that project (my cells for the assignment name are in column C), then I want to send an email to them. I have the email addresses in a separate sheet in the same document. I know how to set up the trigger once this works. I've written the following code (keep in mind two more names need to be added, but you'll get the idea from this). Four things are happening that I don't want. First, it's ignoring the name value. It doesn't matter what info I put into the cell, it sends an email. Second, it sends an email to everyone I add into this list, no matter who the assignment was supposed to be for. Third, it's sending out several emails on the same assignment. I only want the email to be sent out once when the assignment is made. Finally, if the entire row is deleted (I have a script that moves it to another sheet upon completion), it generates the emails again. My code is below. If I'm completely off track, please tell me how to write something that will work. Many thanks!!

function sendEmail() {
  // Sends an email based on assignment
  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ongoing Projects").getRange("C"); 
  var emailAddress = emailRange.getValue();
  // Check if assignment made
  if (emailRange = 'Name here'){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Validation").getRange("D3");
    var emailAddress = emailRange.getValues();
    // Send Alert Email.
    var message = 'You have a new assignment waiting on the Content Project Management Sheet. Please visit the sheet to see your new assignment. '; // Second column
    var subject = 'New Content Development Assignment';
    MailApp.sendEmail(emailAddress, subject, message);}

     if (emailRange = 'Second Name Here'){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Validation").getRange("D2");
       var emailAddress = emailRange.getValues();
       // Send Alert Email.
    var message = 'You have a new assignment waiting on the Content Project Management Sheet. Please visit the sheet to see your new assignment. '; // Second column
    var subject = 'New Content Development Assignment';
       MailApp.sendEmail(emailAddress, subject, message);}

       if (emailRange = 'Third Name Here'){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Validation").getRange("D4");
         var emailAddress = emailRange.getValues();}

    // Send Alert Email.
    var message = 'You have a new assignment waiting on the Content Project Management Sheet. Please visit the sheet to see your new assignment. '; // Second column
    var subject = 'New Content Development Assignment';
    MailApp.sendEmail(emailAddress, subject, message);
    }

Are you able to share a copy of the sheet with dummy data? Also, = is used for assignment, you should be using == or === if you're trying to compare values. — ross

Scot May Scot May · Accepted Answer · 2019-07-02T21:56:40

The problem might be that you are using defining "emailRange" multiple times. "var emailRange = ....." might be overwriting each other. Although you can define variables anywhere in the script, all the variables defined using "var" are defined at once before any code is run on them. So, emailRange is never ever empty, and always passes through your if statement.

I would use multiple variable names, like emailRange, emailRange2, emailRange3, etc. Or, you could create some temporary variables using "let" instead of "var"

Need script code for sending an email based on Google Sheets cell value

2 Answers