CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsrow-number
Mark Bouwman
Mark Bouwman

Reputation: 15

Get Collection Of Cells With A Certain Value Google Sheets

I have a button that I want to click, which will scroll me to a certain position. I've done this in order to get me to row 100:

function ScrollMe(){
    var file = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = file.getActiveSheet();
    var row = 100;
    file.setActiveCell(sheet.getRange(row,1));
}

What I want to do if a find a list of all cells that are in column 'B' that contain (REGEX=>"Version: [0-9]?[0-9]?[0-9][.]?[0-9]?[0-9]? [a-zA-Z]+"), and then go to the last value that this is like. So basically, go to the last cell in column 'B' that starts with "Version: " and then has a single, double, or triple-digit number, a decimal point, and then two numbers after, and then any amounts of letter text after the fact. I want it to look like this:

function ScrollMe(){
    var file = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = file.getActiveSheet();
    //C# lambda
    var row = FindAll(a=> a.COLUMN == 'B' && a.VALUE.RegexMatch("Version: [0-9]?[0-9]?[0-9][.]?[0-9]?[0-9]? [a-zA-Z]+"));
    file.setActiveCell(sheet.getRange(row,1));
}

Upvotes: 1

Views: 125

Answers (1)

Jacques-Guzel Heron
Jacques-Guzel Heron

Reputation: 2598

I assume that you expect the script to find the last cell in the column B that match your regex. If that is the case, you can use this code:

function ScrollMe() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getRange("B:B").getValues();
  var regex = new RegExp(
    'Version: [0-9]?[0-9]?[0-9][.]?[0-9]?[0-9]? [a-zA-Z]+');
  for (var i = 0; i < data.length; i++) {
    if (regex.test(data[i][0])) {
      var lastMatch = i;
    }
  }
  sheet.setActiveRange(sheet.getRange(lastMatch + 1, 2));
}

The previous code used your approach. It will first read the full column B, and after that will iterate to find the last cell that match the regex; and when it finds the cell, it will select it. Please, ask me if you have any doubts about the function.

Upvotes: 1

Related Questions