in Google sheets this is very easy trick to find data like lots of number or name we do Copy and use Ctrl+F find and we do Ctrl-V so in google sheets we can easly find same data like I like google sheets script that when we do Ctrl+C then auto hilight that same data is avalbale in google sheets Test File

Thank you so much for your time

in Google sheets this is very easy trick to find data like lots of number or name we do Copy and use Ctrl+F find and we do Ctrl-V so in google sheets we can easly find same data like I like google sheets script that when we do Ctrl+C then auto hilight that same data is avalbale in google sheets Test File

Thank you so much for your time

• google-sheets
• google-apps-script

Share Improve this question Follow asked Mar 12 at 7:11 Sanjay DevaniSanjay Devani 3177 bronze badges 4

• Not possible with apps script. Maybe possible with browser add-ons/tamper monkey scripts. – TheMaster Commented Mar 12 at 7:14
• may be possible check this out stackoverflow/questions/77264374/… – Sanjay Devani Commented Mar 12 at 7:46
• Yes, there is onSele!tion trigger, but there's no way to capture the CTRL+C key. But it might still be possible to capture other key shortcuts(like Ctrl+alt+shift+1) using macro developers.google/apps-script/guides/sheets/macros – TheMaster Commented Mar 12 at 8:56
• The cell at the top of Column J is selected. When you did this manually, did you enter the search value in this cell and then CTRL-C and CTRL-V to drive the "Find"? And do you propose the same approach for a script? – Tedinoz Commented Mar 14 at 5:53

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Highlighted same value on selected cell

Instead of using Crtl + C you may also try this solution using onSelectionChange trigger to automatically highlight the matching value when you select a cell.

For highlighted cells #FFFF00 (yellow)

for (let i = 0; i < values.length; i++) {
      for (let j = 0; j < values[i].length; j++) {
        backgroundColors[i][j] = values[i][j] == selectedValue ? "#FFFF00" : "#FFFFFF";
      }
    }

Reset all cells to white if a blank cell is selected

for (let i = 0; i < values.length; i++) {
      for (let j = 0; j < values[i].length; j++) {
        backgroundColors[i][j] = "#FFFFFF";
      }
    } 

Script used :

function onSelectionChange(e) {
  const sheet = e.range.getSheet();
  const selectedValue = e.range.getValue();
  const range = sheet.getDataRange();
  const values = range.getValues();
  const backgroundColors = range.getBackgrounds(); 

  if (selectedValue === "") {
  
    for (let i = 0; i < values.length; i++) {
      for (let j = 0; j < values[i].length; j++) {
        backgroundColors[i][j] = "#FFFFFF"; 
      }
    }
  } else {
    for (let i = 0; i < values.length; i++) {
      for (let j = 0; j < values[i].length; j++) {
        backgroundColors[i][j] = values[i][j] == selectedValue ? "#FFFF00" : "#FFFFFF";
      }
    }
  }

  range.setBackgrounds(backgroundColors);
}

Set Up the Trigger :

• Click the clock icon (Triggers).

• Add a new trigger.

• Choose onSelectionChange.

• Set event type to On Edit.

• Click Save.

Sample Output

Note: I uploaded this GIF to show a sample result.

You want to enter, or copy/paste, a value into a cell, and have all cells containing that value to be highlighted.

Consider the following script:

• it is triggered by an installable onEdit trigger. Op must do this.
• The OP enters (or copy/paste) a value into cell J2

---

function searchNumber(e) {
  // Logger.log(JSON.stringify(e)) // DEBUG
  const sheetName = "Sheet3"; // This is from your provided Spreadsheet.
  const targetRow = 2
  const targetCol = 10
  const targetColor = "#FFFF00"

  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() == sheetName && range.rowStart == targetRow && range.columnStart == targetCol){
    // valid edit
    Logger.log("edit on the correct sheet and cell")

    // get the data range
    var dataRange = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn())
    // Logger.log("DEBUG: the data range = "+dataRange.getA1Notation())

    // reset the sheet background colours
    dataRange.setBackground("#FFFFFF")
    // Logger.log("DEBUG: reset the background colours")

    // get the values
    const dataValues = dataRange.getValues();
    // Logger.log("values = "+dataValues) // DEBUG
    // Logger.log("data values rows = "+dataValues.length+", data values columns = "+dataValues[0].length) // DEBUG

    // get the edited value
    const editedValue = range.getValue()
    // Logger.log("DEBUG: editedValue = "+editedValue)

    // loop through the sheet looking for a value matching the edited value
    for (let i = 0; i < dataValues.length; i++) {
      for (let j = 0; j < dataValues[0].length; j++) {
        if (dataValues[i][j] == editedValue ){
            // Logger.log("DEBUG: i:"+i+", j:"+j+", data value= "+dataValues[i][j])
            sheet.getRange(i+1,j+1).setBackground(targetColor)
        } 
      }
    }
  }
  else{
    // not a valid edit
    Logger.log("edit in an invalid cell")
  }

  // Logger.log("DEBUG: done")

}

---

SAMPLE output