Search code examples
javascriptgoogle-apps-scriptgoogle-sheetscustom-function

Custom array function for MIN() in Google Sheets


Since the MIN() function in Sheets only returns a single value and there is no way to make it work with ARRAYFORMULA, I wanted to make a custom function that would take two arrays and compare the values at each entry, and return an array of the minimums. (I know there's a workaround that uses QUERY, but it wasn't going to work for my purposes)

What I have right now will take two arrays with one row and work perfectly. Unfortunately, it breaks when more than one row is introduced. I'm not sure why, so I'm lost on how to move forward. How can I make it work for any size arrays?

When I feed it any two dimensional range, it throws an error:

"TypeError: Cannot set property '0' of undefined"

on this line finalarray[x][y] = Math.min(arr1[x][y], arr2[x][y]);

The current ""working"" code:

function MINARRAY(arr1, arr2) {
  if (arr1.length == arr2.length && arr1[0].length == arr2[0].length)
  {    
      var finalarray = [[]];

      for (x = 0; x < arr1.length; x++)
      {     
        for(y = 0; y < arr1[x].length; y++)
        {
          finalarray[x][y] = Math.min(arr1[x][y], arr2[x][y]);
        }            
      }  
      return finalarray;   
  } 
  else
  {    
    throw new Error("These arrays are different sizes");    
  }
}

Solution

  • finalarray is a 2D array. [[]] sets only the first element of finalarray to a array. It is needed to set all elements of finalarray to a array. Inside the loop, add

    finalarray[x]=[]//set `x`th element as a array or finalarray[x]= finalarray[x] || []
    finalarray[x][y] = Math.min(arr1[x][y], arr2[x][y]);      
    

    Alternatively,

    finalarray[x] = [Math.min(arr1[x][y], arr2[x][y])]