Lookup rows in every sheet then on match, jump to cell

Question

Am trying to get the script to search for a value in sheet 4, range "A2" of the spreadsheet in the rest of the sheets then on match. It jumps to that sheet & row

Got my code working to loop through all sheets & get last row in each sheet. However the if function & jumping to matched cell ain't working.

Appreciate all the help I can get (:

First time using google sheets...

function Lookup() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
//Get Sheet
var sheet = ss.getSheets()[3];
//set row & column
var GCell = sheet.getRange(2, 1);
// read values from set row & column
var CValue = GCell.getValues();
Logger.log(CValue[0][0]);

//Loop all sheets
var allsheets = ss.getSheets();
for (var s in allsheets){
var sheet=allsheets[s]

//Get last row in each sheet
 var ShtID = sheet.getSheetId();
  var lastRow = sheet.getLastRow();

//var range = sheet.getRange("B2");
//Logger.log(range.getRowIndex());



var SchCell; 
for ( j = 1 ; j >= lastRow; j++){
   if(SchCell[j][1] === CValue){
    var Crow = SchCell.getRowIndex();
     ShtID.setActiveCell(ShtID.getRange(Crow, 1));
     Logger.log("Matched Row =" +Crow);
     return;
   }  
}
Logger.log("Last Row" + lastRow);
}

}

Answer 1

• You want to compare "A2" of the sheet ("Sheet5") of index 3 with the value of last row of column "B" for each sheet except for "Sheet5".
• When the value of "A2" of "Sheet5" is the same with the last row of column "B", you want to activate the cell.

I understood what you want as above. If my understanding is correct, how about this modification? I think that there are several answers for your situation. So please think of this as one of them.

Modification points:

• var ShtID = sheet.getSheetId() has no methods of getRange() and setActiveCell, because sheet.getSheetId() returns an integer value.
• SchCell has no values.
• At for ( j = 1 ; j >= lastRow; j++){, this for loop doesn't loop.

Modified script:

function Lookup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //Get Sheet
  var sheet = ss.getSheets()[3];

  //set row & column
  var GCell = sheet.getRange(2, 1);
  // read values from set row & column
  var CValue = GCell.getValues();
  Logger.log(CValue[0][0]);

  // Modified part
  var allsheets = ss.getSheets();
  for (var s = 0; s < allsheets.length; s++) {
    var sheet = allsheets[s];
    if (s != 3) {
      var values = sheet.getRange("B1:B").getValues().filter(String); // Retrieve values of column B
      if (values.length > 0) {
        var v = values[values.length - 1][0]; // Value of last row of column B
        var lastRow = "B" + values.length; // Last row of column B
        if (v == CValue[0][0]) {
          sheet.getRange(lastRow).activate();
          return true;
        }
      }
    }
  };
}

Note:

• In this modified script, when the value which is the same with "A2" of "Sheet5" is found, the cell of the first sheet is activated and focused.
  • In the current stage, several cells of each sheet cannot be activated and focused simultaneously.

References:

• getSheetId()
• activate()

Edit:

• You want to active all cells of column "B" in the first sheet when the cells matched to "A2" of "Sheet5" are found.

If my understanding is correct, how about this? Please modify the modified part to the following script.

// Modified part
var allsheets = ss.getSheets();
for (var s = 0; s < allsheets.length; s++) {
  var sheet = allsheets[s];
  if (s != 3) {
    var values = sheet.getRange("B1:B").getValues().filter(String);
    if (values.length > 0) {
      var ranges = values.reduce(function(ar, e, i) {
        if (e[0] == CValue[0][0]) {
          ar.push("B" + (i + 1));
        }
        return ar;
      }, []);
      if (ranges.length > 0) {
        Logger.log(ranges)
        sheet.setActiveRangeList(sheet.getRangeList(ranges));
        break;
      }
    }
  }
};