“不存在参考".执行自定义功能时发生错误 [英] "Reference does not exist" error when executing a custom function
问题描述
我正在Google Apps脚本中编写函数，看来我需要解决的最后一个错误是引用不存在".调用函数时Google表格中出现错误.我不知道该怎么办，因为这似乎与我的代码无关.
I am writing a function in Google Apps Script and it seems the last error I need to get around is a "reference does not exist" error in Google Sheets when I call my function. I don't know what to do about this because it doesn't seem to be a problem with my code.
这是我的代码现在的样子.它不完整，因为我需要更改它以供用户输入，但这是一个测试.
This is what my code looks like now. It isn't complete because I need to change it for user input, but this is a test.
在Google表格单元格中，输入=sortingtesting()
In a google sheets cell I type in =sortingtesting()
function sortingtesting()
{
var pInfo1 = ['a','b','c','d','e','f','g','h','i','j','k','l','m','o','p','q','r','s']
var pInfo2 = ['a','b','c','d','e','f','g','h','i','j','k','l','m','o','p','q','r','s']
var pInfo3 = ['a','b','c','d','e','f','g','h','i','j','k','l','m','o','p','q','r','s']
var pWO = ['1','','','2','','','3','4','5','6','7','','','8','','','9','10']
var pSearch = ['c', 'b', 'a']
var WO = [];
var Info1 = [];
var Info2 = [];
var Info3 = [];
var Search = [];
for(var i = 0; i < 18; i++)
WO[i] = pWO[i];
for(var i = 0; i < 18; i++)
{
Info1[i] = pInfo1[i];
}
for(var i = 0; i < 18; i++)
{
Info2[i] = pInfo2[i];
}
for(var i = 0; i < 18; i++)
{
Info3[i] = pInfo3[i];
}
for(var i = 0; i < 1; i++)
Search[i] = pSearch[i];
// Declares secondary storage arrays and their counters
var FinalArray1 = [];
var FinalArray2 = [];
var FinalArray3 = [];
var LastArray = [];
var a = 0;
var b = 0;
var c = 0;
var d = 0;
// loop to run and make all of the cells in the work order row relevant to the work order number
for(var row = 0; row < WO.length; row ++)
{
var counter = row - 1;
while(WO[row] == "")
{
WO[row] = WO[counter];
counter--;
}
}
// loop that goes through saving which work orders meet certain search criteria, each search criteria has its own separate secondary array
for(var row = 0; row < Info1.length; row++)
{
if(Info1[row] == Search[0])
{
FinalArray1[a] = WO[row];
a++;
}
}
for(var row = 0; row < Info1.length; row++)
{
if(Info2[row] == Search[1])
{
FinalArray2[b] = WO[row];
b++;
}
}
for(var row = 0; row < Info1.length; row++)
{
if(Info3[row] == Search[2])
{
FinalArray3[c] = WO[row];
c++;
}
}
// loop to run through and get all the work orders that meet all of the criteria
for(var i = 0; i < FinalArray1.length; i++)
{
for(var j = 0; j < FinalArray2.length; j++)
{
for(var k = 0; k < FinalArray3.length; k++)
{
if(FinalArray3[k] == FinalArray2[j] && FinalArray2[j] == FinalArray1[i])
{
LastArray[d] = FinalArray1[i];
d++;
}
}
}
}
return LastArray;
}
找到的解决方案: 这是我的工作代码，其中包含来自Google工作表的数组作为参数，我只是认为将工作原型放到那里会很好:
Solution Found: This is my working code with arrays coming in from google sheets as parameters and I just thought it would be nice to put the working prototype out there:
function sortingtesting(WO, Info, Search)
{
// Declares secondary storage arrays and their counters
var FinalArray1 = [];
var FinalArray2 = [];
var FinalArray3 = [];
var LastArray = [];
var a = 0;
var b = 0;
var c = 0;
var d = 0;
// loop to run and make all of the cells in the work order row relevant to the work order number instead of being blank
for(var row = 0; row < WO.length; row ++)
{
var counter = row - 1;
while(WO[row] == "")
{
WO[row] = WO[counter];
counter--;
}
}
// loop that goes through saving which work orders meet certain search criteria, each search criteria has its own separate secondary array to store the work orders that meet the criteria
for(var col = 0; col < Info[0].length; col++)
{
for(var row = 0; row < Info.length; row++)
{
if(Info[row][col] == Search[0])
{
FinalArray1[a] = WO[row];
a++;
}
else if(Info[row][col] == Search[1])
{
FinalArray2[b] = WO[row];
b++;
}
else if(Info[row][col] == Search[2])
{
FinalArray3[c] = WO[row];
c++;
}
}
}
LastArray[0] = 'N/A';
// loop to run through and get all the work orders that meet all of the criteria
for(var i = 0; i < FinalArray1.length; i++)
{
for(var j = 0; j < FinalArray2.length; j++)
{
for(var k = 0; k < FinalArray3.length; k++)
{
if(FinalArray3[k] == FinalArray2[j] && FinalArray2[j] == FinalArray1[i])
{
LastArray[d] = FinalArray1[i];
d++;
}
}
}
}
return LastArray;
}
推荐答案
TL; DR该函数不应返回空数组.
TL;DR The function should not return an empty array.
通过将return "a valid string";放置在脚本中的各个位置(将代码一分为二)，您将看到返回LastArray;`导致了错误.
By placing return "a valid string"; in various positions in the script (bisecting the code), you will see that return LastArray;` is causing the error.
通过在调试器中运行代码，LastArray是一个空数组.
By running the code in the debugger, LastArray is an empty array.
根据实验，空数组不是公式中调用的函数的有效返回值，也不是包含多个值的数组.包含一个整数的数组是有效的.
From experiments, an empty array is not a valid return value for a function called in a formula, neither is an array containing multiple values. An array of containing one integer is valid.
将var LastArray = [];更改为var LastArray = [1];即可证明这一点.
Changing var LastArray = []; to var LastArray = [1]; demonstrates this.
这篇关于“不存在参考".执行自定义功能时发生错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
