在自定义函数中获取单元格对象 [英] Get cell object in custom function

问题描述

我具有此功能来获取单元格的字体颜色.测试函数可以正常工作(结果我得到了十六进制代码#ff0000 )，但是当我从Google Spreadsheet调用 get_color()函数时，它返回了#ERROR .这似乎是因为我从函数参数中获得了一个纯字符串值，而不是Range对象.我该如何实现?

I have this function to get the font color of a cell. The test function works fine (as a result I get hex code #ff0000), but when I call the get_color() function from a Google Spreadsheet, it returns #ERROR. This appears to be because I get just a plain string value from the function argument, instead of a Range object. How I could achieve it?

也许有一种更简单的方法来获取文本的字体颜色?

Maybe there is a little bit easier way to get font color of text?

function test_get_color() {
  var targetId = 'xxx'
  var cell = SpreadsheetApp.openById(targetId).getSheetByName('Sheet1').getRange('B7');

  Logger.log(get_color(cell)); 
}

function get_color(cell){
  return cell.getFontColor();
}

推荐答案

当您通过提供范围作为参数来调用自定义函数时，该函数实际上会从该范围接收 values . Google表格/自变量中的自定义函数中对此进行了记录.

When you invoke a custom function by providing a range as a parameter, the function actually receives the values from that range. This is documented in Custom Functions in Google Sheets / Arguments.

针对需要像您一样的实际范围引用的函数的技巧是将范围作为字符串传递.

A hack for functions that require an actual range reference, like yours, is to pass the range as a string.

=get_color( "B7" )

函数将如下所示:

/**
 * Get the color of text in the given cell.
 *
 * @param  {"B32"}  cell  Cell reference, enclosed in quotes.
 * @returns               The text color from the given cell.
 * @customfunction
 */
function get_color( cell ) {
  if (typeof cell !== "string")
    throw new Error( "Cell reference must be enclosed in quotes." );

  var range = SpreadsheetApp.getActiveSheet().getRange( cell );
  return range.getFontColor();
}

注意:注释块为该功能提供了Google表格自动完成帮助的种子.请参阅您知道吗?(Google Apps脚本中的自定义功能)以获取有关自定义功能的更多信息.

Note: The comment block seeds the Google Sheets auto-completion help for this function. See Did you know? (Custom Functions in Google Apps Script) for more about custom functions.

这篇关于在自定义函数中获取单元格对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！