[Office Development]AI-Assisted Office JS Add-in Dev

Summary: Let AI help you scaffold an Office JS Add-in and ship a custom function you can call directly from any Excel cell — no VBA, no macro warnings, no platform lock-in.

1. Pain Point

Have you ever run into this: Finance hands you a tangled bonus-calculation rule you need to reuse across 50 sheets and hundreds of cells? Or you've written a VBA function — only to find your colleague uses Mac Excel and the macro simply won't open?

The traditional fix is to either copy-paste the formula (change one cell and they all break) or pivot to VBA (poor cross-platform support, plus the security prompt that pops up every single time, ugh).

There's actually a third, "official" path hiding inside Excel: use Office JS (the Office JavaScript API — Microsoft's official interface for extending Office with JavaScript) to build an add-in — think of it as a small app that lives inside Excel. Once installed, it can add a ribbon side panel of features — or, and this is the star of today's article — it can register a set of custom functions that you call in cells just like SUM: pass in parameters, get a result back. It doesn't care about your OS — Windows, Mac, and Excel on the web all run it.

2. What You'll Have at the End

By the time you're done, you'll have a ready-to-run add-in skeleton that ships with one custom function, =TUTORIAL.ADD(number1, number2), which returns the sum of its two arguments from any cell.

More importantly, you'll have learned how to use AI (think ChatGPT, Copilot, Kimi — any large language model) to produce three things for you:

A real, working manifest.xml (manifest = a configuration file that serves as the add-in's "ID card + user manual", telling Excel what the add-in is called, where to load its code, and what features it offers);
A JavaScript (JavaScript = the programming language used both on the web and inside Office JS add-ins — all sample code in this article is written in it) skeleton for a custom function;
A set of debugging prompts (prompt = the instruction you feed AI; the more specific it is, the more useful AI's response).

Environment prereqs up front (this is where most people get stuck):

Install Node.js (version 18 or later recommended);
Install the Yeoman generator (Yeoman generator = a scaffolding tool that one-shots the standard add-in folder and file structure; the command package is called generator-office);
A version of Office you're signed into. Custom functions depend on the CustomFunctionsRuntime 1.1 requirement set. Office 365 subscription, a relatively recent Office 2021 retail build, and recent Mac versions typically support it; Office 2019 and volume-licensed / LTSC editions generally do not. Before you start, open Excel → File → Account and check your version number / build number, then cross-check against the official support matrix.

3. Hands-On Walkthrough

Four steps. For each one, I'll give you a "prompt + real code" combo you can copy verbatim.

Step 1: Scaffold the project with AI + the Yeoman generator

First, install the toolchain from the command line:

bash
npm install -g yo generator-office

Then ask AI to confirm your choices. Here's the prompt:

"I'm using generator-office to create an Excel custom-functions add-in. Which template should I pick in the interactive yo office prompt (an Excel Custom Functions one, ideally), and please explain what taskpane, functions, and commands inside the generated src directory are each for."

Run yo office, and in the template list pick an "Excel Custom Functions" flavor — you'll see both JavaScript and TypeScript (TypeScript = a stricter superset of JavaScript with type checking, great for larger projects) variants; pick either. After generation you'll have the standard files: manifest.xml, src/functions/, src/taskpane/, and so on.

Heads-up: the newer templates register functions automatically via the JSDoc @customfunction tag (see Section 6 below). Here, to keep the mechanics visible, I'll use the more explicit "JSON metadata + CustomFunctions.associate" classic approach. Pick one of the two and don't mix them.

Step 2: Author a real manifest.xml

A manifest is the add-in's "ID card". Here's a ready-to-run classic-style manifest (the XML manifest pairs with the "explicit JSON metadata" registration model). Replace <Id> with your own GUID (search for "GUID generator" — any one will do), change the Namespace to the prefix you want, and keep https://localhost:3000 as the default local debug URL.

xml
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<OfficeApp
  xmlns="http://schemas.microsoft.com/office/appforoffice/1.1"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
  xsi:type="TaskPaneApp">
  <Id>626fad87-8dc1-4a6a-98f8-5864bf21eafa</Id>
  <Version>1.0.0.0</Version>
  <ProviderName>Cheng Xiao Tutorial Sample</ProviderName>
  <DefaultLocale>en-US</DefaultLocale>
  <DisplayName DefaultValue="My Custom Function Sample" />
  <Description DefaultValue="An Office JS custom-functions add-in scaffolded with AI assistance." />
  <Requirements>
    <Sets>
      <Set Name="CustomFunctionsRuntime" MinVersion="1.1" />
    </Sets>
  </Requirements>
  <Hosts>
    <Host Name="Workbook" />
  </Hosts>
  <DefaultSettings>
    <SourceLocation DefaultValue="https://localhost:3000/taskpane.html" />
  </DefaultSettings>
  <Permissions>ReadWriteDocument</Permissions>
  <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="Functions.Script.Url" />
            </Script>
            <Page>
              <SourceLocation resid="Functions.Page.Url" />
            </Page>
            <Metadata>
              <SourceLocation resid="Functions.Metadata.Url" />
            </Metadata>
            <Namespace resid="Functions.Namespace" />
          </ExtensionPoint>
        </AllFormFactors>
        <DesktopFormFactor>
          <FunctionFile resid="Commands.Url" />
          <ExtensionPoint xsi:type="PrimaryCommandSurface">
            <OfficeTab id="TabHome">
              <Group id="CommandsGroup">
                <Label resid="CommandsGroup.Label" />
                <Control xsi:type="Button" id="TaskPaneButton">
                  <Label resid="TaskPaneButton.Label" />
                  <Supertip>
                    <Title resid="TaskPaneButton.Label" />
                    <Description resid="TaskPaneButton.Tooltip" />
                  </Supertip>
                  <Icon>
                    <bt:Image size="16" resid="Icon.16x16" />
                    <bt:Image size="32" resid="Icon.32x32" />
                    <bt:Image size="80" resid="Icon.80x80" />
                  </Icon>
                  <Action xsi:type="ShowTaskpane">
                    <TaskpaneId>TaskPaneId1</TaskpaneId>
                    <SourceLocation resid="Taskpane.Url" />
                  </Action>
                </Control>
              </Group>
            </OfficeTab>
          </ExtensionPoint>
        </DesktopFormFactor>
      </Host>
    </Hosts>
    <Resources>
      <bt:Images>
        <bt:Image id="Icon.16x16" DefaultValue="https://localhost:3000/assets/icon-16.png" />
        <bt:Image id="Icon.32x32" DefaultValue="https://localhost:3000/assets/icon-32.png" />
        <bt:Image id="Icon.80x80" DefaultValue="https://localhost:3000/assets/icon-80.png" />
      </bt:Images>
      <bt:Urls>
        <bt:Url id="Commands.Url" DefaultValue="https://localhost:3000/commands.html" />
        <bt:Url id="Taskpane.Url" DefaultValue="https://localhost:3000/taskpane.html" />
        <bt:Url id="Functions.Script.Url" DefaultValue="https://localhost:3000/functions.js" />
        <bt:Url id="Functions.Page.Url" DefaultValue="https://localhost:3000/functions.html" />
        <bt:Url id="Functions.Metadata.Url" DefaultValue="https://localhost:3000/functions.json" />
      </bt:Urls>
      <bt:ShortStrings>
        <bt:String id="CommandsGroup.Label" DefaultValue="My Add-in" />
        <bt:String id="TaskPaneButton.Label" DefaultValue="Open Help Pane" />
        <bt:String id="Functions.Namespace" DefaultValue="TUTORIAL" />
      </bt:ShortStrings>
      <bt:LongStrings>
        <bt:String id="TaskPaneButton.Tooltip" DefaultValue="Click to view custom function usage" />
      </bt:LongStrings>
    </Resources>
  </VersionOverrides>
</OfficeApp>

Key point: <Namespace resid="Functions.Namespace" /> decides how you call the function from Excel — the function name resolves to the PREFIX.FUNCNAME form (e.g., TUTORIAL.ADD). The <Namespace> element itself has no Name attribute; the prefix comes from this ShortStrings entry: <bt:String id="Functions.Namespace" DefaultValue="TUTORIAL" /> — that's the official "labels live in resources, manifest only references by resid" pattern. The icon files (icon-16.png, etc.) must actually exist under assets/, otherwise loading fails with a "resource missing" error.

A prompt you can hand AI to audit the manifest:

"Audit this manifest.xml: does the Namespace match the function registration ID I'm using? Do the localhost paths under Script/Metadata/Page line up with the port my dev server uses? Does every resid referenced have a matching definition under Resources? List what I need to change."

Step 3: Write the custom function (functions.json + functions.js)

Under the classic "explicit metadata" model, the function lives in two files: one that describes what it looks like (functions.json), and one that does the math (functions.js).

functions.json (the function's "spec sheet" — Excel reads it to show parameter tooltips and types):

json
{
  "functions": [
    {
      "id": "ADD",
      "name": "ADD",
      "description": "Returns the sum of two numbers.",
      "parameters": [
        { "name": "first", "description": "The first number", "type": "number" },
        { "name": "second", "description": "The second number", "type": "number" }
      ],
      "result": { "type": "number", "dimensionality": "scalar" }
    }
  ]
}

functions.js (the actual computation — uses CustomFunctions.associate to bind the "ADD" described in the spec sheet to the add function below. Note: the id in functions.json and the first argument to CustomFunctions.associate must be exactly equal — both must be "ADD" — otherwise Excel throws #NAME?):

javascript
/* global CustomFunctions */

// Adds two numbers together.
function add(first, second) {
  return first + second;
}

// Register: tell Excel that the custom function ADD corresponds to the function above.
// Note: the first argument "ADD" must match the id in functions.json exactly.
CustomFunctions.associate("ADD", add);

Key point 1: the id in functions.json and the first argument to CustomFunctions.associate("ADD", add) must be identical character-for-character (case included) — otherwise Excel reports #NAME?.

Key point 2: the Office.onReady(...) pattern (Office.onReady = the official entry point that fires once Office has finished initializing) belongs to the task pane / button commands side of the code and should live in commands.js, which the manifest points to via <FunctionFile resid="Commands.Url" />; do not mix it into functions.js — that file only holds "pure functions that Excel pulls in to evaluate cells" and does not own the UI lifecycle.

Key point: many beginners get stuck wondering "typing =TUTORIAL.ADD in Excel does nothing." Note — the custom function is only loaded the first time you actually type and press Enter in a cell; merely opening the side pane won't trigger loading. Also: there is no Excel.customfunction object — registration goes through CustomFunctions.associate (the classic approach), or via the JSDoc @customfunction tag (the modern approach, mentioned in Section 6).

Prompt for AI (to generate your own custom function):

"Using CustomFunctions.associate, write a custom function under namespace TUTORIAL named DISCOUNT: it takes a list of numbers and a discount rate, then returns the discounted total. Give me the functions.json metadata (with parameter types and descriptions) and the complete functions.js code."

Step 4: Run it + let AI help debug

At the command line:

bash
npm install
npm run build
npm run start

npm run start usually boots the local dev server and side-loads the add-in into Excel. If it doesn't pop Excel open automatically, do it manually: in Excel go to Insert → Add-ins → My Add-ins → Upload My Add-in, then pick your manifest.xml.

In any cell, type =TUTORIAL.ADD(3,5) and press Enter — it should show 8.

Stuck? Paste the error to AI:

"When I type =TUTORIAL.ADD(3,5) in Excel I get a #NAME? error. My manifest's Namespace is TUTORIAL; the functions.json id is ADD; functions.js uses CustomFunctions.associate('ADD', add). List the five most likely causes and the matching troubleshooting step for each."

4. How It Works

In one sentence: an add-in is essentially "a webpage running in a browser + a manifest telling Excel where to find it." When you type =TUTORIAL.ADD(3,5) in a cell, Excel follows the Namespace and Metadata in the manifest to locate functions.js, runs your registered add function, and writes the return value back into the cell — the whole pipeline uses the official Office JS API, so it has no dependency on VBA and doesn't care about the OS.

5. Pitfall Guide

Don't test on an Excel version that doesn't support custom functions. Custom functions require the CustomFunctionsRuntime 1.1 requirement set. Office 365 subscription, a relatively recent Office 2021 retail build, and recent Mac versions are generally fine; Office 2019 and volume-licensed / LTSC editions typically are not. Before you start, open Excel → File → Account, check your version number / build number, then cross-reference the official requirement-sets support matrix.
Namespace vs. function name mismatch. Excel's call format is PREFIX.FUNCNAME. The prefix comes from the manifest's <Namespace>, and the function name comes from the id in functions.json (or the first argument to CustomFunctions.associate). All three must agree — otherwise you get #NAME?.
Keep localhost ports in sync. The https://localhost:3000 written in the manifest must match the port your dev server is actually using; changing the port without updating the manifest is the single most common cause of load failures.
Don't skip icon assets. The PNGs referenced by <bt:Image> in the manifest must really exist; a missing one triggers "invalid resource" errors during upload.
Custom functions only load after they're called. Unlike ordinary plugins that activate as soon as you open them, a custom function only loads the first time you actually type and press Enter in a cell that uses it. When debugging, keep an eye on the browser console (F12) — don't just stare at Excel.

6. Next Steps

For more involved math — e.g., streaming results, cancellation, or fetching data from the web asynchronously — learn the streaming / cancellation APIs of CustomFunctions.associate, or switch to the modern style: use the JSDoc @customfunction tag in functions.ts and let the build pipeline generate the metadata so you don't have to hand-maintain functions.json.
If your scenario is really "clean up a pile of messy spreadsheets", you don't necessarily have to write code — first revisit the Power Query covered in B26 (Power Query = Excel's built-in data cleansing / transformation tool, powered by the M language under the hood), the PivotTables of B21/B23 (the drag-fields-to-summarize table), and the DAX measures of B22 (DAX = the formula language used for calculations in PivotTables; short for Data Analysis Expressions). Many times a few clicks beat writing a custom function from scratch.
Going further, look into strict TypeScript types and the Shared Runtime — letting your custom functions and task pane run in the same process. That's L4 territory.

🛠️ AI-Assisted Office JS Add-in Dev