Help! Excel Ate My SharePoint Metadata!

It appeared as if Excel was reverting changes to the metadata somehow in the background, updating SharePoint with stale metadata previously cached on the local machine. Unresolved, this has the potential to cause metadata loss in a production environment.

How Excel can cause metadata loss in your SharePoint Online environment

In an attempt to work out what was going on here, I managed to reproduce the behaviour with the steps outlined below.

1. Create an Excel file in a SharePoint document library. Any library is fine, you can use the default “Shared Documents” library if you wish. Close Excel after the spreadsheet has been created and go back to your SharePoint list.

2. Using the SharePoint Information Panel in the modern library view, give the Title column a value. We will use “Original Value”.

3. Now open the spreadsheet from the document library in the Excel app (not Excel Online). This can be done from the “Open > Open in app” menu in SharePoint.

4. After the spreadsheet has loaded, close Excel and go back to SharePoint.

5. Using the SharePoint Information Panel, set the Title Column to a new value. We will set it to “Updated Value”.

6. Open the spreadsheet again using the Excel app as you did previously. Then use the Excel properties panel (File > Info) to view the document properties. See how the title is still set to “Original Value” even though you have previously updated the value in SharePoint to be “Updated Value”? This is because Excel is using a cached version of the file from your local machine.

7. Now make a change to a cell in the spreadsheet and wait for it to save the changes back to SharePoint (if you do not have Autosave enabled, save the spreadsheet). Look at your SharePoint list. See how the changes you made previously have been silently overwritten by the stale data in the Excel spreadsheet. The value of the Title field has reverted to the old value “Original Value”.

What are the implications?

This might not seem like a big deal, but the implications could be severe depending on the data and the scenario. Many organisations rely on SharePoint to store data that is critical to their business – sometimes even to the safety of their employees. All we did here was open an Excel document and change a cell value and it silently reverted the library metadata back to an out-of-date value. The issue doesn’t just happen to the title column either, all columns in the list are at risk of being updated with stale data.

A Workaround… Disable Caching

The workaround we have found for this (which may not be appropriate under all circumstances) is to have the Office Upload Centre delete documents from the local cache when they are closed. This can be done as follows:

1. Open the “Microsoft Office Upload Centre” from the start menu. Note that multiple versions of this can be installed on the same machine so if you don’t see any files listed in the one you open then check for another folder. It is usually under a folder like “Microsoft Office 2013” or “Microsoft Office 2016 Tools”.

2. Click the “Settings” button and check the “Delete files from the Office Document Cache when they are closed” checkbox.

3. Select the “All Cached Files” value from the drop down in the top right corner and then delete any Excel files in your local cache (ensure they have all been synchronised with the server first).

Live Metadata is an Office add-in that supports editing SharePoint metadata when editing Word documents and Excel spreadsheets.  If you are looking for more information, please contact us at sales @ Qualitem dot com