Jedox explains batching Excel Custom Functions API calls

Office Add-ins team

April 8th, 2021 0

Vladislav Malicevic, Vice President Development & Support at Jedox

Jedox is on a mission to optimize value creation through Enterprise Performance Management (EPM) solutions, helping organizations outperform their competition and enabling people to effectively collaborate. Microsoft Excel has always been the key front end for delivering this mission. Today, Jedox offers the Jedox Online Add-in which allows you to access Jedox’s EPM from within Excel.

Jedox Add-in for Excel Online

How the add-in works

The Jedox Online Add-in allows you to read, analyze, and edit data from Jedox in Excel and save values and comments to the Jedox In-Memory Database.

Once your Jedox data is visible in Excel, you can manage your data and produce visual representations such as charts and graphs using standard Excel features and functions. Additionally, you can use several Jedox functions within the same spreadsheet.

Jedox Online Add-in customers typically have workbooks using Jedox functions, which are based on Excel custom functions that call a backend. Network latency influences performance and user experience, but we’ve used a batching pattern to bundle all calls into a single one. The Microsoft team provided us with a helpful sample in Github. See this article on batching custom function calls for a remote service.

Batching custom function calls sample

We’re sharing our batching sample with you, so you can learn how we have implemented our custom functions.

async function _fetchFromRemoteService(
  requestBatch: Array<{ operation: string, args: any[] }>
): Promise<IServerResponse[]> {
  // Simulate a slow network request to the server;
  await pause(1000);

  return requestBatch.map((request): IServerResponse => {
    const { operation, args } = request;

    try {
      if (operation === "div2") {
        // Divide the first argument by the second argument.
        return {
          result: args[0] / args[1]
        };
      } else if (operation === "mul2") {
        // Multiply the arguments for the given entry.
        const myresult = args[0] * args[1];
        console.log(myresult);
        return {
          result: myresult
        };
      } else {
        return {
          error: `Operation not supported: ${operation}`
        };
      }
    } catch (error) {
      return {
        error: `Operation failed: ${operation}`
      };
    }
  });
}

function pause(ms: number) {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

Additional advantages of modern Office Add-ins

There are a number of advantages to building a modern web add-in. One is the ability to reach users on multiple platforms. The Jedox Online Add-in is available on Excel for the web, Excel on Windows (version 1904 and later), and Excel for Mac (version 16.25 and later). It’s allowed the company to code once and reach customers right in the product they’re already using. Many of the Jedox add-in users plan critical business processes, such as sales planning, in the familiar context of Excel. This allows them to maximize their knowledge of Excel and minimize any difficulty when moving from desktop to web. Another benefit is the fact that the add-in can be installed directly into Excel, without a user or admin having to download and run cumbersome offline installers.