-1

I'm trying to write a custom function that averages some data in a Google sheet. The function currently converts it to an integer equivalent but rather than have the cell say something like =average(convertToInt(map:data)) I would like to have a single function =averageData(map:data) that would calculate the average in the custom function.

How to use the built-in average function in Sheets from a custom function?

JTKear16
  • 1
  • 1
  • To get the average in this manner you will have to use JavaScript to determine the average to be returned as you cannot use spreadsheet functions in the JavaScript code on the server side. – ScampMichael Apr 10 '16 at 21:16

1 Answers1

2

A custom function is written in Google Apps Script (based on JavaScript), which does not provide access to spreadsheet functions. So, once you chose to use a custom function, it's up to you to implement all of its logic.

I don't know exactly what kind of conversion to integer you had in mind, but if it's rounding, the function could look like this

function averageData(arr) {
  return arr.map(function(a) {return Math.round(a);})
            .reduce(function(a, b) {return a+b;}, 0)
            /arr.length;
}

If it's parsing text to int, then parseInt would replace Math.round.

That said, custom functions are noticeably slower and more fragile than built-in functions. For something as simple as the operation you described, I would not want to use a custom function.