Mark Overdijk

Microsoft Forms preview: The ins & outs

Microsoft Forms was formaly introduced via an Office Blog post "Microsoft Forms—a new formative assessment and survey tool in Office 365 Education" and in preview since April 2016 for Office 365 Education subscribers. It allows users to create quizzes, questionnaires, assessments and subscription forms.

The product

Microsoft Forms is a product that specifically targets the education market and allows users to create web based forms in which different types of questions can be created. The tool lends itself to create a pop quiz for a classroom, a questionnaire to gather qualitative information about a topic or a simple subscription form. As said it is specifically targeted towards the education market and therefore only Office 365 Education licensed users will be able to use the product (in preview). Watch the video released by the product group below;

Microsoft aims to deliver an easy and fast solution for teachers to create assesments, which can filled out via all types of browsers on all types of devices. Don't let the simple interface fool you,you do have powerfull options available, such as validation, notification and export to Excel.

Not the new InfoPath

If you are anything like me, your first reaction when hearing that there is something new called Microsoft Forms will most likely to be: “Finally, the replacement product for InfoPath has arrived!”. Well, it has not. Microsoft Forms does not come close to the full suite of options we know from InfoPath. And more importantly, there are no signs whatsoever from Microsoft that it is supposed to replace InfoPath in the future. For that we have to look at Microsoft PowerApps. Microsoft Forms is a product to create assessments, quizes, surveys, etc. Let's show the power of the product by building a little quiz.

Pop quiz!

Let's jump in the product and create a Pop quiz! It'll show off what the poduct is really good at; creating a questionnaire.

The Basics

Microsoft Forms logo
Microsoft Forms logo

So let’s find out what it can do and take a closer look to this new addition in the Office 365 family. First off, Microsoft Forms is a legit product within the Office 365 suite for education licensed users and therefore you start it, like any other product in the suite, from your app launcher.

Launching Microsoft Forms brings you to the My Forms overview, where all your Forms are shown, and a button to start creating a new one. My overview looks like this:

Forms
Forms

When you click the new button you a Form builder is loaded which allows you to enter a title, an introduction text and start adding questions. There are five types of questions that can be added to a Microsoft From.

Forms
Forms

First, we have the “choice” type which allows you to define a question and list a set of options that can be the answer. Unique to this the “choice” type is that you can use the “other” option if you want to provide a way for your form users to answer outside of the given options. This type of question is typically used for general information questions where there is no wrong answer like: “how did you find out this quiz?”.

Forms
Forms

Second, there is the “quiz” type which also works with defined answer options. Unique to this type is that there is actually a correct answer which can be set. Also you can provide feedback for each option to explain why an answer is correct or incorrect. The “quiz” type question is really the one that gives Microsoft Forms its educational flavor, because this is used to verify knowledge instead of gathering information.

Forms
Forms

Third, the “text” type for which the answer is given in a text box. Unique to this type is that there is an option to allow for a long answer, which gives the person taking the quiz a bigger textbox for the answer.

Forms
Forms

Fourth, we have the “rating” type which allows you to answer using a scale. This scale can be set to stars or numbers and can run from 1 to 5 or from 1 to 10. The “rating” question is often used in questionnaire to gather information about how the test subject agrees or disagrees with certain statements.

Forms
Forms

Fifth and last, there is the “date” type question for which the answer is given by selecting a date from the calendar. A date type answer is often seen in subscription or application forms or in questionnaires to ask about someone birthday for example. However, with a little creativity you can work this type of question into a quiz if the answer is a date (perfect for history exams) with is question like: “What was the founding date of Rapid Circle”.

Forms
Forms

Advanced options

For each of the five types of questions you can indicate if a question is required or optional. This is almost common practice with any type of question tool, but since it is such a powerful way to ensure data completeness I did not want to let this go unmentioned.

Also, for all types of questions you have the option to add subtitle. This could be used for providing a hint about the answer or giving guidance about how to answer the question.

“Choice” and “quiz” type question can be turned from single answer questions into multiple answer questions with a flick of a switch. However, the way that Microsoft Forms is letting the user know that multiple answers are possible is very subtle. For single answer questions the option selection boxes are round and for multiple answer questions the option selection boxes are square. So when making a multiple answers question, I would definitely recommend putting something like “(multiple answers possible)” into the question. Otherwise you well surely get complaints from your quiz takers.

Also for “choice” and “quiz” type of questions you can select the setting to shuffle the options. This will present the answers in a different order every time the quiz is loaded, which has several advantages. One, and I know you are thinking the same, it makes it harder to cheat. Two, when looking beyond the possible bad behavior of quiz takers, there has been a lot of research on how the order in which options are presented influences the option that is most likely to be chosen by quiz takers or the most likely to be correct. So if you as a quiz creator want to remove this bias, shuffling the answers is a nice option that helps you.

For the “text” type question, it is possible to provide restrictions. For example that the answer should be a number (nice for math problems) or that the answer should be between two values. All the restriction options are number based restrictions, so they actually help you to turn the “text” type question into a sixth type of question, namely the “number” type.

For the form as whole there are also some additional settings that can be turned on or off. For example, you can choose if you want to apply a deadline or if you want to shuffle the questions.

Forms
Forms

Sending out the Quiz

When you are done creating the quiz there are several ways to send out word about your newly created quiz. Obviously you can share the link by copying and pasting it to a certain location or email the link.

