如何在Google Spreadsheets中将范围传递给自定义函数？

function myFunction(range) {
  var firstColumn = range.getColumn();
  // loop over the range
}

=myFunction(A1:A4)

#1 楼

因此，我经过长时间的努力寻找了一个好的答案，这就是我所发现的：


未修改的range参数传递范围内的单元格值，而不是范围本身（如Gergely所述）
例如：执行此操作时，要先在字符串中传递范围（例如：=myFunction(a1:a2)），才能在函数中使用范围，然后使用函数内的以下代码将其转换为范围：

Function myFunction(pRange){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(pRange);
}

最后，如果您希望传递范围的好处（例如智能地将范围引用复制到其他单元格），并且要将其作为字符串传递，则必须使用默认函数，该函数接受范围作为参数并输出可以使用的字符串。我详细介绍了以下两种方法，但我更喜欢第二种方法。

对于所有Google电子表格：=myFunction("a1:a2")

对于新的Google表格：

=myFunction(ADDRESS(row(A1),COLUMN(A1),4)&":"&ADDRESS(row(A2),COLUMN(A2),4))

#2 楼

range被视为javascript的2d数组。您可以使用range.length获得行数，并使用range[0].length获得列数。如果要从r行和c列中获取值，请使用：range[r][c]。

#3 楼

将Range传递给Google电子表格函数时，框架会隐式执行paramRange.getValues()，并且您的函数会以字符串，数字或对象的二维数组（例如Date）接收Range中的值。
Range对象不是传递给自定义电子表格函数。

下面的TYPEOF()函数将告诉您作为参数接收的数据类型。
公式

=TYPEOF(A1:A4)

将这样调用脚本：

function calculateCell() {
  var resultCell= SpreadsheetApp.getActiveSheet().getActiveCell();
  var paramRange= SpreadsheetApp.getActiveSheet().getRange('A1:A4');
  var paramValues= paramRange.getValues();

  var resultValue= TYPEOF(paramValues);

  resultCell.setValue(resultValue);
}

function TYPEOF(value) {
  if (typeof value !== 'object')
    return typeof value;

  var details= '';
  if (value instanceof Array) {
    details+= '[' + value.length + ']';
    if (value[0] instanceof Array)
      details+= '[' + value[0].length + ']';
  }

  var className= value.constructor.name;
  return className + details;
}

#4 楼

可以做到的。可以通过在活动单元格（包含公式的单元格）中解析公式来获得对传递范围的引用。这假定自定义函数是单独使用的，而不是作为更复杂的表达式的一部分使用的：例如=myfunction(A1:C3)而不是=sqrt(4+myfunction(A1:C3))。

此函数返回所传递范围的第一列索引。

function myfunction(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join('!') + ' is not a valid range');
  }

  // everything so far was only range extraction
  // the specific logic of the function begins here

  var firstColumn = range.getColumn();  // or whatever you want to do with the range
  return firstColumn;
}

#5 楼

我在user79865的答案中扩展了这个绝妙的主意，以使其适用于更多情况，并向自定义函数传递了多个参数。

要使用它，请复制以下代码，然后在自定义中函数调用GetParamRanges()像这样，并为其传递函数名称：

function CustomFunc(ref1, ref2) {

  var ranges = GetParamRanges("CustomFunc"); // substitute your function name here

  // ranges[0] contains the range object for ref1 (or null)
  // ranges[1] contains the range object for ref2 (or null)

  ... do what you want

  return what_you_want;
}

下面是返回单元格颜色的示例：

/**
* Returns the background color of a cell
*
* @param {cell_ref} The address of the cell
* @return The color of the cell
* @customfunction
*/
function GetColor(ref) {

  return GetParamRanges("GetColor")[0].getBackground();
}

以下是代码和更多说明：

