javascriptgoogle-sheetsgoogle-apps-script

For loop running only for first element


I'm using the below code to check if the input value match with any value of google sheet column.

The logger returns an array of all phone numbers in the specified column, but the for loop works only for the first phone number in the array. And also I would be grateful if you guide how to get the same results using if (allNumbers.flat().indexOf(newPhone) != -1) and which way is better.

function phoneSearch(newPhone) {
  try {
    var ss = SpreadsheetApp.openByUrl("URL");
    var sheet = ss.getSheetByName("sheet name");
    var allNumbers = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1).getDisplayValues();
    Logger.log(allNumbers.length);

    for (var i = 0; i < allNumbers.length; i++) {
      if (allNumbers[i][0] == newPhone) {
        var result = "not available"
      } else {
        var result = "available"
      };
      return result;
    }
  } catch (e) {}
}

Solution

  • Your function always returns from the first iteration of the loop. You should return only if the number is taken, and if it isn't, continue checking the other numbers.

    function phoneSearch(newPhone){
        try {
            var ss = SpreadsheetApp.openByUrl("URL");
            var sheet = ss.getSheetByName("sheet name");
            var allNumbers = sheet.getRange(2,2,sheet.getLastRow() -1,1).getDisplayValues();
            Logger.log(allNumbers.length);
        
            for (var i = 0; i < allNumbers.length; i++) {
                if (allNumbers[i][0] == newPhone) {
                    return "not available"
                }
             }
            
             // If "not available" wasn't returned from the loop, the number is avialable
             return "available";
        } catch(e) {
             // Probably need some more robust treatment here
        }
    }