I have conditional formatting in my google sheet to fill certain cells with different colors according to their values(dates). I made custom functions GetHex() to get cell color and GetColoredCellValue() to return the cell value with the hex code "#ff0000" but the latter function is not returning anything after I put a cell range in Google Sheet
=GetColoredCellValue("A2:HF2")

我在谷歌工作表中有条件格式，可以根据值（日期）用不同的颜色填充某些单元格。我制作了自定义函数GetHex（）来获取单元格颜色，GetColoredCellValue（）来返回十六进制代码为“#ff0000”的单元格值，但在我将单元格范围放入Google Sheet=GetColoredCellValue（“A2:HF2”）后，后一个函数不会返回任何内容

I've never coded seriously before nor used Excel thoroughly so I'm sorry if my code does not make sense. And thank you in advance!

我以前从未认真编码过，也从未彻底使用过Excel，所以如果我的代码没有意义，我很抱歉。并提前感谢您！

These are the functions i've written.

这些是我写的函数。

function GetHex(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getRange(input);
  var result = cell.getBackground();
  return result
}

function GetColoredCellValue(input){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange(input);
  var cell;
  for(var i=0; i<range.length; i++){
    for(var j=0; j<range[0].length; j++){
      cell = range[i][j];
      if(GetHex(cell)== "#ff0000"){
        return cell.getValues();
      }
    }
  }
}

When i run the code in AppScript the Execution Log shows this error

当我在AppScript中运行代码时，执行日志显示此错误

Execution log
2:08:43 PM Notice Execution started
2:08:43 PM Error

Exception: Invalid argument: range
GetHex @ Code.gs:3

I wanted to get the cell value of cells with "#ff0000" bg color from each row in a different column. And the template for my spreadsheet would look like this.
I attached a screenshot of what my table in spreadsheet would look like as an example.

我想从不同列中的每一行中获得bg颜色为“#ff0000”的单元格的单元格值。我的电子表格模板是这样的。我附上了一张电子表格中表格的截图作为例子。

table example

表格示例

Modification points:

• In your script, range of var range = ss.getRange(input) is a Class Range object. So, range.length returns undefined. I thought that this might be the reason for your current issue.


• And also, when GetHex(cell) is used in a loop, the process cost becomes high.

When these points are reflected in your script, how about the following modification?

当这些点反映在你的脚本中时，下面的修改如何？

Modified script:

function GetColoredCellValue(input) {
  var ss = SpreadsheetApp.getActiveSheet(); // or SpreadsheetApp.getActiveSpreadsheet()
  var range = ss.getRange(input);
  var backgroundColors = range.getBackgrounds();
  var values = range.getValues();
  var dstValues = values.map((r, i) => (r.map((c, j) => backgroundColors[i][j] == "#ff0000" ? c : null)));
  return dstValues;
}

• When this script is used as =GetColoredCellValue("A2:HF2"), the cell values of the background color of #ff0000 are returned.

  当此脚本用作=GetColoredCellValue（“A2:HF2”）时，将返回#ff0000背景色的单元格值。

  
  



• When you want to ignore the cells except for #ff0000, please modify as follows.

  如果要忽略#ff0000以外的单元格，请按如下方式进行修改。

  
  
  
  

  • From

    从…起

    
    

      var dstValues = values.map((r, i) => (r.map((c, j) => backgroundColors[i][j] == "#ff0000" ? c : null)));
    

    
    
  
  

  • To

    到

    
    

      var dstValues = values.flatMap((r, i) => (r.flatMap((c, j) => backgroundColors[i][j] == "#ff0000" ? c : [])));
    

    
    
  
  
  
  

References:

• Class Range


• map()