2

I have been trying to make a Google App Script code which highlight the cell if it has specific text like "L".

I have made a below code but its not working and when i run this no error appears. i do not know what is the problem.

Can you have a look at it, please that why its not working.

function formatting() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dec'); var range = sheet.getRange("C:AG"); if (range == 'L') { ss.range.setBackgroundColor('#ea9999'); } }
Improve this question

1 Answer 1

Reset to default
1

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:

enter image description here

and specify a custom color with your hex code:

enter image description here

JavaScript References:

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Also note that they may want to use conditional formatting instead of Google Apps Script :)
Thank you for the answer @Marios and grateful that you explained it very briefly. thank you so much for the help.
@Martí true thanks, I added it for the shake of completion
@Strenuous I added a conditional formatting way of doing the same thing.
@Marios Thank you but there is a reason to go with script thank you.