Creating an email notification from a status change.

[NJT]

Hi 

Could you help me with something?

I have a sheet with the following sample page.

The Column titled DCR Status has a drop down menu with three options. Open, Closed and Rejected. When the status changed from open to closed, an email is sent out to the corresponding email in col B. with a subject and text in the email.

So if D2 status is changed to Closed. An email is sent to te...@gmail.com (content of B2). The subject of the email should be "DCR_1 CLOSED".

The text is as below - 

Hi
DCR_1 has been closed. Please check workbook "Data Circuit Update Request (Responses)" for latest update.

BCC Customer Fibre Team"

Any help would be appreciated. Thanks so much.

Regards

[SirDave tmo Soule]

let body = `Hi

DCR_1 has been closed. Please check workbook "Data Circuit Update Request (Responses)" for latest update.

BCC Customer Fibre Team"`;

MailApp.sendEmail('te...@gmail.com', 'DCR_1 CLOSED', body);

[RAKESH SHARMA]

Hi,

please go thro with following mentioned script,

// Trigger to run when the status changes
function onEdit(e) {
  var sheet = e.source.getSheetByName('YourSheetName'); // Replace 'YourSheetName' with the actual name of your sheet
  var range = e.range;
 
  // Check if the edited cell is in the DCR Status column (assuming DCR Status is in column D)
  if (range.getColumn() == 4) {
    var status = e.value.toLowerCase();
    var email = sheet.getRange(range.getRow(), 2).getValue(); // Assuming email is in column B
    var subject = "DCR_" + sheet.getRange(range.getRow(), 1).getValue() + " CLOSED"; // Assuming DCR number is in column A

    if (status === "closed") {
      sendEmail(email, subject);
    }
  }
}

// Function to send email
function sendEmail(email, subject) {
  var body = "Hi\n\nDCR has been closed. Please check workbook 'Data Circuit Update Request (Responses)' for the latest update.\n\nBCC Customer Fibre Team";

  MailApp.sendEmail({
    to: email,
    subject: subject,
    body: body,
    bcc: "customer...@example.com" // Replace with the actual BCC email address
  });
}

Please put your sheet name in yellow color & pur your correct mail id .....than run the script ...if any problem contact with me...

Regard's

Rakesh Sharma  

The information contained in this e-mail and any files transmitted with it is confidential and may be subject to legal professional privilege. It is intended solely for the use of the addressee(s).   If you are not the intended recipient of this e-mail, please note that any review, dissemination, disclosure, alteration, printing, copying or transmission of this e-mail and/or any file transmitted with it, is prohibited and may be unlawful.  If you have received this e-mail by mistake, please promptly inform the sender by reply e-mail and delete the material.  Whilst this e-mail message has been swept for the presence of computer viruses, eir does not, except as required by law, represent, warrant and/or guarantee that the integrity of this communication has been maintained nor that the communication is free of errors, viruses, interception or interference.

eircom Limited, Registered as a Branch in Ireland Number 907674.  Incorporated in Jersey Number 116389.  Branch Address: 2 Heuston South Quarter, St. John’s Road West, Dublin 8, D08 Y42N,Ireland.

---

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3b842633-2bc9-4579-9285-6efbb54c7ef5n%40googlegroups.com.

[NJT]

Hi Rakesh

Could you help me out with something. I am getting an error when running this script.

Could you help out? 

This is the error. Something wrong with the source function in line 3.

12:08:16 PM

Error

TypeError: Cannot read properties of undefined (reading 'source')

onEdit

@ EMAIL Notification.gs:3

[Brett Grear]

That script will automatically run when you make an edit on the spreadsheet.  It looks like you've run the script from the script editor.  e.source will only exist if the script runs when you change the data in a cell.

[Naveen Joseph Thomas]

Hi Guys

I have done the change of status in column 4. I am wondering if I need to put in a hyperlink for this sheet? or give acces to the script somehow?

Regards
Naveen

				Naveen J Thomas BCC WORK CONTROLLER
				E: naveenjos...@openeir.ie 2022 Bianconi Avenue, Citywest, Dublin Confidential Regulated Information

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/TL5LK_lHqZU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/15f7441d-599f-47dc-a715-ab5a008fd6e9n%40googlegroups.com.