But next to that, Microsoft shows a nice realization of their mobile first strategy by allowing you to create a QR code for your quiz so people can scan it with their smartphone. Of course we did a test among colleagues, and it worked liked a charm. This function is especially interesting when promoting a training or event for which users need to subscribe. On the poster or flyer you can easily include the QR code so people walking by can scan it and immediately subscribe.

The last way to offer your quiz to users is by embedding it onto a webpage. This could be a SharePoint page, but any other webpage will do as well.

Forms
Forms
Forms QR
Forms QR

When spreading the word about your quiz, questionnaire or subscription form you can still control who can fill it. While the options are not very extensive (to say the least) the most important choice is available, which is to allow people outside your organization to fill out the form.

Forms
Forms

Feedback to the User

When someone fills out you Microsoft Form they get a piece of feedback after submitting. Next to the standard messages that thank the user for submitting and verifying that the form was submitted successfully, extra feedback is given when “quiz” type questions are incorporated in your form.

First, as discussed, a “quiz” type question offers the option to provide a comment per answer option which is shown after submitting the form. Second, in the advanced settings you can determine if the user should see the correct answer for a “quiz” type question after submitting. And Third, a user score is calculated based on the amount of “quiz” type questions they have answered correctly.

This last one is a bit tricky because it only looks at the “quiz” type questions in the form. So if you have a form with 8 questions and 4 of them are “quiz” type question, then the maximum score a person can get based on the feedback is 4 out of 4. From a technology point of view it makes sense, because for the “choice”, “text”, “rating” and “date” type questions you cannot indicated what the correct answer is so it just ignores those questions. But from a user experience it is pretty weird if you just answered 8 questions and you see that your score is 3 out of 4. And since there is no option to switch off this feedback about the user’s score, this definitely takes some communication effort to avoid confusion or complaint. So I would advise you to add a note covering this in the description text at the top of the form.

The responses

If you did a good job building and sharing your Microsoft Form, you will have plenty of responses in no time, which are automatically analyzed for you in the responses section of your form. Here you will find some statistics about the form as a whole and more detailed statistics about each individual question.

Forms
Forms

I have to say that the automatic statistics that are generated are quite good and cover the basic requirements around insight in your responses. But before we go into detail, I would like to point you to the “Open in Excel” button at the top right hand side which will allow to completely go berserk in analyzing the responses in your own way.

Forms excel
Forms excel

For “choice” and “quiz” type questions the responses are presented in a table like fashion as well as a chart. For “text” and “date” type questions the number of responses are presented along with the last three responses. And for “rating” type questions the number of responses is shown together with the average rating.

And for each question you have to option to click the “Details” button which shows all the responses for that particular question in a dialog box.

Forms
Forms

Final thoughts on Microsoft Forms preview

Microsoft Forms is a very complete quiz tool that will help you to create quizzes, questionnaires and simple subscription forms in a quick and easy way. Especially for a product which still is in Preview, I have to say that this first version already covers a lot of requirements. However, there are two major points of critique when looking at Microsoft Forms.

First, the name. It is very misleading in the sense that it brings high expectations to anyone who knows about the fact that InfoPath will be leaving us in the future. Because if you review Microsoft Forms Preview from the perspective of it replacing InfoPath, then you will be very disappointed.

Second, the audience. Microsoft offers the Preview exclusively to Office 365 education licensed users, while this product can also be very helpful outside the educational realm. Many corporations, government bodies and non-profit organizations could use this product. Creating a quiz for your internal training programs, making a questionnaire for customer satisfaction research or building subscription forms for an event is daily business for any type of organization and therefore the restriction to only offer this product to the educational market seems like a strange strategy. It even feels unfair for non-education licensed users. Logically, there are many many people lobbying to bring Microsoft Forms to all Office 365 users when it becomes Generally Available and I am one of them.

So Microsoft Forms shows to be a promising tool for creating quizzes, questionnaires and subscription forms. It covers the basics and in 90 percent of cases will do just fine. But it is not the long awaited replacement of InfoPath, so that remains on the wish list, and will live a life in the shadows of the Office 365 suite if it remains to be solely targeted at education licensed users.

FAQ

How can I get Office Forms Preview?

Sign up to gain access to the preview via https://forms.office.com. Unfortunately it's only available right now for Office 365 Education and the US market. If you are outside the US, but do have access to an Office 365 Education tenant. Sign up, but fill out an US address.

Will it only be available for Education tenants?

At the moment it's only available for Education tenants. Microsoft is exploring all posibilities, but has nothing to share about that as of yet.

Will it be available in my region/language?

Yes, Microsoft Forms will be launched for all Office 365 Education regions and languages.

Is this the Infopath replacement?

You might think that when you read the product name, but... No, this isn't even close. Look at Microsoft PowerApps as the Infopath replacement

Is this the final product?

It's in preview with no live date set, so you may expect changes. These can be small and/or large. If you'd like you can contribute via the feedback button when you're using Office Forms or post your ideas and upvote others on the Office 365 uservoice (https://office365.uservoice.com/).

Is there a Microsoft Support article available?

Yes, use your favorite search engine or follow the link: Microsoft Support - What is Microsoft Forms?

The preview is available for US right now. Anything I should be aware of when outside the US, but still apply?

Yes, as it's running for US only right now, all data is stored in the Microsoft Data Centers in the US. So if you're in Europe for instance, the data entered in Microsoft Forms preview will be stored on US servers. This will be until the product becomes available for your region.

Watch the keynote "The Future of SharePoint" here.

Lots of changes and innovations have been announced at the 'Future of SharePoint' conference last month. We've watched the keynote and found it very interesting. We think you might find this interesting as well. So below, find the full keynote!

