Avoid Having Duplicate Email Everytime Googlesheet is edited by Users (Google Script)

user_16579271 0
Viewed 72

I am new in javascript and still learning. I had made a Spread Sheet as a tracker of work data with multiple users working on it. WorkArea tabWorkArea is where we tag the time and if completed. Another tab is the Error Tab ErrorTAB is a query tab and shows envelope number if tagged incorrectly (no time input and was tagged completed). In the error tab there is a counta formula on cell "S3" which will determine if there an error and will show "TRUE". My problem is that my code is sending too many emails every time users make an edit. If possible, I want it to send email once. Can you help me on this?

Here is the code I made with onEdit trigger

function sendEmail(e) {
  var sp = PropertiesService.getScriptProperties();
  var errorTab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Error');
  var cellRef = errorTab.getRange('S3').getValue();
  var dataRef = "Kindly Check " +  errorTab.getRange('B3').getValue() +  errorTab.getRange('H3').getValue() + errorTab.getRange('N3').getValue();
  var oldValue  = sp.getProperty('S3') || 0;

  if(cellRef == false && cellRef == oldValue) return;
  else 
  var option = {
    cc: "admin1@gmail.com",
  }
  {GmailApp.sendEmail("admin@gmail.com",
  "Dating Contracts Email Notification for Error Tab",
   dataRef,option);
   sp.getProperty('S3', cellRef);
  }
}

google-apps-script google-sheets gmail javascript
1 Answers

From your following replying,

I want your idea that if email sent it will show sent on T.

when you want to put sent to the column "T" (in your situation, the cell "T3") after the email is sent, how about the following modification?

By the way, I think that sp.getProperty('S3', cellRef); occurs an error. Because getProperty is only one argument. I thought that you might have wanted to be sp.setProperty('S3', cellRef);.

Modified script:

function sendEmail() {
  var sp = PropertiesService.getScriptProperties();
  var errorTab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Error');
  var [cellRef, sent] = errorTab.getRange('S3:T3').getValues()[0];
  if (sent == "sent") return;
  var [b3,,,,,,h3,,,,,,n3] = errorTab.getRange('B3:N3').getValues()[0];
  var dataRef = "Kindly Check " + b3 + h3 + n3;
  var oldValue = sp.getProperty('S3') || 0;
  if (cellRef == false && cellRef == oldValue) return;
  var option = { cc: "admin1@gmail.com" }
  GmailApp.sendEmail("admin@gmail.com", "Dating Contracts Email Notification for Error Tab", dataRef, option);
  errorTab.getRange("T3").setValue("sent")
  sp.setProperty('S3', cellRef);
}
user_71086530
Related