Find Value in Google Sheet App Script

Question

I've been searching around for a script that finds text within a range. I found a couple and modified this code for my particular needs. All I'm doing is creating a "login" type system. The user inputs their email (E5) on one spreadsheet and then when I hit the button it runs the script to searches another spreadsheet that contains emails (A1:A) and sub status in B - so total range is (A1:B).

Problem is, the script only works if the email matches cell A1. So if [email protected] is in (E5) or what the user types in, and on the other spreadsheet in A1 [email protected] is there - it works. But if I moved it down to A5 or something else than A1, it no longer works. I'm confused why the for loop isn't continuing to go down the column to look for matching text.

Here's the code I'm using:

function loginFunction() {
  var s = SpreadsheetApp.getActiveSheet();
  //Username is in (E5)
  var email = s.getRange("E5").getValue();
  var data = SpreadsheetApp.openById('Deleted for privacy').getRange("A1:B")
  var value = data.getValues();

  for (var i = 0; i < value.length; i++) {
    if (value[i][0] === email) {
      if (value[i][1] === "yes") {
        //Email is located (col A) and sub (col B) is yes
        Logger.log('Yes');
        return SpreadsheetApp.getUi().alert('Connected!');
      } else {
        //Email is located (col A) and sub (col B) is no
        Logger.log('Not Sub');
        return SpreadsheetApp.getUi().alert('No Subscription Found');
      }
    } else {
      Logger.log('False');
      return SpreadsheetApp.getUi().alert('Not Connected');
      //Email not found
    }
  }
}

TheMaster TheMaster · Accepted Answer · 2018-07-25T20:16:22

You're using return after all three condition checks. You should continue the loop:

Try modifying from:

  } else {
      Logger.log('False');
      return SpreadsheetApp.getUi().alert('Not Connected');
      //Email not found

to

  } else if(i==value.length-1){ //if i is the last value    
      Logger.log('False');
      return SpreadsheetApp.getUi().alert('Not Connected');
      //Email not found

Find Value in Google Sheet App Script

1 Answers