Full keynote presentation "Future of SharePoint"

Want to know more?

Read more in-depth information in other blogs/articles by myself and my colleagues: List of #FutureOfSharePoint posts

This blog post is part of the series Future of SharePoint. More on this topic can be found athttp://08b.4d7.myftpupload.com/tag/FutureOfSharePoint/

SharePoint update in 2016: Mobile & Intelligent

On May the 4th 2016 Microsoft held their Future of SharePoint event. It presented all the new stuff which is on it's way for SharePoint and OneDrive (for Business). This post is about a break out session by Microsoft Mechanics, which highlights the new updates on its way for SharePoint in 2016. Scroll down to view the video (18:21 min), but I encourage to first read the summary. Big chances are coming. The first of which, is the update on the document libraries we reported on: New experience SharePoint Online Library.

SharePoint update

SharePoint mobile app

There will be a SharePoint which brings the SharePoint online experience to the mobile phone. It has tabs for Links, Sites and People. All driven by OfficeGraph, Search and Office 365. It's fully responsive and shows the new SharePoint sites (nextgen portals) on your mobile device. Partners should be able to modify the mobile styling to match the client. Below are 4 screenshots of the new app.

1. Links: Links list

2. Sites: showing sites you've interacted with based on OfficeGraph

3. Responsive view of a site

4. People: Search and mobile Delve profile

SharePoint App - Links
SharePoint App - Links
SharePoint App - Sites
SharePoint App - Sites
SharePoint App - Site
SharePoint App - Site
SharePoint App - People
SharePoint App - People

Sites SharePoint!

The sites app in the app launcher has it's name finally restored, it's SharePoint!. With the name update the page will be updated too. The power of OfficeGraph will be added to show an overview of sites used frequently lately but also the some extra info. Everything has been moved around to provide more real estate for the "Frequent" sites. On the left we have the option to create a new site, the Following list to display Followed sites and recently modified.

2 screenshots below;

1. Sites overview:

2. Create site: New interface to create a team or publishing site.

SharePoint overview
SharePoint overview
SharePoint - create new site
SharePoint - create new site

Nextgen Sites & Page authoring

New updates to team and publishing sites will be added to SharePoint Online. These will be fully integrated with Groups and have a new look. Navigation options are still available on the left. The biggest change that's shown is the new page authoring. Well, not really new, but the Sway interface for authoring has been added. Have a look, as it works really well in Sway, it's intuitive for users. As you may notice, adding sections is not only for documents, texts, etc. You can add Power Bi , Office Form, Quick Poll, Power App and other add-ins directly to the page.

3 screenshots below;

1. SharePoint site: New portal. with a linked group, pages slider (news) and activity.

2. Page authoring: the Sway interface to edit pages

3. Search content roll-up app: roll up content

SharePoint site
SharePoint site
SharePoint - page authoring
SharePoint - page authoring
SharePoint page
SharePoint page

New Experience for Lists

Lists will get the same new experience update as the document libraries. Still the option to place list view sections on pages, but the list itself gets a new experience.

3 screenshots below;

1. New list experience: matching the new document library experience

2. Grouped view: Grouped view in document library

3. Search: search results in document library

SharePoint List update
SharePoint List update
SharePoint - grouped view
SharePoint - grouped view
SharePoint - doc.lib
SharePoint - doc.lib

Flow

IFTTT for SharePoint. Creating automated workflows for Office 365 and 3rd party web services. This way users can create, through an UI, workflows between your favorite apps and services to get notifications, synchronize files, collect data, and more. We can automate simply tasks. We start with the trigger, like "receive a new e-mail" after which the workflow can configured. For instance adding a condition (if received from user "X") and adding actions (set category to "X", save the attachment to OneDrive for Business and sent a notification via Slack).

Screenshot:

1. Flow: creating a new flow

2. Services: connect web services to automate tasks

Flow
Flow
Flow - Services
Flow - Services

 Microsoft Mechanics episode

All coming to Office 365 in 2016

New Experience Sharepoint Online Library

The experience of SharePoint libraries is changing

Micrsoft is pushing update MC44849 which changes the user experience of libraries in SharePoint Online. They've communicated via the Message center and an article on Microsoft Office 365 Support. We've been expecting this update. For a while now we've grown fond of the new experience of the OneDrive for Business look & feel. It was to expect that this was the testing ground for the updating all SharePoint libraries to this new experience. That time is now.

Has my tenant been updated?

There are 2 ways to check whether your tenant has been updated. 1. The new experience setting has been added to the SharePoint Admin Center. Go to your SharePoint Admin Center and choose Settings. If your tenant has been updated, you should now have an extra setting: SharePoint Lists and Libraries experience.

Tenant settings new experience
Tenant settings new experience

2. When you load a library in SharePoint Online and there's a big notification banner waiting for the user. It tells "Document Libraries are getting a new look!". When this is shown, the update has been deployed.

Document Libraries are getting a new look!Document Libraries are getting a new look!
Document Libraries are getting a new look!Document Libraries are getting a new look!

Video overview

View all the changes and new functionality in this short walk through video (3:30min) about the new experience for SharePoint libraries.

Most important changes

These are the most important changes that were deployed which have a big impact on the interaction and functionality of the SharePoint library;

nieuwe ervaring - new item

- UI

