“Reference does not exist” error when executing a

问题:

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.

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

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

回答1:

TL;DR The function should not return an empty array.

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.

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.

Changing var LastArray = []; to var LastArray = [1]; demonstrates this.