Condition if a range contains a specific color

[Julien PANEVEL]

Hi there,

I am looking to make a script that allows me to format a target cell if in my reference range there is a cell with a specific background color.

So if a cell in my range 'A1:E1' has a red background, then 'F1' takes on the value 'ALERT' with a red font. Otherwise, 'F1' evaluates to 'OK' with a green font.

The following code does not work :

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var sheet = spreadsheet.getActiveSheet();

const red = '#ff0000';

const green = '#00ff00';

var referenceRange = sheet.getRange('A1:E1');

var rangeColor = referenceRange.getBackgroundColor();

var targetRange = sheet.getRange('F1');

if (rangecolor === red)

{

targetRange.setFontColor(red).setValue('ALERTE')

}

  else { targetRange.setFontColor(green).setValue('OK')

};

The problem is that I'm only getting the background color of cell 'A1'.

If I put a red background in A1, it gives me in 'F1': "ALERT";

On the other hand, if there is no red in A1 but there is in the other cells of the range, it gives me in 'F1': "OK".

I tried with getBackgroundColors => 'F1' always returns: "OK" in every case.

Basically, I would like that from the moment there is a red cell in the range, it returns me "ALERT" in 'F1'.

<can you help me ?

[Martin Molloy]

Try this

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rangeColors = sheet.getRange('A1:E1').getBackgrounds()  //rangeColors is a 2d array - even if only 1 row of data

  for(var i in rangeColors){
    var alertYN = 'No'
    for(var j in rangeColors[i]){
      var cellColor = rangeColors[i][j]
      if (cellColor === '#ff0000'){
        alertYN = 'Yes'
        }      
    }
  setAlerte(sheet, alertYN, i)
  }
}

function setAlerte(sheet, alertYN, i){

  const red = '#ff0000';
  const green = '#00ff00';  

  var row = parseInt(i)+1 // make sure that i is a number and first row is 1
  var targetRange = sheet.getRange(row, 6)
 
  if (alertYN == 'Yes')

  {
    targetRange.setFontColor(red).setValue('ALERTE')
  }else {
    targetRange.setFontColor(green).setValue('OK')
  };  

  SpreadsheetApp.flush()
}

It will cope with multiple rows.

--
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/186e524f-6ca3-414f-aece-576df65bd258n%40googlegroups.com.

[Julien PANEVEL]

It's working perfectly !

Thank you so much !

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/9ai9yECRt9s/unsubscribe.
To unsubscribe from this group and all its topics, 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/CAO8s2PhhJgKmfpgY7KLyPvAMWqY8rREOsJL%2BHj2Dpkaud_Xk0A%40mail.gmail.com.

--

          

 Julien PANEVEL

Directeur

Centre Aquatique Communautaire

UNITÉ SPORT LOISIRS

Tel :   05 96 76 58 83

Fax : 05 96 76 58 89

Petit Manoir - 97232 Le Lamentin -  Martinique

                

Protégeons l’environnement, n’imprimons ce document que si nécessaire

[Julien PANEVEL]

ok !

and if i want to apply this code to multirange : for exemple : 'A1:E1', 'A4:E4', 'A8:E8' ?

[Martin Molloy]

The easiest thing to do would be to simply change the range from A1:E1 to A1:E8 - that would work for all 8 rows.

If you want it to skip some rows that's a little more complicated but there are a few ways to do it. Depends on exactly what you want.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAEeZ9mYmFJCinT6CAP_hxk4afiQd4mkGNGAwMAUVQkZWr-txoA%40mail.gmail.com.

[Julien PANEVEL]

okay !

Unfortunately, i've no choice,

It would have been necessary for me that the colors of the range 'A1:E1' return a text in 'E1',

then that the colors of the range 'A4:E4' return another text in 'E4' etc...

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAO8s2Pg0wxqnnd35TGh797Ua%2Bi-O%2BA0rkXRNj8DPoFfwxgNKJg%40mail.gmail.com.

[Martin Molloy]

Hi Julien

Yes it will do that.

It will also return OK or Alerte in row 2, row3,  row5, row6, row7 and row8.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAEeZ9mZkd7KVrzLC6FgHg0VyenpKQDWGjj-ATnLHdRr3Pz3XdA%40mail.gmail.com.