If an library is displayed, the library is loaded in a screen similar to the default OneDrive for Business style (blue/white). It feels like you've left the (sub)site and are looking at the library in a different app/site. This can be confusing at first for users if they weren't informed of this change. A way to explain this, is by disconnecting the documents "source"(the library) of the site with the list view web part. The list view web part is a window to the source and the user opens the source which is presented in a different way.

- Upload

The functionality of the upload doesn't change, via drag-and-drop or through the menu option, but the ability to upload complete folders and files is new. Microsoft has learned that a lot of users were still using the classic view of OneDrive for Businees, so they could use the "Open in Explorer" function and upload complete folders. So it makes sense they have included this functionality.

- Navigation

As said, the library is displayed in it's own environment, almost looking like a separate Office 365 App/Add-in. The top bar/global navigation have been removed, together with the breadcrumb and any custom styling/master page. But the quick launch/current navigation still remain on the left. That is the user's way out/back.

- Link as contenttype

nieuwe ervaring - information pane

With the update comes the possibility for a user to add a link as an item (*.url file). It's now a default item in the "new item" menu. This means an user can add a link to a file/site/etc and place it in the library. This helps in the battle to eliminate duplicate files which can run out of sync.

- Information pane

The live preview of a file and the display of a few properties have been moved from the item's context menu to the information pane, which slides out on the right when the "I" (info) icon is clicked. This pane shows the library's properties by default. When a file is selected, it shows the live preview of the file, the properties of the file (which can be edited inline), sharing options, version history and the file properties. Very usefull.

- Spotlight/Pin

The user can now pin files and put them in the spotlight. It's like a featured image or document. If a file is pinned, a banner is added on top where the pinned files are shown as big live preview tiles. Add files there which are favorites, featured and/or important.

nieuwe ervaring - gallery view

FAQ

Have we a lost all navigation? Not quite. The quick launch / current navigation is still present on the left side. That's the ticket back to the site. Further more there is still the Office 365 app launcher.

