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

SharePoint Online From Scratch Updated With PowerApps And Flow

Microsoft has enhanced Office 365 SharePoint in several different ways lately, and as an author I am of course eager to cover them all. Therefore I have now released a new version of SharePoint Online from Scratch, on Amazon.

Practical use
SharePoint Online from Scratch focuses on no-code, hands-on instructions and explanations on how to practically make use of a SharePoint tenant for collaboration within an organization. The book also gives examples on how to use SharePoint for management of common business processes.

Multimedia tutorials
Microsoft has a lot of SharePoint instructions online, but they have no real course or manual with a progression. You must know what you are looking for to use the Microsoft tutorials, and how can you search for something when you don't even know that it exists?

Before you can browse Microsoft's articles you need to know the basics and a bit more of SharePoint, and you can learn that in my book. Video demonstrations are great, but to be optimal they need to be studied in a context. SharePoint Online from Scratch gives links to kalmstrom.com Tips articles with demos, and I believe this combination of textbook and video is a good way to explain things.

New experience and new page model
Microsoft has recently introduced a new user interface for SharePoint Online apps (new experience) and pages (new model). The new interface is more intuitive and easier to use than the classic one, but for several reasons you cannot just go over and start using it everywhere.

All app templates don't have the new experience yet, and among those are such common apps as the calendar and tasks list. Furthermore everything you can do in the classic interface is not possible in the new one. You can for example not connect lists to Outlook or Access, and you cannot customize new model pages to the same extent as you can do with wiki pages.

The new experience, on the other hand, has features that are not available in the classic one. As a result of these differences, SharePoint administrators need to understand both interfaces, and SharePoint Online from Scratch now describes both. This has given the book more pages, but I have kept the price at the same level as before.

PowerApps and Flow
SharePoint Online lists that use the new experience have a PowerApp button in the command bar, so that users can start creating mobile friendly apps directly from within the list they want to use. PowerApps are created automatically this way, but for best result you should modify them. I give some examples on how to do that in the new version of SharePoint Online from Scratch.

Another new button in the modern experience lets IT professions create flows, which is Microsoft's replacement for SharePoint workflows. These can be used extensively with various services, and when you create a flow from a SharePoint list you will have a choice of all templates that include SharePoint.

In SharePoint Online from Scratch I show how you can continue building your flow from such templates, and I also give a general description on how Flow works. The classic workflows from the earlier versions of the book are still there, because everything cannot be done with a flow or even a SharePoint 2013 workflow. A good SharePoint admin should know all the options!

On Amazon.com today
The new version of SharePoint Online from Scratch is released on all Amazon sites. I have joined the Kindle Matchbook program, so of you buy the paperback first, you may buy the e-book for a very low price. That way, you can take advantage of both the nice layout of the paperback and the e-book hyperlinking and use the edition that suits you best at each occasion.

By Peter Kalmström
CEO and Systems Designer
kalmstrom.com Business Solutions

SharePoint Pages Instead Of Library Files For Company Guidelines

Organizations that use SharePoint tend to share most of their documents in libraries, and this is often the best way – but not always! In a new Tips article, I suggest a method that uses pages instead of documents. It is less common but quite striking when used on certain kinds of documents. In my demo I use new model pages, but everything that can be done in the new model can also be achieved with wiki pages.

Pages versus documents
Pages can of course only be edited and shared inside SharePoint, so you cannot always use them instead of documents. However, I recommend you to try pages when suitable. Once you get away from documents, you will find that it is easier to both read and edit pages as compared to documents. Furthermore, pages load much quicker than documents and can be more interactive, containing live videos etc.

Company guidelines
Most organizations have documents for company guidelines that should be shared among users. For such documents the page method is suitable, if you want to present the guidelines in a more elegant way than just uploading them to a document library. Therefore I have used a few of these documents as an example in my demo.

Subsite with pages
The page method idea is to create a subsite called Company Guidelines and add the content of each document on a separate page in that site. The page should of course have the same name as the guidelines document.

Custom links
When all pages are added to the subsite, I remove the default links from the Quick Launch. Instead I add links to the pages with company guidelines. This is done in the Site pages library, where I also set one of the pages as the default one.

With a link to the Company Guidelines site in the top navigation, users can easily reach the guidelines, and when the site is open they have all the pages at hand in the Quick Launch.

By Peter Kalmström
CEO and Systems Designer
kalmstrom.com Business Solutions

SharePoint Online Content Type With New Experience

We have recently updated our Tips articles about content types to the latest version of Office 365 and the new experience interface. Using content types is the best way to make sure that items in a SharePoint list or library will have the desired content. A content type is a kind of template that decides what each item should contain, how it should look and which metadata should be collected.

Make similar content look alike
All organizations produce a lot of content, like invoices, quotes, manuals, guidelines and reports, and this content should preferably be created and managed in a consistent way. It should, for example, contain the correct metadata. When each kind of content has its own content type, it is easy for users and managers to create and keep track of SharePoint content.

Quotes library
In my experience, the concept of content types requires some learning. To make it easy to understand, I have recorded some demos on how to create a content type for a Quotes library.

When there is no custom content type in a SharePoint library, users can create any type of document that is allowed by the default Document content type. No metadata columns will be visible, and there is no way to limit the creation choice to a special type of documents, for example Word.

By adding a custom content type to the library and remove the default one (only from this library, of course), I can limit the user choices and give the library the desired columns for metadata.

Word template
To further enhance the content type, I add a Word template to it. I use the Word Quick Parts to create a table where the cells correspond to the metadata columns in the SharePoint library, and this way the required metadata can be easily added to the document and also become printable.

In the final Quotes library it is only possible to create new documents based on my Word template, and even if I cannot force users to actually fill out the metadata it is very easy to do it. The columns are present in the document itself, and dates and values in choice columns can be selected.

Library template
The last step is to save the Quotes library as a template, so that it can be used on other sites. It will now show up among the other templates when you use the "Add an app" command.

Even if I have taken a Quote document content type as an example in the articles, content types can be defined for any item type, including list items, media files and folders. The method is similar whichever item you use, so I hope the Tips articles will be helpful.

By Peter Kalmström
CEO and Systems Designer
kalmstrom.com Business Solutions