Custom function inside ARRAYFORMULA

Question

I created a custom function to calculate a distance between two points using their latitude and longitude.

So, if I run a single line formula like this, it works fine:

=greatCircle(C3,D3,E3,F3)

But, trying to apply an ArrayFormula function, to calculate an entire column, doesn't work. Here is the formula:

=ARRAYFORMULA(greatCircle(C3:C132,D3:D132,E3:E132,F3:F132))

Is there any way to run a custom function inside an ArrayFormula?

Here's what I've tried in order to handle arrays, like from here:

function greatCircle(lat1,lon1,lat2,lon2) {
  if (lat1.map) {
    return lat1.map(greatCircle); // Recurse over array if lat1 is array
  } else {
    // Handle single-arguments...
  }
}

Answer 1

You were on the right track with re-using the function. The issue is that Array#map doesn't really work with multiple arrays like you had it. The simplest thing for this is to add the results in an array with a for loop and return it when you're done.

Here's the partial function:

function greatCircle(lat1, lon1, lat2, lon2)
{
  // To check for an array, use Array.isArray
  if (Array.isArray(lat1))
  {
    // Column is actually an array of single-element arrays.
    let result = [];
    for (let i = 0; i < lat1.length; i++)
    {
      // Remember to push an array since we want to return a column
      result.push(
      [ 
        greatCircle(
          // To get a number from the column, you want the first element of the array
          // While we could also have just made this yet another recursive call,
          //  that would have made the code harder to understand
          lat1[i][0], lon1[i][0], 
          lat2[i][0], lon2[i][0]
        ) 
      ]);
    }
      
    return result;
  }
  else if (typeof lat1 === 'number')
  {
    // Your implementation for single-valued arguments:
    // ...
  }
}

Also, there is no need for ARRAYFORMULA when calling custom functions that take arrays. Just this will do:

greatCircle(C3:C132,D3:D132,E3:E132,F3:F132)