# 🧩 B27｜AI-Assisted Office JS Add-in (Custom Functions) — Companion Assets

> **Series**: AI for Office Work: From Beginner to Pro
> **Article**: B27 (Track 7 Office Dev / Level 3 Intermediate)
> **Title (EN)**: AI-Assisted Office JS Add-in Development
> **Title (ZH)**: AI 辅助开发 Office JS 加载项（自定义函数）

---

## 1. Who is this for? What problem does it solve?

For anyone who needs a **custom function callable in Excel just like `SUM`** — finance commission rules used across 50 sheets, cross-platform (Windows + Mac + Web), and no macro security warnings.

The main tutorial explains how to **use AI to scaffold an Office JS add-in**. This folder ships a **real, runnable minimum** — pick up `manifest.xml` + `functions.json` + `functions.js` + `commands.js`, sideload into Excel, and try.

| File | Purpose |
|------|---------|
| `manifest_en.xml` | The add-in's "ID card" (English comments) |
| `manifest_zh.xml` | Same, Chinese comments |
| `functions_en.json` | Function "descriptions" (English) |
| `functions_zh.json` | Same, Chinese |
| `functions_en.js` | Real computation + `CustomFunctions.associate` registration (English comments) |
| `functions_zh.js` | Same, Chinese comments |
| `commands_en.js` | Taskpane button commands (English) |
| `commands_zh.js` | Same, Chinese |
| `package.json` | npm project descriptor + sideload hint |

After running this folder, you have a "custom function + taskpane button" skeleton that works. Next time you want to add a function, just edit two files (`functions.json` + `functions.js`).

---

## 2. How to use (5 steps to sideload)

### Step 1: Environment check

- Node.js 18+ installed (`node --version` works);
- Office: **Office 365 subscription**, recent **Office 2021 retail**, or **Mac version** are supported. **Office 2019** and **LTSC** typically do NOT support custom functions. Check Excel → File → Account for the build number, then verify against the official CustomFunctionsRuntime 1.1 matrix.

### Step 2: Copy files into a project directory

Copy all files from this folder into a local project directory, e.g. `D:\projects\b27-officejs\`. Note:

- The `<Id>` in `manifest.xml` is a GUID — **replace with your own** (search "GUID generator");
- Default port is `https://localhost:3000`; if you change it, update the 5 URLs in `<bt:Urls>` AND the 3 icon URLs in `<bt:Images>`;
- Icon files `icon-16.png / icon-32.png / icon-80.png` must physically exist in `assets/`, or sideload fails with "resource missing".

### Step 3: Start a local https server

The simplest path (works on Windows / Mac / Linux):

```bash
# Enter the project directory
cd D:\projects\b27-officejs

# Spin up a static server at https://localhost:3000
npx http-server -p 3000 -c-1 --cors .

# On first visit, trust the self-signed cert in your browser
```

### Step 4: Sideload into Excel

1. Open Excel;
2. **Insert → Add-ins → My Add-ins → Upload My Add-in**;
3. Pick your `manifest.xml`;
4. You should see "My Custom Functions Sample" appear under My Add-ins;
5. In any cell, type **`=TUTORIAL.ADD(3, 5)`** and press Enter — should return **8**;
6. Try **`=TUTORIAL.DISCOUNT(A1:A10, 0.1)`** — sum of A1:A10 minus 10%.

### Step 5: Ask AI to debug

Stuck? Paste the error to AI with this prompt:

```
In Excel, =TUTORIAL.ADD(3,5) returns #NAME?.
- manifest.xml <Namespace resid="Functions.Namespace" /> resolves to "TUTORIAL" via ShortStrings
- functions.json id = "ADD"
- functions.js uses CustomFunctions.associate("ADD", add)
- All SourceLocations are https://localhost:3000
List the 5 most likely causes and their debug steps.
```

---

## 3. File inventory

| File | Type | Purpose | Lines |
|------|------|---------|-------|
| `B27_OfficeJS_EnglishREADME.md` | Markdown | This file | — |
| `B27_OfficeJS_中文README.md` | Markdown | Chinese README | — |
| `manifest_en.xml` | XML | Add-in manifest (English comments) | ~125 |
| `manifest_zh.xml` | XML | Add-in manifest (Chinese comments) | ~125 |
| `functions_en.json` | JSON | Function metadata (English) | ~45 |
| `functions_zh.json` | JSON | Function metadata (Chinese) | ~45 |
| `functions_en.js` | JS | Implementation + registration (English) | ~55 |
| `functions_zh.js` | JS | Implementation + registration (Chinese) | ~55 |
| `commands_en.js` | JS | Taskpane button commands (English) | ~50 |
| `commands_zh.js` | JS | Taskpane button commands (Chinese) | ~50 |
| `package.json` | JSON | npm project descriptor + sideload hint | ~25 |

---

## 4. Real code (key snippets explained line-by-line)

### ① manifest.xml: the three custom-function pieces + Namespace

```xml
<Requirements>
  <Sets>
    <!-- CustomFunctionsRuntime 1.1 is REQUIRED for custom functions -->
    <Set Name="CustomFunctionsRuntime" MinVersion="1.1" />
  </Sets>
</Requirements>
```

```xml
<!-- Attach the CustomFunctions extension point inside AllFormFactors -->
<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 uses a resid reference -->
    <Namespace resid="Functions.Namespace" />
  </ExtensionPoint>
</AllFormFactors>
```

```xml
<!-- The actual namespace value lives in <Resources> -->
<bt:ShortStrings>
  <bt:String id="Functions.Namespace" DefaultValue="TUTORIAL" />
</bt:ShortStrings>
```

**Three takeaways**:
- `CustomFunctionsRuntime 1.1` is mandatory — without it, every call returns `#NAME?`;
- All three `SourceLocation` use the **child-element** syntax (NOT the `DefaultValue` attribute) and reference `Resources` by `resid`;
- `<Namespace>` also uses `resid` (don't write `<Namespace Name="...">` — the `Namespace` element has no `Name` attribute).

### ② functions.json: the function "manual"

```json
{
  "functions": [
    {
      "id": "ADD",
      "name": "ADD",
      "description": "Adds two numbers and returns the result.",
      "parameters": [
        { "name": "first",  "description": "First number",  "type": "number", "dimensionality": "scalar" },
        { "name": "second", "description": "Second number", "type": "number", "dimensionality": "scalar" }
      ],
      "result": { "type": "number", "dimensionality": "scalar" }
    }
  ]
}
```

**Four fields**:
- `id`: must match `CustomFunctions.associate("ADD", add)` first argument EXACTLY (case-sensitive);
- `parameters[].type`: `number / string / boolean / any`;
- `parameters[].dimensionality`: `scalar` (single value) or `matrix` (range);
- `result`: the return type.

### ③ functions.js: implementation + registration

```javascript
/* global CustomFunctions */

// Add two numbers — the actual work is one line.
function add(first, second) {
  return first + second;
}

// Register: the first argument "ADD" must exactly match functions.json `id`
CustomFunctions.associate("ADD", add);
```

**Key points**:
- This file contains **only pure functions** — do NOT put `Office.onReady` here;
- `CustomFunctions.associate("ADD", add)` first argument must exactly match `functions.json` `id`, otherwise `#NAME?`;
- There is no `Excel.customfunction` object; registration is via JSON metadata + `CustomFunctions.associate`, or the new JSDoc `@customfunction` style.

### ④ commands.js: taskpane button callback

```javascript
Office.onReady(function (info) {
  if (info.host === Office.HostType.Excel) {
    console.log("Office JS add-in ready");
  }
});

function showUsage(event) {
  try {
    console.log("=TUTORIAL.ADD(3,5) → 8");
  } finally {
    event.completed();   // MUST call — button stays spinning otherwise
  }
}

// Expose so the manifest's button can find this function
Office.actions.associate("showUsage", showUsage);
```

**Two ironclad rules**:
- Button callback **must** call `event.completed()` — otherwise the button keeps spinning and Excel freezes;
- `Office.onReady` belongs to UI lifecycle, must live in `commands.js`, **never** in `functions.js`.

---

## 5. FAQ / Pitfalls

1. **`=TUTORIAL.ADD(3,5)` returns `#NAME?`**
   Four most common causes: ① Excel version doesn't support CustomFunctionsRuntime 1.1; ② Namespace doesn't match functions.json `id`; ③ localhost port doesn't match manifest; ④ Icon files missing (sideload already failed silently).

2. **"Invalid resource" on sideload**
   Manifest references an icon PNG that doesn't exist. Put `icon-16/32/80.png` under `https://localhost:3000/assets/` and verify they're reachable in the browser.

3. **Button click freezes Excel**
   `event.completed()` not called / not in `finally`. Wrap with `finally { event.completed(); }`.

4. **`Office is not defined`**
   Didn't load `@microsoft/office-js`, or `commands.html` is missing `<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js"></script>`.

5. **functions.js edits don't take effect**
   Browser cache. Hard refresh (`Ctrl + Shift + R`), or re-sideload the manifest.

---

## 6. Next steps

After this folder, you have both "custom function + taskpane button" capabilities. Natural next steps:

- **More modern style**: switch to JSDoc `@customfunction` so the build tool auto-generates metadata from `functions.ts`, no hand-maintained `functions.json`;
- **Async / API calls**: turn `add` into `async function`, `fetch` data, return; add `CustomFunctions.associate("ADD", add, { stream: true })` for streaming results;
- **Better UI**: in `taskpane.html`, add a button that triggers `Office.context.ui.displayDialogAsync(...)` for a friendlier "manual";
- **Stay light**: if you only need to batch-process Excel, you don't need an add-in — see **B25 (Python + openpyxl)** and **B26 (Power Query M)** for lighter options.

---

> 📌 **Quick checklist** (verify before sideload):
> - [ ] Excel is Office 365 / Office 2021 retail / Mac;
> - [ ] `manifest.xml` `<Id>` replaced with your own GUID;
> - [ ] All `localhost:3000` in `manifest.xml` match the dev server port;
> - [ ] Icons `icon-16/32/80.png` physically exist in `assets/`;
> - [ ] `functions.json` `id` matches `CustomFunctions.associate` first argument EXACTLY;
> - [ ] `commands.js` button callback calls `event.completed()`;
> - [ ] `functions.js` has no `Office.onReady`; `commands.js` has no pure functions.