Tag Archives: automation

Integrating Forms into Teams, bringing it home with Microsoft Flow

This post is the third post in this mini series as we look at how easy the Office 365 stack integrates. In this series, we will:


You have a form built, maybe to collect requests, feedback, issues, conference booth sign ups, raffle tickets, whatever. In a previous post we covered how to have the form submit content directly into your Excel file, removing the need to export the responses each and every time. We also covered how to embed the form in Teams

But, how do we now notify users that a form has been submitted? The creator of the form can receive email notifications, but there’s no i in TEAM. We want the team to know someone submitted a request, and then the team can do something about it.

Won’t the Teams Forms Connector suffice?

No. Not for what I want to do here. There are some limitations with this connector:

  • The notification is super generic, I can’t configure what values are included in the notification
  • It only shares the total number of shared responses, nothing about each response as we get them
  • I can only connect to one form at a time, per channel
  • I can only select forms which are group forms, my personal forms don’t show up

Here’s what you get with the connector, a daily status of results? BORING!

Microsoft Forms connector in teams is sad

Good bye connectors and hello Flow!

Microsoft Flow to the rescue!

Let’s bust out a Flow for letting your team know about new submissions!

Entire Microsoft Flow writing Forms to Teams

This Flow is a little more complex than might seem necessary. Since I’m using multi-value fields in the form, I need to parse those values. We’ll cover that in a little bit.

Let’s dive in deeper and see what we’re doing here.

Trigger: Microsoft Forms: When a new response is submitted

 

This trigger causes the the Flow to start whenever there’s a new submission. You’ll notice when you add it you get a drop down option. This is great if you have a personal Form, you can select one of your existing Forms:

Microsoft Forms trigger dropdown

However, in my case, and many others, we are selecting a Form that is a Group or a Shared form. (This form is a group form since I have it writing directly to the Excel file) In these scenarios we need to use the ID of the Form.

