Change cell value based on cell color in google spreadsheet

Question

I've been searching for a way to change a cell value (e.g. "Text" if cell color is red) based on another cell color?

Is there a way to do this?

I know there's a way to change a cell color based on cell value, but I want the opposite way,

anyone have idea? be it a script or formula

Answer 1

I did this script to update some colors, but it may give you an Idea on how to do it.

Keep in mind that in Google Sheets, the x,y matrix is always x=row and then y=column. So when you update values you must think first value of the pair is the row and then column, in order to avoid mistakes.

Also, getRange is 1 based and arrays are 0 based.

Finally, getBackground operation is quite expensive because it needs to fetch data from the sheet for each time you call it, if you will run a script over several cells, then it is better to use getBackgrounds() in order to get the matrix of all backgrounds in the range.

/** @OnlyCurrentDoc */
function calculateValues() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("origin")
  var data = sheet1.getDataRange();
  var values = data.getValues();
  var bg = data.getBackgrounds()

  Logger.log("Rows: " + values.length)
  Logger.log("Columns: " + values[0].length)

  for (var y = 0; y < values.length; y++) {

  Logger.log("Row: " + (y+1) + " with length " + values[y].length)

    for (var x = 0; x  < values[y].length; x++) {
      var i = x+1
      var j = y+1

      Logger.log("row, column: " + j + "," + i + " bg: " + bg[y][x])

      if(bg[y][x] == "#34a853") {  // green
        sheet1.getRange(j,i).setValue(2)

      } else if (bg[y][x] == "#fbbc04") {  //yellow
        sheet1.getRange(j,i).setValue(1)

      } else if (bg[y][x] == "#d9d9d9") { //gray
        sheet1.getRange(j,i).setValue(0)

      } else if (bg[y][x] == "#6aa84f" ) { // green 2 
        sheet1.getRange(j,i).setBackground("#34a853")
        sheet1.getRange(j,i).setValue(2)

      } else if (bg[y][x] == "#f1c232"  ) { // yellow 2 
        sheet1.getRange(j,i).setBackground("#fbbc04")
        sheet1.getRange(j,i).setValue(1)
      
      } else if (bg[y][x] == "#b7b7b7") { // gray 2 
        sheet1.getRange(j,i).setBackground("#d9d9d9")
        sheet1.getRange(j,i).setValue(0)
      }
 
    }

  }

}