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:

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;}

CodePudding user response：

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