Google 应用套件脚本 - 执行自定义函数时出错"Reference does not exist"

Question

我正在谷歌应用程序脚本中编写一个函数，似乎我需要解决的最后一个错误是；引用不存在"；当我调用函数时，Google Sheets中出现错误。我不知道该怎么办，因为我的代码似乎没有问题。

这就是我的代码现在的样子。它并不完整，因为我需要为用户输入更改它，但这是一个测试。

在谷歌表格单元格中，我在=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;
}

找到的解决方案：这是我的工作代码，使用来自谷歌表单的数组作为参数，我只是觉得把工作原型放在那里会很好：

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;
}

Answer 1

TL；DR函数不应返回空数组。

通过将return "a valid string";放置在脚本中的不同位置(将代码一分为二(，您将看到返回LastArray；`导致错误。

通过在调试器中运行代码，LastArray是一个空数组。

根据实验，空数组不是公式中调用的函数的有效返回值，包含多个值的数组也不是。包含一个整数的数组是有效的。

将var LastArray = [];更改为var LastArray = [1];说明了这一点。