15

I need to run a script triggered by an onedit() to only one sheet of many.

I have tried the following, but currently I can't get the script to work on just the desired sheet ("Inventory") Im sure this will be very simple for someone that knows:

function onEdit(e) {
    var range = e.range;
    if(range.getSheetName() == "Inventory") {
        if(range.getValue() == "notify") {
            range.setBackgroundColor('red');
            var productname = range.offset(0,-3).getValue();
            var productinventory = range.offset(0,-2).getValue();
            var message = "Product variant " + productname + " has dropped to " + productinventory;
            var subject = "Low Stock Notification";
            var emailAddress = "email@email.com";
            MailApp.sendEmail(emailAddress, subject, message);
            range.offset(0,1).setValue("notified");
        }
    }
}

Thanks!

Alistair McMillan
  • 1,160
  • 10
  • 23
adamsportstech
  • 287
  • 2
  • 5
  • 14

2 Answers2

23

try something like this and see if it works:

function onEdit(e) {
    var activeSheet = e.source.getActiveSheet();
    var range = e.range;
    if (activeSheet.getName() !== "Inventory" || e.value !== "notify") return;
    range.setBackgroundColor('red');
    var productname = range.offset(0, -3).getValue();
    var productinventory = range.offset(0, -2).getValue();
    var message = "Product variant " + productname + " has dropped to " + productinventory;
    var subject = "Low Stock Notification";
    var emailAddress = "email@email.com";
    MailApp.sendEmail(emailAddress, subject, message);
    range.offset(0, 1).setValue("notified");
}

Now the script will exit if the active Sheet is not 'Inventory' or the edited value is not 'notify'.

Alistair McMillan
  • 1,160
  • 10
  • 23
JPV
  • 26,499
  • 4
  • 33
  • 48
  • 3
    Although this is a onEdit script, you will have to use an installable trigger (add a trigger > on edit of the spreadsheet) because of the emailing. – JPV Nov 24 '14 at 19:05
  • Thanks JPV, unfortunately this didn't work. It doesn't seem to be recognising the sheet. The rest of the function works fine when used on any sheet as I have already installed the onedit. I just cant seem to get it working on just this one! Thanks for any suggestions! – adamsportstech Nov 24 '14 at 20:15
  • 1
    Did you check if the name in tab doesn't contain extra spaces ? – JPV Nov 24 '14 at 21:24
  • Yeah just checked that, still doesn't seem to work. Gets caught on that first statement still. Any other suggestions? – adamsportstech Nov 24 '14 at 21:34
  • 1
    I just tested the exact script above and it works fine. please dubbelcheck the tab name and also change the function name to something other than onEdit to avoid internal conflict with the simple onEdit. For example installableOnEdit(e) – Serge insas Nov 24 '14 at 21:38
  • Thanks both of you, renaming the function seems to have got it working! – adamsportstech Nov 24 '14 at 21:57
  • Great... it's never a good idea to use onEdit as a function name when it's not a simple onEdit... it tries to run on both ways and one of them inevitably fails... – Serge insas Nov 24 '14 at 22:03
  • Thanks for the heads-up, Serge. – JPV Nov 24 '14 at 22:06
  • Haven't changed any of the code from when it was working, but for some unknown reason this seems to have stopped working for me. Could this be an update etc? Is it working for you both still? Thanks! – adamsportstech Nov 26 '14 at 10:32
  • Remember that Javascript string comparison is case sensitive. Even with ==. – Christiaan Westerbeek Jan 07 '15 at 10:37
  • Hi Guys, any chance you could help with a similar issue on google script: http://stackoverflow.com/questions/28551543/pull-csv-to-google-sheets-with-basic-authentication – adamsportstech Feb 17 '15 at 09:55
  • @JPV Your comment fixed it for me - Apparently normal onEdit run as the user where the actual trigger runs as the developer – Gal Bracha Sep 24 '17 at 15:20
8

I suggest that you try this:

range.getSheet().getSheetName() == "Inventory"
Greenonline
  • 1,330
  • 8
  • 23
  • 31
Eun Kyung Lee
  • 81
  • 1
  • 1