CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tarik Hodzic
Tarik Hodzic

Reputation: 357

Google Sheets script conditional formatting a cell based on value of that cell

I have a Google sheet where one column contains values. It is column number 10 in the sheet and I need the cells of that column to change color if the cell contains the word 'Declined'. I am having issues. Here is what I have tried

var statusColumn = sheet.getRange(10, sheet.getLastRow()-1);
var oValues = statusColumn.getValues();

  for (var i = 0; i < oValues.length; i++) {
    if (oValues[i] == 'Declined'){
      sheet.getRange().setBackGroundColor('yellow');
    }
  }

This does not work. Any help?

Upvotes: 1

Views: 241

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • From It is column number 10 in the sheet, you want to search the values of the column "J".
  • You want to set the background color of cell, when the cell value is Declined.

If my understanding is correct, how about this modification?

Modification points:

  • var statusColumn = sheet.getRange(10, sheet.getLastRow()-1) is one cell. In your situation, you can use getRange(row, column, numRows).
  • You can use setBackground(color) instead of setBackGroundColor().

Modified script:

var statusColumn = sheet.getRange(1, 10, sheet.getLastRow(), 1); // For example, if you want to retrieve the values from row 2, please modify to sheet.getRange(2, 10, sheet.getLastRow(), 1);
var oValues = statusColumn.getValues();
for (var i = 0; i < oValues.length; i++) {
  if (oValues[i][0] == 'Declined'){
    sheet.getRange(i + 1, 10).setBackground('yellow');
  }
}

Note:

  • In your question, the top row of cell you want to retrieve is not shown. So in this modified script, 1 was used.

References:

If I misunderstood your question and this was not the result you want, I apologize. At that time, can you provide a sample Spreadsheet including what you want? I would like to modify the script.

Upvotes: 1

Related Questions