Can the new experience be disabled? Yes. Via the SharePoint Admin Center you can disable this. go to the SharePoint Admin Center en choose Settings (direct link: https://[DOMAIN]-admin.sharepoint.com/_layouts/15/online/TenantSettings.aspx). Look for the sub header "SharePoint Lists and Libraries experience" and choose the "classic experience". This sets all document libraries to classic experience for the whole tenant.

Can I prevent the update from deploying on my tenant? No. You could delay updates by switching to a different service model. This will delay the deploy of updates with several months. But this also means that all other updates will be delayed as well. And you might not want to do so. Read more about service models on Technet: Change management for Office 365 clients.

Why is the new experience option enabled by default? Microsoft is innovating and wants you to join and make use of new features. This will at least stimulate users to take a look at first, while still giving the option (for now) to revert back to the classic experience. This stimulates usage and makes us think about a strategy on how to implement and communicate the new features.

Want to know more? Contact us at Mark.Overdijk@RapidCircle.com or Support@RapidCircle.com.

Powershell: Publishing all files in a SharePoint Online library programmatically

One of our clients build up a library of 500+ documents. After these were modified (added meta data and the content went through several rounds of corrections), we were asked to mass publish all files so the site could go live. Which leaves us with 2 options; 1. manually check-in, publish, approve all files. 2. add some CSOM & PowerShell together in a file and do it programmatically. Off course, I, Mark Overdijk, chose to persue the second option. I asked Massimo Prota to assist in getting a script ready. The first version of the script turned out rather usefull, so I added some extra features and more out-put to & interaction with the user. This latest version is generic enough so it can be re-used.

Features

- No limitation on # of files for a list/library - Added code to filter which files should be published - User will be promoted for password and confirmation - Feedback to user on screen - If a file is checked out, the script will check in before proceeding - If Content Approval is enabled for the list/library, the script will approve the file - Screen out-put will be saved to a txt file which includes the current date/time in the filename

Prerequisites Powershell

Step 1. Gather parameters

For the script to run properly, you'll need the following parameters;

.SiteUrl: This is the full URL to the (sub)site where the list is stored for which you want to publish/approve the files

.ListName: This is the Title of the list for which you want to publish/approve the files

.UserName: This is the UserName that has enough permissions to publish/approve the files

Step 2. Run PowerShell script

Start Windows Powershell as administrator.

Be sure to first set the ExecutionPolicy correctly so you are able to run scripts. Set-ExecutionPolicy Unrestricted [ENTER] Input "A" for all After the ExecutionPolicy is set, we can run the script file.

[code language="powershell"] #################################### # Script: PublishFilesSPO.ps1 # # Version: 2.0 # # Rapid Circle (c) 2016 # # by Mark Overdijk & Massimo Prota # ####################################

# Clear the screen Clear-Host

# Add Wave16 references to SharePoint client assemblies - required for CSOM Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll" Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

# Parameters # Specify the subsite URL where the list/library resides $SiteUrl = "https://DOMAIN.sharepoint.com/SUBSITE" # Title of the List/Library $ListName = "TITLE" # Username with sufficient publish/approve permissions $UserName = "USER@DOMAIN.com" # User will be prompted for password

# Set Transcript file name $Now = Get-date -UFormat %Y%m%d_%H%M%S $File = "PublishFilesSPO_$Now.txt" #Start Transcript Start-Transcript -path $File | out-null

# Display the data to the user Write-Host "/// Values entered for use in script ///" -foregroundcolor cyan Write-Host "Site: " -foregroundcolor white -nonewline; Write-Host $SiteUrl -foregroundcolor green Write-Host "List name: " -foregroundcolor white -nonewline; Write-Host $ListName -foregroundcolor green Write-Host "Useraccount: " -foregroundcolor white -nonewline; Write-Host $UserName -foregroundcolor green # Prompt User for Password $SecurePassword = Read-Host -Prompt "Password" -AsSecureString Write-Host "All files in " -foregroundcolor white -nonewline; Write-Host $ListName -foregroundcolor green -nonewline; Write-Host " on site " -foregroundcolor white -nonewline; Write-Host $SiteUrl -foregroundcolor green -nonewline; Write-Host " will be published by UserName " -foregroundcolor white -nonewline; Write-Host $UserName -foregroundcolor green Write-Host " "

# Prompt to confirm Write-Host "Are these values correct? (Y/N) " -foregroundcolor yellow -nonewline; $confirmation = Read-Host

# Run script when user confirms if ($confirmation -eq 'y') {

# Bind to site collection $Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword) $Context.Credentials = $credentials

# Bind to list $list = $Context.Web.Lists.GetByTitle($ListName) # Query for All Items $query = New-Object Microsoft.SharePoint.Client.CamlQuery $query.ViewXml = " " $collListItem = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()) $Context.Load($List) $Context.Load($collListItem) $Context.ExecuteQuery()

# Go through process for all items foreach ($ListItem in $collListItem){ # Adding spacer Write-Host " " Write-Host "/////////////////////////////////////////////////////////////" Write-Host " " # Write the Item ID, the FileName and the Modified date for each items which is will be published Write-Host "Working on file: " -foregroundcolor yellow -nonewline; Write-Host $ListItem.Id, $ListItem["FileLeafRef"], $ListItem["Modified"]

# Un-comment below "if" when you want to add a filter which files will be published # Fill out the details which files should be skipped. Example will skip all files which where modifed last < 31-jan-2015 # # if ( # $ListItem["Modified"] -lt "01/31/2015 00:00:00 AM"){ # Write-Host "This item was last modified before January 31st 2015" -foregroundcolor red # Write-Host "Skip file" -foregroundcolor red # continue # }

# Check if file is checked out by checking if the "CheckedOut By" column does not equal empty if ($ListItem["CheckoutUser"] -ne $null){ # Item is not checked out, Check in process is applied Write-Host "File: " $ListItem["FileLeafRef"] "is checked out." -ForegroundColor Cyan $listItem.File.CheckIn("Auto check-in by PowerShell script", [Microsoft.SharePoint.Client.CheckinType]::MajorCheckIn) Write-Host "- File Checked in" -ForegroundColor Green } # Publishing the file Write-Host "Publishing file:" $ListItem["FileLeafRef"] -ForegroundColor Cyan $listItem.File.Publish("Auto publish by PowerShell script") Write-Host "- File Published" -ForegroundColor Green

# Check if the file is approved by checking if the "Approval status" column does not equal "0" (= Approved) if ($List.EnableModeration -eq $true){ # if Content Approval is enabled, the file will be approved if ($ListItem["_ModerationStatus"] -ne '0'){ # File is not approved, approval process is applied Write-Host "File:" $ListItem["FileLeafRef"] "needs approval" -ForegroundColor Cyan $listItem.File.Approve("Auto approval by PowerShell script") Write-Host "- File Approved" -ForegroundColor Green } else { Write-Host "- File has already been Approved" -ForegroundColor Green } } $Context.Load($listItem) $Context.ExecuteQuery() } # Adding footer Write-Host " " Write-Host "/////////////////////////////////////////////////////////////" Write-Host " " Write-Host "Script is done" -ForegroundColor Green Write-Host "Files have been published/approved" -ForegroundColor Green Write-Host "Thank you for using PublishFilesSPO.ps1 by Rapid Circle" -foregroundcolor cyan Write-Host " " } # Stop script when user doesn't confirm else { Write-Host " " Write-Host "Script cancelled by user" -foregroundcolor red Write-Host " " } Stop-Transcript | out-null ############################## # Rapid Circle # # http://08b.4d7.myftpupload.com # ############################## [/code]

PowerShell: Terminate a workflow for all items in a list on SharePoint Online

This is a follow up on our previous post "PowerShell: Start a workflow for all items in a list on SharePoint Online". As it's great that now there's a script available to start a workflow for all items, it would also be great to have the ability to stop all workflows if necessary. So I, Mark Overdijk, got to work again with Massimo Prota to get this script in place. The script is very similar to the StartWorkflow Powershell script, but the difference is that we don't retrieve the workflow through WorkflowAssociations but we have to use WorkflowInstances.

Prerequisites Powershell

Step 1. Gather parameters

For the script to run properly, you'll need the following parameters;

.SiteUrl: This is the full URL to the (sub)site where the list is stored for which you want to run the workflow

.ListName: This is the Title of the list for which you want to run the workflow

.UserName: This is the UserName that has enough permissions to run the workflow

Step 2. run PowerShell script

Start Windows Powershell as administrator.

Be sure to first set the ExecutionPolicy correctly so you are able to run scripts. Set-ExecutionPolicy Unrestricted [ENTER] Input "A" for all After the ExecutionPolicy is set, we can run the script file.

Copy/Paste the code below in a txt file and save as an *.ps1 file (in this example "StopWorkflow.ps1"). Fill out the parameters with the gathered information and run the script.

PowerShell stop workflow
PowerShell stop workflow

[code language="powershell"] # Add Wave16 references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll" Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.WorkflowServices.dll"

# Specify tenant admin and site URL $SiteUrl = "https://[TENANT].sharepoint.com/" $ListName = "[TITLE OF THE LIST]" $UserName = "[USERNAME]" $SecurePassword = Read-Host -Prompt "Enter password" -AsSecureString

# Bind to site collection $ClientContext = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword) $ClientContext.Credentials = $credentials $ClientContext.ExecuteQuery()

# Get List $List = $ClientContext.Web.Lists.GetByTitle($ListName)

$ClientContext.Load($List) $ClientContext.ExecuteQuery()

$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()) $ClientContext.Load($ListItems) $ClientContext.ExecuteQuery()

