Microsoft Flow in a real world scenario: using Office Quick Parts to fill out documents (part 02)

Part 02 (This article)

Email notification on smartphone in hand. New mail message in inbox, mailing letters or reading sms on mobile phone vector illustration

After the last step in the part 01 of this article series, we  are resuming with this flow. We now can save this, and quickly give it a test run if it delivers what we need.

Hit “Save”  button and check the flow for any errors from “Flow Checker” option on the top right corner. Also,  you can test any flow straightaway from here.

Today27_thumb[2]

Let’s do a dry run here. You have two options to test it out. Use data from the last run or trigger as a new instance. Hit “Save and Test” to begin.

Today28_thumb[2]

And hit “Continue” to start it.

Today23_thumb[2]

Inputs to be done here for the metadata fields we mapped and then simply hit “Run

Today24_thumb[2]

Flow also has an wonderful activity tracker. A cool new interface allows you to monitor your Flow activities from a one place.

Today25_thumb[2]

Click on “See flow run activity” to find out the status of this instance. This run has been succeeded.

Today26_thumb[2]

We can go back to the library and open up the document to see if our Quick Parts have been updated as per this run. Bare in mind that Word Online might have an issue when you open this kind of a document. Quick parts are working well with Office Desktop Application and Word online Preview Mode but, unfortunately having some issues in Word Online in edit mode. Therefore, make sure you test it with Word Application before you wonder where it went wrong as quick parts may still show empty in online mode even after running the Flow successfully.

Here’s how it should look like after the execution.

Today30_thumb[2]

Sending Email Alerts up on completion

After composing of the document, we can configure the flow to send out email alerts. In this scenario, I’m going to send alerts to the person who triggered this flow, alerting him with the composed document as an attachment.

Let’s go ahead and add a new step as usual.

Today31

Search for “Get file content” SharePoint action

Today37

We have to rename it as “Get file content new” so it makes better sense as we already have a step with the default name.

Today38

Next up, parameters. Choose the appropriate site URL from the drop down and file identified has to be the ID. Leave the “Infer Content Type” with its default and that’s it.

Today39

Now, add another new step which we will be using to configure the email alert. search for “Send an email” and select the “Send an email (V2) (Preview)”

Today32

Parameters to be configured as shown below. search for “User email” attribute for Receiver field and choose it.

Today33

My subject line will be “Text: customer name field” concatenated. Feel free to have your own here. Type the text and look for “Customer Name” attribute and place it next to the text in the subject line as shown below.

The Body will be just a plain text which indicates the purpose of this mail. Again, it’s all up to you to insert anything here.

Today34

For the attachment name, use File name with extension, from the Update file properties section.  For the File Content, get that from the Get file content new file section.

Today35

You can extend the content section by adding more attachment/content but that’s optional. just leave the “Importance” as normal here which depends on your situation though. Feel free to adjust these parameters as required.

Today36

Now the exciting part !. We can run our full flow to see if everything works as expected. So I will go ahead and kick it.

Today40

Click on “see flow run activity” to see the status of this instance.

Today41

And click again on the latest instance from the history of this flow.

Today42

You will see the entire history of the instance selected. And best of all, in just a simple click you can see the action result in a handy dandy UI ! Isn’t this amazing ?

Today43

Now let’s switch to our outlook account. In my case it’s my account which was used to kick the Flow. And, I can see the latest item with all our parameters including the attachment which means the hard works have paid off !!

Today44

In addition to that, Flow Dashboard is a great place to keep up with all your things around Flows across the entire tenant. You can turn on/off a flow with just a button swipe!. Statuses and other handy dandy stuff all brought in to a one place to make your life more easier.

 Today29_thumb[2]

Until next flow post, Happy Flowing !

Start over with part 01 

DISCLAIMER NOTE: This is an enthusiast post and is not sponsored by Microsoft or any other vendor.

Advertisement

Microsoft Flow in a real world scenario: using Office Quick Parts to fill out documents (Part 01)

Part 01 (This Article)

Yes ! a simple and yet solid Microsoft Flow can be configured to fill-out a Word Document stored in your SharePoint library in conjunction with Quick Parts.

7-AutomationCrosstheNation-1600x900-1600x900

Image Courtesy: brookfieldinstitute

With this blog series, I’m going to demonstrate, how you can utilize legacy and yet famous Office Quick Parts with Microsoft Flow to optimize your content creation!. Well, Quick Parts aren’t new, they have been in Office suite since 2010 but still being famous among content creators due to its usability. 

To get started, you need the following fundamentals:

  • Office 365 subscription with SharePoint, MS Flow Capabilities
  • A document library to store documents
  • Microsoft Office Application Installed in your PC or Mac

Once you have them, simply log in to SharePoint online and open up the desired site. You need a document library to store documents and also relevant fields (metadata) to be created to match our quick parts (Date, Company Name, Project Name etc..).

In my case, I have a simple library with few metadata fields created as below.

A

Following fields in blue, are our quick parts that we will be linking with the SharePoint library fields. They can be belong to a content type (centralized) or a specific to an library, nevertheless we can use quick parts in a document so that anyone can easily fill them out as part of a process.

Today4

Once you have added columns in to the library (much more easier in Modern SharePoint experience!), create a new Word document in that library or if you already have a document, just upload it to the library (just drag and drop). my library is the default “Shared Document” and the columns are “Customer, Project name, Date, Project value”, just four, simple !

Once you have uploaded, open it from Word application as shown below.

Today1

From Word, direct to Insert –> Quick Parts –> Document Property –> Choose the desired field/s

Today2

Here I’m adding Customer field which looks like below once added.

Today3

You have to click on each spot where you want Quick part to be placed and it will insert accordingly. I have added some fields multiple times here which is perfectly fine. Normally, a single field value can be used in various location across a document.

Today4

Changes are usually saved automatically. If you notice the save icon on the top bar of Word Application. So just go ahead and close this now.

Today5

Let’s head back to our library and get started with building the Flow for this scenario. Its easier to work with any office 365 tool nowadays due to the ultimate UI enhancements Microsoft have recently done.

Click on Flow –> Create a flow, from the Library

Today6

You will now be redirected to Flow web site and you must sign in using your O365 account to be able to go ahead from here onwards.

Once you signed in, choose New –> Create from blank from the Microsoft Flow home page. Pretty simple right ?

Today7

Type in “Manual” and hit “Enter” to search. Select the “Manually triggered button” as shown below.

Today8

Next up, we have to choose our SharePoint action. Type in “Get File Content” and select the highlighted action to proceed.

Today9

On the trigger options, Choose appropriate input types to match your fields. Keep in mind that your trigger can be whatever you want it to be, in my scenario, I’m using a manual trigger which is easy for me to demonstrate the use case.

Today12

Now to format our input parameters in the trigger. In my case, I’m adding 4 input types (Date, text and number) according to the Quick Parts I have. You can have more or less, inputs based on your story.

Customer: Text Field | Amount: Number | Doc Date: Date | Project: Text

Today13

Next up, is to configure our 1st SharePoint action to act as we want. we have to bind our SharePoint library to the flow. Choose the site where you have the library from the dropdown. I am using SharePoint because, in this case that’s is where my data resides, but, you can use various other services such as Salesforce, or PowerApps to bring inputs in to the file. The idea of this post is to demonstrate that how data coming from (anywhere) can be automatically entered in to a Word document.

Today10

And select the document library from the listed ones. Leave “Infer Content Type” as Yes as well.

Today11

Now let’s add a new action here. Click on “New” and add the Create File SharePoint action as below.

Today16

This action will update the file properties when the flow is triggered. Select the Site address and folder path (Library). Define the File name by choosing the appropriate fields as you wish. File content should come from the File Content Action itself.

Today17

Once this is done, the Flow so far would look like this. make sure you map the fields properly so you wont have any errors during the first run. A positive first impression is something awesome you know that !!

Today18

Then, click on “New” and search for “Update file properties” SharePoint action from the list to add our next action.

Today19

In here too, we have to configure some parameters to reflect fields and quick parts appropriately. Choose the same site and libraries and Item ID should be “Item ID” from create file. I am using Customer Name field as the title here but its up to you to have your own choice.

Today20

Continue to read part 02

DISCLAIMER NOTE: This is an enthusiast post and is not sponsored by Microsoft or any other vendor.

