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.
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:
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 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!