google-apps-script - "Reference does not exist"执行自定义函数时出错

Question

我正在使用 Google Apps 脚本编写一个函数，似乎我需要解决的最后一个错误是当我调用我的函数时 Google 表格中的“引用不存在”错误。我不知道该怎么办，因为这似乎不是我的代码的问题。

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

在 Google 表格单元格中，我输入 =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 工作表的数组作为参数，我只是认为将工作原型(prototype)放在那里会很好:

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"; 放在脚本的不同位置(将代码一分为二)，您将看到 return LastArray;` 导致了错误。

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

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

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