No-Code column formatting in SharePoint Online

SharePoint Online now offers a simple way of formatting columns even if you don’t have any programming experience. Knowing a bit of JSON would be great indeed because, for now this out of the box capability is limited to few types of columns. Yet, you can use JSON for other types that are not supported out of the box.

There are two ways to apply column formatting:

  1. You can apply column formatting to an existing column by selecting the column drop down and choosing column settings –> format this column.
  2. Or you can apply column formatting during the creation of the column, or by editing the properties of the column.

Right now formatting works for 3 columns types:

  • Yes/No (Boolean)
  • Choice
  • Date

In this example scenario, I have a simple list with 3 columns and we will use the 2nd approach to apply colors. This list contains few records of events which I would like to apply color formatting based on the status of each record.

There has to be a logic behind the field in order to apply a formatting, hence, it’s obvious that you do not need formatting for every column. e.g. Choice filed always contains multiple values which makes scenes (if value equals true =color the record with Green) whereas in a text field there is no such a logic.

clip_image002

Format section is opened nicely on the right pane. Now it just a matter of choosing the appropriate color for the particular column. Each value of the field can be colored so that the end users can easily recognize the status of the record.

To change the color, simply click on the color pallet icon and choose the color.

clip_image004

clip_image006

In this sample, I have defined Green as completed, Blue for Planning and Pink for Scheduled activities accordingly. This change is applicable for all records of this including the existing and new. Any new records shall impact the color formatting.

clip_image008

Advanced Mode is here. If you have your own JSON, go ahead and paste it here and hit “Save” to publish it.

clip_image010

After formatting the columns, it’s still possible to see the code and tweak it if you’d like. Even for unsupported columns, you can paste your JSON code to apply the formatting even though there is no out of the box formatting available as of now.  Go to list/library settings, and click the name of a column.  Scroll down to see the Column Formatting box.

clip_image012

Limiting SharePoint People Picker field to an specific group of people

People picker is a vital field in SharePoint which allows you to pick a person or a group from your organization’s active directory. With this post I’m going to emphasize one of the out of the box ability of the people picker. We can restrict/limit this field for a specific group or people so that the person who inters data would only able to retrieve set of targeted people within a group (e.g. A sales person can only fetch another coworker in his team).

Firstly you have to have a target group. If you don’t have one, go ahead and get it created and then return to your target List/library and add a new “Person or Group” field by clicking “Add New Column

clip_image001[7]

Choose the type of it as “Person or Group

clip_image002[6]

Better to have a meaningful description too, for this field and the selection has to be “People Only“. In the “Choose From” option, select your target group which you have created.

This simply means the person who entering data can only retrieve people within this group. That’s it and you can go ahead and save this change.

clip_image003

Now let’s try to insert a record here. As you can see, even though I have hundreds of users in my AD, I am not able to retrieve any entry here because my target group is empty. Once I add people there only I can retrieve them.

clip_image004

So let’s try to add a user in to the group. I have added myself in to “Internal IT” group.

clip_image005

clip_image006

It comes up as you can see here. Just a matter of saving the record now

clip_image007

That’s it !

clip_image008

Zipped Files Experience in SharePoint

SharePoint online and OneDrive for business now lets you upload and use zipped files. Zipped files such as .zip or .rar works seamlessly in OneDrive and SharePoint even better than in your device. Not necessary to hesitate about having a plugin or client, now do you have to manually extract to explore the content. It’s just simple as clicking on the file and you will have it explored right there.

To try this out simply login to your SharePoint online portal or OneDrive library and upload a zipped file.

clip_image001

Also, if you have a folder structure within a zipped file with multiple folders inside it, you are able to navigate across them back and forth which makes it easier to use.

clip_image002

clip_image003

Find and export the list of users who has not completed About Me section in their Office 365 profile

This is the article 08 in this series.

Having a complete profile in Office365 is not just a benefit for user himself, but for the entire organization which directly impacts on productivity. A finished profile leads to better visibility and eventually results in faster communications across hundreds of thousands of employees in an enterprise setup. Ultimate idea of Office Delve (latest interface of profile comes with more capabilities such as recent activities) is to provide overall insights of a user information and his activities/engagements which makes obvious sense for anyone.

