Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – For loop running only for first element – Google Sheets, Google Apps Script

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) {}
}
Tags:

3

Answers


  1. 0

    Understanding the Issue:

    The primary problem with your code is the placement of the return statement within the loop. As soon as the first iteration of the loop is executed, the function returns result, preventing the loop from checking the rest of the phone numbers.

    Corrected Code:

    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";
      }
    }
    return "available";
  } catch (e) {
    // Handle errors here
  }
}

    Explanation of Changes:

    • The return "available"; statement is moved outside the loop. This ensures that the loop iterates through all phone numbers before returning a result.
    • The return "not available"; statement remains within the loop to immediately exit the function if a match is found.
      Using indexOf:
    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);

    var flatNumbers = allNumbers.flat();
    if (flatNumbers.indexOf(newPhone) !== -1) {
      return "not available";
    } else {
      return "available";
    }
  } catch (e) {
    // Handle errors here
  }
}

    Explanation:

    • allNumbers.flat() converts the 2D array into a 1D array, making it suitable for indexOf.
    • indexOf returns the index of the element if found, or -1 if not found.
    • The result is determined based on the indexOf result.

    Which Method is Better:

    • For small datasets, indexOf might be slightly faster.
    • For larger datasets, the loop might be more efficient as it can potentially stop earlier if a match is found.
    • If you need to perform additional operations on each phone number, the loop would be more flexible.
      Ultimately, the best method depends on your specific use case and performance requirements.

    Additional Considerations:

    • Consider using getValues() instead of getDisplayValues() if you’re working with numerical values.
    • Implement proper error handling to catch potential exceptions.
    • Optimize the code further based on the size of your dataset and specific requirements.
      By following these guidelines, you should be able to effectively check if a phone number exists in your Google Sheet.
    Login or Signup to reply.
  2. 0

    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
    }
}
    Login or Signup to reply.
  3. 0

    Others on this page have explained that your loop returns after one iteration

    Array.flat is a useful way to do what you want.

    It is as simple as

    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();
    return allNumbers.flat().includes(newNumber) ? 'not available' : 'available`;
  } catch (e) {}
}

    If you are worried about the execution time, you really should do this

    window.addEventListener('load', () => {
  let allNumbers = []; 
  try { // store the list once
    var ss = SpreadsheetApp.openByUrl("URL");
    var sheet = ss.getSheetByName("sheet name");
    allNumbers = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1).getDisplayValues();
    allNumbers = allNumbers.flat();
  } catch (e) {}

  const numberAvailable = (newPhone) => !allNumbers.includes(newNumber);

  const addNumber = (newPhone) {
    if (numberAvailable(newPhone)) {
      allNumbers.push(newPhone); // update the list used by testing availability
      // write allNumbers to sheet or add newNumber
    } else {
      console.log('Number not available'); // handle existing number
    }
  };
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search