Create custom functions in Excel (preview)

Custom functions enable developers to add new functions to Excel by defining those functions in JavaScript as part of an add-in. Users within Excel can access custom functions just as they would any native function in Excel, such as SUM(). This article describes how to create custom functions in Excel.

Note

Custom functions are currently available in developer preview and are supported on the following platforms:

  • Excel Online
  • Excel for Windows (version 1810 or later)
  • Excel for Mac (version 13.329 or later)

To use custom functions within Excel Online, login by using either your Office 365 subscription or a Microsoft account.

To use custom functions within Excel for Windows or Excel for Mac, you must have an Office 365 subscription, join the Office Insider program (Insider level -- formerly called "Insider Fast"), and use a sufficiently recent build of Excel (as specified earlier in this note).

If you don't already have an Office 365 subscription, you can get one by joining the Office 365 Developer Program.

The following illustration shows an end user inserting a custom function into a cell of an Excel worksheet. The CONTOSO.ADD42 custom function is designed to add 42 to the pair of numbers that the user specifies as input parameters to the function.

animated image showing an end user inserting the CONTOSO.ADD42 custom function into a cell of an Excel worksheet

The following code defines the ADD42 custom function.

function add42(a, b) {
  return a + b + 42;
}

Note

The Known issues section later in this article specifies current limitations of custom functions.

Components of a custom functions add-in project

If you use the Yo Office generator to create an Excel custom functions add-in project, you'll see the following files in the project that the generator creates:

File File format Description
./src/customfunctions.js
or
./src/customfunctions.ts
JavaScript
or
TypeScript
Contains the code that defines custom functions.
./config/customfunctions.json JSON Contains metadata that describes custom functions and enables Excel to register the custom functions and make them available to end users.
./index.html HTML Provides a <script> reference to the JavaScript file that defines custom functions.
./manifest.xml XML Specifies the namespace for all custom functions within the add-in and the location of the JavaScript, JSON, and HTML files that are listed previously in this table.

The following sections provide more information about these files.

Script file

The script file (./src/customfunctions.js or ./src/customfunctions.ts in the project that the Yo Office generator creates) contains the code that defines custom functions and maps the names of the custom functions to objects in the JSON metadata file.

For example, the following code defines the custom functions add and increment and then specifies mapping information for both functions. The add function is mapped to the object in the JSON metadata file where the value of the id property is ADD, and the increment function is mapped to the object in the metadata file where the value of the id property is INCREMENT. See Custom functions best practices for more information about mapping function names in the script file to objects in the JSON metadata file.

function add(first, second){
  return first + second;
}

function increment(incrementBy, callback) {
  var result = 0;
  var timer = setInterval(function() {
    result += incrementBy;
    callback.setResult(result);
  }, 1000);

  callback.onCanceled = function() {
    clearInterval(timer);
  };
}

// map `id` values in the JSON metadata file to the JavaScript function names
CustomFunctionMappings.ADD = add;
CustomFunctionMappings.INCREMENT = increment;

JSON metadata file

The custom functions metadata file (./config/customfunctions.json in the project that the Yo Office generator creates) provides the information that Excel requires to register custom functions and make them available to end users. Custom functions are registered when a user runs an add-in for the first time. After that, they are available to that same user in all workbooks (i.e., not only in the workbook where the add-in initially ran.)

Tip

Server settings on the server that hosts the JSON file must have CORS enabled in order for custom functions to work correctly in Excel Online.

The following code in customfunctions.json specifies the metadata for the add function and the increment function that were described previously. The table that follows this code sample provides detailed information about the individual properties within this JSON object. See Custom functions best practices for more information about specifying the value of id and name properties in the JSON metadata file.

{
  "$schema": "https://developer.microsoft.com/en-us/json-schemas/office-js/custom-functions.schema.json",
  "functions": [
    {
      "id": "ADD",
      "name": "ADD",
      "description": "Add two numbers",
      "helpUrl": "http://www.contoso.com",
      "result": {
        "type": "number",
        "dimensionality": "scalar"
      },
      "parameters": [
        {
          "name": "first",
          "description": "first number to add",
          "type": "number",
          "dimensionality": "scalar"
        },
        {
          "name": "second",
          "description": "second number to add",
          "type": "number",
          "dimensionality": "scalar"
        }
      ]
    },
    {
      "id": "INCREMENT",
      "name": "INCREMENT",
      "description": "Periodically increment a value",
      "helpUrl": "http://www.contoso.com",
      "result": {
          "type": "number",
          "dimensionality": "scalar"
    },
    "parameters": [
        {
            "name": "increment",
            "description": "Amount to increment",
            "type": "number",
            "dimensionality": "scalar"
        }
    ],
    "options": {
        "cancelable": true,
        "stream": true
      }
    }
  ]
}

The following table lists the properties that are typically present in the JSON metadata file. For more detailed information about the JSON metadata file, see Custom functions metadata.

