Google Sheets: How to navigate based on cell value matches in a specific column?

Other Questions Google Sheets
1

I’m working on a Google Sheets project and need some help with redirecting users based on input matches. Here’s what I’m trying to do:

I have a script that checks if a user’s input matches any value in column L (starting from L2) of ‘Sheet1’. If there’s a match, it should go to a confirmation page. If not, it goes to an error page.

The problem is, it only works for the first value in column L. So if I have:

L2: Alice
L3: Bob
L4: Charlie
L5: David

It works fine if someone inputs ‘Alice’, but if they input ‘Bob’, ‘Charlie’, or ‘David’, they get the error page even though those names are in the list.

I’ve tried using a for loop to check all the values, but it’s not working as expected. Here’s a simplified version of what I’ve got:

var values = sh.getRange(2, 12, sh.getLastRow()-1, 1).getValues();
var userInput = newRow[1];

for (var i = 0; i < values.length; i++) {
  if (values[i] == userInput) {
    // Go to confirmation page
  } else {
    // Go to error page
  }
}

Any ideas on how to fix this? I’m pretty new to Google Scripts, so any help would be great. Thanks!

2

hey isaac, i think i see the problem. ur loop is checking each value separately and overwriting the result each time. try this instead:

var found = false;
for (var i = 0; i < values.length; i++) {
if (values[i] == userInput) {
found = true;
break;
}
}
if (found) {
// go to confirmation page
} else {
// go to error page
}

this should work for all names in the list. lmk if u need more help!

3

I’ve faced a similar issue before, and I can share what worked for me. Instead of using a for loop, I found that using the built-in indexOf() method was more efficient and less prone to errors. Here’s how you could modify your code:

var values = sh.getRange(2, 12, sh.getLastRow()-1, 1).getValues().flat();
var userInput = newRow[1];

if (values.indexOf(userInput) !== -1) {
  // Go to confirmation page
} else {
  // Go to error page
}

This approach flattens the 2D array returned by getValues() into a 1D array, then uses indexOf() to check if the userInput exists. It’s faster and cleaner than looping through each value manually. Also, it handles empty cells gracefully. Try it and see if it resolves your issue.

4

Your approach is on the right track, but there’s a more efficient way to handle this in Google Sheets. Consider using the MATCH function combined with IFERROR. Here’s a formula you can use in your script:

=IFERROR(MATCH(userInput, Sheet1!L2:L, 0), 0)

This will return the position of the match if found, or 0 if not found. In your script, you can then use:

var matchResult = SpreadsheetApp.getActiveSpreadsheet().getRange(‘Sheet1!L2:L’).getValues();
var match = matchResult.flat().indexOf(userInput) + 2;

if (match > 1) {
// Go to confirmation page
} else {
// Go to error page
}

This method is faster and more reliable for large datasets. It also eliminates the need for manual looping, reducing potential errors.