Get Live Survey Results into SharePoint
 SharePoint
Online lists gives a possibility to create Forms surveys
directly from the list and see the answers as they come
in, as new items in the list. But these surveys can only
be shared within the organization. They cannot be used when
you want to have input from customers or other persons outside
your organization.
When you create a Forms survey in the Forms site, however,
it can be shared to anyone without authentication. But these
surveys do not add the answers to SharePoint! The incoming
answers can only be synchronized with Excel.
 The
kalmstrom.com team has created multiple solutions that takes
care of the last step between Excel and SharePoint. The
problem is to transfer the answers as they come in,
but that can be done with a PowerShell script.
Get the data to SharePoint
 One
problem with Forms surveys is that the results are saved
in Forms and 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 flows on top of the results is extremely tricky.
Solve problems with PowerShell
 SharePoint
and Microsoft 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
Power Automate flow that runs on each file modification
in the SharePoint document library where the Excel file
with the survey results is stored.
- The flow in turn calls to an
Azure Function with an 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 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
|
