DEV Community


Posted on

Combining Custom Functions with Taskpane for office Add-ins

Understanding an Add-in

An add-in is a software component or program that adds new features or functions to an existing software application. Add-ins are designed to extend the functionality of an application, such as a spreadsheet or word processing program, and typically provide additional capabilities beyond the core features provided by the software.

Creating an Add-in using Yeoman Generator

A Yeoman generators is an exceptional tool that defines a set of rules and templates for creating office add-ins. The Yeoman Generators are specific to a particular type of project or technology stack, such as a generator for a React web app or a generator for a Node.js API.

When you run a Yeoman generator, it automatically generates a set of files and folders that make up the project structure. This can include things like configuration files, sample code, and other assets. This can save developers a lot of time and effort by automating the setup and configuration of new projects.

Image description

The challenging part about this generator is combining the features of 2 or more projects. We will look into combining the custom functions feature with the Taskpane.

Let us look into what Custom Functions are

Custom functions in Excel, also known as user-defined functions, are functions that individual users can create which allows you to create your own formula or function in Excel. Using these custom functions, we can performs a specific calculation or task that is not already available in Excel.

With custom functions, we can perform complex calculations, manipulate text and automate repetitive tasks that are not possible with built-in functions. Custom functions can also be designed to work with other Excel functions, making it easier to create more powerful and flexible formulas.

Image description

To create a custom function in Excel, the Yeoman Generator automatically provides a pre-built project structure where the custom functions can be coded. This can save you time, improve the accuracy of your calculations, and make your spreadsheets more efficient and user-friendly.

So what does the Taskpane hold

In Microsoft Excel, a task pane is a feature that provides easy access to common actions and tools. The task pane is a panel that typically appears on the right side of the Excel window, and it can be opened by clicking on a button or menu item in the Excel ribbon.

The task pane can be used to perform a variety of tasks, such as inserting charts, tables, and PivotTables, formatting data, and adding custom calculations. It can also be used to access features that are specific to certain add-ins, such as third-party data analysis tools.

Image description

The task pane is designed to provide quick and convenient access to tools and options that are frequently used, making it a useful feature for both novice and experienced Excel users, depending on your needs and preferences.

Combining the 2 projects

Combining a task pane and a custom function in a software application can provide users with a powerful tool for performing complex tasks quickly and efficiently.

A task pane is a window that is typically displayed alongside the main application window, providing access to additional tools or features. Task panes can be used to display custom user interfaces, provide quick access to frequently used functions, or display additional information related to the current task. A custom function, on the other hand, is a user-defined function that extends the functionality of the application by performing a specific task or calculation that is not included in the built-in functions.

While using the Yeoman Generator, the pre-built files are different for a Custom Function project and a Taskpane project. Hence in order to combine a task pane and a custom function, you need to overlay a few features for both the projects to function properly.

Using the following steps we can overlap these files so that all the functionalities of custom functions and taskpane work in sync.

  1. Create the project for excel add-in as a custom function project by using yeoman generator. It is easier to integrate features of taskpane with custom functions.

  2. Add the dependencies and the dev-dependencies for the word plugin in the package.json file.

  3. Remember to configure the version of the dependencies which are compatible for the word add-in.

  4. Add the custom function plugin in the webpack.config file
    Image description

  5. Change the front end in taskpane.html file according to requirement so that you can change the default custom function front end that is displayed in taskpane.

  6. Comment out the code which runs as the default in taskpane.ts but keep the Office.Onready() function working.

  7. Add your custom functions in the functions.ts file.

  8. Make sure these changes are configured or available in the manifest.xml file.

     <Runtime resid="Taskpane.Url" lifetime="long"/>

     <SourceLocation resid="Taskpane.Url"/>
Enter fullscreen mode Exit fullscreen mode

In conclusion, combining custom functions and taskpane features can greatly enhance the functionality and user experience of Microsoft Office applications. Custom functions allow users to create their own formulas and automate complex tasks, while taskpane features provide a visual interface for users to interact with and manipulate data. By combining these features, users can create more efficient workflows and accomplish tasks, making it easier for them to accomplish their tasks and achieve their goals.

Top comments (1)

chaithanyameda profile image

I have used this same and created some excel custom functions, I am able to modify the UI as I wished. Now I want to add Auth0 authentication for this so that it will launch some landing page and then redirect to Auth0 login page. After successful authentication it will redirect to taskpane.html.

Can anyone please share if you have any idea or sample project regarding same.