Home > database >  Set Null Value when number (search) not found
Set Null Value when number (search) not found

Time:03-20

I have added this line to the below function sheet.getRange("L3").setValue("LOG Number Exists"); But how to return Blank value (in L3)back when there is no duplicate

Ref: https://docs.google.com/spreadsheets/d/1ngvrwjC9eoLBu0gpLUdskBdkKylhoSPTjBk5sY-V3D8/edit#gid=0

 function onEdit(e) {
    const sheetName1 = "Sheet1"; 
    const sheetName2 = "Sheet2"; 

    const range = e.range;
    const sheet = range.getSheet();
    if (sheet.getSheetName() != sheetName1 || range.getA1Notation() != "D5") return;
    const sheet2 = e.source.getSheetByName(sheetName2);
    const searchValue = range.getValue();
    const m = typeof searchValue == "string" ? Number(searchValue.match(/(\d )/)[1]) : searchValue;
    const search = sheet2.getRange("A2:A"   sheet2.getLastRow()).createTextFinder(`^R${m}$|^${m}$`).useRegularExpression(true). matchEntireCell(true).findAll();

   sheet.getRange("L4").setBackground(search.length > 0 ? "red" : null);
   sheet.getRange("L3").setValue("LOG Number Exists");
  }

CodePudding user response:

In your situation, the following modification is your expected result?

From:

sheet.getRange("L3").setValue("LOG Number Exists");

To:

sheet.getRange("L3").setValue(search.length > 0 ? "LOG Number Exists" : "");
  • In this modification, when the length of search is not 0, "LOG Number Exists" is put. When the length of search is 0, the cell of "L3" is the empty cell.
  • Related