Home >Services >Example, Live Excel Survey Results in SharePoint     kalmstrom.com site map icon Site map  

Get Live Excel Survey Results Into SharePoint

Excel icon 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 customer.

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. The demo below is fetched from an article about the Excel survey in the kalmstrom.com Tips section.

The Excel survey is easy to set up and work with, and it has the added benefit of anonymous sharing. This means that you don't have to create accounts for the people you want to get survey answers from. Instead, you can allow people from outside of the organization to fill out your form without any log in.

SharePoint icon

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.

PowerShell iconSolve 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. Microsoft Flow icon 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 customer 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!
Peter Kalmström
Products Buy FAQ Services Tips Books Contact About Us Tools

  Security and integrity

Copyright  Kalmstrom Enterprises AB  All rights reserved