# Create WorkflowServicesManager instance $WorkflowServicesManager = New-Object Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager($ClientContext, $ClientContext.Web)

# Connect to WorkflowSubscriptionService $WorkflowSubscriptionService = $WorkflowServicesManager.GetWorkflowSubscriptionService()

# Connect WorkflowInstanceService instance $WorkflowInstanceService = $WorkflowServicesManager.GetWorkflowInstanceService()

$ClientContext.Load($WorkflowServicesManager) $ClientContext.Load($WorkflowSubscriptionService) $ClientContext.Load($WorkflowInstanceService) $ClientContext.ExecuteQuery()

# Get WorkflowAssociations with List $WorkflowAssociations = $WorkflowSubscriptionService.EnumerateSubscriptionsByList($List.Id) $ClientContext.Load($WorkflowAssociations) $ClientContext.ExecuteQuery()

# Prepare Terminate Workflow Payload $EmptyObject = New-Object System.Object $Dict = New-Object 'System.Collections.Generic.Dictionary[System.String,System.Object]'

# Loop Terminate Workflow For ($j=0; $j -lt $ListItems.Count; $j++){

$msg = [string]::Format("Killing workflows {0} on ListItemID {1}", $WorkflowAssociations[0].Name, $ListItems[$j].Id) Write-Host $msg

$itemWfInstances = $WorkflowInstanceService.EnumerateInstancesForListItem($List.Id, $ListItems[$j].Id) $ClientContext.Load($itemWfInstances) $ClientContext.ExecuteQuery() for ($k=0;$k -lt $itemWfInstances.Count;$k++) { try { $WorkflowInstanceService.TerminateWorkflow($itemWfInstances[$k]) $msg = "Worfklow terminated on " + $ListItems[$j].Id $ClientContext.ExecuteQuery() } catch { $msg = "Error terminating workflow on " + $ListItems[$j].Id + " Details: $_" }

Write-Host $msg } } [/code]

PowerShell: Start a workflow for all items in a list on SharePoint Online

For one of our Office 365 clients (mix of E1 and E3 licences) we created a workflow which will check the status of an item and, depending on this status, sends out e-mails and updates other columns. As the list was already in use, it was necessary to start the workflow for all present items. But to start the workflow manually for all 477  items, was not preferable. So I, Mark Overdijk, asked Massimo Prota to help me on the quest to see if it would be possible to to do it via PowerShell. As there are no PowerShell commands available for SharePoint Online to access the workflow instance, we searched for CSOM solutions. We came across this script on github. Thanks to Azam-A we had a base script to work from. What we changed/added were the following;

  • Referenced the new wave16 components as Office 365 is already on wave16.
  • Added feedback in the script when it runs. It'll show for each item the item ID for which the script is starting the workflow.
  • For obvious security reasons we're not storing the user's Admin password as plain text, but prompt for the password.

Prerequisites Powershell

Step 1. Gather required parameters

For the script to run properly, you'll need the following parameters;

.SiteUrl: This is the full URL to the (sub)site where the list is stored for which you want to run the workflow

.ListName: This is the Title of the list for which you want to run the workflow

.UserName: This is the UserName that has enough permissions to run the workflow

Step 2. Run PowerShell script

Start Windows Powershell as administrator.

Be sure to first set the ExecutionPolicy correctly so you are able to run scripts. Set-ExecutionPolicy Unrestricted [ENTER] Input "A" for all After the ExecutionPolicy is set, we can run the script file.

Copy/Paste the code below in a txt file and save as an *.ps1 file (in this example "StartWorkflow.ps1"). Fill out the parameters with the gathered information and run the script.

StartWorkflow1 screenshot in  Powershell
StartWorkflow1 screenshot in Powershell

[code language="powershell"] # Add Wave16 references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM Add-Type -Path (Resolve-Path "$env:CommonProgramFiles\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll") Add-Type -Path (Resolve-Path "$env:CommonProgramFiles\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll") Add-Type -Path (Resolve-Path "$env:CommonProgramFiles\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.WorkflowServices.dll")

# Specify tenant admin and site URL $SiteUrl = "https://[TENANT].sharepoint.com/" $ListName = "[TITLE OF THE LIST]" $UserName = "[USERNAME]" $SecurePassword = Read-Host -Prompt "Enter password" -AsSecureString

# Connect to site $ClientContext = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword) $ClientContext.Credentials = $credentials $ClientContext.ExecuteQuery()

# Get List and List Items $List = $ClientContext.Web.Lists.GetByTitle($ListName) $ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()) $ClientContext.Load($List) $ClientContext.Load($ListItems) $ClientContext.ExecuteQuery()

