How to handle async API calls inside ONLYOFFICE custom functions

Custom functions are a powerful and flexible way to extend the functionality of the ONLYOFFICE Spreadsheet Editor. As part of our ongoing effort to improve the platform, the recent release of ONLYOFFICE Docs 9.0  adds support for asynchronous API calls within custom functions. In this blog post, we’ll show you how to incorporate them into your own solutions.

About ONLYOFFICE custom functions

Custom functions are a versatile and potent addition to ONLYOFFICE macros in the Spreadsheet Editor. They allow you to create your own functions that can be exported and used directly within the spreadsheet.

(function () {
    /**
      * This is a custom function that can be used in the editor.
      * @customfunction

      * @param {number} value - description of the parameter.
      * @returns {number} Description of the returned data.
      */
    function customFunctionName(value) {
        // Add your custom calculation logic here
        return // your calculation result;
    }
    // Register the custom function with the editor
    Api.AddCustomFunction(customFunctionName);
})();

Where to find: View > Macros > Custom functions

What changed

With the release of ONLYOFFICE Docs 9.0, we’ve introduced the ability to call asynchronous functions within your custom scripts.

Now, you can:

• Perform asynchronous web requests to any services.
• Make asynchronous requests to AI providers.
• Use asynchronous JS code in async functions.
• Process asynchronous responses.
• Export results directly to your spreadsheet.

  (function () {
    // Make an asynchronous request
    let asyncFunc = async function (value) {
      let request = await fetch("https://yourURL");
      const jsonData = await request.json();
      if (value === undefined) {
        value = 2;
      }
      return jsonData;
    };
    /**
     * This is a custom function that can be used in the editor.
     * @customfunction

     * @param {number} value - description of the parameter.
     * @returns {number} Description of the returned data.
     */
    async function customFunctionName(value) {
      // Call the asynchronous function and return its result
      return await asyncFunc(value);
    }
    // Register the custom function with the editor
    Api.AddCustomFunction(customFunctionName);
  })();

Using async API calls inside custom functions

For this example, we’ll use the Sales Tax API from API Ninjas, which returns the state sales tax rate for any US ZIP code provided in the request.

Note! To use this ONLYOFFICE custom function, you’ll need to provide your own API key, which you can obtain from the API Ninjas website.

Building the macro

First  we define an asynchronous function getSalesTax:

let getSalesTax = async function (value) {
}

It takes a value parameter, which is expected to be a US ZIP code.

If no ZIP code is provided, the function defaults to 90210:

let getSalesTax = async function (value) {
}
 if (undefined === value) {
      value = 90210;
    }

Since some US ZIP codes start with 0, we ensure the ZIP code is a 5-digit string:

 // Convert to string and add missing zeros if needed
    let zipStr = value.toString();
    while (zipStr.length < 5) {
      zipStr = '0' + zipStr;
    }

For example, 31 becomes 00031.

Then we build the request URL for the API by appending the ZIP code as a query parameter:

And send a GET request to the API:

 let request = await fetch(url, {
      method: 'GET',
      headers: {
        'X-Api-Key': 'yourAPIkey',
        'Content-Type': 'application/json'
      }
    });

After that we parse the JSON response into a usable JavaScript object:

  let jsonData = await request.json();

We extract the state_rate field from the first item in the returned array. It is the state sales tax rate for the provided ZIP. And we return this number:

 const taxRate = jsonData[0].state_rate;
    return taxRate;

Then we define the parameters of the custom function:

 /**
   * Function that returns state sales tax.
   * @customfunction

   * @param {number} value - zip code.
   * @returns {number} Returns state sales tax data.
   */

We get the response from the asynchronous function:

 async function salestax(value) {
    return await getSalesTax(value);
  }

And we register the custom function:

  // Add the custom function 
  Api.AddCustomFunction(salestax);
})();

Now we can call the SALESTAX() function right within a spreadsheet.

The entire code is the following:

(function () {
  // Function that returns sales tax data from api-ninjas.com
  let getSalesTax = async function (value) {


    if (undefined === value) {
      value = 90210;
    }


    // Convert to string and add missing zeros if needed
    let zipStr = value.toString();
    while (zipStr.length < 5) {
      zipStr = '0' + zipStr;
    }


    const url = 'https://api.api-ninjas.com/v1/salestax?zip_code=' + zipStr;


    let request = await fetch(url, {
      method: 'GET',
      headers: {
        'X-Api-Key': 'yourAPIkey',
        'Content-Type': 'application/json'
      }
    });


    let jsonData = await request.json();
    const taxRate = jsonData[0].state_rate;
    return taxRate;
  };


  /**
   * Function that returns state sales tax.
   * @customfunction

   * @param {number} value - zip code.
   * @returns {number} Returns state sales tax data.
   */
  async function salestax(value) {
    return await getSalesTax(value);
  }


  // Add the custom function 
  Api.AddCustomFunction(salestax);
})();

Now, let’s save this custom function and see how it works!

We hope this new feature empowers you to create even more robust and versatile solutions for your tasks. At ONLYOFFICE, we are committed to designing and delivering technologies that can be tailored to your specific needs.

We encourage you to experiment with asynchronous custom functions and take full advantage of this expanded functionality. If you have any interesting ideas or suggestions, we’d love to hear from you!  We’re always open to feedback and collaboration. Best of luck in your exploratory endeavours!