delve_thumb3

Nevertheless, none of these would be in action unless you have a complete profile with basic details entered in. No matter how much HR would try to push, we still spot a lot of random users who haven’t completed their profiles.

With this short and sweet article series, I’m trying to give you the steps that we followed during the identifying process as requested by our HR. screenshots may differ than our production setup, but you surely will get the point here.

I had to use some PowerShell scripting to get this list out from Office 365 and generate a CSV file for each criteria so that HR can directly reach out to the user via emails and advice to take an action to update the profile on the spot. As a result, we were able to get 100% completeness of profiles across a 5000+ employee organization.

There is no out of the box reporting when it comes to Profile Completeness in Office 365, therefore we have no option other than PowerShell. PowerShell is the ultimate tool for O365 administration, whenever graphical interface has a barrier, hence, make sure you dig around it to understand its capabilities to go beyond.

FINDING USERS WITH EMPTY ABOUT ME SECTION IN THEIR PROFILE (This Article)

In this article I’m trying to explain the steps it takes to find out the users who has not filled “About Me” section in their Office 365 profile (or simply, delve profile).

So here we go, following are the requirements before we get started:

  • Azure AD PowerShell Module – download here
  • Azure AD Administrator rights
  • SharePoint Online Administrator rights
  • SharePoint Online PnP Module – Download here

Script steps breakdown:

First and foremost, we need to fetch the Office365 credentials and then connect to both SharePoint Online Admin Centre and Azure Active Directory.

$cred = get-Credential
Connect-AzureAD -Credential $cred
Connect-PnpOnline -Url https://mantoso-admin.sharepoint.com/ -Credentials $cred

Then let’s fetch all users in this tenant, who are internal to the company and that have at least one license assigned to them.

$Users = Get-AzureADUser | Where {$_.UserType -eq 'Member' -and $_.AssignedLicenses -ne $null}

Now to create an empty array in which we will later store the output (user list who has not filled the About me field).

$NoAMUsers = @()

Now we will dig in through each user, and check if they hold a SharePoint profile (This is because About Me field is hosted in SharePoint online, not in Azure AD). If the property exists, and empty, it simply means the About me section has not filled by this user.

foreach ($user in $Users) 
{
    $SPProfile  = Get-PnPUserProfileProperty -Account $user.UserPrincipalName -ErrorAction SilentlyContinue
        if ($SPProfile -ne $null)
        {
          if ($SPProfile.UserProfileProperties.AboutMe -eq "")
            {
               $NoAMUsers += $user
            }
        }
}

And, finally we can export the SharePoint result to a CSV through below part.

$NoAMUsers | Select DisplayName, UserPrincipalName | Export-Csv -Path "C:\Tools\reports\NoAMUsers.csv" -NoTypeInformation

If you need to obtain a similar report on other user criteria’s, here are the other articles of this series which would help you to achieve it.

  1. Find and export list of users with no Manager Name set in Office 365 profile:
  2. Find and export list of users with no Manager Name set in Office 365 profile:
  3. Find and export list of users with no Profile Picture set in Office 365 Account:
  4. Find and export list of users with no Birthday set in Office 365 profile:
  5. Find and export list of users with no Country set in Office 365 Profile:
  6. Find and export list of users with no Department set in Office 365 Profile:
  7. Find and export list of users with no Skills Defined in Office 365 profile:
  8. Find and export the list of users who has not completed About Me section in their Office 365 profile

DISCLAIMER NOTE: This is an enthusiast post and is not sponsored by Microsoft or any other vendor.

Embedding an Excel Spreadsheet in to a Modern SharePoint Page

You might have noticed that SharePoint no longer offers some of its native features. Excel Web part is one of them which was announced my Microsoft sometimes back. With that, we lost the ability to integrate an Excel sheet in to a SharePoint page using that Web part, however, worry no further because, we still can do the same without it.

With this post, we are going to find out an alternative for this struggle in SharePoint online. We now have fantastic looking, flat and neat pages in SharePoint online, thanks to Microsoft for standardizing the user experience in a better way. Editing pages and organizing items inside them in a much more content oriented manner is no longer a hard job, because there are much more out of the box features in SPO than it supposed to few years ago.

