Get Live Excel Survey Results Into SharePoint With PowerShell and Azure

Excel surveys are useful when you want to get input from customers or other persons outside your organization. An Excel survey is created and answered in Excel Online and does not require login.The problem is to transfer the answers from Excel Online to SharePoint as they come in, but that can be done with a PowerShell script. We recently created such a solution for a client.

The Excel survey option
When external sharing is enabled in a SharePoint site collection, the Excel survey is shown as an additional option when users create a new library item. We have an article about the Excel survey in the kalmstrom.com Tips section, from where the demo below has been fetched.

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.

Solve problems with PowerShell
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:

1. Created a flow that runs on each file modification in the SharePoint document library where the Excel survey is stored.
2. The flow in turn calls to an Azure Function with a http get request, essentially notifying the function that there is work to do.
3. 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.
4. Our client can now continue building workflows and flows based on what happens in the SharePoint list.

We are very happy with this technique and are already using similar steps to solve other business needs. Essentially it allows us to run a PowerShell script based on a trigger in SharePoint. That an Azure Function can be run on a free shared infrastructure is also helpful, of course.

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