根据Google电子表格中的单元格颜色更改单元格值 [英] Change cell value based on cell color in google spreadsheet

问题描述

我一直在寻找一种方法来改变基于另一种细胞颜色的细胞值（例如，如果细胞颜色是红色的文本）？

一个方法来做到这一点？

我知道有一种方法可以根据单元格值更改单元格颜色，但我想要相反的方式，

任何人都有想法？它是一个脚本或公式

解决方案

Google appscript中有这样的内容，不确定是否有直接公式电子表格，但在这里是：

我将 Sheet1 的 A1 单元格改为红色即#ff0000，然后使用以下代码获得颜色：

  function test（）
 {
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（Sheet1）; 
 var color = sheet.getRange（1，1）.getBackground（）; 
 Logger.log（color）; 
} 
  

输出

＃ff0000

所以，您只需检查 if（color ==＃ff0000）（或任何你想要的颜色的代码）然后设定值。

编辑

以下代码将满足您的要求。我还添加了评论，以便您可以进一步发展。

  function myFunction（）{
 var sheet1 = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（Sheet1）; 
 var sheet2 = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（Sheet2）; 
 
 var data1 = sheet1.getDataRange（）。getValues（）; // sheet1单元格值的二维数组
 var bg; 
 
 for（var i = 1; i< data1.length; i ++）
 {
 bg = sheet1.getRange（i + 1,2）.getBackground（）; 
 if（bg ==＃ff0000）//在else中添加更多颜色if和else如果您有5-6种不同的颜色，则此颜色为红色
 {
 sheet2.getRange （i + 1，3）.setValue（For Verification）; //在sheet2的对应行中设置值
} 
} 
} 
 
 
 / ** 
 *用于待填写记录在将来，如果您是手动
 *填写表格1 
 ** / 
   *设置onEdit的触发器p> 
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
解决方案
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
**/


                        
这篇关于根据Google电子表格中的单元格颜色更改单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！