With that in mind, I will go ahead open up my SPO document library to upload my excel spreadsheet which I’m planning to embed in to the page.

22

After uploading the file, simply click on it to open it from the browser.

excel0

Then head on to File –> Share –> Click “Embed

excel1

Now go ahead and copy that embed code from here. There are plenty of preferences for you to customize here as well. You can choose to appear it as you want or even restrict from behaviors when users interacting with it. Dimensions will help you to adjust it right to the page as well. So do not hesitate to check them out and preview it Realtime on the window right pane.

excel2

Now let’s go back to SharePoint page which I already have (you can create  anew page and embed it there if you do not want to put it in the home page. In my case I will just embed to the Home page.

Click on “+” button to add a Webpart. You have to add the “Embed” Webpart here.

excel3

Once you choose that, you will be prompted with the following screen. nothing much to do here except pasting our embed code we copied from the Excel before. And hit “Publish” to finish it.

excel4

You now have an excel sheet nicely embedded to your Modern SharePoint page even though there is no Excel Webpart.

Configure OneDrive access delegation up on account removal

When you remove a user from Office 365 or Active Directory, you can decide what to do with this user’s content, e-mail account and related product licenses. For more information on this refer to my previous article- Things to consider when deleting a user account from an Office 365 subscription.

Access%20Delegation-01

With this article, we will discover how we can configure OneDrive to automatically delegate access to someone else up on a user’s departure (after the removal of the account). In simple terms, let’s configure delegation.

Default setting for a deleted user is- the access granted to the Manager of the particular user for 30 days period (unless you have customized the retention period). But, what if a user has no Manager defined and access delegation is disabled, too ? In that case, OneDrive will follow the steps described in my previous article (Things to consider when deleting a user account from Office 365). So make sure you read through that, too.

Here are the steps:

Sign in to Office 365 Admin Center as an Global Admin. If you are prompted with a Access message, probably you do not have Global Admin rights. You may either have to request or perhaps you may not be the right person to do this task in your organization so refer to the right guy.

Next up, Expand the navigation with “Show all” option.

clip_image002_thumb[2]

Head on to “All admin centers

clip_image003_thumb[2]

And choose “SharePoint Admin Center

1

From here let’s head on to Classic SharePoint Admin site because these settings we are going to manage are not yet available in Modern SharePoint Admin Interface.

2

Once you are in the classic page, go to “User profiles” tab.

4 

Then “Setup My Sites” from My Site Settings tab.

3

Now scroll down to the bottom of Setup My Sites page till you see the following screen.

Enable the access delegation here and define a secondary owner as well. As it described well in the description:

  • Access delegation option allows OneDrive to automatically delegate the control to Manager up on any user identity removal.
  • If you define a secondary owner, that might be useful in a scenario where the Manager of a particular user is unavailable but OneDrive is still delegated to the secondary owner.

5

Additionally, you can enable this option as well. It simply means that you can have a one person who is the secondary owner of all user’s Mysite/OneDrive content.

6

Read my previous post to understand the fundamental things to be considered when deleting a user account from Office 365 and Customize OneDrive retention period article to set your own retention period for OneDrive accounts.

DISCLAIMER NOTE: This is an enthusiast post and is not sponsored by Microsoft or any other vendor.

Keep it simple: Adding bulk set of users to a SharePoint Group using REST API

Large enterprises always require bulk operations to make things faster and easier. Recently I faced a situation where hundreds of users needed to be added to SharePoint Groups. This is a time consuming task in large SharePoint setups when you have to manually add users one by one. No ! Manual approach isn’t going to work for such things.

rube-goldberg-machine-100593802-primary.idge

REST API in SharePoint can be used in this scenario to cut down the efforts and time from hours or even days to few seconds. with this post I will discuss the way I achieved this goal so that you can follow me if you are in such a situation.

Background: I have an excel sheet filled with all required users with the UserName column. This excel spreadsheet is uplaoded to the SiteAssets library. This code will be looking at this library and read the excel sheet to pick the users from it.  Simple as that !

Change the site URL attribute’s value to reflect yours and you are good to go



var i;
var l;
var a1;
var user;
var spGroup;
function AddUserFromExcel()
{
l=1;
var Excel;
Excel = new ActiveXObject(“Excel.Application”);
Excel.Visible = false;
for(l=1;l<3;l++)
{
a1=Excel.Workbooks.Open(“http://mantososp/SiteAssets/Users.xlsx?Web=1”).ActiveSheet.Cells(l,1).Value;
var a4=”Domain\\”+a1;
var clientContext = new SP.ClientContext.get_current();
var web=clientContext.get_web();
var siteGroups = clientContext.get_web().get_siteGroups();
spGroup=siteGroups.getById(4990);
user=web.ensureUser(a4);
var userCollection=spGroup.get_users();
userCollection.addUser(user);
clientContext.load(user);
clientContext.load(spGroup);
clientContext.executeQueryAsync(onQuerySucceeded, onQueryFailed);


function onQuerySucceeded()
{
alert(‘success’);
}
function onQueryFailed()
{
alert(‘Request failed.’);
}
}
}



var i;
var l;
var a1;
var user;
var spGroup;
function AddUserFromExcel()
{
l=1;
var Excel;
Excel = new ActiveXObject(“Excel.Application”);
Excel.Visible = false;
for(l=1;l<3;l++)
{
a1=Excel.Workbooks.Open(“http://mantososp/SiteAssets/Users.xlsx?Web=1”).ActiveSheet.Cells(l,1).Value;
var a4=”Domain\\”+a1;
var clientContext = new SP.ClientContext.get_current();
var web=clientContext.get_web();
var siteGroups = clientContext.get_web().get_siteGroups();
spGroup=siteGroups.getById(4990);
user=web.ensureUser(a4);
var userCollection=spGroup.get_users();
userCollection.addUser(user);
clientContext.load(user);
clientContext.load(spGroup);
clientContext.executeQueryAsync(onQuerySucceeded, onQueryFailed);


function onQuerySucceeded()
{
alert(‘success’);
}
function onQueryFailed()
{
alert(‘Request failed.’);
}
}
}

Fix: SharePoint blank site template gone missing

imageWe used blank templates back in SharePoint 2010 days for various reasons but it isn’t there anymore as being hidden from 2013 onwards. As in this screenshot, when you open up Central Administration and try to create a Site Collection, “Blank Site” Template will not be visible in SharePoint 2013 or later versions.

100

Nevertheless, worry no further as this article will demonstrate the way to get it back just in case you badly need it. Just follow the steps (recommended to try in a test setup before you have it in production environment).

There are two ways to enable it again.

Method A:

This is a permanent change which allows you to use this template anytime. However, you can revert it back if not necessary. Find this folder in your SharePoint Server/s – C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\1033\XML

Then from there, open up WEBTEMP.xml file from the notepad and search for this tag line.

<Configuration ID=”1″ Title=”Blank Site” Hidden=”TRUE” ImageUrl=”/_layouts/15/images/stbs.png?rev=23″ Description=”A blank site for you to customize based on your requirements.” DisplayCategory=”Collaboration” AllowGlobalFeatureAssociations=”False” > </Configuration>

You can clearly see the Hidden attribute has been set to TRUE here on the 1st line. Make it back to “FALSE” and the tag should now look like below.

<Configuration ID=”1″ Title=”Blank Site” Hidden=”FALSE” ImageUrl=”/_layouts/15/images/stbs.png?rev=23″ Description=”A blank site for you to customize based on your requirements.” DisplayCategory=”Collaboration” AllowGlobalFeatureAssociations=”False” > </Configuration>

Save the changes and close the file then do a IIS reset on the target server/s.

Method B:

You can use PowerShell to create a site using Blank Site template as well. This is a temporary method where you just use the hidden template by calling it without seeing it in the “Create new site” choice list, and, you have to use PowerShell to provision the site.

Add-PSSnapin “Microsoft.SharePoint.PowerShell”

Get-SPWebTemplate

$template = Get-SPWebTemplate “STS#1”

Then run this: It will simply create a brand new site collection using the “Blank Site” template for you. There is no need of changing the backend if you choose this way,  however, there’s no UI to get this done.

New-SPSite -Url “http://mantosospfarm/sites/NewSiteName” -OwnerAlias “Mantoso\SP_Admin” -Template $template