App Script Conditional Formatting to apply on sheet by name

Issues with the code:

Three things to mention:

• range is a range object, not a string. In the if condition you are comparing an object of type range with an object of type string. You need to use getValue to get the values of the range object and then compare that with the string L.

• This code will take a lot of time to complete because you have a large range of cells you want to check but also you are iteratively using GAS API methods. As explained in Best Practices it is way more efficient to use batch operations like getValues, getBackgrounds and setBackgrounds.

• Another improvement you can make is to use getLastRow to restrict the row limit of your range since you are looking for non-empty values. There is no reason for checking empty cells after the last row with content.

Google Apps Script Solution:

function formatting() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dec');
  const range = sheet.getRange("C1:AG"+sheet.getLastRow());
  const values = range.getValues();
  const bcolors = range.getBackgrounds();
  const new_bcolors = values.map((r,i)=>r.map((c,j)=>c=='L'?'#ea9999':bcolors[i][j]))
  range.setBackgrounds(new_bcolors)
 }

Google Sheets Solution:

Another idea would be to just create a conditional formatting in Google Sheets:

and specify a custom color with your hex code:

JavaScript References:

• map

• ternary operator