# Retrieve WorkflowService related objects $WorkflowServicesManager = New-Object Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager($ClientContext, $ClientContext.Web) $WorkflowSubscriptionService = $WorkflowServicesManager.GetWorkflowSubscriptionService() $WorkflowInstanceService = $WorkflowServicesManager.GetWorkflowInstanceService() $ClientContext.Load($WorkflowServicesManager) $ClientContext.Load($WorkflowSubscriptionService) $ClientContext.Load($WorkflowInstanceService) $ClientContext.ExecuteQuery() # Get WorkflowAssociations with List $WorkflowAssociations = $WorkflowSubscriptionService.EnumerateSubscriptionsByList($List.Id) $ClientContext.Load($WorkflowAssociations) $ClientContext.ExecuteQuery()

# Prepare Start Workflow Payload $Dict = New-Object 'System.Collections.Generic.Dictionary[System.String,System.Object]'

# Loop List Items to Start Workflow For ($j=0; $j -lt $ListItems.Count; $j++){ $msg = [string]::Format("Starting workflow {0}, on ListItemId {1}", $WorkflowAssociations[0].Name, $ListItems[$j].Id) Write-Host $msg #Start Workflow on List Item $Action = $WorkflowInstanceService.StartWorkflowOnListItem($WorkflowAssociations[0], $ListItems[$j].Id, $Dict) $ClientContext.ExecuteQuery() }[/code]

If, for some reason, you want to stop/terminate all workflows, check this blogpost: PowerShell: Terminate a workflow for all items in a list on SharePoint Online

Search Result Source: Why you should use them more often

SharePoint 2013 encompasses a very fancy query configurator and "builder" in the search results web part. An admin can configure the query to filter down and/or scope the results the search web part will show. This is fantastic news as you no longer need to write SQL to provide a simple solution for client requirements like:

  • "search results should only show images"
  • "search results should display results from HR department sub site"

 

Why a result source?

The biggest issue I have with this solution, is that it is almost entirely based on the on the "Local SharePoint results" result source. Which means that the search results web part will load the complete index and then apply the query rules.

 

I wonder however, why do we put such a load on the system to show the full index and then tell it that we'll only need 2% of the results or just the *.PSD files. We can speed things up by configuring a result source, so the system will only get the results from e.g. only the *.PSD files. This will result in a performance increase because both the result source and the applied query have faster loading times. This is much appreciated considering we're applying more and more front-end design to display templates. Whenever we can save load time, we should always do so.

 

Another benefit is that if we do want to add extra filters - for example when we have a result source for one site but two search result pages (no.1 = all and no.2 = documents only) - the result source can be applied to both pages. This results in an even better performance on the 2nd page, because the performance increase is applied twice. The loaded index is smaller and the filter is applied to a smaller result set.

 

Besides all this, there are also functional benefits related to using result sources. First of all, result sources can be applied to all result web parts within the collection of creation. So, when a more experienced admin creates a result source within a site collection, other less experienced admins within that same site collection can apply the result source to result web parts.

 

A result source will also help with version control and updates. When you configure each search results web part within the web part itself completely, the code is fragmented and can't be updated from a single source or be reused. This is not the case with result sources. These can be updated from the source and the source could be a Site, a Site Collection or a Web application/Tenant.

 

So to conclude, result sources improve performance of search results web parts, they are scalable and manageable.

When to use result sources?

In theory, a result source can be used for all search result web parts. It'll be a mess however if you create a result source for all detailed searches. A simple guideline is "when a scope is smaller than a site collection, more than three site collections (including my site/search center) are present or a filter is applied". What does that mean exactly? A few examples:

 

Result Source:

  • All results from site HR including sub sites.
  • All video files from site collection "Video".

Full index:

  • All results.
  • All results from a site collection (total no. of site collection is 3).

 

Next time

In the next post I'll share the details on how to configure a result source.

For now I would like to know if you use result source or why you disagree with using them.

Walkthrough: Add Geolocation column to your list in Office 365

A while ago a client (with an Office 365 E3 subscription) came to us with the wish to create a map to plot locations of external contractors on. My first thoughts, as an Office 365 consultant, went towards using the tools at hand. SharePoint 2013/Online has a Geolocation column type and the list view type "Map view". The client agreed to use this feature and I went about setting up the solution. So I posed the self-fulfilling prophecy: "How hard can it be?"...

As the list with the data was already in place, I was neither keen on letting a developer create a solution which either creates a new list with the column in it (and me migrating data) nor writing a solution which adds the column programmatically once. I wanted to add the column directly through (a reusable) script and went on to do my desk research. This ended up taking way too much time as almost all information found…

  • ...were solutions for SharePoint 2013 on premise,
  • ...were articles on the end-result,
  • ...posted failing scripts,
  • ...did not offer information on the Bing Maps key,
  • ...did not offer guides/information specifically for Office 365/SharePoint Online scenario's.

Something as simple as "what to use as the Bing Maps application URL for an Office 365 tenant?" was not to be found.

It took a while, but when I finally got the settings right for a Bing Maps key and a working script, I decided on 2 things;

  1. Create a generic script, because as a consultant I'll want to use this script more than once for multiple tenants.
  2. Write a blog post as a definitive guide to add the geolocation column type in Office 365/SharePoint Online as a resource for the community

Scenario

For the walkthrough I'm using the following scenario; As a global admin for the tenant https://yourcompany.sharepoint.com, I'm adding the geolocation column type to the list "Contact" on the sub site https://yourcompany.sharepoint.com/sites/sales and naming the column "Office".

Step 1. Get a Bing Maps Key

Go to Bings Maps Dev Center; https://www.bingmapsportal.com/

Log in with your Live account (@live.com, @outlook.com, etcetera) or create one to gain access.

Go to My account > Create or view keys

bingmapsdev1
bingmapsdev1

