I’m trying to find a way to delete a row on one sheet based on a row selected by a checkbox on another sheet, using an onEdit function.

The checkbox is in Column A on Sheet1; the row is variable, since Sheet1 is a search page. The unique search result / search string is in Column B. The records that I want to delete from this search page are on a second sheet, Sheet2. In Sheet2, some of the same data is duplicated from Sheet1, but there’s no checkbox. So, the unique identifier in Column B on Sheet1 is Column A on Sheet2, and that’s where I want the deletions to happen. In Sheet2, the Column A values are always unique, so I wouldn't think this calls for an array.

So, to summarize, this script would say: if a row on Sheet1 is checked, look at the value of Column B in that row. Then, search Column A in Sheet2. If matched, deleteRow. So, my question is: How would this script be written?

However, there’s one slight complication, which I hesitate to mention for fear that the question will face closure due to “lack of focus.” (so, if you just want to answer the above and ignore this bit, that's still wonderful!). I'm only asking because I feel like it affects how the script would be coded in the first place. I only want this deletion to happen if Column E on Sheet1 says Indirect. If Column E is blank, or contains other text, then the script runs as follows:

function onEdit(e) {
  if (e.range.columnStart != 1) return;
  if (e.value != 'TRUE') return;
  var sheet = SpreadsheetApp.getActive();
  var ignored_sheets = ['Sheet2','Sheet3'];
  if (ignored_sheets.includes(sheet.getName())) return;
  if(e.range.getSheet().getRange(e.range.getRow(),5).getValue().length > 0){ 
    sheet.toast("Can't overwrite data.");
    sheet.getActiveCell().setValue(false);
    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("Append record?", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if (result != SpreadsheetApp.getUi().Button.OK) {
    sheet.toast("Operation canceled.");
    sheet.getActiveCell().setValue(false);
    return;}
  sheet.toast("Operation complete.");
  sheet.getActiveCell().setValue(false);
  sheet.getRange('B3').clearContent();
  sheet.getSheetByName('Sheet2').appendRow(row);
  sheet.getSheetByName('Sheet1').setActiveCell('B3');
}

CodePudding user response：

As per problem statement you mentioned in comments:-

The third function I want to add is the ability to delete records, if their column E value is "Indirect"

Sample script will look like this :-

function onEdit(e)
{
  const value = e.value
  const range = e.range
  const sheet = range.getSheet();
  const row = range.getRow();
  const column = range.getColumn();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const checkValue = sheet.getRange(row, 5).getValue(); // Column E value
  if(column === 1 && value == 'TRUE' && checkValue === 'Indirect')
  { 
    const ref_IDtoSearch = sheet.getRange(row, 2).getValue(); // ref id in sheet 1
    const targetSheet = ss.getSheetByName('Sheet2')
    const refRange = targetSheet.getRange('A1:A').getValues().flat(); // Range ref column in sheet 2
    const posRow = refRange.indexOf(ref_IDtoSearch)
    if(posRow > -1)
    {
      targetSheet.deleteRow(posRow 1)
    }
  }
}

Incorporate it in your existing onEdit code.

Reference:

deleteRow()