Create a view that includes only the list items that need to be updated. It probably took longer than manually updating the items, but now I have information and experience to share with you. With the help of my colleagues, some friends, and people in the Power Addicts community on Twitter, I was able to complete the request. Luckily, each SharePoint list item has a unique ID, and that was all we needed to match the items in the Excel file with the items in the SharePoint list.Ĭoming up with the idea was much easier than building the flow. My company doesn’t allow confidential data to be stored in the cloud. However, that Excel file has to reside in SharePoint Online or OneDrive for Business. I recommended using Power Automate for the bulk updates because it can read from an Excel file. Updating them in bulk would save team A hours of work each week. Next, team B then sends the Excel file back to team A.įinally, team A updates the hundreds or thousands of items in the SharePoint list.Īs you can imagine, updating that many items takes a lot of time. (Requests were being processed hundreds to thousands in a single day.)
Second, team B updates a date field to indicate when the request was processed.
#EXCEL DATA TO SHAREPOINT LIST MANUAL#
The manual process was something like this:įirst, team A exports a SharePoint 2016 list to Excel and sends the Excel file to team B. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.Recently, a team at work asked if the task of updating ~4200 items in a SharePoint list could be automated. If an Answer is helpful, please click " Accept Answer" and upvote it. I hope this information has been useful, please let me know if you still need assistance. Select “save as” to save the file with a unique name(like date) to a stable location. Then you can open the template file every day and click “Refresh”, the items refreshed in your list last day will be updated. Go to Connections from top ribbon->Connections->Properties->Definition:ģ) Save the Excel file as a template file. I suggest you to refer to the following steps to meet your needs:ġ) Connect Excel to a SharePoint list data source. Per my understanding, you want to export items in the last 7 days to an Excel file every day and save it as a record. So “Export to Excel” may work abnormally. The root of your issue is that there are too many records in your list and they exceed the list view threshold(5000 items).
#EXCEL DATA TO SHAREPOINT LIST HOW TO#
Then you can filter the data you want to retrieve from the table.Ībout how to filter data in a table, you can refer to this article: Filter data in a range or table Import data from external data sources (Power Query)Īfter finishing the steps above, you may get an Excel table which contains all your 10,000 items in your list.
If the error still exists, I suggest you to connect Excel to a SharePoint list data source.Īs the office version will affect the implementation steps, you can refer to this article and find specific steps in “Connect to a SharePoint list” section: If the filtered view then returns less than 5000 items, retry to export the list to excel and it may normally work this time. You can create index column(s) to set as your filter condition.Ībout how to create an indexed column, you can refer to this article: Add an index to a SharePoint column
Hi cause of this error message is that the list is not filtered using an indexed column.
0 kommentar(er)
