How to reject input 'submit' in "Status" column if "format" or "status" column is empty.

• "Status" column have data validation drop down list so I can't use data validation to reject status input. I'm seeking for google script to do that.
• I need to use column header "status", "format", "Channel" into code to ensure that code working correctly if column index change.
• Show popup mess to warning that user need to fill "channel" and "format" before change status to 'submit'

Update code working correctly

function reject2_onEdit(e) {
  const ss = SpreadsheetApp.getActiveSheet()
  var sheet = e.range.getSheet();
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
  var status_col = headers.indexOf('Status') 1;
  var format_col = headers.indexOf('Format') 1;
  var channel_col = headers.indexOf('Channel') 1;

  if (e.range.getColumn() == status_col) {
    const row = parseInt(e.range.getRowIndex())
    if (e.value == "Submit") {
      if (ss.getRange(parseInt(row), format_col).getValue() == "" || ss.getRange(parseInt(row), channel_col).getValue() == "") {
        // Added the old value
        ss.getRange(row, status_col).setValue(e.oldValue)
        SpreadsheetApp.getUi().alert("PLEASE FILL THE FORMAT AND CHANNEL")
      }
    }
  }
}

CodePudding user response：

You can use the onEdit() trigger so that every time the Status column is edited it checks the status of Channel and Format to make sure they are not empty.

Here is an example:

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSheet()
  ss.getRange(7, 1).setValue(e)
  if (e.range.getColumn() == 4) {
    const row = parseInt(e.range.getRowIndex())
    if (e.value == "Submit") {
      if (ss.getRange(parseInt(row), 2).getValue() == "" || ss.getRange(parseInt(row), 3).getValue() == "") {
        // Added the old value
        ss.getRange(row, 4).setValue(e.oldValue)
        SpreadsheetApp.getUi().alert("PLEASE FILL THE FORMAT AND CHANNEL")
      }
    }
  }
}

Documentation

• onEdit() Trigger
• Google Sheets events
• Classes Range and Sheet