The Excel survey option
The Excel survey is easy to set up and work with, and it also has the added benefit of anonymous sharing. That is, you don't have to create accounts for the people you want to have survey answers from. Instead you can allow people from outside of the organization to fill out your form without any log in.
Get the data to SharePoint
One potential problem with Excel Surveys is that the results are saved in an Excel table instead of in a SharePoint list. When the survey is finished, you can of course use the different "Export to SharePoint" techniques that I have described in several Tips articles. However, to use the submitted responses as they come in and build workflows or flows on top of the results is extremely tricky.
SharePoint and Office 365 comes with a plethora of features that we and our customers make use of every day. Sometimes you need a bit of glue to put it all together, and these days PowerShell is usually our answer.
In this case we used an Azure Function built in PowerShell to resolve the transfer problem:
-
Created a flow that runs on each file modification in the SharePoint document library where the Excel survey is stored.
- The flow in turn calls to an Azure Function with a http get request, essentially notifying the function that there is work to do.
- The Azure Function built in PowerShell connects to the SharePoint document library, finds the new information in the Excel table and adds corresponding new rows to a SharePoint list.
- Our client can now continue building workflows and flows based on what happens in the SharePoint list.
Combining the power of SharePoint with Azure like this, opens up a world of possibilities. This is only the beginning!
By Peter Kalmström
CEO and Systems Designer
kalmstrom.com Business Solutions
No comments:
Post a Comment