CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Imtiaz Public
Imtiaz Public

Reputation: 11

search specific cell from sheet and select/highlight in another sheet Google Script

I'm trying to select any cell in Col A and click on the button "Go To List" and it should take me to Sheet2 and hightlight the cell with selected value (I'm basically trying to write a macro co copy a specific cell in col A in Sheet1 and search in Col A in Sheet2.

example I select 1004 in sheet1 and click on the button it should take me to Sheet2 and search for 1004 and highlight/select that specific cell/row is also fine.

I'm stuck with the half code any help would be appreciated.

https://docs.google.com/spreadsheets/d/1PoS9XUK02iIddmCFOFE1QrsmV3UVEzZKJ3TE6VnyP3U/edit?usp=sharing

Upvotes: 0

Views: 64

Answers (2)

Cooper
Cooper

Reputation: 64082

function locateSameDataInAnotherSheet() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Sheet1") 
  const v = sh1.getActiveRange().getValue();
  const sh2 = ss.getSheetByName("Sheet2");
  const vs = sh2.getRange(1,1,sh2.getLastRow()).getValues().flat();
  let idx = vs.indexOf(v);
  if(~idx) {
    sh2.getRange(idx + 1,1).activate();
  } else {
    SpreadsheetApp.getUi().alert("Value not found");
  }
}

enter image description here

Upvotes: 1

Tanaike
Tanaike

Reputation: 201428

When I saw your provided Spreadsheet, I confirmed your current script as follows.

function gototrip() {
 var ss= SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var r= s.getActiveCell();
  var v= r.getValue();
  var s2=ss.setActiveSheet(ss.getSheetByName('Sheet2'), true);
}

Modification points:

  • In your script, when the script is run, "Sheet2" is activated. Unfortunately, the selected value is not searched.

When these points and your goal are reflected in a sample script, how about the following sample script?

Sample script:

function gototrip() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcRange = ss.getActiveRange();
  if (srcRange.isBlank()) return;
  const dstSheet = ss.getSheetByName("Sheet2");
  const dst = dstSheet.getRange("A1:A" + dstSheet.getLastRow()).createTextFinder(srcRange.getValue()).matchEntireCell(true).findNext();
  if (!dst) return;
  dstSheet.getDataRange().setBackground(null);
  dstSheet.activate();
  dst.offset(0, 0, 1, 3).setBackground("red");
}

  • When this script is run, the value is retrieved from the selected cell of "Sheet1", and the retrieved value is searched from column "A" of "Sheet2". When the value is found, after the current background color is reset and the background color of the searched row is changed to red color.

  • By modifying dst.offset(0, 0, 1, 3).setBackground("red");, please modify the background color for your actual situation.

Note:

  • From your provided Spreadsheet, this sample script supposes that there are no same IDs. Please be careful about this.

References:

Upvotes: 0

Related Questions