Trying to send email from apps script not functioning-CodePudding

I have been working on a script that will get the recipient email address by finding a match and using that variable in GmailApp. I am not getting it to send the email even though I set it to an installable onedit trigger and gave it the proper permissions. I tried just manually typing in an email address and that did not work either. Can somebody please help me out.

function findDuplicate() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName('Working');
  var vs1 = sh1.getRange('H1:H'   sh1.getLastRow()).getValues().flat();
  var sh2 = ss.getSheetByName('Match');
  var vs2 = sh1.getRange('A1:A'   sh2.getLastRow()).getValues().flat();
  for (let i = 0; i < (vs2.length - 1); i  ) {
    if (vs1[i] == vs2[i]) {
      return sh2.getRange(i   2, 1).getValue()
      
    }
  }
  var mailMatch = sh2;
      var sub = "This is a test email"
      var bod = "Sample body of the email"
      GmailApp.sendEmail(mailMatch, sub, bod);
    }

Example sheet: https://docs.google.com/spreadsheets/d/1OKFoS17le-Y5SAOecoLE4EJxiKqKVjRLRHtMzwHNwxM/edit?usp=sharing

CodePudding user response：

Issues:

You have a return keyword, which will stop function execution the first time it enters the if block, so the code will never reach GmailApp.sendEmail.
You are retrieving column A from sheet Working, whereas you want to retrieve column A from sheet Match: var vs2 = sh1.getRange('A1:A' sh2.getLastRow()).getValues().flat();.
You are assigning a Sheet (sh2) to the variable mailMatch, which you want to use as an argument for GmailApp.sendEmail. This argument should be a string instead (an email address). Therefore, even if your code could reach that line, the method sendEmail would fail because you are not providing valid arguments.

Code sample:

function findDuplicate() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName('Working');
  var vs1 = sh1.getRange('H1:H'   sh1.getLastRow()).getValues().flat();
  var sh2 = ss.getSheetByName('Match');
  var vs2 = sh2.getRange('A2:B'   sh2.getLastRow()).getValues();
  var matchRows = vs2.filter(row => row[0].length && vs1.includes(row[0]));
  matchRows.forEach(row => {
    var mailMatch = row[1];
    var sub = "This is a test email"
    var bod = "Sample body of the email"
    GmailApp.sendEmail(mailMatch, sub, bod);
  });
}

Note:

The sample above will send multiple emails if there are multiple matching names in your data. If you only want to send the first match, you could do this instead:

var mailMatch = vs2.find(row => row[0].length && vs1.includes(row[0]))[1];
var sub = "This is a test email"
var bod = "Sample body of the email"
GmailApp.sendEmail(mailMatch, sub, bod);