Tag: SPO

Did you know … you can add manager approvals in Microsoft Flow?

In my previous post, I demonstrated how the Office 365 User search could be used to record a Form responder’s email address instead of their logon ID. But other information about the user is available too – including their manager. This means you can use Flow to build workflows that require manager approval where “manager” is dynamically determined. How?

Select the “Get manager (V2)” action from the “Office 365 User” connector.

In this example, I am extending the sample workflow that creates a SPO list item from a Forms submission. I have inserted a step after getting the responder’s profile information. Click in the “User (UPN)” and select the input that contains the responder’s account (which, again, Forms calls an e-mail address)

Insert a new action

Search for and select “Approvals” and then select the “Start and wait for an approval (V2)” action.

Click the drop-down for “Approval type” – since I am only adding a single approver, the first two options are essentially the same. I can, however, use “Everyone must approve” to have more than one level of manager required for approval, or a manager plus static application owner.

To assign the approval, find the dynamic content from the manager lookup and select “Mail” from that section. Add details that will appear in the approval notification – something to clearly explain what the person is approving.

Save your workflow. Now when a Form response is submitted, the responder’s manager will receive an e-mail which allows them to approve or reject the request.

If they use the Flow site, they will see the approval in their Approvals section.


Did you know … you can store Microsoft Forms responses in a SharePoint Online list with Flow?

Microsoft Forms is a great way to compose simple forms and gather responses but analyzing the data in Excel … well I generally prefer processes to be automated. To use Forms data in automated processes, I store the form response in a SharePoint Online (SPO) list. How do you get the form response into a SPO list? Microsoft Flow.

Note – Microsoft Flow cycles are not free. We receive a number of free cycles each month for each Office 365 license purchased. The company-wide number of free flow cycles is quite large, but not infinite. Be cognizant of what constitutes a cycle. In this case, a cycle is each new form submission … if I expect 20,000 people to submit the form this month, I am using 20,000 flow cycles. Not all trigger cycles are as straightforward as this. As an example, Flow could send me an alert when I receive an e-mail from my manager. A cycle is not triggered on “e-mail from my manager received in my mailbox” but rather “new e-mail message received in my mailbox”. For a mailbox dedicated to a specific purpose, the number of messages received per month may be exactly what you want to process. In my mailbox, system alerts, ITSM notifications, script results and such would quickly use up a significant portion of Windstream’s free cycle allotment.

First you need a Form. When editing your form, grab the URL and find the “FormID” section. Save that string – we’ll need that in the workflow.

Before you start – if you plan to convert your personal form into a group form, do that before creating the Flow workflow. The form ID changes when a form is moved between group and personal forms, and you will need to adjust the workflow accordingly.

To store information in a SharePoint Online list, you’ll also need a SPO list. You can create one in a dedicated SPO site or the SPO site behind a Teams space.

Record the SPO site address and list name – we’ll need that in the workflow.

Now that we’ve got all of the components ready, we can build a workflow to connect them. Log into https://flow.microsoft.com

On the left-hand navigation bar, click “My Flows”.

Click the drop-down next to “New” and select “Create from blank”

Click “Create from blank” to confirm your choice.

Before you do anything else, give your Flow a descriptive name. Click on “Untitled”, type something else, and hit enter. If you don’t name your flow, it will get a default name based on the actions taken within the workflow … which makes it really hard to differentiate between five different workflows that take Forms responses and store them in SPO lists.

The trigger for the workflow will be that a new Forms response is submitted.

Select the “When a new response is submitted” trigger.

Select “Enter custom value” and paste in the form ID we recorded earlier.

Click “+ New step” to add a new step.

To use the data from the Form submission, we need to get the response data. Enter “Forms” again and select “Microsoft Forms”

Select the “Get response details” action.

In “Form Id”, enter a custom value and paste in the Form ID. Click in the “Response Id” field. In the right-hand navigation menu, click “See more”.

Select “List of response notifications Response Id”

You’ll see that the response ID is added to “Select an output from the previous step”. Click “Add an action”

Type “SharePoint” and select “SharePoint”

Select “Create item” from the “Actions” tab

Select “Enter custom value” for the Site Address and enter the site address we recorded earlier. Then click the drop-down for “List Name” – select the list into which you want to insert Forms responses.

Below “List Name”, additional fields will appear – one for each column of your list. Click into a column. In the right-hand bar, you will see the Form attributes that can be placed into the List column. For each List column, select the Form component you want stored in that field.

Flow is aware of column types – if you click into a date column, it will try to present Forms information that holds dates. If you find an option missing, click “See more” to see an unfiltered list of Forms attributes.

When you are selecting values to use in an action, the possible selections are grouped by the action. For a simple form/list combination like this, using the default step names is not too confusing, but if you build more complex workflows with more data elements … figuring out which “Get response details” is which becomes difficult.

It can be clearer if you provide a unique name for each trigger and action – the values from the LJRDemo form are clearly part of the “Get LJRDemo Form response details” section.

To rename a workflow element, click the ellipses and select “Rename”

Type a new name and hit enter

Click “Save” to save and activate your workflow.

Submit answers using your form, then check your SPO list.

