Search code examples
google-apps-scriptgoogle-sheets

How can I check if a numerical value is within a range of cells in google sheets?

I would like to find if a certain value is in a range using app scripts for google sheets.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeBikeNumbers = sheet.getDataRange("A5:A5000");
var values = rangeBikeNumbers.getValues();

If I have my range rangeBikeNumbers, how can I check if the number "42" for example is in that range. I have searched for hours now and have beeb unable to find any answer to this. indexOf only seems to return -1, regardless of whether or not the value is in the range.

var indexDataNumber = values.indexOf(42); for example always ends up being -1

Solution

  • I believe your goal as follows.

    • You want to check whether the value of 42 is existing in the range of A5:A5000.

    In this case, I would like to propose to use TextFinder. Because when TexiFinder is used, the process cost is low. Ref By the way, getDataRange has not arguments. From your script, I thought that you might want var rangeBikeNumbers = sheet.getRange("A5:A5000");.

    When this is reflected to your script, it becomes as follows.

    Modified script:

    function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangeBikeNumbers = sheet.getRange("A5:A5000");
  var find = rangeBikeNumbers.createTextFinder("42").matchEntireCell(true).findNext();
  if (find) {

    // In this case, the value of 42 is existing in the range.

  } else {

    // In this case, the value of 42 is NOT existing in the range.

  }
}

    Note:

    • About var indexDataNumber = values.indexOf(42); for example always ends up being -1, I think that the reason of this issue is due to that values is 2 dimensional array. If you want to use this, you can also use the following script.

        function myFunction() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var rangeBikeNumbers = sheet.getRange("A5:A5000");
    var values = rangeBikeNumbers.getValues();

    var find = values.map(([e]) => e).indexOf(42); // of values.flat().indexOf(42);
    if (find > -1) {
      // In this case, the value of 42 is existing in the range.
    } else {
      // In this case, the value of 42 is NOT existing in the range.
    }
  }

    References: