Change default File Open Behavior of SharePoint Online

The default option in SharePoint online to open files stored in document libraries is “Open in Browser”. You can leave it like that as long as you don’t have an specific requirement to change this behavior.

In some cases, end users prefer to open file by client application due to many reasons and they are fair reasons, mostly.

1. Client application offers rich capabilities which allows users to get things done effectively and efficiently
2. Some organizations using SharePoint as the central document management platform across the entire company. At this point they might prefer to have the files opened by client application as default.

There are various ways to enable this functionality which impact different levels.

1. Document library level
2. Single site collection level
3. Across the entire tenant

If you’d like to set a specific document library to open files in client application by default. Simply log in to your SharePoint online tenant, direct to the desired library and select the radio button under the Advance Settings as you wish (This overrides the setting applied at site collection level and open documents in client application instead of browser)

Navigate to the Document Library –> Click on Settings gear –> Library Settings from the menu.

Under General Settings –> Click on “Advanced Settings”

To enable this for a specific site collection (applies for the entire collection unless you have chosen from individual libraries manually as shown in the screenshot above to opt out)

To do this for an specific site collection, we have to activate a site collection level feature. Simply log in to your SharePoint online tenant, direct to the desired site.

Go to Site Settings –> Site Collection Features

Click on “Activate” button next to “Open Documents in Client Applications by Default” feature

You can use the following PowerShell script to do get the same thing done in a bulk mode across all site collections in the tenant (ORG WIDE).

This is a SharePoint PnP PowerShell script which uses an CSV file as the source for site names.

1. First you have to get all the site URLs exported from the SharePoint Admin Centre in Office 365 Admin Portal
2. Then save it as an CSV file and point this script to that file (Change the CSV path in the script)

Your CSV should look like this (Site URLs separated in to individual columns, not rows. If you are having hard time getting this format, it’s quite easy, use the Transpose feature under Paste special)

Note: Obviously, this script will only cover the existing site collections of your tenant. For any upcoming new site collection created after running this has to enable it manually again.

$username = Read-Host "Provide the username"
$password = Read-Host -Prompt "Password for $username" -AsSecureString
$O365credential = New-Object PSCredential($userName,$passWord)

# Chnage CSV path here
$site = Import-csv C:\Official\Tools\remain.csv

Foreach ($URL in $site.URL)
{
try {
    Connect-PnPOnline -Url $URL -Credentials $O365credential

    Write-Host "Connected to " $URL 
    Write-Host "Enabling features on" $URL 

	# Enter Feature Id & scope

    Enable-PnPFeature -Identity 8a4b8de2-6fd8-41e9-923c-c7c3c00f8295 -Force -Scope Site

    Write-Host "Disconnecting from " $URL 
    Disconnect-PnPOnline    
    }
    Catch 
    {
    Write-Host "Got error" $error
    }
}
Write-Host "Completed"

Advertisement

Excel unable to access SharePoint Online files, fails with an error “sorry we couldn’t open”

This happened to few of my clients time to time in SharePoint online environments. You may have seen it but weirdly for some users only? You are not alone.

One of the errors is ‘Sorry we couldn’t open https://mantoso.sharepoint.com/DocumentLibrary/excelfile.xlsx’

And, the other error is – ‘Microsoft Excel cannot access the file ‘https://mantoso.sharepoint.com/DocumentLibrary/excelfile.xlsx’. There are several possible reasons:

• The file name or path does not exist.
  
• The file is being used by another program.
  
• The workbook you are trying to save has the same name as a currently open workbook.’

This issue in my perspective, can be caused by Office Document Cache of your Office Desktop application. Here’s how I managed to get rid of it.

Open Windows Explorer and copy and paste one of the following locations into the address bar:

Clearing Office Document Cache for Office 2016

%localappdata%\Microsoft\Office\16.0\OfficeFileCache

Clearing Office Document Cache for Office 2013

%localappdata%\Microsoft\Office\15.0\OfficeFileCache

Select all files beginning with ‘FS’

And delete those files. Restart the Excel application and you should now be able to open files from SharePoint.

OneDrive sync error: ‘You are already syncing this account’

This message appeared on plenty of end user devices across many of my clients when they try to synchronize their SharePoint Libraries using OneDrive sync client (Not OneDrive library itself though), no big deal, it was all about browser in our case (could differ in some case as well, I presume). The exact error is ‘You’re already syncing this account’. Open your OneDrive – Your Organization Name folder or sign in with a different account’

A common nature of the scenario was that, everyone got this error was using either Microsoft Edge or Internet Explorer. The immediate solution was using an alternative browser instead, we tried Chrome and it worked like charm.

When Chrome prompted options, choose: ‘Open URL : OneDrive client protocol’

And, you can now start syncing the library

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

Part 02 (This article)

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.

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.

And hit “Continue” to start it.

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

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

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

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.

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.

Search for “Get file content” SharePoint action

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.

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.

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)”

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

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.

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.

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.

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.

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

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

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 ?

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 !!

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.

 

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.

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.

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.

Azure AD App Only Authentication

In a simple way, App Only authentication is the ideal method if you want to execute  a task by daemon. This allows you to execute some code without the permissions of a user or without an auth token of a user.

As part of a series of articles, idea of this 1st post is to give you an basic  fundamental understanding on creating an Azure AD App and grant permissions for this App to communicate with SPO.

let’s get this started. Simply head on to your Office365 home page and switch to Admin Centers. From the left pane, click on “Azure Active Directory”. From Azure AD, search for “App Registrations” and click “Add new application registration” link.

A new application interface will pop-up for you. Enter a name, Application type and Sign-on URL and click “Create”. Sign-in URL can be any and it also can be amended later to reflect a different one. A future post will discuss this again on what sort of URLs are used here.

 

Once the app creation done, you will be given with the app ID and other details related to it.

Next- Select Settings –> Required permissions and Add

In this case the API going to be SPO. You can choose the right API based on the requirement.

Next, hit “Grant Permission” button on the required permissions tab to provide none-tenant admin user access the application.

A self-signed or public (commercial) certificate must be provided now and then update the Azure AD manifest accordingly.

Following PS can be used to provision the certificate but ensure you have installed OfficeDev PnP PowerShell.

$certroot = 'C:\Site Creator'
$certname = "IntelAi-Cert-1"
$password = ConvertTo-SecureString "P@$$w0rd" -AsPlainText -Force
$startdate = Get-Date
$enddate = $startdate.AddYears(4)
makecert.exe -r -pe -n "CN=$certname" -b ($startdate.ToString("MM/dd/yyyy")) -e ($enddate.ToString("MM/dd/yyyy")) -ss my -len 2048
$cert = Get-ChildItem Cert:\CurrentUser\My | ? {$_.Subject -eq "CN=$certname"}
Export-Certificate -Type CERT -FilePath "$certroot\$certname.cer" -Cert $cert -Force
Export-PfxCertificate -FilePath "$certroot\$certname.pfx" -Cert $cert -Password $password -Force

Following line will copy a string to your clipboard

Get-PnPAzureADManifestKeyCredentials -CertPath 'C:\Site Creator\IntelAi-Cert-1.cer' | clip

Following is how the copied string would look like. It has to be added to the manifest file of the Azure AD application.

"keyCredentials": [
 {
  "customKeyIdentifier": "5lca+kziogw7T6MB4kUrxseK5m8=",
  "keyId": "84153f1a-90b7-4802-b99a-bb75d4f9a35b",
  "type": "AsymmetricX509Cert",
  "usage": "Verify",
  "value": "MIIDAjCCAe6gAwIBAgIQkawCJU0cWYxH8RamKNuqqTAJBgUrDgMCHQUAMBkx
 }
],

Select your application under app registrations in Azure AD. Replace the “KeyCredentials”:[], section, as shown below.

Now this can be tested whether the application has required permissions to connect to the SharePoint Online site. For the ClientID, you need to provide application ID of the app you have created.

$password = ConvertTo-SecureString "P@$$w0rd" -AsPlainText -Force
Connect-PnPOnline -Url https://site.sharepoint.com/ -ClientId 0c01f61e-ba27-4ae7-ab19-174884a949fc -CertificatePath 'C:\Site Creator\Site-Cert-1.pfx' -CertificatePassword $password -Tenant intelai.onmicrosoft.com
$myWeb = Get-PnPWeb
$myWeb.Title

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