Property Description
id A unique ID for the function. This ID can only contain alphanumeric characters and periods and should not be changed after it is set.
name Name of the function that the end user sees in Excel. In Excel, this function name will be prefixed by the custom functions namespace that's specified in the XML manifest file.
helpUrl URL for the page that is shown when a user requests help.
description Describes what the function does. This value appears as a tooltip when the function is the selected item in the autocomplete menu within Excel.
result Object that defines the type of information that is returned by the function. For detailed information about this object, see result.
parameters Array that defines the input parameters for the function. For detailed information about this object, see parameters.
options Enables you to customize some aspects of how and when Excel executes the function. For more information about how this property can be used, see Streaming functions and Canceling a function later in this article.

Manifest file

The XML manifest file for an add-in that defines custom functions (./manifest.xml in the project that the Yo Office generator creates) specifies the namespace for all custom functions within the add-in and the location of the JavaScript, JSON, and HTML files. The following XML markup shows an example of the <ExtensionPoint> and <Resources> elements that you must include in an add-in's manifest to enable custom functions.

<VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
    <Hosts>
        <Host xsi:type="Workbook">
            <AllFormFactors>
                <ExtensionPoint xsi:type="CustomFunctions">
                    <Script>
                        <SourceLocation resid="JS-URL" /> <!--resid points to location of JavaScript file-->
                    </Script>
                    <Page>
                        <SourceLocation resid="HTML-URL"/> <!--resid points to location of HTML file-->
                    </Page>
                    <Metadata>
                        <SourceLocation resid="JSON-URL" /> <!--resid points to location of JSON file-->
                    </Metadata>
                    <Namespace resid="namespace" />
                </ExtensionPoint>
            </AllFormFactors>
        </Host>
    </Hosts>
    <Resources>
        <bt:Urls>
            <bt:Url id="JSON-URL" DefaultValue="http://127.0.0.1:8080/customfunctions.json" /> <!--specifies the location of your JSON file-->
            <bt:Url id="JS-URL" DefaultValue="http://127.0.0.1:8080/customfunctions.js" /> <!--specifies the location of your JavaScript file-->
            <bt:Url id="HTML-URL" DefaultValue="http://127.0.0.1:8080/index.html" /> <!--specifies the location of your HTML file-->
        </bt:Urls>
        <bt:ShortStrings>
            <bt:String id="namespace" DefaultValue="CONTOSO" /> <!--specifies the namespace that will be prepended to a function's name when it is called in Excel. Can only contain alphanumeric characters and periods.-->
        </bt:ShortStrings>
    </Resources>
</VersionOverrides>

Note

Functions in Excel are prepended by the namespace specified in your XML manifest file. A function's namespace comes before the function name and they are separated by a period. For example, to call the function ADD42 in the cell of an Excel worksheet, you would type =CONTOSO.ADD42, because CONTOSO is the namespace and ADD42 is the name of the function specified in the JSON file. The namespace is intended to be used as an identifier for your company or the add-in. A namespace can only contain alphanumeric characters and periods.

Functions that return data from external sources

If a custom function retrieves data from an external source such as the web, it must:

  1. Return a JavaScript Promise to Excel.

  2. Resolve the Promise with the final value using the callback function.

Custom functions display a #GETTING_DATA temporary result in the cell while Excel waits for the final result. Users can interact normally with the rest of the worksheet while they wait for the result.

In the following code sample, the getTemperature() custom function retrieves the current temperature of a thermometer. Note that sendWebRequest is a hypothetical function (not specified here) that uses XHR to call a temperature web service.

function getTemperature(thermometerID){
    return new Promise(function(setResult){
        sendWebRequest(thermometerID, function(data){
            setResult(data.temperature);
        });
    });
}

Streaming functions

Streaming custom functions enable you to output data to cells repeatedly over time, without requiring a user to explicitly request data refresh. The following code sample is a custom function that adds a number to the result every second. Note the following about this code:

  • Excel displays each new value automatically using the setResult callback.

  • The second input parameter, handler, is not displayed to end users in Excel when they select the function from the autocomplete menu.

  • The onCanceled callback defines the function that executes when the function is canceled. You must implement a cancellation handler like this for any streaming function. For more information, see Canceling a function.

function incrementValue(increment, handler){
  var result = 0;
  setInterval(function(){
    result += increment;
    handler.setResult(result);
  }, 1000);

  handler.onCanceled = function(){
    clearInterval(timer);
  }
}

When you specify metadata for a streaming function in the JSON metadata file, you must set the properties "cancelable": true and "stream": true within the options object, as shown in the following example.

{
  "id": "INCREMENT",
  "name": "INCREMENT",
  "description": "Periodically increment a value",
  "helpUrl": "http://www.contoso.com",
  "result": {
    "type": "number",
    "dimensionality": "scalar"
  },
  "parameters": [
    {
      "name": "increment",
      "description": "Amount to increment",
      "type": "number",
      "dimensionality": "scalar"
    }
  ],
  "options": {
    "cancelable": true,
    "stream": true
  }
}

Canceling a function

In some situations, you may need to cancel the execution of a streaming custom function to reduce its bandwidth consumption, working memory, and CPU load. Excel cancels the execution of a function in the following situations:

  • When the user edits or deletes a cell that references the function.

  • When one of the arguments (inputs) for the function changes. In this case, a new function call is triggered following the cancellation.

  • When the user triggers recalculation manually. In this case, a new function call is triggered following the cancellation.

