Search code examples
google-apps-scripttriggersgoogle-sheetsgoogle-formseventtrigger

Issue with Google sheet script to email a person based on the name in a cell


I have a sheet that is linked to a form for customer complaints. I want it so that when a complaint is escalated to a certain person that is listed in a cell (column H) it emails that person telling them they have an issue they need to look at. I took parts of another script someone else here wrote and tried to modify it to work but it's only emailing the first person listed in the script no matter what the contents of the cell is. Also there are times where the cell can be blank and no email will need to be sent. Sorry in advance I am very new at this.

function send() {
//Sort entries so most recent is at top of spreadsheet and pause
getSpreadSheet();
Utilities.sleep(500);

//Declare global variables
  var to;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var eaddress = sheet.getRange('H2').getValue().toString();

// Determine who to email
  if (eaddress = "User 1") {
    to = "[email protected]";
  } else if (eaddress = "User 2") {
    to = "[email protected]";
  } else if (eaddress = "User 3") {
    to = "[email protected]";
  } else if (eaddress = "User 4") {
    to = "[email protected]";
  }

// Email sent

var subject = 'Customer Complaint';
var body = 'A customer complaint form has been submitted and escalated to you'
MailApp.sendEmail(to, subject, body);
}

Solution

  • function send()
    {
      //Sort entries so most recent is at top of spreadsheet and pause
      getSpreadSheet();
    
      var to;
      var ss=SpreadsheetApp.getActiveSpreadsheet();
      var sheet=ss.getActiveSheet();
      var eaddress=sheet.getRange('H2').getValue();
      if(eaddress)
      {
        switch(eaddress)
        {
          case 'User 1':
            to="[email protected]"
            break;
          case 'User 2':
            to="[email protected]"
            break;
          case 'User 3':
            to="[email protected]"
            break;
          case 'User 4':
            to="[email protected]"
            break;
        }
        var subject = 'Customer Complaint';
        var body = 'A customer complaint form has been submitted and escalated to you'
        MailApp.sendEmail(to, subject, body);
      }
    }