/**
* Returns an array of the range object(s) referenced by the parameters in a call to a custom function from a cell
* The array will have an entry for each parameter. If the parameter was a reference to a cell or range then 
* its array element will contain the corresponding range object, otherwise it will be null.
*
* Limitations:
* - A range is returned only if a parameter expression is a single reference.
*   For example,=CustomFunc(A1+A2) would not return a range.
* - The parameter expressions in the cell formula may not contain commas or brackets.
*   For example, =CustomFunc(A1:A3,ATAN2(4,3),B:E) would not parse correctly.
* - The custom function may not appear more than once in the cell formula.
* - Sheet names may not contain commas, quotes or closing brackets.
* - The cell formula may contain white space around the commas separating the custom function parameters, or after
*   the custom function name, but not elsewhere within the custom function invocation.
* - There may be other limitations.
* 
* Examples:
*   Cell formula: =CUSTOMFUNC($A)
*   Usage:        var ranges = GetParamRanges("CustomFunc");
*   Result:       ranges[0]: range object for cell A1 in the sheet containing the formula
*
*   Cell formula: =CUSTOMFUNC(3, 'Expenses'!B7)
*   Usage:        var ranges = GetParamRanges("CustomFunc");
*   Result:       ranges[0]: null
*                 ranges[1]: range object for cell B7 in sheet Expenses
* 
*   Cell formula: =sqrt(4+myfunction(A1:C3))
*   Usage:        var ranges = GetParamRanges("MyFunction");
*   Result:       ranges[0]: range object for cells A1 through C3 in the sheet containing the formula
*
*   Cell formula: =CustomFunc(A1+A2, A1, A2)
*   Usage:        var ranges = GetParamRanges("CustomFunc");
*   Result:       ranges[0]: null
*                 ranges[1]: range object for cell A1 in the sheet containing the formula
*                 ranges[2]: range object for cell A2 in the sheet containing the formula
*
* @param {funcName} The name of the custom function (string, case-insensitive)
* @return The range(s) referenced by the parameters to the call
*/
function GetParamRanges(funcName) {
  var ourSheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var re = new RegExp(".+" + funcName + "\s*\((.*?)\)","i");
  var ranges=[]; // array of results

  try {
    var args = formula.match(re)[1].split(/\s*,\s*/) // arguments to custom function, separated by commas
    // if there are no args it fails and drops out here

    for (var i=0; i<args.length; i++) {
      var arg=args[i].split('!'); // see if arg has a sheet name
      try {
        if (arg.length == 1) { // if there's no sheet name then use the whole arg as the range definition
          ranges[i] = ourSheet.getRange(arg[0]);
        }
        else { // if there's a sheet name, use it (after removing quotes around it)
          var sheetName=arg[0].replace(/'/g, '');
          var otherSheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
          ranges[i] = otherSheet.getRange(arg[1]);
        }
      }
      catch(e) { // assume it couldn't be identified as a range for whatever reason
        ranges[i]=null;
      }
    }
  }
  catch(e) {}

  return ranges
}

#6 楼

作为替代方法，受@Lipis注释的启发，您可以使用行/列坐标作为附加参数来调用函数：

=myFunction(B1:C2; ROW(B1); COLUMN(B1); ROW(C2); COLUMN(C2))

在这种形式下，公式可以轻松地复制（或拖动）到其他单元格中，并且将自动调整单元格/范围引用。

您的脚本功能需要这样更新：

function myFunction(rangeValues, startRow, startColumn, endRow, endColumn) {
  var range = SpreadsheetApp.getActiveSheet().getRange(startRow, startColumn, endRow - startRow + 1, endColumn - startColumn + 1);
  return "Range: " + range.getA1Notation();
}

请注意，getRange函数采用startRow，startColumn，然后采用行数和列数-而不是结束行和结束列。因此，算术。

#7 楼

我通过Google Apps脚本区分了Google Spreadsheet中的两个不同的自定义函数：


一个需要API的函数； SpreadsheetApp，（示例）
不进行任何调用的，（示例）

第一个功能几乎可以执行任何操作。除了调用电子表格服务外，它还可以调用GmailApp或Google提供的任何服务。 API调用将减慢该过程。可以传递范围或通过函数检索范围，以访问所有可用的方法。

第二个功能仅限于“电子表格”。在这里，人们可以利用JavaScript来重新处理数据。这些功能通常非常快。传递的范围不过是包含值的2D数组。


在您的问题中，您首先需要调用.getColumn()方法。如上所述，这清楚地表明您需要1类自定义函数。

有关如何设置电子表格和表格以及创建自定义功能的信息，请参见以下答案。

#8 楼

几个月前，我正在研究此问题，并提出了一个非常简单的想法：创建一个新表格，以每个单元格的名称为内容：
单元格A1看起来像：

= arrayformula(cell("address",a1:z500))

将工作表命名为“ Ref”。
然后，当需要引用单元格作为字符串而不是内容的引用时，请使用：

= some_new_function('Ref'!C45)

当然，您需要检查该函数是否传递了字符串（一个单元格）或一维或二维数组。如果得到一个数组，它将具有所有单元格地址作为字符串，但是从第一个单元格以及宽度和高度，您可以弄清楚所需的内容。

#9 楼

我整个上午一直在努力，并看到上述非答案讨论内容的证据。有理智的我都想要传递的单元格的地址，而不是值。答案很简单。无法完成。

我发现了一些变通办法，它们依赖于找出哪个单元格包含公式。很难说这是否会对以上Senseful有帮助。所以我在做什么？

The data.

     [A]      [B]       [C]       [D]     [E]     [F]       [H]
[1] Name      Wins     Losses    Shots   Points   Fouls   Most recent
                                                          WL Ratio
[2] Sophia     4         2         15      7       1         0
[3] Gloria     11        3         11      6       0         0
[4] Rene       2         0         4       0       0         0
[5] Sophia     7         4         18      9       1         1.5

H列是Sophia的（胜利-PrevWins）/（损失-PrevLosses）

（7-4 ）/（4-2）= 1.5

，但是我们不知道Sophia以前出现在哪一行。
如果将A硬编码为名称列，则可以全部使用VLOOKUP来完成。 。
在VLOOKUP之后，我得到了一些#NA（找不到名称）和＃DIV0（分母为零），并用= IF（IF（...））包裹起来，以在这些情况下显示更多可口的文本。 br />现在我的表情非常笨拙，笨拙且难以维护。所以我想要宏扩展（不存在）或自定义函数。

但是当我创建一个辅助对象SubtractPrevValue（cell）时，它接收的是“ 7”而不是B5。
没有内置的方法可以从传递的参数中获取单元格或范围对象。
如果我让用户用手工输入双引号的单元格名称，那么我可以做到...
SubtractPrevValue（“ B5”）。但这确实阻碍了复制/粘贴和相对单元格功能。

但是后来我找到了解决方法。

SpreadsheetApp.getActiveRange（）是包含公式的单元格。
这就是我真正需要知道的全部。行号。
下面的函数获取一个NUMERIC列号，并减去该列中的上一个匹配项。真的很慢。在显示“ Thinking ...”（思考...）时延迟一到两秒钟，所以我回到了难以理解的，难以维护的工作表公式。

#10 楼

除了提供“ Range”对象之外，Google Developers几乎传递了一个随机数据类型。因此，我开发了以下宏来打印输入的值。

function tp_detail(arg)
{
    var details = '';

    try
    {
        if(typeof arg == 'undefined')
           return details += 'empty';

        if (typeof arg !== 'object')
        {
            if (typeof arg == 'undefined')
                return details += 'empty';

            if (arg.map)
            {
                var rv = 'map: {';
                var count = 1;
                for (var a in arg)
                {
                    rv += '[' + count + '] ' + tp_detail(a);
                    count = count + 1;
                }
                rv += '}; '
                return rv;
            }
            return (typeof arg) + '(\'' + arg + '\')';
        }


        if (arg instanceof Array)
        {
            details += 'arr[' + arg.length + ']: {';
            for (var i = 0; i < arg.length; i++)
            {
                details += '[' + i + '] ' + tp_detail(arg[i]) + ';';
            }
            details += '} ';
        }
        else
        {

            var className = arg.constructor.name;
            return className + '(' + arg + ')';
        }
    }
    catch (e)
    {
        var details = '';
        details = 'err : ' + e;
    }


    return details;
}

#11 楼

创建函数并通过范围作为参数：

function fn(input) {
    var cell = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange(SpreadsheetApp.getActiveRange().getFormula().match(/=\w+\((.*)\)/i)[1].split('!'));
    // above ... cell is "range", can convert to array?
    var sum=0;
    for (var i in cell.getValues() ){
        sum = sum + Number(cell.getValues()[i]);
    }  
    return sum;
}

电子表格中的用法：

=fn(A1:A10)

它将显示值如sum(A1:A10)。

#12 楼

我整理了以前的答案

**
 *
 * @customfunction
 **/
function GFR(){
  var ar = SpreadsheetApp.getActiveRange();
  var as = SpreadsheetApp.getActive();
  return ar.getFormula()
    .replace(/=gfr\((.*?)\)/i, '')
    .split(/[,;]/)
    .reduce(function(p, a1Notation){
      try{
        var range = as.getRange(a1Notation);
        p.push(Utilities.formatString(
          'Is "%s" a Range? %s', 
          a1Notation,
          !!range.getDisplayValues
        ));
      } catch(err){
        p.push([a1Notation + ' ' + err.message]);
      }
    return p;
  },[]);
}

它使用当前公式并检查它是否为范围。

=GFR(A1:A5;1;C1:C2;D1&D2)返回

|Is "A1:A5" a Range? true|
|1 Range not found       |
|Is "C1:C2" a Range? true|
|D1&D2 Range not found   |

对于TC的完整组成，可以调用类似的名称

var range = as.getRange(a1Notation);
var column = range.getColumn();