We've created a custom function called TS_BIND that is working fine on Mac and Web but not on Windows. On Windows, any function we create is not called internally when the user tries to use them. We use a shared runtime for this add-in.
MS Office Version and Build: Version 2208 - Build 15601.20578
Windows Version and Build: Version 21H2 - Build 19044.2728
functions.ts
/* eslint-disable @typescript-eslint/no-explicit-any */
/** global clearInterval, console, CustomFunctions, setInterval **/
interface IRangeData {
index: number;
address: string;
value: any;
}
/**
* Two-way data binding between two cells. Select two cells that need to have the same value, this function will keep them in sync. Alter the value of one cell and the other cell will be updated with the same value.
* @customfunction BIND
* @param {any} cell1 The first cell to bind
* @param {any} cell2 The second cell to bind
* @returns {any} The updated value
* @requiresAddress
* @requiresParameterAddresses
*/
export function bind(cell1: any, cell2: any, invocation: CustomFunctions.Invocation): any {
Excel.run(async (context) => {
let range1: IRangeData;
let range2: IRangeData;
let tsCache = context.workbook.worksheets.getItemOrNullObject("ts-bind-cache");
await context.sync();
if (tsCache.isNullObject) tsCache = context.workbook.worksheets.add("ts-bind-cache");
tsCache.visibility = "Hidden";
const addressesRange = tsCache.getRange("A1").getResizedRange(999, 1);
addressesRange.load("values");
await context.sync();
const addresses = addressesRange.values.filter((address) => address[0] !== null && address[0] !== "");
const sheetCellCode1 = invocation.parameterAddresses[0]?.replace(/['+]/g, "");
const sheetCellCode2 = invocation.parameterAddresses[1]?.replace(/['+]/g, "");
addresses.forEach((address, index) => {
if (address[0] === sheetCellCode1) {
range1 = {
index,
address: address[0],
value: address[1],
};
}
if (address[0] === sheetCellCode2) {
range2 = {
index,
address: address[0],
value: address[1],
};
}
});
if (!range1?.address) {
const cacheRange1 = tsCache.getRange(`A${addresses.length + 1}:B${addresses.length + 1}`);
cacheRange1.values = [[sheetCellCode1, cell1]];
await context.sync();
}
if (!range2?.address) {
const cacheRange2 = tsCache.getRange(`A${addresses.length + 2}:B${addresses.length + 2}`);
cacheRange2.values = [[sheetCellCode2, cell2]];
await context.sync();
}
if (cell1 !== cell2) {
if (range1?.value !== cell1) {
const sheetName = sheetCellCode2.split("!")[0];
const sheet = context.workbook.worksheets.getItemOrNullObject(sheetName);
await context.sync();
if (!sheet.isNullObject) {
const range = sheet.getRange(sheetCellCode2.split("!")[1]);
range.values = [[cell1]];
await context.sync();
if (range1) {
const cacheRange1 = tsCache.getRange(`B${range1.index + 1}`);
cacheRange1.values = [[cell1]];
}
if (range2) {
const cacheRange2 = tsCache.getRange(`B${range2.index + 1}`);
cacheRange2.values = [[cell1]];
}
await context.sync();
}
}
if (range2?.value !== cell2) {
const sheetName = sheetCellCode1.split("!")[0];
const sheet = context.workbook.worksheets.getItemOrNullObject(sheetName);
await context.sync();
if (!sheet.isNullObject) {
const range = sheet.getRange(sheetCellCode1.split("!")[1]);
range.values = [[cell2]];
await context.sync();
if (range1) {
const cacheRange1 = tsCache.getRange(`B${range1.index + 1}`);
cacheRange1.values = [[cell2]];
}
if (range2) {
const cacheRange2 = tsCache.getRange(`B${range2.index + 1}`);
cacheRange2.values = [[cell2]];
}
await context.sync();
}
}
}
});
return cell1;
}
manifest.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" xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="TaskPaneApp">
<Id>749ebdde-13cf-459b-829b-89c11cfa2b23</Id>
<Version>1.1.1.0</Version>
<ProviderName>TermSheet LLC</ProviderName>
<DefaultLocale>en-US</DefaultLocale>
<DisplayName DefaultValue="TermSheet for Excel"/>
<Description DefaultValue="TermSheet Add-In for Microsoft Excel."/>
<IconUrl DefaultValue="https://localhost:3000/assets/icon-32.png"/>
<HighResolutionIconUrl DefaultValue="https://localhost:3000/assets/icon-64.png"/>
<SupportUrl DefaultValue="https://termsheet.com/"/>
<AppDomains>
<AppDomain>https://termsheet.com</AppDomain>
</AppDomains>
<Hosts>
<Host Name="Workbook"/>
</Hosts>
<Requirements>
<Sets DefaultMinVersion="1.1">
<Set Name="SharedRuntime" MinVersion="1.1"/>
</Sets>
</Requirements>
<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">
<Runtimes>
<Runtime resid="Taskpane.Url" lifetime="long"/>
</Runtimes>
<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>
<GetStarted>
<Title resid="GetStarted.Title"/>
<Description resid="GetStarted.Description"/>
<LearnMoreUrl resid="GetStarted.LearnMoreUrl"/>
</GetStarted>
<FunctionFile resid="Taskpane.Url"/>
<ExtensionPoint xsi:type="PrimaryCommandSurface">
<OfficeTab id="TabHome">
<Group id="CommandsGroup">
<Label resid="CommandsGroup.Label"/>
<Icon>
<bt:Image size="16" resid="Icon.16x16"/>
<bt:Image size="32" resid="Icon.32x32"/>
<bt:Image size="80" resid="Icon.80x80"/>
</Icon>
<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>ButtonId1</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="Functions.Script.Url" DefaultValue="https://localhost:3000/functions.js"/>
<bt:Url id="Functions.Metadata.Url" DefaultValue="https://localhost:3000/functions.json"/>
<bt:Url id="Functions.Page.Url" DefaultValue="https://localhost:3000/taskpane.html"/>
<bt:Url id="GetStarted.LearnMoreUrl" DefaultValue="https://go.microsoft.com/fwlink/?LinkId=276812"/>
<bt:Url id="Taskpane.Url" DefaultValue="https://localhost:3000/taskpane.html"/>
</bt:Urls>
<bt:ShortStrings>
<bt:String id="Functions.Namespace" DefaultValue="TS"/>
<bt:String id="GetStarted.Title" DefaultValue="Get started with TermSheet!"/>
<bt:String id="CommandsGroup.Label" DefaultValue="TermSheet for Excel"/>
<bt:String id="TaskpaneButton.Label" DefaultValue="TermSheet for Excel"/>
</bt:ShortStrings>
<bt:LongStrings>
<bt:String id="GetStarted.Description" DefaultValue="Manage your TermSheet worksheets."/>
<bt:String id="TaskpaneButton.Tooltip" DefaultValue="Click to Open TermSheet"/>
</bt:LongStrings>
</Resources>
</VersionOverrides>
</OfficeApp>
We're getting this error below and ended up discovering that the custom functions are not called on Windows Excel App, at least for this add-in.
That was the solution from the Microsoft team, and it worked for me. Thanks a lot, guys, for hopping in.
Hello Marcos,
Good day!
After closely working with our engineering team please find the update. In the parsing of manifest, Runtimes will be parsed first, and its unique identifier is resid. In your manifest, the sharedruntime's resid is "Taskpane.Url"(). When Excel installs the Custom Function (this happens after Runtimes is parsed), it will read the resid in CustomFunctionExtensionPoint's page item, and then find the runtime corresponding to the resid in the parsed Runtimes. In your scenario, Excel cannot find a runtime with resid "Function.Page.Url", so it recognized your custom function as non-sharedruntime by default. By explaining this specific process, I want you to inform that in the parsing of manifest, Excel will match the custom function and runtime by resid , not the specific value of this resid in "<bt:Urls>" tag. This matching rule also applies to other resid. Please modify the resid from Functions.Page.Url to Taskpane.Url.
If the issue still exists, please feel free to reply back to the email I will be happy to assist you further on the request.
Regards, Karasi Jayakrishna (He/Him) Partner Frontline Advocate (Microsoft Commercial Marketplace) For Microsoft Customer Service & Support
