I have created an Excel Task Pane Web Add-in in Visual Studio. Since then, I have introduced TypeScript, upgraded the project files from .NET Framework to .NET 7, and configured to use shared runtime. Currently I do not use the Task Pane, only executing actions.
At this point, I have made an Excel Web Add-in, which have a custom ribbon tab, but this add-in only works with a specifically formatted workbook; that is the add-in expect certain worksheets, tables, or named range to be present in the workbook to work. To make sure that the ribbon does not show when the opened workbook isn't supported, I would like to replace this ribbon tab with a contextual ribbon tab, and show the tab only, if the add-in recognizes the workbook, by detecting certain elements of it, or finds a meta-information embedded in the workbook.
I have followed Microsoft's Create custom contextual tabs in Office add-ins, and managed to redefine the custom ribbon tab as a contextual tab, however, the custom ribbon tab still shows until it is first updated.
When I also remove the custom ribbon tab definition from the manifest file, then the requestUpdate method call throws an error saying something along the line, that ‘null’ doesn't have a property of ‘tabs’. Seemingly the custom ribbon tad doesn't support the OverriddenByRibbonApi tag, and if I specify it for the group, the tab will show, but without any button. I could workaround this, by defining the groups in the Home tab, and mark them with the <OverriddenByRibbonApi>true</OverriddenByRibbonApi> markup. This has some drawback and would only prefer this approach as a last-effort solution.
I've also noticed, that even though the ribbon shows for every workbook that is opened, the add-in only starts executing when the user interacts with the add-in, and after that, every time that workbook is loaded, the add-in will start executing.
I checked the content of the workbook and noticed that the one I was testing with holds reference to my add-in, but the workbook I created even before the add-in existed, does not. This behaviour would be perfect for my purposes, unfortunately I do not even know how to look for a documentation describing how to bind an add-in to a workbook to load the add-in.
How can I make a contextual ribbon tab, without the need to define any custom ribbon tab in the manifest file, and only show the contextual ribbon tab, if and only if the workbook specifically designed for that add-in?
Excerpt from my manifest file:
<?xml version="1.0" encoding="UTF-8"?>
<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"
xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
xsi:type="TaskPaneApp">
<!-- ... -->
<Hosts>
<Host Name="Workbook" />
</Hosts>
<Requirements>
<Sets DefaultMinVersion="1.1">
<Set Name="SharedRuntime" MinVersion="1.2"/>
</Sets>
</Requirements>
<DefaultSettings>
<SourceLocation DefaultValue="~remoteAppUrl/Functions/FunctionFile.html" />
</DefaultSettings>
<!-- ... -->
<VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
<Hosts>
<Host xsi:type="Workbook">
<Runtimes>
<Runtime resid="AddIn.Functions.Url" lifetime="long"/>
</Runtimes>
<DesktopFormFactor>
<FunctionFile resid="..." />
<ExtensionPoint xsi:type="PrimaryCommandSurface">
<!-- Would like to remove this. -->
<CustomTab id="...">
<Group id="...">
<OverriddenByRibbonApi>true</OverriddenByRibbonApi>
<!-- ... -->
</Group>
<Label resid="..."/>
</CustomTab>
</ExtensionPoint>
</DesktopFormFactor>
</Host>
</Hosts>
<Resources>
<!-- ... -->
</Resources>
</VersionOverrides>
</OfficeApp>
Excerpt from the functionFile.ts:
type OfficeRibbon = any;
function getGlobal(): any {
return typeof self !== undefined ? self
: typeof window !== undefined ? window
: typeof global !== undefined ? global
: undefined;
}
function getRibbon(): OfficeRibbon {
return getGlobal().ribbon;
}
function setRibbon(value: OfficeRibbon): void {
if (trace) console. Debug("Update ribbon:", value);
getGlobal().ribbon = value;
}
Office.onReady(async (info: { host: Office.HostType, platform: Office.PlatformType }): Promise<any> => {
if (trace) console.debug("Office ready.");
if (trace) console.debug(info);
if (Office.context.requirements.isSetSupported("RibbonApi", "1.2")) {
let fetchJsonResponse: Response;
let jsonText: string;
let ribbonJson: any;
let host: string;
console.log("Ribbon API 1.2 is supported");
host = window.location.href.match(/https:\/\/[^/]*\//i)[0];
supportRibbon12 = true;
if (trace) console.debug("Fetching ribbon descriptor file.");
fetchJsonResponse = await fetch('../Scripts/ribbon.json');
if (trace) console.debug("Fetching ribbon descriptor file content.");
// In the JSON file, I referred to host as ~remoteAppUrl.
// This may change for localization purposes.
jsonText = (await fetchJsonResponse.text()).replace(/~remoteAppUrl/g, host.slice(0, -1));
ribbonJson = JSON.parse(jsonText);
if (trace) console.debug("Creating ribbon.", ribbonJson);
setRibbon(ribbonJson);
await Office.ribbon.requestCreateControls(ribbonJson);
}
await Excel.run(async (context: Excel.RequestContext): Promise<void> => {
await onWorkbookLoaded(context);
if (trace) {
// Additional debugging code.
// ...
}
});
return info;
});
async function onWorkbookLoaded(context: Excel.RequestContext) {
// Check if workbook have signature, and if so, continue with following:
await prepareWorkbook(context);
}
async function prepareWorkbook(context: Excel.RequestContext) {
// ...
if (supportRibbon12) {
try {
let ribbon: OfficeRibbon = getRibbon();
if (trace) console.debug("Update ribbon state.");
ribbon.tabs[0].visible = true;
await Office.ribbon.requestUpdate(ribbon);
} catch (error) {
console.error(error);
}
}
console.info("Add-in prepared.");
}
Edit: The exception message I get if I remove the <CustomTab> from the manifest:
RichApi.Error: Cannot read properties of null (reading 'tabs')
at new n (excel-web-16.00.js:25:314669)
at i.processRequestExecutorResponseMessage (excel-web-16.00.js:25:378835)
at excel-web-16.00.js:25:376898