To enable the ability to cancel a function, you must implement a cancellation handler within the JavaScript function and specify the property "cancelable": true within the options object in the JSON metadata that describes the function. The code samples in the previous section of this article provide an example of these techniques.

Saving and sharing state

Custom functions can save data in global JavaScript variables, which can be used in subsequent calls. Saved state is useful when users call the same custom function from more than one cell, because all instances of the function can access the state. For example, you may save the data returned from a call to a web resource to avoid making additional calls to the same web resource.

The following code sample shows an implementation of a temperature-streaming function that saves state globally. Note the following about this code:

  • The streamTemperature function updates the temperature value that's displayed in the cell every second and it uses the savedTemperatures variable as its data source.

  • Because streamTemperature is a streaming function, it implements a cancellation handler that will run when the function is canceled.

  • If a user calls the streamTemperature function from multiple cells in Excel, the streamTemperature function reads data from the same savedTemperatures variable each time it runs.

  • The refreshTemperature function reads the temperature of a particular thermometer every second and stores the result in the savedTemperatures variable. Because the refreshTemperature function is not exposed to end users in Excel, it does not need to be registered in the JSON file.

var savedTemperatures;

function streamTemperature(thermometerID, handler){
  if(!savedTemperatures[thermometerID]){
    refreshTemperature(thermometerID); // starts fetching temperatures if the thermometer hasn't been read yet
  }

  function getNextTemperature(){
    handler.setResult(savedTemperatures[thermometerID]); // setResult sends the saved temperature value to Excel.
    var delayTime = 1000; // Amount of milliseconds to delay a request by.
    setTimeout(getNextTemperature, delayTime); // Wait 1 second before updating Excel again.

    handler.onCancelled() = function {
      clearTimeout(delayTime);
    }
  }
  getNextTemperature();
}

function refreshTemperature(thermometerID){
  sendWebRequest(thermometerID, function(data){
    savedTemperatures[thermometerID] = data.temperature;
  });
  setTimeout(function(){
    refreshTemperature(thermometerID);
  }, 1000); // Wait 1 second before reading the thermometer again, and then update the saved temperature of thermometerID.
}

Working with ranges of data

Your custom function may accept a range of data as an input parameter, or it may return a range of data. In JavaScript, a range of data is represented as a 2-dimensional array.

For example, suppose that your function returns the second highest value from a range of numbers stored in Excel. The following function accepts the parameter values, which is of type Excel.CustomFunctionDimensionality.matrix. Note that in the JSON metadata for this function, you would set the parameter's type property to matrix.

function secondHighest(values){
  let highest = values[0][0], secondHighest = values[0][0];
  for(var i = 0; i < values.length; i++){
    for(var j = 1; j < values[i].length; j++){
      if(values[i][j] >= highest){
        secondHighest = highest;
        highest = values[i][j];
      }
      else if(values[i][j] >= secondHighest){
        secondHighest = values[i][j];
      }
    }
  }
  return secondHighest;
}

Handling errors

When you build an add-in that defines custom functions, be sure to include error handling logic to account for runtime errors. Error handling for custom functions is the same as error handling for the Excel JavaScript API at large. In the following code sample, .catch will handle any errors that occur previously in the code.

function getComment(x) {
  let url = "https://www.contoso.com/comments/" + x;

  return fetch(url)
    .then(function (data) {
      return data.json();
    })
    .then((json) => {
      return json.body;
    })
    .catch(function (error) {
      throw error;
    })
}

Known issues

  • Help URLs and parameter descriptions are not yet used by Excel.
  • Custom functions are not currently available on Excel for mobile clients.
  • Volatile functions (those that recalculate automatically whenever unrelated data changes in the spreadsheet) are not yet supported.
  • Deployment via the Office 365 Admin Portal and AppSource are not yet enabled.
  • Custom functions in Excel Online may stop working during a session after a period of inactivity. Refresh the browser page (F5) and re-enter a custom function to restore the feature.
  • You may see the #GETTING_DATA temporary result within the cell(s) of a worksheet if you have multiple add-ins running on Excel for Windows. Close all Excel windows and restart Excel.
  • Debugging tools specifically for custom functions may be available in the future. In the meantime, you can debug on Excel Online using F12 developer tools. See more details in Custom functions best practices.

Changelog

  • Nov 7, 2017: Shipped* the custom functions preview and samples
  • Nov 20, 2017: Fixed compatibility bug for those using builds 8801 and later
  • Nov 28, 2017: Shipped* support for cancellation on asynchronous functions (requires change for streaming functions)
  • May 7, 2018: Shipped* support for Mac, Excel Online, and synchronous functions running in-process
  • September 20, 2018: Shipped support for custom functions JavaScript runtime. For more information, see Runtime for Excel custom functions.
  • October 20, 2018: With the October Insiders build, Custom Functions now requires the 'id' parameter in your custom functions metadata for Windows Desktop and Online. On Mac, this parameter should be ignored.

* to the Office Insider channel (formerly called "Insider Fast")

See also