调试Google表格自定义功能 [英] Debugging a Google Sheets Custom Function
问题描述
我已经在Google表格中定义了以下自定义函数:
$ b
/ **
*通过比较另一列中匹配的单元格,在单元格中使用逗号分隔查找来查找一列中的最大值
*,
*。
*
* @param {string}引用逗号分隔的字符串引用值。
* @param {array} keys要匹配的字符串数组。
* @param {array} values要比较的值的数组。
* @return所有参考值的最大值。
* @customfunction
* /
函数MAX_LOOKUP(引用,键,值){
if(!references ||!keys ||!values)return;
var refs = {};
references.split(/,?/)。forEach(function(key){refs [key] = 1});
var val,max = -Infinity;
for(var i = keys.length; i - ;){
if(refs [keys [i]]&(val = values [i])> max)max = VAL;
}
返回最大值> -Infinity?最大:;
$ b在电子表格中 - 使用类似于 = MAX_LOOKUP(G9 ,A:A,I:I)
- 这总是返回一个空字符串。但是,当我在脚本编辑器中测试它时,它可以正常工作:
function test_MAX_LOOKUP(){
var result = MAX_LOOKUP(
foo,bar,baz,
[no,bar,no,baz,foo,no],
[ 99,42,99,17,1,99]
);
Logger.log(result); //正确的是42
}
很显然,我得到的值从电子表格和我的测试,但我怎么知道是什么问题?如果我在函数中设置了断点,它只会在从脚本编辑器调试时发生。如果我在函数中放入 Logger.log
调用,它们只会在我从脚本编辑器运行时记录下来。
如何查看传递给函数函数的值?
解决方案要查看正在传递的内容,请使用print通过从你的函数返回字符串来调试样式。例如:
函数MAX_LOOKUP(引用,键值){
return JSON.stringify(references);
//返回JSON.stringify(keys);
//返回JSON.stringify(values);
}
这很麻烦,但是您可以在编辑函数之间来回切换返回的内容,保存脚本,然后查看电子表格以查看调试答案。 (提示:在单独的窗口中打开电子表格和脚本编辑器,而不是单独的标签。)
使用上述技术,我们可以看到上面脚本的问题是像 A:A
这样的列范围为每行返回单值数组,而不是单元格的值。脚本需要更改为:
if(refs [keys [i] [0]]&&(val =值[i] [0])> max)max = val;
I have the defined the following custom function in Google Sheets:
/**
* Finds the maximum value in one column
* by comparing cells matched in another column,
* using a comma-delimited lookup in a cell.
*
* @param {string} references Comma-delimited string referencing values.
* @param {array} keys Array of strings to match.
* @param {array} values Array of values to compare.
* @return The maximum value of all referenced values.
* @customfunction
*/
function MAX_LOOKUP(references,keys,values){
if (!references || !keys || !values) return "";
var refs = {};
references.split(/, ?/).forEach(function(key){ refs[key]=1 });
var val,max = -Infinity;
for (var i=keys.length;i--;){
if (refs[keys[i]] && (val=values[i])>max) max=val;
}
return max>-Infinity ? max : "";
}
In the spreadsheet—used like =MAX_LOOKUP(G9,A:A,I:I)
—this always returns an empty string. When I test it in the Script Editor, however, it works correctly:
function test_MAX_LOOKUP(){
var result = MAX_LOOKUP(
"foo, bar, baz",
["no", "bar", "no", "baz", "foo", "no"],
[ 99, 42, 99, 17, 1, 99 ]
);
Logger.log( result ); // Is correctly 42
}
Obviously there's some difference between the values I'm getting from the spreadsheet and my test, but how do I figure out what is the problem? If I set a breakpoint in the function, it only happens when debugging from the Script Editor. If I put Logger.log
calls in the function, they only are logged when I run from the Script Editor.
How can I see what values are being passed to my function function?
解决方案 To see what's being passed, use "print" style debugging by returning strings from your function. For example:
function MAX_LOOKUP(references,keys,values){
return JSON.stringify(references);
//return JSON.stringify(keys);
//return JSON.stringify(values);
}
It's cumbersome, but you can step back and forth between editing the function to change what's returned, saving the script, and then view the spreadsheet to see your debug answer. (Tip: open the spreadsheet and script editor in separate windows, not separate tabs.)
Using the above technique we can see that the problem with the above script is that a column range like A:A
returns single-value arrays for each row, not the value of the cells. The script needs to be changed to:
if (refs[keys[i][0]] && (val=values[i][0])>max) max=val;
这篇关于调试Google表格自定义功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!