From following Microsoft's documentation, I've been able to import an Excel spreadsheet into SharePoint Online as a List and also export a SharePoint List to an Excel spreadsheet. What I haven't been able to do is figure out a way to make changes in Excel and push updates to its related SharePoint List. The CRUD functions in SharePoint Lists make it tedious and inefficient to make a lot of updates in the GUI, hoping there's a way to make updates via Excel.
Add a comment
|
3 Answers
There are a few possibilities.
The last SharePoint version to support some flavor of data synchronization between Excel and SharePoint lists was circa SharePoint Server 2010 on premises. You don't specify your version, but unless you run something that old, you're out of luck.
With Office 365 you could use Power Automate to monitor the workbook, given it lives in SharePoint or OneDrive for Business, and you could use the Excel connector actions to write data from Excel to SharePoint.
The easiest, though, is to do all data entry in SharePoint and use Excel only for the reporting/data export. You can easily show/edit a SharePoint list in grid view, so you don't have to use the SharePoint list form interface to edit data.
Or you could write a user interface with PowerApps.
But a two-way sync between Excel and SharePoint is not an out-of-the-box option anymore.
-
Thank you. And I'm working with SharePoint Online or whatever the SaaS flavor is called these days. Sounds like I'm out of luck for the most part, though I might try to workout the PowerAutomate process at some point. Maybe it's just MacBooks but I have a lot of problems editing in Grid View in the GUI, the browser keeps going back for some reason. Appreciate your informative reply.– NathanCommented Nov 17, 2020 at 19:15
-
ah, Mac. Yeah, well. They never worked well with many productivity tools and Mac is often years behind compared to the functionality of a Windows machine.– teylynCommented Nov 17, 2020 at 19:25
As @teylyn stated, the native two-way sync between Excel and SharePoint lists was removed by Microsoft more than 10 years ago.
We have developed a small piece of software that restores that behavior between Excel and SharePoint. You can read more about it and try it here: https://www.synchronizer-for-excel-and-sharepoint.com/
I've done this before successfully for an enterprise level solution and automated the process. The tool I built allowed a database dump in Excel, from a separate program, to synchronize with a SharePoint list.
The process was managed through a single web page, which displayed key information while the upload / synchronization was in process (i.e., progress bar, errors, statistics, etc).
Step 1: Parse the Excel file using javascript. I used sheetjs to perform all input operations. See https://docs.sheetjs.com/docs/getting-started/example to get started.
Step 2: Use CRUD functions to update the list(s) and preprocess data. This was extremely useful to perform filtered updates, where the CRUD actions were controlled based on data from the SharePoint list.
You can do just about anything with this approach, but it does require planning and testing. If you have a large list, start out by testing on a small set of data first.
