Home > Back-end >  A way to search for words in Google sheet and highlight rows containing them
A way to search for words in Google sheet and highlight rows containing them

Time:10-16

I started from a code I found working, but am stuck in the application of it. I have a list of words and if they find match in a sheet file I should colour the background of the cell containing it (or the row containing it). But as I get the values using the SHTvalues.map it seems it cannot be directly used as it produce an array of results. Below the code.

thank you!

 var findText = "FIRST WORD";
 var findText2='SECOND WORD';

var sheet = SpreadsheetApp.openById('23PzZzz23442321212i1rJDnasXD');
var CurrSheet = sheet.getSheetByName('sheetname');
var SHTvalues = CurrSheet.createTextFinder(findText).findAll();
var result = SHTvalues.map(r => ({row: r.getRow(), col: r.getColumn()}));
var SHTvalues = CurrSheet.createTextFinder(findText2).findAll();
var result2 = SHTvalues.map(r => ({row: r.getRow(), col: r.getColumn()}));
console.log(result)
console.log(result2)
sheet.getSheetByName('sheetname').getRange(result).setBackground('yellow');
sheet.getSheetByName('sheetname').getRange(result2).setBackground('yellow');
}

CodePudding user response:

It would probably make more sense to do this with Google Sheets built-in enter image description here

  • Related