3

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;
}
2
  • 1
    Can you set a breakpoint and identify the line that is actually failing? Commented Jun 1, 2015 at 14:24
  • When it is set up like this it doesn't have a line that is failing, but when google sheets calls the function it says there is a reference error Commented Jun 1, 2015 at 17:43

1 Answer 1

9

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.

Sign up to request clarification or add additional context in comments.

1 Comment

This worked great. I still use 'return LastArray' but I set LastArray[0] to N/A, that way if there aren't any matches to fill the array it will just remain at N/A instead of being an empty array. I'll post the currently working code as an edit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.