To do so, navigate to your form (jump to http://forms.office.com). Click your form and then copy the FormId from the URL, as highlighted in red:

https://forms.office.com/Pages/DesignPage.aspx#FormId=KFGnnESilkWHe_JIK.....PRCQ0PWcu

Go back to your Flow, in the trigger drop down, select Enter custom value and paste in this value.

Flow Trigger: Microsoft Forms - When a new response is submitted

Action: Microsoft Forms – Get response details

Flow Action Microsoft Forms - Get response details

The trigger only returns the ID of the submitted form, no details, so now we need to pull the details. We do this with the Get response details trigger.

The Apply to each wrapper will appear automatically, when you interact with the trigger’s data, Flow automatically wraps it with a loop action that ensures that it’ll process all requests.

After you add the action Microsoft Forms – Get response details, you want to use the same form you used in the trigger, either select it from the drop down or use it’s ID (same thing we did in the trigger).

Then for the Response Id value, use the dynamic value Response Id from the trigger. This will prompt Flow to add the Apply to each wrapper.

Action: Office 365 Users – Get user profile (V2)

Flow action Office 365 Users - Get use profile

The Form details only includes the responder’s email address, and I’d like their display name included. I am using this action to simply get some details about the user who submitted the request.

Why V2? Cause it’s newer. Seriously, that’s all. There are several actions and triggers that have a V1/V2 variations. I always start with V2, and if it doesn’t have what I need I think switch to V1. My reasoning is that V1 may get deprecated sooner than V2, and V2 is newer and should have more options and be more reliable…. should…

Action: Data operations – Parse JSON

Flow action data operations parse json

Our Form allows the user to select multiple choices, as seen here:

Microsoft Forms Multiple Choice questions

If you’re not dealing with multiple choice questions, skip this section and head to the last one.

These selected values get saved as a string, in a JSON array format, like ["10a-12p","2p-4p"]. If this doesn’t make sense, that’s okay. That string value is actually a multi-value array, just in a single string format. These next two actions, Monday Array and and Tuesday Array convert this string value into an array object for us to work with.

The content value is the question from the form, Ideal time on Monday, and Ideal time on Tuesday, in their own actions.

The schema value is below, you can try copy/paste it, but I cheated to make this schema. If you click the Use sample payload to generate schema link at the bottom of the action, you can type in what the data looks like. So I clicked that, typed in ["asd","asd"] and clicked Done.

Data Operations Parse JSON sample schema

This inserted the schema for me, whew! (alternatively you can just copy/paste from here)

{
 "type": "array",
 "items": {
     "type": "string"
  }
}

Action: Data Operations – Join

Microsoft Flow action Data Operations Join array

Next, I wanted to join the selected values together, and use some user friendly words. Remember, the selected value looks like ["10a-12p","2p-4p"]. By parsing the JSON into an array first, I can now join it so it’ll look like “10a-12p or 2p-4p”. Better right? (There’s an example at the bottom of what the original formatting looks like.)

The From value has the Body value from Monday Array, and Tuesday Array, in their respective actions.

The Join with is the word “or” with spaces on either side, like space or space, like ” or “. Simple right?

Action: Microsoft Teams – Post message

Microsoft flow action Teams - Post message

Finally, we post this info to Microsoft Teams. Select the target Team Id and Channel Id, and then specify the message.

The message supports HTML, which is nice, so we can add some formatting here. Here’s what I typed:

<p> [Display Name from Office 365 Users action] has signed up for the conference booth!</p>
<p><strong>Monday:</strong> [Output from Monday Times action]</p>
<p><strong>Tuesday:</strong> [Output from Tuesday Times action]</p>

And blamo!

Forms details in a message in Microsoft Teams

And here’s what the formatting would look like if I didn’t parse the JSON and join the array.

Unformatted post in Microsoft Teams from Forms

Yuck!

Are Incoming Webhooks an option?

Absolutely! I will have another post soon comparing using Incoming Webhooks to the Teams Post a message action, stay tuned!

Done?

That’s it for now, but there’s always more. In my form, I also collect the shirt size, so I could submit that data to an order request database, a SharePoint list, or even an Excel file, for someone to collect all orders.

We could also use the provided cell phone number to vet their information against Office 365, and kick off a review and approval process to update their contact information in O365!

I hope this Flow helped, and showed a good use case for integrating with Forms and Teams! ‘Til next time, Happy Flowin’!

Advertisements

Loading SharePoint lists from Excel using PowerShell

One of my biggest headaches with developing and testing in SharePoint is my lack of test data to work with. Sometimes I need to generate a load of test data so I can perform searches or test performance on custom code. Like in this blog post, I needed to load up a bunch of fake data so I could walk through an example.

To resolve this issue I made  a quick PowerShell script which will read a CSV file and import the data I need. The script needs to be customized each time, but it’s pretty easy. You can download the script called Import-DataIntoSharePoint from my CodePlex project.

This script can also be used to automate importing data into SharePoint. Assuming you have an external system which is exporting to CSV, this script can be scheduled via Windows Tasks to run and import the data on a periodic basis.

First, create your CSV file. How? Open Excel, and load your columns and rows of data. It’s easiest to make the first row your column titles, and have the column titles match the fields in the SharePoint list you’re importing into. It’s ideal not to use spaces in the names as well.

Now load up your data! The values have to be the same as SharePoint would expect. For most fields that’s pretty easy. Text and numbers can be entered as is. Dates should include the time, i.e. 1/20/2012 11:00 AM. Choice fields work like text fields. User and lookup fields are unique, those should be formatted like ID;#Title, i.e. for a user 2;#David Lozzi, or for a lookup 34;#Request for PO. This part can be tricky. Check out my post on Using PowerShell to play with SharePoint Items to better understand the data format.

After you’ve loaded up your data, save the Excel file as a CSV file, and put it in the same folder as the downloaded script.

Open the script up in NotePad (I prefer NotePad++). Make the following modifications as needed

Import Data into SharePoint via PowerShell

  1. Set your web’s URL, i.e. http://servername, or http://servername/site/site
  2. Set your lists’s name, i.e. Calendar, Tasks, My List
  3. Set your CSV file name
  4. Add all fields you wish to add. The $new[ is your SharePoint field name, the $i. is your CSV file column name.

Now save it and run it! With any luck you’ll get a nice response like

Importing data into SharePoint with PowerShell