Forum Discussion
How do you update a SharePoint page with Microsoft Form submissions kept in an Excel file?
I am working on a SharePoint site used to track Change Requests. I am pretty new to using Microsoft services for this kind of use case, but after some struggles, I discovered that I can use a Microsoft Form to track new Change Requests on an Excel sheet that is located within the SharePoint directories. The issue that I am experiencing is that I cannot pull live data from the Excel file.
My objective is to have a SharePoint page named "Schedule of Change" which lists the Change Requests from the excel file on the SharePoint site itself. For example, if a user fills out the Microsoft form and a new row with this submission data is added to the excel file, the SharePoint site should also see this and add it to the Schedule of Change page. In other words, I want the content on the Schedule of Change page to stay up to date with the content of the excel file.
I am pretty open to how the info is presented. Ideally, I would like to have a table on the Schedule of Change page where each change request is a new row, but if this isn't possible, then I will take what I can get.
What I've Tried
I attempted to create a list from an excel file, however it only pulled the data once upon list creation, and it would not update when any new change requests were made.
I also researched this issue on the Microsoft forum and found some pages that recommend creating an automation, but the instructions didn't match what I saw and I wasn't able to automate this successfully.
Can anyone provide instructions on how to read excel rows and add them to a SharePoint page even as new data comes into the excel form?
- SvenSieverdingBronze Contributor
Hi Geocie,
You have two options:
1) Embed the Excel File
- Go to your SharePoint Site
- Go to "Gear"->"Add a Page"->"Create Page"
- Name that Page "Schedule of Change"
- Remove the text webpart from that page by selecting it and clicking the "recycle bin" icon
- Press the "plus" sign in the middle and add a "File and Media" Webpart
- Browse to the Excel File
Now your Excel file is embedded on that page.
If the form refreshes the file, then you will see the updates.
But this always looks like an excel file and the page might take some time to load
1) With a List and a Flow- Go to your SharePoint Site
- on the left side click "New" and select "List"
- Create a "Blank list" and name it "Change Requests"
- Add all the columns you want to display in that list
- Go to "Gear"->"Add a Page"->"Create Page"
- Name that Page "Schedule of Change"
- Remove the text webpart from that page by selecting it and clicking the "recycle bin" icon
- Press the "plus" sign in the middle and add a "List" Webpart
- Select the "Change Request" List
- Publish your page
Now you have a page and a blank list. You can customize that list view to look totally different, if you like. (using view formatting: https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/view-formatting).
But that list is still empty. Now you need to build an automation that fills that list, if a new entry has been submitted.- Go to https://make.powerautomate.com/
- On the left side, click create
- Select "Automated cloud flow"
- Name it "Change Requests" and select "When a new response is submitted" (Microsoft Forms) as a trigger
- Press "Create"
- Select your form in the Form id column of the "When a new response is submitted" Action
- Click "New Step" and select "Get Response Details" (Microsoft Forms)
- Select your form in the Form id column of the "Get Response Details" Action
- For the "Response id" select the field and then select "Response Id" from Dynamic Content
- Click "New Step" and select "Create Item" (SharePoint)
- Select your SharePoint Site Adress and the list name "Change Requests"
- Now it depends on how many fields you added to your list. In my example i only used the column title.
Click into each column of your list and select the corresponding dynamic content from the "Get Response details" Action - Now the flow is complete... click the save button on the upper right
Now everytime someone enter data into your form, this flow is activated and creates a new list item in the "Change Request" list.
Best Regards,Sven
- troublemaker2020Copper Contributor
SvenSieverding Does the questions have to match the columns in the excel file? Needing to do the same thing and having issues with the automate updating the Excel file.