Sheets notifications for protected tabs

[dalkesm]

I have a Sheet with contents that are accessed by people outside our organization. But they only need access to one of the tabs within the Sheet.  When I add/delete new information in that tab, I'd like those people to receive an email notification that something has been added/deleted. However, if I get them to set up Tools/Notifications, would they also get notifications from the protected/hidden tabs? I don't want them to be getting notifications for everything; just the one tab. And if they get notifications for everything, then I would be constantly trying to protect/unhide those tabs so they did not see the ones they're not supposed to.

If tools/notifications does not work for keeping the other data tabs confidential, is there another way for them to receive an email notification of the added/deleted contents of their tab; without peppering them with notifications every time a change is made to the whole Sheet?

Ty!

[JohnRae · Accepted solution]

I recommend using a script file from Extensions > Apps Script in google sheets and write a custom formula. Here's a script I've written which you can easily modify:

function sendEmailOnEdit(e) {
  const ss = e.source;
  const sheet = ss.getActiveSheet();
  const sName = sheet.getName();

  if (sName === "Book Room") {
    // Fetch email addresses from the "Send Email Notification" sheet
    const emailSheet = ss.getSheetByName("Send Email Notification");
    const emailRange = emailSheet.getRange("A2:A"); // Starting from A2 downwards
    const emails = emailRange.getValues().flat().filter(String).join(",");

    const range = e.range.getA1Notation();
    const subject = `Cell was edited in ${sName}`;
    const message = `The cell in range ${range} on sheet ${sName} was edited. \nNew Value: ${e.value}`;
    
    if (emails) {
      MailApp.sendEmail(emails, subject, message);
    }
  }
}

Note: sName refers to the name of the sheet being monitored for changes. The function getSheetByName fetches the specified sheet where email addresses are listed. 

Check this Guide: Automated Email Notifications on Google Sheets Using Apps Script 

[dalkesm]

Ty for this answer. I will send it on to my co-worker to see if it suits their purposes.