Google Sheets - Email when cell value equals Y or D?

Would like to get an email when cell N24 on sheet 'Input Info' changes to Y or D, and ignore letter G, if needed for the script.
It'd be nice to have the email say 'I need to edit.' when Y is inputted, and say 'Done editing.' when D is inputted.
Have the script run when they edit the cell, but not run when they edit other cells in that sheet, or workbook.
Also please add ss.getUrl(), so when I open the email, I can just click the link.

I've searched a few sites and tried them, but when they enter another cell, it sends me an email.
Also a few I've tried, I get and error message, probably because google has changed the scripting language for certain variables.
A couple sites said to use a Trigger, but that didn't work either. Even though I pointed it to that function.

Any help would be greatly appreciated.

are you able to provide a link to sample sheet with the script/s that you have tried?

This is my workaround so far. This is what I have so far.

function needToEdit(e) {
  var ss = SpreadsheetApp.getActive();
  var  sheet = ss.getSheetByName("Input Info");

  {
  var valueToCheck = sheet.getRange("O25").getValue();
  if(valueToCheck < 2)
  {
    MailApp.sendEmail("myemail@gmail.com", "Yes I need to edit.", "context");
  }
  }

  {
  var valueToCheck = sheet.getRange("O25").getValue();
    if(valueToCheck > 1)
  {
    MailApp.sendEmail("myemail@gmail.com", "I'm done editing.", "context");
  }
  }
}

I set a trigger to run onOpen. So for now when they put in a Y or D, then they will just have to refresh the page.
Is there a way of setting valueToCheck equal a certain number? I'm not sure how to do something like if and else statements.
And this is what I put in cell O25.

=ifs(O24="Y",1,O24="D",2,O24="G","G",O24="","nil")

I did try this, but it didn't work.

if (cellValue == "Y") {
// Send Alert Email.
MailApp.sendEmail(emailAddress, subject, message1);
 }
if (cellValue == "D") {
// Send Alert Email.
MailApp.sendEmail(emailAddress, subject, message2);
 }

im confused why if someone is going to do an edit that you would want a popup telling them they can do what they were doing.

in any case, what you are talking about is a "pop up alert message".

Before they edit the 'Input Info' spreadsheet. I need to unhide all empty rows running 4 scripts. I tried it as one script, but I believe Google had a time limit on how long a script ran.
Then they will type in names of people in different cells based upon column headings.
So basically when they are done editing, I run the 'hide script' to hide all empty rows.

If you want to DM your gmail address, I'll duplicate the sheet, nothing in it at the moment. Then just give you full access so you can see it.

For people who will be using this workbook, there is no need for them to log in.
They can edit the unprotected cells only. I'll set up a couple different sheets for you.

Anyways that works well. Thanks for you help, it was greatly appreciated.

And when I get an email, I'll be opening up the sheet anyways, so I'll see in that cell what they have typed.
I do have a custom menu that I only see, and I do what I need from there.
I added a goEdit script to the menu (Go Edit), so when I click on it, it clears the content and sends out the ui.alert.

function goEdit(){
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss;
  var sheets = ["Input Info"];
  for(var i in sheets){
    ss = sh.getSheetByName(sheets[i]);
    ss.getRange("N24").clearContent();
    var ui = SpreadsheetApp.getUi();
    ui.alert('You may now edit the "Input Info" sheet');
  }
}

Originally Posted by janmorris

i believe that you should limit edit to only those you have shared the link with (ie: logged in users), otherwise there will be no way of knowing who is accessing the data and changing it, you dont need your data wiped by some clown that shouldnt be accessing it (like someones naughty child, or a disgruntled associate )


now i understand why you need the popup. it sounds like you are adding a lot of functionality to this project.

Basically each person(store) that I share this with, will have their own separate folder.
At year end, I'll duplicate the current year(2021) and rename the duplicate to GWH - 2021. And in the current version, I'd just need to edit like 2 cells.
And for the current 2021 version, I'll rename it to 2022. And the 2022 version I have a script to wipe data that they have inputted. So it;ll be a clean version the next time they use it.

Pros for the user: No need to send them a new link to a new spreadsheet. Just open the existing link.
Cons for me: having to do the duplications and clear data on the current sheet they use, and rename the files, and clear data. Which I don't mind doing. Maybe 2 minutes work per folder.

i have done some testing of the message1/message2 variation, and it seems to be working ok. what i have noticed is that when a change is made, sometimes an error will be presented because App Scripts havent recognised the changes. the process i use to overcome it is to first save the file, then do Debug twice (two times), the second time is like forcing the App Scripts to have another look because you dont believe it ("i couldnt find it".. "ok, i think you are blind, so go and have another look just to make sure" ).

here is the complete script for message1/message2 variation using O24 as the changing reference:

