We spent a considerable amount of time today trying several approaches to exposing data from Excel 2013 spreadsheets on SharePoint Online, Office 365 post Office 2013 upgrade. The best method is to forget about publishing an Excel 2013 table to a SharePoint list and work with SkyDrive Pro and the Excel Web App on SharePoint Online. Here is how we came to our conclusion:
When we organized our local Excel 2013 data into a table, and then published the table to a SharePoint list we hit two big obstacles to implementing this approach for daily operations:
- As Microsoft notes in a post to the Office web site titled Synchronize a SharePoint list with a spreadsheet program, “[w]hichever way you choose, this synchronization is one-way, or unidirectional. Changes made in the SharePoint list can be updated in the spreadsheet program, but any changes that you make in the spreadsheet program are not written to the SharePoint list.” If your team is updating spreadsheets with any frequency, and the spreadsheet data requires arithmetic functions, then adding the data from the SharePoint list to an premises spreadsheet will substantially degrade efficiency. Any arithmetic functions will have to be performed after entry to the list.
- Adding simple workflows to the SharePoint list to enable calculations requires the use of SharePoint Designer 2013, which may or may not be approved at your organization
Once we learned how to save our Excel spreadsheet data to our on premises SkyDrive Pro folder, we successfully added our information to a document in a SharePoint Online shared document library. From then on the sync feature of SkyDrive Pro has worked fine. We can opt to edit the document on SharePoint Online with the Excel Web App, which works well for us. Our spreadsheet data only requires simple arithmetic functions.
In the next post to this blog we will look at how to build a list in SharePoint Online, Office 365 and use SharePoint Designer 2013 to add simple calculation capabilities to the cells.
© Rehmani Consulting, Inc. & Ira Michael Blonder, 2013 All Rights Reserved