Tutorial: Create custom functions in Excel

Build an Excel add-in that provides JavaScript custom functions alongside built-in functions such as SUM. You'll create functions that perform a calculation, retrieve data from the web, and stream real-time updates into a worksheet.

In this tutorial, you:

Prerequisites

• Node.js (the latest Active LTS version). Visit the Node.js site to download and install the right version for your operating system.

• The latest version of Yeoman and the Yeoman generator for Office Add-ins. To install these tools globally, run the following command via the command prompt.

  npm install -g yo generator-office
  

  Note

  Even if you've previously installed the Yeoman generator, we recommend you update your package to the latest version from npm.

• Office connected to a Microsoft 365 subscription (including Office on the web).

  Note

  If you don't already have Office, you might qualify for a Microsoft 365 E5 developer subscription through the Microsoft 365 Developer Program; for details, see the FAQ. Alternatively, you can sign up for a 1-month free trial or purchase a Microsoft 365 plan.

Create a custom functions project

Create the code project for your custom function add-in. The Yeoman generator for Office Add-ins sets up the project with prebuilt custom functions to try. If you already generated a project in the custom functions quickstart, use that project and continue at Create a custom function that requests data from the web.

1. Run the following command to create an add-in project using the Yeoman generator. A folder that contains the project will be added to the current directory.

   yo office
   

   Note

   When you run the yo office command, you may receive prompts about the data collection policies of Yeoman and the Office Add-in CLI tools. Use the information that's provided to respond to the prompts as you see fit.

   When prompted, provide the following information to create your add-in project.

   • Choose a project type: Excel Custom Functions using a Shared Runtime
   • Choose a script type: JavaScript
   • What do you want to name your add-in? My custom functions add-in

   

   The Yeoman generator creates the project files and installs supporting Node components.

2. Go to the root folder of the project.

   cd "My custom functions add-in"
   

3. Build the project.

   npm run build
   

   Note

   Office Add-ins should use HTTPS, not HTTP, even when you're developing. If you're prompted to install a certificate after you run npm run build, accept the prompt to install the certificate that the Yeoman generator provides.

4. Start the local web server, which runs in Node.js. You can try out the custom function add-in in Excel.

npm run start -- web --document {url}

Try a prebuilt custom function

The project contains prebuilt custom functions in ./src/functions/functions.js. The ./manifest.xml file assigns them to the CONTOSO namespace, which you use to access the functions in Excel.

Next, try the ADD custom function by completing the following steps.

1. In Excel, go to any cell and enter =CONTOSO. Notice that the autocomplete menu shows the list of all functions in the CONTOSO namespace.

2. Enter =CONTOSO.ADD(10,200) in the cell and then select Enter.

The ADD custom function returns 210.

If the CONTOSO namespace isn't available in the autocomplete menu, take the following steps to register the add-in in Excel.

Create a custom function that requests data from the web

Integrating data from the web is a great way to extend Excel through custom functions. Create a getStarCount custom function that retrieves the number of stars for a GitHub repository.

1. In the My custom functions add-in project, open ./src/functions/functions.js in your code editor.

2. In functions.js, add the following code.

   /**
    * Gets the star count for a GitHub repository.
    * @customfunction
    * @param {string} userName GitHub user or organization name.
    * @param {string} repoName GitHub repository name.
    * @returns {number} Number of stars given to the GitHub repository.
    */
   async function getStarCount(userName, repoName) {
     try {
       const url = `https://api.github.com/repos/${userName}/${repoName}`;
       const response = await fetch(url);
   
       if (!response.ok) {
         throw new Error(response.statusText);
       }
   
       const jsonResponse = await response.json();
       return jsonResponse.stargazers_count;
     } catch (error) {
       throw new CustomFunctions.Error(CustomFunctions.ErrorCode.notAvailable, String(error));
     }
   }
   

3. Run the following command to rebuild the project.

   npm run build
   

4. Complete the following steps (for Excel on the web, Windows, or Mac) to re-register the add-in in Excel. You must complete these steps before the new function is available.

Create a streaming asynchronous custom function

The getStarCount function returns the number of stars at a specific moment. A streaming function, in contrast, can update a cell repeatedly. It includes an invocation parameter that represents the cell that called the function.

The following sample contains two functions. currentTime returns the current time as a string. The streaming clock function calls invocation.setResult to update the cell every second and uses invocation.onCanceled to stop the timer when Excel cancels the function.

The My custom functions add-in project already contains these functions in ./src/functions/functions.js.

/**
 * Returns the current time
 * @returns {string} String with the current time formatted for the current locale.
 */
function currentTime() {
  return new Date().toLocaleTimeString();
}

/**
 * Displays the current time once a second.
 * @customfunction
 * @param {CustomFunctions.StreamingInvocation<string>} invocation Custom function invocation
 */
function clock(invocation) {
  const timer = setInterval(() => {
    const time = currentTime();
    invocation.setResult(time);
  }, 1000);

  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

To try the streaming function, enter =CONTOSO.CLOCK() in cell C1, and then select Enter. The cell displays the current time and updates every second. You can use the same timer pattern with functions that request real-time data from the web.

Troubleshooting

You might encounter problems if you run the tutorial multiple times. If the Office cache already has an instance of a function with the same name, your add-in gets an error when it sideloads.

To prevent this conflict, clear the Office cache before running npm run start. If your npm process is already running, enter npm run stop, clear the Office cache, and then restart npm.

Next steps

You created a new custom functions project, tried out a prebuilt function, created a custom function that requests data from the web, and created a custom function that streams data. Next, learn how to Share custom function data with the task pane.