function sendEmailAlert() {
  // Setup Variables
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cellValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Input Info").getRange("O24").getValue();
  var storeValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Input Info").getRange("D1").getValue();
  var sheetname = ss.getActiveSheet().getName();
  var user = Session.getActiveUser().getEmail();
  var emailAddress = 'YOUREMAILADDRESS@gmail.com';
  var subject = 'New Entry: ' + ss.getName();
  var message1 = 'Store ' + storeValue + ' needs to edit. ' + ss.getUrl();
  var message2 = 'Store ' + storeValue + ' is done editing. ' + ss.getUrl();
  // Check Cell Value
  if (cellValue == "Y") {
  // Send Alert Email.
  MailApp.sendEmail(emailAddress, subject, message1);
    }
  if (cellValue == "D") {
  // Send Alert Email.
  MailApp.sendEmail(emailAddress, subject, message2);
 }
}

as before, modify the email address

Originally Posted by janmorris

i have done some testing of the message1/message2 variation, and it seems to be working ok. what i have noticed is that when a change is made, sometimes an error will be presented because App Scripts havent recognised the changes. the process i use to overcome it is to first save the file, then do Debug twice (two times), the second time is like forcing the App Scripts to have another look because you dont believe it ("i couldnt find it".. "ok, i think you are blind, so go and have another look just to make sure" ).

here is the complete script for message1/message2 variation using O24 as the changing reference:

function sendEmailAlert() {
  // Setup Variables
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cellValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Input Info").getRange("O24").getValue();
  var storeValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Input Info").getRange("D1").getValue();
  var sheetname = ss.getActiveSheet().getName();
  var user = Session.getActiveUser().getEmail();
  var emailAddress = 'YOUREMAILADDRESS@gmail.com';
  var subject = 'New Entry: ' + ss.getName();
  var message1 = 'Store ' + storeValue + ' needs to edit. ' + ss.getUrl();
  var message2 = 'Store ' + storeValue + ' is done editing. ' + ss.getUrl();
  // Check Cell Value
  if (cellValue == "Y") {
  // Send Alert Email.
  MailApp.sendEmail(emailAddress, subject, message1);
    }
  if (cellValue == "D") {
  // Send Alert Email.
  MailApp.sendEmail(emailAddress, subject, message2);
 }
}

as before, modify the email address

Yes! This works. I'm pretty sure I tried it the exact same way, but was getting 2 emails.

Awesome! Thanks for all your help! +REP

I believe that's exactly how I had it set up. But for some reason it would send me 2 emails. I'll try again with your code.

So I decided to remove the email notification. Now when they type Y or D, it will run the appropriate function.
I'll be updating the hide and show functions to include all the sheets. I did a quick test before I came up with this and there was no timeout when running that function.

function needEdit() {
  var cellValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Input Info").getRange("N25").getValue();
  if (cellValue == "Y") {
    if(SpreadsheetApp.getActiveSheet().getRange("N25").getValue() == "Y")
     {
      show(); // Launches the script
    }
  }
    if (cellValue == "Y") {
    if(SpreadsheetApp.getActiveSheet().getRange("N25").getValue() == "Y")
     {
      showWeek1(); // Launches the script
    }
  }
  if (cellValue == "D") {
    if(SpreadsheetApp.getActiveSheet().getRange("N25").getValue() == "D")
    {
      hide(); // Launches the script
    }
  }
    if (cellValue == "D") {
    if(SpreadsheetApp.getActiveSheet().getRange("N25").getValue() == "D")
    {
      hideWeek1(); // Launches the script
    }
  }
}

function showWeek1() {
var sheets = ["Day 1","Day 2","Day 3","Day 4","Day 5","Day 6","Day 7","Week 1 A", "Totals (Week 1)", "Total All Weeks"];
for (var i = 0, sLen = sheets.length; i < sLen; i++) {
    var sheet = SpreadsheetApp.getActive()
        .getSheetByName(sheets[i])
    var val = sheet.getRange('a:a')
        .getValues();
    for (var j = 0, vLen = val.length; j < vLen; j++) {
        if (!val[j][0]) sheet.showRows(j + 1)
        }
    }
      var ui = SpreadsheetApp.getUi();
      ui.alert('You may now edit.');   
}
function hideWeek1() {
var sheets = ["Day 1","Day 2","Day 3","Day 4","Day 5","Day 6","Day 7","Week 1 A", "Totals (Week 1)", "Total All Weeks"];
for (var i = 0, sLen = sheets.length; i < sLen; i++) {
    var sheet = SpreadsheetApp.getActive()
        .getSheetByName(sheets[i])
    var val = sheet.getRange('a:a')
        .getValues();
    for (var j = 0, vLen = val.length; j < vLen; j++) {
        if (!val[j][0]) sheet.hideRows(j + 1)
        }
    }
      var ui = SpreadsheetApp.getUi();
      ui.alert('All empty rows are now hidden. You may now edit this workbook');
}

function show() {
      var ui = SpreadsheetApp.getUi();
      ui.alert('You will get a message once all rows are visible.');
}

function hide() {
      var ui = SpreadsheetApp.getUi();
      ui.alert('You will get a message once all rows are hidden.');
}