In Google Sheets onedit function, abort function if variable cell value is not blank

Question

I have a search page driven by checkboxes and an onEdit function in a Google Sheet. It allows users to search a dataset, select a single row (record) from the search results, and append a user code if assuming responsibility for that record. It includes a dialog box asking them if they wish to proceed before the function executes. If yes, it proceeds and resets. If no, it aborts and waits for a new command.

The data setup is:

Col A	Col B	Col C	Col D	Col E
Chkbox	Name	Age	Place	User

The user code data enters in column E of the dataset. What I need is for the onEdit function to abort if another user has already assumed responsibility for that record. In other words, say User A searches and selects a record for "Sarah Stout," and clicks the checkbox to code that as "USER A" to take responsibility for Ms. Stout (and USER A will appear for anyone else's subsequent searches). However, User B has already done the same thing, so in column E, it already says USER B. I want the function to abort so that User A override that from the search page.

I tried:

if (NOT(ISBLANK($E$6))) return;

because the first search result would appear in row 6 (but the first row with a checkbox), but that didn't work. I just don't understand the syntax as well in this, so I don't know how to specify the column. Here's the onEdit code

function onEdit(e) {
if (e.range.columnStart != 1) return;
if (e.value != 'TRUE') return;
var sheet = SpreadsheetApp.getActive();
var ignored_sheets = ['Sheet2','Sheet3','ReportOutput'];
if (ignored_sheets.includes(sheet.getName())) return;
var row_index = e.range.rowStart;
var row = sheet.getRange('B' + row_index + ':D' + row_index).getDisplayValues().flat();
if (row[1] == '') return;
var result = SpreadsheetApp.getUi()
.alert("Do you wish to mark this record?", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
if (result != SpreadsheetApp.getUi().Button.OK) {
sheet.toast("Request canceled.");
sheet.getActiveCell().setValue(false);
return;}
sheet.toast("Request initiated.");
sheet.getActiveCell().setValue(false);
sheet.getRange('B3').clearContent();
sheet.getSheetByName('ReportOutput').appendRow(row);
sheet.getSheetByName('Sheet1').setActiveCell('B3');
}

Does anyone have any ideas on how to make this work? Where am I going wrong?

I know I'll need an IF statement, and I know I'll need something like this if they check the box when they shouldn't:

    {sheet.toast("Unable to overwrite data.");
    sheet.getActiveCell().setValue(false);
    return;}

Answer 1

Suggestion

If I've understood your question clearly, you want your onEdit(e) function to abort running if the value on column E, based on the selected row, already contains a user code.

Sample Code:

You need to add these lines of code after the var sheet = SpreadsheetApp.getActive(); line.

  //If the selected row on column E is not blank
  if(e.range.getSheet().getRange(e.range.getRow(),5).getValue().length > 0){ 
    sheet.toast("Process aborted.\ \""+e.range.getSheet().getRange(e.range.getRow(),5).getValue()+"\" has already taken responsibility for \""+e.range.getSheet().getRange(e.range.getRow(),2).getValue()+"\"");
    sheet.getActiveCell().setValue(false);
    return;
  };

Sample Demonstration

• Sample Sheet:

• Result:

Say "User A" checks the box for Sarah Stout but the column E for Sarah already contains USER B

After a split second