CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user14286902
user14286902

Reputation:

Highlight Rows based on Active Cell But background color shouldn't remove

Using below code which is working perfectly but one one thing is wrong that I have been trying to remove from so long but couldn't find a way to fix the problem.

Issue is that when I run the code and move the cursor to any cell it removes the all background color of active sheet. I don't want to remove the background colors.

Finding a solution moving an cursor on any cell just highlight the row with selected color if I moved to other cell upper or lower, Background color should not be removed.

   function onSelectionChange(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const maxRows = sheet.getMaxRows();
  const maxColumns = sheet.getMaxColumns();
  sheet.getRange(2, 1, maxRows - 1, maxColumns).setBackground(null);
  if (range.getRow() > 1) {
    sheet.getRange(range.getRow(), 1, 1, maxColumns).setBackground("#c9daf8");
  }
}

Upvotes: 1

Views: 210

Answers (1)

Marios
Marios

Reputation: 27350

Explanation - Updated based on OPs comments:

  • OP wants to set the background color of the white cells #ffffff with a blue color #c9daf8.
  • But if the row contains other colors rather than white he wants to keep these background colors and only change the background color of the white cells.
  • One way to achieve that, is to get the current background colors and use map() to replace only the white colors #ffffff with #c9daf8:

const bc = range_row.getBackgrounds().flat().map(c=>c=='#ffffff'?'#c9daf8':c);

Solution:

function onSelectionChange(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const maxRows = sheet.getMaxRows();
  const maxColumns = sheet.getMaxColumns();
  if (range.getRow() > 1) {
    const range_row = sheet.getRange(range.getRow(), 1, 1, maxColumns);
    const bc = range_row.getBackgrounds().flat().map(c=>c=='#ffffff'?'#c9daf8':c);
    range_row.setBackgrounds([bc]);
  }
}

Upvotes: 1

Related Questions