Change cell value based on cell color in google sp

问题:

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

回答1:

There is something like this in Google appscript, not sure if any direct formula is also available in spreadsheet, but here it is:

I colored A1 cell of Sheet1 as Red i.e. #ff0000 and then got the color using the following code:

function test()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var color = sheet.getRange(1, 1).getBackground();
  Logger.log(color);
}

Output

#ff0000

So, you just have to check if(color == "#ff0000") (or code of any color you want) and then set values.

EDIT

Here is the code that will fulfill your requirements. I have also added the comments so that you can develope it further.

function myFunction() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

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

  for(var i=1; i<data1.length; i++)
  {
    bg = sheet1.getRange(i+1, 2).getBackground();
    if(bg == "#ff0000")  //Add more colors in else if and else if you have 5-6 different colors, this one is for red
    {
      sheet2.getRange(i+1, 3).setValue("For Verification");  //Set value in corresponding row of sheet2
    }    
  }
}


/**
* For to-be filled records in future, you can 
* set a trigger of onEdit if you are manually 
* filling sheet 1
**/