CODEWITHSUNDEEP
google-apps-scriptsearchbackgroundhighlight
Liepājas Liedaga vidusskola
Liepājas Liedaga vidusskola

Reputation: 89

Remove background color in Google sheets apps script based on search

I have this page which has a search box and upon finding highlights the cell. Upon finding the cell's background is colored yellow for better visualization. What I stumbled upon, is finding how to remove the yellow background once a different search criteria is fired. Below is the whole gs code, whereas the bottom part is of issue:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. It runs automatically when you hand edit the spreadsheet.');
  }
  quickFind_(e);
}

/**
* Finds cells that match the regular expression entered in a magic cell.
*
* @param {Object} e The onEdit() event object.
*/
function quickFind_(e) {
  const sheets = /./i; // use /./i to make the function work in all sheets
  const magicCell = 'C1';
  const sheet = e.range.getSheet();
  if (!e.value
    || e.range.getA1Notation() !== magicCell
    || !sheet.getName().match(sheets)) {
    return;
  }
  let searchFor;
  try {
    searchFor = new RegExp(e.value, 'i');
  } catch (error) {
    searchFor = e.value.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  }
  const magicCellR1C1 = 'R' + e.range.rowStart + 'C' + e.range.columnStart;
  const data = sheet.getDataRange().getDisplayValues();
  const matches = [];
  data
    .forEach((row, rowIndex) => row
      .forEach((value, columnIndex) => {
        if (value.match(searchFor)) {
          const cellR1C1 = 'R' + (rowIndex + 1) + 'C' + (columnIndex + 1);
          if (cellR1C1 !== magicCellR1C1) {
            matches.push(cellR1C1);
          }
        }
      }));
      
  if (matches.length) {
    sheet.getRangeList(matches).activate();
    sheet.getRangeList(matches).setBackground("yellow");
  }
   else  {
    sheet.getRangeList(matches).setBackground(null);
   }
  
}

Upvotes: 2

Views: 3065

Answers (2)

user2782968
user2782968

Reputation: 31

I was able to resolve this one by passing a null to the setBackground function.

wsObj.getRange("C1").setBackground(null);

Upvotes: 3

Yuri Khristich
Yuri Khristich

Reputation: 14502

The most simply way is to clear all backgrounds on the sheet (except two first rows in your case). Here is the function:

function clear_backgrounds() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange().offset(2,0); // offset to keep color of first 2 rows
  var bg = range.getValues();
  for (row in bg) for (col in bg[row]) bg[row][col] = null;
  range.setBackgrounds(bg);
}

You can call the function somewhere in the middle of your script. Something like this:

...
if (matches.length) {
  clear_backgrounds(); // <---— here
  sheet.getRangeList(matches).activate();
  sheet.getRangeList(matches).setBackground("yellow");
}
...

But if you want to keep colors of some cells in the table it needs a more complicated solution.

Update

If you want to clear backgrounds every time when 'C1' is empty you need to change the lines:

  if ( !e.value
    || e.range.getA1Notation() !== magicCell
    || !sheet.getName().match(sheets)) {
    return;
  }

with this:

  if (e.range.getA1Notation() !== magicCell
    || !sheet.getName().match(sheets)) {
    return;
  }
  if (e.value === '') { clear_backgrounds(); return; }

Upvotes: 1

Related Questions