Explanation on sharing custom properties between Office 2013 and Office Online

officeCustomProperties.md

Office VSTO + jsAPI custom properties manual

Explanation

So far in VSTO we were saving properties separately in every Worksheet. JavaScript API for Office doesn't have such function (as of 13/07/2018). Instead we can save those properties in Workbook's documentProperties which are accessable both in VSTO and JS.

Note: CP means CustomProperties (too long to write every time)

JS (actually TS):

Docs

Loading

There is a bug with loading CP, so in order to load them we have to ensure that the list is not empty:

async function loadCustomPropertiess() {
	await Excel.run(async (context) => {
		var customProperty = context.workbook.properties.custom;
		var customPropertyCount = customProperty.getCount();
		await  context.sync();
		
		if (customPropertyCount.value > 0) {
			customProperty.load();
			await context.sync();
			customProperty.items.forEach(prop => console.log(prop));
		} else {
			console.log("No custom properties");
		}
	});
}

Adding

Documentation says it can override existing key.

async function addCustomProperty(key: string, value: object) {
	await Excel.run(async (context) => {
		var customProperty = context.workbook.properties.custom;
		customProperty.add(key,value);
	});
}

Deleting

Deleting is done from the level of property we want to delete. (Docs)

async  function  deleteCustomProperty(key:  string) {
	await  Excel.run(async (context) => {
		var customProperties = context.workbook.properties.custom;
		var prop = customProperties.getItemOrNullObject(key);
		prop.load();
		await context.sync();
		if (prop.value) {
			prop.delete();
		}
	});
}

C# (VSTO)

Equivalent of CP is DocumentProperties object which can be get from Application.ActiveWorkbook.CustomDocumentProperties.

Loading

Old

Take a notice that it is done for one sheet

private static Excel.CustomProperty getSheetProperty(Excel.Worksheet sheet, string propName)
{
    Excel.CustomProperties props = sheet.CustomProperties;
    foreach (Excel.CustomProperty prop in props)
    {
        string pn = prop.Name;
        if (propName.Equals(pn))
            return prop;
    }
    return null;
}

New

propName will be probably sheet's name which will be key for that sheet's property

private static DocumentProperty getSheetProperty(string propName)
{
    DocumentProperties customDocumentProperties = Application.ActiveWorkbook.CustomDocumentProperties;
    foreach (DocumentProperty prop in customDocumentProperties)
    {
        if (prop.Name.Equals(propName))
            return prop;
    }
    return null;
}

Deleting

Old

Excel.CustomProperty prop = getSheetProperty(sheet, propName);
if (prop != null)
    prop.Delete();

New

Theoretically it is possible to access property by key but I didn't manage to do it :(

DocumentProperties customDocumentProperties = Application.ActiveWorkbook.CustomDocumentProperties;
foreach (DocumentProperty dc in customDocumentProperties)
{
    if (dc.Name == propName)
        dc.Delete();
}

Adding

In both cases you need to delete data first.

Old

sheet.CustomProperties.Add(propName, JSonUtils.serialize(property));

New

customDocumentProperties.Add(propName, false, MsoDocProperties.msoPropertyTypeString, JSonUtils.serialize(property));

Links

• Starting point - read this before anything else!
• Office-js docs
• VSTO Excel docs - nothing interesting
• Office-js load bug
• Some VSTO forum post
• My StackOverflow question
• Sebastian's StackOverflow question