MarcinusX · July 13, 2018 12:56 · Jul 13, 2018
diff --git a/officeCustomProperties.md b/officeCustomProperties.md
@@ -0,0 +1,121 @@
+# 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](https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/custompropertycollection.md)
+### Loading
+There is a [bug](https://github.com/OfficeDev/office-js/issues/179) 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](https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/custompropertycollection.md#addkey-string-value-object) 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](https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/customproperty.md#delete))
+```
+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](https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.core.documentproperties?redirectedfrom=MSDN&view=office-pia) object which can be get from [Application.ActiveWorkbook.CustomDocumentProperties](https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._workbook.customdocumentproperties.aspx).
+### 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](https://xkcd.com/979/) - read this before anything else!
+* [Office-js docs](https://github.com/OfficeDev/office-js-docs/tree/master/reference/excel)
+* [VSTO Excel docs](https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx) - nothing interesting
+* [Office-js load bug](https://github.com/OfficeDev/office-js/issues/179)
+* [Some VSTO forum post](https://social.msdn.microsoft.com/Forums/vstudio/en-US/c5a6473d-3360-4e69-b83e-e66d82a4be8a/excel-custom-property?forum=vsto)
+* [My StackOverflow question](https://stackoverflow.com/questions/51307813/loading-custom-property-in-excel-online/51324594#51324594)
+* [Sebastian's StackOverflow question](https://stackoverflow.com/questions/51246744/access-excel-customproperties-from-office-js)
No results found