Question 160380 in Javascript Question posted in
A very good W3school tutorial can be found here.

Can Google AppScript highlight cells based on a condition?

I have an empty column, let’s say Column ‘C’. Column ‘C’ is where a user can add his comments. When he adds his comments in the rows under Column ‘C’, it can be random. For instance:

Loop over column values and Highlight cell with yell

I want to build a function and run that function, so it highlights the cell in yellow only where comments have been added. The function will account for dynamic rows added.

So far I am able to achieve this.

Code.gs

function highlightCellValues () {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var last = sheet.getLastRow();
var column = sheet.getLastColumn();
var results= sheet.getRange(2,1,last,column).getValues();
Logger.log(results);

//Log Results
// [[Fruits, Mango, This is random comment 1], [Sports, Football, ], [Desert, Ice Cream, ], [Movies, Batman Begins, This is random comment 2], [, , ]]

results.forEach(function (value){

var columnValues = value[2];

Logger.log(columnValues);



})
Tags:

2

Answers


  1. 0

    Give a try to this code:

    function highlightCells() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1'); //Replace 'Sheet1' with your sheet name
  const targetRange = sheet.getRange(2,3,sheet.getLastRow()-1); //Selecting range in column 3 from row 2 to last row with values
  const targetValues = targetRange.getValues(); //getting the range values
  const targetBG = targetRange.getBackgrounds(); //Getting range's background colors 

  //Loop through all cells in the range
  for (let i = 0; i < targetValues.length; i++){

    //If cell is not empty. Assign color
    if(targetValues[i][0] !== ""){
       targetBG[i][0] = 'yellow';//you can replace 'yellow with any color you want'
    }else{
       targetBG[i][0] = 'white';//if no value in cell. bg is white
    }

   }

   targetRange.setBackgrounds(targetBG);//Set the backgrounds in the targeted range.

 }
    Login or Signup to reply.
  2. 0

    Try this:

    function onEdit(e) {
  e.source.toast("Entry");
  const sh = e.range.getSheet();
  const shts = ["Sheet1","Sheet2","Sheet3"];
  const idx = shts.indexOf(sh.getName());
  if(~idx && e.range.columnStart == 3 && e.range.rowStart > 1) {
    e.source.toast("Gate1");
    e.range.setBackground((e.value)?"#ffff00":"#ffffff");
  }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search