0

I am trying to get a basic script working where when a certain value is in any cell in a range, an email notification is sent:

The range is 1 column with around 40 rows, with either A or B in them, I want the notification to be sent if any of those values is 'B'.

Ideally, I'd like this to run once per day, so everyday it will check and see if a value is B and then send a notification.

What I have so far is:

function EmailUpdates() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var data = ss.getRange("Pacing!M6:M50").getValues();
  var subject = "Overpacing";
  var message = "Changes are needed";
  if(data == "B");
  {
  MailApp.sendEmail("email.address@gmail.com",subject,message);
  }
}

At the moment the email notification is working fine, however it's not properly checking the if date = b part of the code, and sending off the email regardless of whether A or B are in the cells.

Would be much appreciated if someone with more experience (which is probably 99% of people here) could take a look and spot what's going wrong.

Thanks for any help!

J91
  • 3
  • 1

1 Answers1

0

  • getValues() returns a 2D array

  • Use Array.some to check if "any" of those values is 'B'.

    if (data/*looks like [[m5],[m6],..]*/.some(([m]) => m === "B")){/*Send mail*/}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thanks so much for this, very helpful! I've attempted this but I'm getting errors still- if(data.some(([[m6],[m7]]) => m === "B")) sorry as mentioned I'm very inexperienced here so not entirely sure what has broken here – J91 Jun 23 '22 at 13:00
  • @J91 The code as is will work. You don't have to change anything except add `MailApp.sendEmail()`. If it doesn't, let me know the error thrown. – TheMaster Jun 23 '22 at 13:21
  • This has worked - thank you so much this has saved me hours of time! – J91 Jun 23 '22 at 16:33
  • @J91 Consider accepting the answer by clicking the checkmark on the left of this post. – TheMaster Jun 23 '22 at 16:41