change background color cell based on value

[Arne Ballegeer]

Good morning
Can someone help me with my starting script. I would like the cells of the first row starting with 'za' and 'zo' to have a different color, eg red. I can loop through the data but can't change the background.

Here's my code snippet

function color() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName('enkel');

const range = sheet.getRange(1, 3, 1, sheet.getLastColumn()).getValues();

//const testRange = sheet.getRange("C1").getValue();

Logger.log(range);

const data = range[0];

Logger.log(data);

for (let i = 0; i < data.length; i++) {

let dag = data[i];

Logger.log(dag);

if (dag.slice(0, 2) === 'za' || dag.slice(0, 2) === 'zo') {

console.log('dit is gelukt');

}

}

}

[Laurie Nason]

Hi,

Any reason you wouldn't use Conditional formatting on that row? That would be very easy to set up and not need a script at all.

Laurie

--
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/4146224d-3413-47d7-a404-af9aa945bb3en%40googlegroups.com.

[Arne Ballegeer]

Hi Laurie

Thanks for your comment.

In my (finished) spreadsheet I would like to have the weekends in a different color for a better overview. See Appendix. This is indeed possible with conditional formatting, but only for the header, while I also want the rows of the weekends in a different color. Unless you have a better tip? I hope I'm a bit clear ;)

grt

Arne

Op woensdag 24 november 2021 om 10:39:59 UTC+1 schreef Laurie Nason:

[Laurie Nason]

Hi Arne,

You can still use conditional formatting to block out the rows beneath the headers - you'll need to use a custom formula to do it.

The custom formula that will do it 

=AND(A$1<>"",OR(weekday(A$1)=1,weekday(A$1)=7))

- now if you are in the middle east like we are and our weekend is on different days, you can use the optional parameter on the weekday function to change it. One other thing that might need to be checked is the order of your conditional formatting - if you are highlighting rows across weekends, and you want the weekend highlighted rather than rows, make sure it's higher up the list of formatting rules.

I hope this is helpful - if it's not please disregard!

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e624d6fa-228f-4dfe-a2c3-a348d5b2522bn%40googlegroups.com.

[Arne Ballegeer]

Hi Laurie

This is very helpful. Thank you!
I wish you a good weekend, whenever it starts with you ;)

grt

Arne

Op donderdag 25 november 2021 om 05:32:36 UTC+1 schreef Laurie Nason: