Introducing custom functions for ONLYOFFICE macros

We are excited to introduce custom functions in ONLYOFFICE Docs 8.1. This new feature will significantly enhance your experience with macros in the ONLYOFFICE Spreadsheet Editor.

About custom functions

It is a feature that allows you to insert custom functions for calculating various parameters and formulas. With it you can add specific functions to perform particular calculations, which can then be used in your macro code.

Here is an example of a custom function that calculates the sum of two numbers based on specific conditions:

Here is how we use this function in our macro code:

And the final result is:

Where to find: Spreadsheet Editor > Plugins tab > Macros > Custom functions

More examples

Let’s explore further examples of custom functions:

• Geometric mean
  The geometric mean is a measure of central tendency useful for data sets that involve multiplication or span several orders of magnitude. It is calculated as the Nth root of the product of N numbers:

GM=(∏i=1n​xi​)n1​

Let’s implement a custom function for the geometric mean using JavaScript. We’ll utilize the Math.sqrt method:

(function()
{
   /**
     * Function that calculates the geometric mean of two numbers
     * @customfunction

     * @param {number} num1 The first number.
     * @param {number} num2 The second number.
     * @returns {number} The geometric mean of the two numbers.
    */
    function gm(num1, num2) {
        return Math.sqrt(num1 * num2);
    }
    Api.AddCustomFunction(gm);
})();

As you can see, we used the Api.AddCustomFunction method to add the gm function and make it executable in our macro scope.

Now we will invoke it in our macro code:

(function()
{
var oWorksheet = Api.GetActiveSheet();
oWorksheet.GetRange("A1").SetValue("=GM(16, 36)");
})();

• Harmonic mean
  The harmonic mean is a measure of central tendency that is useful for data sets involving rates or ratios. It is particularly appropriate when the data values are defined in relation to some unit, such as speed (distance per time) or density (mass per volume).

H=∑i=1n​xi​1​n​

Here is the custom function implementation:

(function()
{
    /**
     * Function that calculates the harmonic mean of two numbers
     * @customfunction

     * @param {number} num1 The first number.
     * @param {number} num2 The second number.
     * @returns {number} The harmonic mean of the two numbers.
    */
    function hm(num1, num2) {
        return 2 / ((1 / num1) + (1 / num2));
    }
    Api.AddCustomFunction(hm);
})();

And the macro implementation:

(function()
{
var oWorksheet = Api.GetActiveSheet();
oWorksheet.GetRange("A1").SetValue("=HM(16, 36)");
})();

• Root mean square
  The root mean square (RMS) is a statistical measure of the magnitude of a varying quantity. It is especially useful in contexts where the values can be positive or negative and you want to measure the overall size without regard to the sign.

RMS=n1​∑i=1n​xi2​​

To construct this custom function we will use the Math.sqrt method again, but with different parameters:

(function()
{
    /**
     * Function that calculates the root mean square (RMS) of two numbers
     * @customfunction

     * @param {number} num1 The first number.
     * @param {number} num2 The second number.
     * @returns {number} The root mean square (RMS) of the two numbers.
    */
    function rms(num1, num2) {
        return Math.sqrt((num1 ** 2 + num2 ** 2) / 2);
    }
    Api.AddCustomFunction(rms);
})();

And we invoke the function in our macro code:

(function()
{
var oWorksheet = Api.GetActiveSheet();
oWorksheet.GetRange("A1").SetValue("=RMS(5, 8)");
})();

Now, let’s see those custom functions in action!

We hope that this newly added feature becomes a valuable asset in your toolkit, streamlining your work flow with the ability to create custom functions tailored to your needs. At ONLYOFFICE, our primary goal is to empower you to excel by implementing innovative tools and features. If you have any questions or suggestions, please don’t hesitate to reach out to us. Your input is highly appreciated, and we are open to discussion and collaboration.