Conditional formatting entire row in Sheets

68 views
Skip to first unread message

rob wilson

unread,
Dec 3, 2023, 1:44:17 PM12/3/23
to Google Apps Script Community
I want to set the background colour of an entire row (or a defined part of the row) in a Google Sheet, if certain conditions exist. I have followed this in Stack Overflow and this in the Reference docs.

My test sheet is shown below:.Screenshot 2023-12-03 18.34.20.png

and the test code is:
Screenshot 2023-12-03 18.37.13.png

The first problem is that the formatting applied is not the selected row range - it only formats from the start of the row until the column before the x is found. I expected it to apply to the range in setRanges().

A second problem is that I have to manually delete the Conditional format rules from the sheet, as they don't get removed when I run the function. Is there a way to delete them?
 via the code?

Thanks for any help!

Keith Andersen

unread,
Dec 3, 2023, 1:49:56 PM12/3/23
to google-apps-sc...@googlegroups.com
What are your conditions that have to be met before turning the row a color?



--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ddb52243-ad29-484d-943a-cec9c92f73e8n%40googlegroups.com.

rob wilson

unread,
Dec 3, 2023, 3:31:37 PM12/3/23
to Google Apps Script Community
It's actually simpler to replace the .when condition with .whenTextContains("x")
But I still get the formatting restricted to the columns to the left of the x characters:

Screenshot 2023-12-03 20.31.13.png

rob wilson

unread,
Dec 3, 2023, 3:41:48 PM12/3/23
to Google Apps Script Community
Update: using SEARCH works! It highlights the row as wanted when I put an x in column B:

.whenFormulaSatisfied('=SEARCH("x",$B3)')

Now I need to get it to test for ISDATE!


rob wilson

unread,
Dec 3, 2023, 4:39:05 PM12/3/23
to Google Apps Script Community
OK I now have it working as I wanted.  It's not quite as simple as I imagined, but here goes.

The code now tests for dates  entered in  4 columns (D through G) .
  • If D (started date) is OK and G (collected date) is not OK then colour columns A through O in light green
  • If dates in D through G are all OK then colour the same range in magenta.

function applyConditionalFormatting() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var rangeToHighlight = sheet.getRange("A3:O900");
//rule1: highlight the row in green if start date entered (column D)
//and no collected date entered (column G)
var rule1 = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=AND(ISDATE($D3),NOT(ISDATE($G3)))')
.setBackground("lightgreen")
.setRanges([rangeToHighlight])
.build();
//rule2: highlight the row in magenta if all dates entered (column D-G)
var rule2 = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=AND(ISDATE($D3),ISDATE($E3),ISDATE($F3),ISDATE($G3))')
.setBackground("magenta")
.setRanges([rangeToHighlight])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule1);
rules.push(rule2);
sheet.setConditionalFormatRules(rules);
}
Screenshot 2023-12-03 21.37.36.png
On Sunday, 3 December 2023 at 18:49:56 UTC Keith Andersen wrote:

Laurie Nason

unread,
Dec 3, 2023, 11:50:27 PM12/3/23
to google-apps-sc...@googlegroups.com
I'm glad you got this sorted out. Though - just using regular conditional formatting would probably have been much simpler than writing a script? (however, maybe less interesting in working out how the script works :-) )
For example - your turning the row green would be the following:
Screenshot 2023-12-04 at 7.44.39 AM.png

And the custom formula for the Magenta would be: 
=AND(NOT(ISBLANK($D2)),NOT(ISBLANK($E2)),NOT(ISBLANK($F2)),NOT(ISBLANK($G2)))
Screenshot 2023-12-04 at 7.45.44 AM.png

You would need to make sure that the magenta rule is above the green one -and that should work for your question. 
Screenshot 2023-12-04 at 7.49.20 AM.png
As an aside - if you are having people type in the dates into the cells - I would also add a "Data Validation" rule where all those cells need to be a " is Valid Date" - then your user can double click the cell and pick a date from the date picker that appears - and they can't put bogus data in there either!

Laurie




--

Laurie


Yousuf Ahmed

unread,
Dec 8, 2023, 5:17:28 AM12/8/23
to google-apps-sc...@googlegroups.com
Please give me editor code I will solve it

Reply all
Reply to author
Forward
0 new messages