Google Sheets: Pop up message when a specific value is entered in a column

Question

This is my first go at creating something in Apps Script for Google Sheets and I'm a bit stuck/overwhelmed. In the reading I've done I think what I'm attempting is rather basic. I'm just not getting to the solution, so I need a nudge...

Need: I have a sheet that is tracking equipment that is checked out and returned to a central area. The sheet has multiple pages with identical layouts.

On each page I have one column where (3) different options can be chosen from a dropdown list. IN, OUT, and UNAVAILABLE

For each of the (3) entry options, I would like a popup message to appear when the entry is changed.

So in the range of F2:F100, if the entry is changed to "OUT" from "IN" I would like a pop-up to appear with a message of "Clear all fields to the right." and an "OK" button.

I have found a lot of examples to make a pop up when ANY field is changed, or when a sheet is opened. I'm stuck on limiting these pop-ups to only select fields/ranges/pages

Thank you all for the help.

Answer 1

SOLUTION

You can refer to this sample script below that runs via onEdit trigger, where the pop-up message will only run if any selected cell is under column F & if that cell's value was changed to "OUT" on any sheet on a spreadsheet file:

function onEdit() {
  var ss = SpreadsheetApp.getActive();
  //Check if selected cell is in column F (or col #6) was changed to "OUT"
  if(ss.getActiveCell().getColumn() == 6 & ss.getRange(ss.getActiveCell().getA1Notation()).getValue() == "OUT"){
    SpreadsheetApp.getUi().alert('Clear all fields to the right.');
  }
}

If ever you need to add more conditions like if you only want to run the script to a specific sheet name, you can add ss.getSheetName() == '[Name of the sheet]' on the if condition separated by & sign, as seen on the sample below:

if(ss.getSheetName() == 'Sheet2' & ss.getActiveCell().getColumn() == 6 & ss.getRange(ss.getActiveCell().getA1Notation()).getValue() == "OUT"){
    SpreadsheetApp.getUi().alert('Clear all fields to the right.');
  }

Sample Result:

If a selected cell on col F is changed from IN to OUT via a drop-down option:

A pop-up message "Clear all fields to the right." will show on the sheet