I’m trying to create a search function that looks through multiple columns in my spreadsheet to find rows matching specific criteria. The tricky part is that some of my search criteria might be empty, and when they are, I want to ignore those conditions completely.
Basically, I want to mark rows as “match found” only when they satisfy all the non-empty search conditions. Here’s what I’ve written so far:
function performMultiSearch() {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Sheet");
var criteria1 = activeSheet.getRange("AF25").getValue();
var criteria2 = activeSheet.getRange("AF26").getValue();
var criteria3 = activeSheet.getRange("AF27").getValue();
var dataCol1 = activeSheet.getRange(3,8,activeSheet.getLastRow()).getValues();
var dataCol2 = activeSheet.getRange(3,9,activeSheet.getLastRow()).getValues();
var dataCol3 = activeSheet.getRange(3,10,activeSheet.getLastRow()).getValues();
for (var j = 0, total = dataCol1.length; j < total; j++)
for (var j = 0, total = dataCol2.length; j < total; j++)
{if (criteria1 === "")
(dataCol2[j][0] == criteria2)
else if (dataCol1[j][0] == criteria1)
{if (dataCol2[j][0] == criteria2)
{if (criteria2 === "")
(dataCol3[j][0] == criteria3)
else if (dataCol2[j][0] == criteria2)
{if (criteria3 === "")
else if (dataCol3[j][0] == criteria3)
{activeSheet.getRange(j + 3, 32).setValue("match found")
The code works fine when all three criteria cells contain values, but returns nothing when one or more criteria are blank. How can I fix this logic to properly skip empty search values?
your biggest problem is broken syntax - incomplete if statements and missing braces all over the place. that double for loop declaration will crash before it even runs. fix it by checking each row against all criteria at once instead of nesting everything. the empty criteria issue is easy to solve once you get the structure right.
Your code has a few problems - incomplete conditional logic, syntax errors in the nested ifs, and missing closing braces. Plus the logic isn’t handling empty criteria right.
I’d scrap the nested approach entirely. Instead, create one match condition per row where you check if all non-empty criteria are satisfied. Try something like (criteria1 === "" || dataCol1[j][0] === criteria1) && (criteria2 === "" || dataCol2[j][0] === criteria2) for each condition.
Also caught duplicate for loop declarations - that’ll break execution. You only need one loop going through rows, then inside that loop check all three columns against their criteria for that specific row.
The trick is treating empty criteria as “don’t care” - they automatically pass, while non-empty ones need exact matches.
Your nested if statements are getting messy. I ran into the same thing with a multi-criteria filter - way cleaner to build an array of conditions first, then check them all together. Here’s what worked for me: make boolean variables for each check (like match1 = criteria1 === "" || dataCol1[j][0] == criteria1) then combine with AND logic. Empty criteria automatically return true, filled ones check for matches. Also saw you’ve got two separate for loops - that’ll cause problems. You want one loop handling all three columns per row. The trick is making empty criteria default to “always true” instead of trying to skip them in your conditionals.