*The parameters (number[],String,String,(class)) don't match the method signature for MailApp.sendEmail.* *Invalid email: d * *DNS error: http://m*

Question

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getActiveSheet();

function onOpen()

{

var menu = [

             {name: "Send Wishes", functionName: "birthdayReminders"}

           ];

            ss.addMenu("Birthday Wishes", menu); 

}

function birthdayReminders()

{

var emailAddress = sheet.getRange("A:A").getValues();

var subject = sheet.getRange("B:B").getValues();

var message = sheet.getRange("C:C").getValues();

var bday = sheet.getRange("D:D").getValues();

var emailStatus = sheet.getRange("E:E").getValues();

var currentDate = new Date();

var sheetLength = sheet.getDataRange().getNumRows();

var str = currentDate.toString();

var res = str.slice(0, 10);

var birthdayImage = sheet.getRange("F:F").getValues();

var imageLoaddata;

try

{

  var j=0;
  
  for (var i=1; i<birthdayImage.length; i++)

  {

    if(!(birthdayImage[i]== "" || birthdayImage[i]== "undefined" || birthdayImage[i]== "null"))

  {

   imageLoaddata = UrlFetchApp

                   .fetch(birthdayImage[i])

                   .getBlob()

                   .setName("imageLoad");

   j++; 

   console.log("image data " +i+imageLoaddata)

  }

else

  {

          console.log("image data first empty else " +i)

  }

  }
  
var k =1;

for(var i=1; i<bday.length; i++)

  {

    console.log("image data  second for" +i+birthdayImage[i]) 

  if (res == bday[i].toString().slice(0,10) && emailStatus[i] != "EMAIL_SENT")

    {

     var imageLoad;

       if(!(birthdayImage[i]== "" || birthdayImage[i]== "undefined" || birthdayImage[i]== "null"))

    {

      console.log("sdsdsdinside");

      imageLoad = UrlFetchApp

                          .fetch(birthdayImage[i])

                          .getBlob()

                          .setName("imageLoad");

      console.log("sdsdsd"+imageLoad);

       }

      else

{
    
     imageLoad = UrlFetchApp
  
                        .fetch(birthdayImage[k])

                        .getBlob()

                        .setName("imageLoad");
   
        k++;

        if(k>j)
       
       {

          k=1;
       
       }
      
}
                          
      MailApp.sendEmail(emailAddress[i], subject[i],"",
                       
 { htmlBody:message[i] +"<BR><BR>"+ "<img src='cid:nlFlag'><BR><BR>Thanks<BR>Hr Team<BR>LatentView",

                      inlineImages:

                      {

                        nlFlag: imageLoad

                      }
});

        sheet.getRange(1 + i, 5).setValue("EMAIL_SENT");

        SpreadsheetApp.flush();
 
    }

  }

 }

  catch(e)

  {

    Logger.log(MailApp.sendEmail("[email protected]", "Birthday Reminder-Delivery Failure ", "Your automatic birthday reminder email failed to send email due to :"+ e));

  }

}

The above is the Google app script to send an automated birthday email to our employees as we use Google Workspace mail service. The script was working fine for more than a year and since April it is not working.

Got a notification in the App script window that this script has been successfully migrated from Rhino to V8 runtime on Apr 20, 2023

I have seen the solution in this forum for this error "The parameters (number[],String,String,(class)) don't match the method signature for MailApp.sendEmail." that the getValues to be changed to getValue.

I tried changing from var emailAddress = sheet.getRange("A:A").getValues(); to var emailAddress = sheet.getRange("A:A").getValue(); but got an error as below.

Your automatic birthday reminder email failed to send email due to :Exception: Invalid email: d

Later changed the getValues to getValue in the entire code and got an error as below.

Your automatic birthday reminder email failed to send email due to :Exception: DNS error: http://m

Any help here is much appreciated.

Answer 1

In your code

var emailAddress = sheet.getRange("A:A").getValues();

returns a 2D array of rows and one columns from your spreadsheet.

So change:

MailApp.sendEmail(emailAddress[i], subject[i],"",{ htmlBody:message[i] +  

To:

MailApp.sendEmail(emailAddress[i][0], subject[i][0],"",{ htmlBody:message[i][0] +  

You may need to do this in other places of your code but this one was obvious.