How to delete cell with specific value-CodePudding

I've made a code that adds whatever value you put in B2 in a list if cell E2 = "ADD ITEM", i'm trying to make it also delete the same value from the list with the same button action while E2 = "DELETE ITEM". I've already made the part that adds the values and it works fine but i can't progress no the deletion part.

function itens() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var lista = ss.getSheetByName("BASE LAB(TBD)");

    var source = lista.getRange("B2");
    var opt = lista.getRange("F2");

    var patrimonio = source.getValue();
    var option = opt.getValue();

    var lastLineOfData = lista.getDataRange().getLastRow();

    if (option == "ADICIONAR ITEM") {
        lista.getRange(lastLineOfData   1, 1, 1, 1).setValue(patrimonio);
        ss.toast("EQUIPAMENTO ADICIONADO");
    }
}

CodePudding user response：

You can use createTextFinder() to search your chosen range for some text, then iterate through the results and use deleteCells() or clearContent() to delete the data. Using deleteCells() you can shift the rows so you don't have empty rows after deleting the data, while clearContent() will leave empty spaces if that's what you prefer.

Here's a sample:

function removeItem(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lista = ss.getSheetByName("BASE LAB(TBD)");
  var range = lista.getRange("A:A") // the range to be searched
  var textFinder = range.createTextFinder(input)
  var results = textFinder.findAll()
  
  for (var i = results.length-1; i >=0; i--) {
      results[i].deleteCells(SpreadsheetApp.Dimension.ROWS)
  }
}

You can call this function from your current one like this:

function itens() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lista = ss.getSheetByName("BASE LAB(TBD)");

  var source = lista.getRange("B2");
  var opt = lista.getRange("F2");

  var patrimonio = source.getValue();
  var option = opt.getValue();

  var lastLineOfData = lista.getDataRange().getLastRow();

  if (option == "ADICIONAR ITEM") {
    lista.getRange(lastLineOfData   1, 1, 1, 1).setValue(patrimonio);
    ss.toast("EQUIPAMENTO ADICIONADO");
  } else if (option == "DELETE ITEM") {
    removeItem(patrimonio)
  }
}