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 ofsearch
is 0, the cell of "L3" is the empty cell.