To create a new API key follow the "Click here to create a new key" hyperlink

bingmapsdev2
bingmapsdev2
bingmapsdev3
bingmapsdev3

Fill out the form to create your API key

. Application name: The name you would like to use for your key. It helps you to identify the key in your overview

. Application URL: The URL of your root SharePoint portal (https://tenant.sharepoint.com)

. Key type (Trial/Basic): Choose whether you're using the key for 1) a test site (max 10,000 calls p/mth and max 90-day trial period) or 2) a live site (free for max 125,000 calls p/yr) (more info here)

. Application type: What is the application? App, site,for non-profit use, etc.

In this scenario, the admin fills it out;

  • Application name: Sales Office
  • Application URL: https://yourcompany.sharepoint.com
  • Key type: Basic
  • Application Type: Public Website

After you click Create and the Captcha was filled out correctly, the page refreshes and displays your new key below. You'll receive a 64 character key.

bingmapsdev4
bingmapsdev4

Step 2. Gather required information

For the script to run properly, you'll need the following information;

. Site URL: URL to the site where the list is.

. Login account: at least admin permission as you're changing list settings.

. List Name: name of the list to add the geolocation column type.

. Column Name: title of the geolocation column.

. Bing Maps Key: to register the app and remove the notification in map view.

In this example, the admin has gathered the following info;

  • Site URL: https://yourcompany.sharepoint.com/sites/sales
  • Creds: Admin@YourCompany.onmicrosoft.com
  • List Name: Contact
  • Column Name: Office
  • Bing Maps Key: [PASTE KEY HERE]

Now we can run the script.

Step 3. Run script

Start SharePoint Online Management Shell as administrator

If you don't have SharePoint Online Management Shell, you can download it @ Microsoft Download Center

set-executionpolicy Unrestricted Clear-Host [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') <# Get User input #> $SiteURL = [Microsoft.VisualBasic.Interaction]::InputBox("Enter Site URL, example: https://yourtenant.sharepoint.com/sites/yoursite", "URL", "") $Login = [Microsoft.VisualBasic.Interaction]::InputBox("Office 365 Username, example: youradmin@yourtenant.onmicrosoft.com", "Username", "") $ListName = [Microsoft.VisualBasic.Interaction]::InputBox("List name to add Geolocation column", "ListName", "") $ColumnName = [Microsoft.VisualBasic.Interaction]::InputBox("Column name for the Geolocation column", "ColumnName", "") $BingMapsKey = [Microsoft.VisualBasic.Interaction]::InputBox("Bing Maps key", "Key", "") <# Show results #> Write-Host "/// Values entered for use in script ///" -foregroundcolor magenta Write-Host "Site: " -foregroundcolor white -nonewline; Write-Host $SiteURL -foregroundcolor green Write-Host "Useraccount: " -foregroundcolor white -nonewline; Write-Host $Login -foregroundcolor green Write-Host "List name: " -foregroundcolor white -nonewline; Write-Host $ListName -foregroundcolor green Write-Host "Geolocation column name: " -foregroundcolor white -nonewline; Write-Host $ColumnName -foregroundcolor green Write-Host "Bing Maps key: " -foregroundcolor white -nonewline; Write-Host $BingMapsKey -foregroundcolor green Write-Host " " <# Confirm before proceed #> Write-Host "Are these values correct? (Y/N) " -foregroundcolor yellow -nonewline; $confirmation = Read-Host if ($confirmation -eq 'y') { $WebUrl = $SiteURL $EmailAddress = $Login $Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl) $Credentials = Get-Credential -UserName $EmailAddress -Message "Please enter your Office 365 Password" $Context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($EmailAddress,$Credentials.Password) $List = $Context.Web.Lists.GetByTitle("$ListName") $FieldXml = "<Field Type='Geolocation' DisplayName='$ColumnName'/>" $Option=[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView $List.Fields.AddFieldAsXml($fieldxml,$true,$option) $Context.Load($list) $Context.ExecuteQuery() $web = $Context.Web $web.AllProperties["BING_MAPS_KEY"] = $BingMapsKey $web.Update() $Context.ExecuteQuery() $Context.Dispose() Write-Host " " Write-Host "Done!" -foregroundcolor green Write-Host " " } else { Write-Host " " Write-Host "Script cancelled" -foregroundcolor red Write-Host " " }
The actual programming part in the script I modified from the script posted ina blog post by Albert Hoitingh. I wanted to remove the hardcoded values from the code, so the script can be run based on user inputs. So I added the interface (input boxes, confirmation, write-hosts), replaced the hard coded values and added comments.

When you run the script, PowerShell will ask the user to input the information we gathered in Step 2.

geoscript1
geoscript1
geoscript2
geoscript2
geoscript3
geoscript3
geoscript4
geoscript4
geoscript5
geoscript5

After the last values have been entered, the admin will see a confirmation screen where the values can be review and confirmed (if the input is incorrect, the script can be cancelled by entering "N" to not proceed - screenshot).

geoscript6
geoscript6

After confirmation, the admin will be prompted to enter the password.

geoscript7
geoscript7

If everything was filled out correctly, the script will run and returns with the "Done!" notification upon completion.

geoscript8
geoscript8

Return to your SharePoint online list and you'll notice when creating a new view for your list "Contact", you gained the option Map View. When checking the list settings, the column "Office" has been added of the type Geolocation.

mapview1
mapview1

Are you missing information, do you want me to clarify anything, do you want to post a conversation starter or do you just want to say thanks? Leave a comment.