CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
abbood
abbood

Reputation: 23548

how to query a google sheet column for a certain value

Let's say i have value 10, and I got a google sheet with first column id that has many values. I would like to run a script that goes through all the values in that column, and once it finds a match, it sets the active selection on that row

Upvotes: 0

Views: 628

Answers (1)

user3717023
user3717023

Reputation:

The following script searches the first column of the active sheet for the value of 10, and sets the selection on that value if found.

function setActive() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var idValues = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues().map(function(a) {
    return a[0];
  });
  var row = idValues.indexOf(10) + 1;
  if (row > 0) {
    SpreadsheetApp.setActiveRange(sheet.getRange(row, 1));
  }
  else {
    throw new Error('Id not found');
  }
}

The array idValues is being flattened before applying indexOf, since a column is returned by getValues as a double array [[1], [2], [3]].

Upvotes: 1

Related Questions