CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Cadell
Cadell

Reputation: 61

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

Upvotes: 2

Views: 2574

Answers (2)

Cooper
Cooper

Reputation: 64042

Select any active range that you wish to search and it will search for the seed in that at range. The seed is currently defaulted to 42 but you can change it.

function findSeedInRange(seed = 42) {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getActiveRange();
  const row = rg.getRow();
  const col = rg.getColumn();
  var found = false;
  rg.getValues().forEach((r, i) => {
    r.forEach((c, j) => {
      if (c == seed) {
        let r = sh.getRange(i + row, j + col).getA1Notation();
        ui.alert(`Found ${seed} in ${r}`);
        found = true;
      }
    })
  })
  if(!found) {
    ui.alert(`Did not find ${seed}`);
  } else {
    ui.alert('That is all.')
  }
}

Here's another approach:

function findSeedInRange() {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getActiveRange();
  const resp = ui.prompt('Enter Seed', 'Enter Seed', ui.ButtonSet.OK_CANCEL)
  if (resp.getSelectedButton() == ui.Button.OK) {
    var seed = parseInt(resp.getResponseText());
    const row = rg.getRow();
    const col = rg.getColumn();
    var found = false;
    rg.getValues().forEach((r, i) => {
      r.forEach((c, j) => {
        if (c == seed) {
          let r = sh.getRange(i + row, j + col).getA1Notation();
          ui.alert(`Found ${seed} in ${r}`);
          found = true;
        }
      });
    });
    if (!found) {
      ui.alert(`Did not find ${seed}`);
    } else {
      ui.alert('That is all.')
    }
  } else {
    ui.alert('Operation cancelled.')
  }
}

Upvotes: 1

Tanaike
Tanaike

Reputation: 201348

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:

Upvotes: 7

Related Questions