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”

clip_image001

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

clip_image002

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)

clip_image001[1]

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.

clip_image003

$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"

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’

image

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.

image

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

files

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

excel

Get End User OneDrive URL with PowerShell

This minor task was part of a major activity I carried out for one of the clients recently here in Australia. Used the SharePoint PnP PowerShell to do this, pretty simple with the commands and controls it offers.

conecting

SharePoint PnP cmdlets reference – https://github.com/SharePoint/PnP-PowerShell

First and foremost, ensure you have installed the latest PnP module in your machine. Run this cmdlet to get the latest bits installed

Update-Module SharePointPnPPowerShell*

pnpmodule1

pnpmodule2

Now verify the version by running this

Get-Module SharePointPnPPowerShell* -ListAvailable | Select-Object Name,Version | Sort-Object Version -Descending

Firstly you must connect to SharePoint Online using Connect-PnPOnline cmdlet (none MFA environments)

$cred = Get-Credential
Connect-PnPOnline -Url https://<tenant>-admin.sharepoint.com/ -Credentials $cred

image

If your Office 365 Environment is MFA enabled, use this instead (Notice the login is different to the traditional method)

Connect-PnPOnline -Url https://sitename-admin.sharepoint.com -UseWebLogin

weblogin

Now we can run the Get-PnPUserProfileProperty cmdlet to get the information about the user’s profile and select only the PersonalUrl which is the URL of that user’s OneDrive for Business.

$username = "<UserName>"
$OneDriveUrl = Get-PnPUserProfileProperty -Account $username | select PersonalUrl
$OneDriveUrl
$username = "<UserName>" $OneDriveUrl = Get-PnPUserProfileProperty -Account
$username | select PersonalUrl

GetOneDrive

There is it ! that’s the ultimate end user URL of OneDrive

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.

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.