Change cell value based on the cell's background color in Google Sheets

1
votes

In Google Sheets, I would like to change the cell value (in this case to add text) based on the cell's background color. For example, if the cell's background color is green (#62a84f) as in the picture attached, I would like to automatically insert text "g" in the cell. Please see the picture I found the following script but cannot figure out how to get it working:

function myFunction() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Prosinec+Leden");

  var data1 = sheet1.getDataRange().getValues();  //2D array of sheet1 cell values
  var bg;

  for(var i=1; i<data1.length; i++)
  {
    bg = sheet1.getRange(?).getBackground();
    if(bg == "#62a84f")  
    {
      sheet1.getRange(?).setValue("x");  //Set value in corresponding row of sheet1
    }    
  }
}

*Little background info - I work in an elementary school where we evaluate the progress of our pupils every week and it would save us a lot of work to have this script work.

1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

Explanation:

While that's might not be an issue, the color "#62a84f" you selected is not one of the default colors in the color picker menu. In order to test the following code, I used the default #34a853:

enter image description here

But if you are sure that your color is indeed "#62a84f" then go ahead and use that and change the relevant part of my code to your case.

I used double map to iterate over every cell in the rng. The bigger the range the slower the performance of the code. I would highly advice you to use some specific range and not the whole data range.

Namely, you might want to replace:

const rng = sheet1.getDataRange(); with const rng = sheet1.getRange('B2:D10');

assuming that you want to check for green cells in the range 'B2:D10'.

Another reason you would want to use this approach is that you may have some green cells with no content outside of the DataRange and therefore your range won't contain these cells. So think carefully what is your scenario and choose the method that suits you the best.

Solution:

function myFunction() {
  const sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Prosinec+Leden");
  const rng = sheet1.getDataRange();
  const colors = rng.getBackgrounds();
  const values = rng.getValues();
  colors.map((r,i)=>r.map((c,j)=>{
    if(c=='#34a853'){
    values[i][j]="g";
    }}));
  rng.clearContent();
  rng.setValues(values);   
}