-1

I have a google spreadsheet that periodically updates itself and I would like it to send me email alerts when a keyword is matched. I have drafted a script based on various sources including this.

Example table

function SendEmail() {
  sheet = SpreadsheetApp.openById("1r-OUE562rqo37NyjIbDUUAR3qei2IgTjXHX9UfyQbLE").getSheetByName("Sheet1");
  Logger.log(SpreadsheetApp.getActiveSheet().getName());
  
  if (e.range.columnStart != 6 || e.value != "potential") return;
  const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,6).getValues();
  
  Logger.log(rData)

  let desc = rData[0][2];
  let dates = rData[0][3];
  let status = rData[0][4];
  let now = new Date().toLocaleString("en-UK");
  
  let msg = desc + "\n" + dates + "\n" + now + "\n" + status
  Logger.log(msg);

  GmailApp.sendEmail("myemail@example.com", "Alert title", msg)
}

More info:

  1. The original table is generated by the IMPORTHTML function in google sheets.
  2. The note column (F) is added with the function =IF(OR(ISNUMBER(SEARCH("xxx",C2)),ISNUMBER(SEARCH("xyz",C2))),"potential","NA")

So, once column F returns "potential" indicating that a string is matched in column C, I want it to send an email alert. If it returns "NA", no furhter action is needed. At the end of the day, I would add a daily time trigger to it similar to how often the IMPORTHTML updates itself.

My problem lies with the IF statement in the script. Can someone help me improve or edit it?

I am fairly new to coding in general. Please explain the changes to me, if possible. Been stuck at this for a week now.

Thanks in advance!

Chrystine Yan
  • 1
  • 1

1 Answers1

0

You are not passing the event object.

function SendEmail(e) {
idfurw
  • 5,727
  • 2
  • 5
  • 18