Notice that what Forms calls my e-mail address is actually my logon ID. This is very useful for IT folks who can easily correlate logon IDs to actual individuals. Maybe not so useful for you. Flow provides directory lookup capabilities if you want to turn the logon ID field into something … more readable. Edit your Flow. After the get response details step, click the little plus to insert a new step.

Select “Add an action”

Search for “Office 365 Users”, select it, and select “Get user profile (V2)”.

Click in the “User (UPN)” box and select the Form respondent’s “e-mail”

Edit the action which creates the SPO list item. You will now have dynamic content both from the Form response and the Office 365 user lookup. In this case, I will use “Mail” in the Submitter column.

Save you changes. Since we’ve already submitted a Form response, we can test the Flow without submitting a new response. Click “Test” in the upper right-hand corner of your screen.

Select one of the previous runs (there may only be one!) and click “Save & Test” to re-run the Form response through your workflow.

Now the submitter is recorded as an e-mail address.

SPO Guest Access Stops Working

I ran across an interesting issue today — Windstream’s got a really awesome SPO site for SD Project Management – tracking orders, equipment orders, 3rd party cabling installations, etc. The cool part about the site being hosted in SharePoint Online is that a customer can get set up as a federated partner and be granted access to see equipment readiness and installation scheduling within our system.

Guest access is an interesting concept – while I have an account in our tenant that is linked to my Active Directory account in our domain, you can also create links to accounts in other company’s directories. The guest account can then be set up to access our Azure resources – added to Azure groups, added to SharePoint Online groups, invited to join Teams.

A guest user had her computer replaced and could no longer access the site – SPO insisted that she was not a valid user. Looking in Azure AD, the account existed; the audit log even showed successful authentication events. I’m not sure if the computer replacement was a coincidence, the new computer had a different configuration, or if your browser stashes some information that allowed her to avoid authentication failures, but her guest account in our tenant was no longer working.

For companies that don’t have Azure AD, when an individual accepts guest account access … the guest account link in our tenant lists “Microsoft Account” as the source.

But when the company sets up Azure, the auth framework seems to get confused by the Azure AD account. Easy enough solution – we’ve got to delete the guest account that’s linked to their MS Account from Azure AD. Bonus step specific to SPO, a site administrator needs to use <site>/_layouts/15/people.aspx?MembershipGroupId=0 to delete the guest account from the SPO site.


Once the “Microsoft Account” guest account has been removed, the guest can be re-invited. They’ll step through the registration process again but the guest account will be linked up to their Azure AD account.

 Re-add the new guest account to whatever they were using & their access will be restored.


SharePoint Rest API Does Not Allow Unindexed Queries

I’ve been developing code templates for CRUD operations (that’s a real acronym — Create, Read, Update, Delete) against SharePoint — we need to use SharePoint lists to replace database tables. Retrieving information worked fine until I tried to filter the data through the REST call. SharePoint throws a generic error about exceeding some admin-set limit. (1) I know the limit, I can see the limit. The limit is 5,000, and I know my filtered result set is 121 records. WAY lower than 5,000. Oh, and (2) I can run the query without the filter — I’m paging it! — and read all 29,887 records so what does the limit have to do with anything? Reasoning with an HTTP response … well, doesn’t work. No matter how unassailable my argument is, the API call still returned:

    {"code":"-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
       "value":"The attempted operation is prohibited because it exceeds the list 
                view threshold enforced by the administrator."}}}

It is, it turns out, a poorly worded error. I started thinking about the query limits on my LDAP servers — we have hard limits to operations and also require most people perform queries against indexed attributes. It’s computationally expensive to search through unindexed attributes (and the Right Thing To Do, generally speaking, is add an index for something that is a frequent query target). I wondered if there was an analogous “no unindexed queries” setting in SharePoint. Quick enough to test — add an index on the column(s) you use in the filter. In the site content listing, click the sideways hamburger menu by the list name. Select “Settings”

Scroll down to Index Columns and click the hyperlink.

Click ‘Create a new index’

Wait for the index process to complete, then try the filtered request again … I’ve got data! Evidently SharePoint ODATA filter queries to the REST API need to be performed against indexed columns. I’m sure Microsoft has that documented somewhere but quite a bit of Googling didn’t get me anywhere … so I’m posting this in case anyone else encounters the same error.

Did you know … you can be notified when changed are made to a SharePoint Online Wiki?

My group has started using a SharePoint Online Wiki – it’s a quick way to have an aesthetically pleasing knowledge base, but I’d rather not have to check “Updated Pages” periodically to see if there’s new content. Configuring SharePoint to notify me when new pages are added (or existing pages updated) avoids needing to check for new content.

From the Wiki, click “Page”

On the “Page” ribbon bar, select “View All Pages”

Click “Library” in the ribbon bar, then click the “Alert Me” drop-down.

Select “Set an alert on this library”

Configure your alert – you can receive notifications when new pages changed or added or only when new pages are added.

You can filter out changes you make by selecting “Someone else changes a wiki page”, and you can receive digest updates instead of getting a notification for each individual change. Click “OK” to save the notification, and you’ll start getting e-mails when the Wiki is updated.

If you want to adjust or cease receiving the alerts, select “Manage My Alerts” instead of ‘Set alert on this library’

Click on the alert name to edit the alert (or check the box in front of the alert name and select “Delete Selected Alerts” to remove it).