Monitoring SharePoint Online Storage Pool Using a PowerAutomate Flow

<

Fixed: Outlook desktop unable to render SharePoint hosted images embedded in to mail body through PowerAutomate send email action

This post comes out from a recent support experience to one of my clients. He was trying to use Microsoft PowerAutomate flow to embed a SharePoint library hosted image in to an email notification which sent regularly across the organization for all users. Usual way for doing this is using the <img src tag in the send email action but interestingly, these behaviors was a showstopper until I figured out the solution after several hours (described at the end of the article).

Behaviors:

1. Embedded images from a SharePoint URL did not render in Outlook desktop App
2. Same image renders in OWA (Outlook Web App) however, there was a slight delay in loading the image.
3. Same image hosted in public hosting locations rendered nicely as well (e.g – https://www.w3schools.com/, Google etc..)

SharePoint hosted image in Outlook Desktop App: Strange enough !

External hosted image in Outlook Desktop App:

Tried and did not work for my case:

• Change the text format to HTMLOutlook client application (File -> Options -> Mail -> Compose message in HTML format). This was already in place for my scenario.
• Add the domain to safe/trusted sites in the browser settings
• Plethora of other solutions from Microsoft articles and forums

The real solution:

To overcome this issue, you simply have to use these additional lines in your email step. Add a compose action and point it to ‘Get file content using path’ SharePoint action (or you can directly add this line to the mail body HTML)

Add a new Compose action in to the PowerAutomate flow 

Set the Expression as below (you can copy and paste this)

dataUri(outputs('Get_file_content_using_path')?['body'])

Amend the Mailbody to reflect the following or add the above mentioned expression directly to the HTML body. Either way should work.

<p><br>
File content using path<br> 

<img src="@{outputs('Compose_2')}"></img>
</p>

Full flow: Tested and working perfectly across web client and desktop client of Outlook

Result: SharePoint hosted image is now rendered perfectly in Outlook desktop App.

Root cause analysis:

The key point is the use of dataUri() to convert the file content to the right format that HTML can understand. An image or a file in general from a SharePoint link is not public link. Only authenticated users can view the image which is the root cause of this issue.

If a user logs in into Outlook on the web from  a browser, their Microsoft identification data are stored locally within the browser context as cookies. As a result, when opening an email that contains a SharePoint link in HTML image tags, a request will be sent to SharePoint servers with the same identification and the image can be downloaded if the user has enough privilege.

It’s the same way that if you have already logged in into one of the google services, you won’t have to login again when accessing other Google services in deferent tabs.

On the other hand, Outlook client is simply an email application which does not share the same architecture as a browser. Outlook account credentials are stored differently and Outlook does not have mechanism to detect and pass credentials to other Microsoft services. Hence, when it sees a SharePoint link in HTML image tags, a request without any authorization data will be sent to the SharePoint servers and of course, it will be refused. If a hyperlink added to the email, clicking on the link will open a browser and you will need to sign in.  In the end, the main purpose of Outlook client is for email-related tasks and it will not handle authentication for other services.

Advertisement

Removing orphaned OneDrive secondary site collection admins

This is a scenario where, the user was deleted from Azure AD months ago but the OneDrive secondary site collection administrator permission assignments (OneDrive secondary admin) were intact as a thumbprints. This target account  supposed to be a service account utilized during a file server migration project and apparently assigned with OneDrive secondary site collection admin permission across all users in the tenancy.

The generic SharePoint Online commands did not do the job because “The user account does not exists in the AD” hence the identity validation fails at the first place. The OneDrive admin UI will do the job for a single OneDrive account but doesn’t help much in bulk operation scenarios like the one I dealt with.

Workaround: To remove this I used SharePoint PnP PowerShell command which was the only way around it.

Add yourself first in to one of the site collections (OneDrive accounts) before running the command so that you can verify the status ‘before’ and the result ‘after’.

For a single site collection (OneDrive Personal site in this case), run PowerShell as admin and execute these lines after customizing with your tenant, URL and user details. For this case, we will be using ‘Span ID’ to point to the abandoned account which usually goes as follows i:0#.f|membership|service.svc@tenant.onmicrosoft.com

#Config Variables - Customize this to match yours 
$SiteURL = "https://mantoso-my.sharepoint.com/personal/manoj_karunarathne_mantoso_com"
$UserID="i:0#.f|membership|account@tenant.onmicrosoft.com"
 
#Connect to PnP Online Service MFA
Connect-PnPOnline -Url $SiteURL -UseWebLogin
 
#sharepoint online powershell delete user from site collection
Remove-PnPUser -Identity $UserID -Force

If your result is similar to below, the command has done its job ! now go check that permission box and you should not see that account anymore.

Export All Sites, libraries and lists of a SharePoint Online Site Collection

If you remember classic SharePoint, it had that nice looking (and yet unreliable sometimes) feature called “SharePoint Site Structure” which was eventually deprecated as move & copy functions were introduced. This was very insightful to understand the site and content hierarchy across the entire SharePoint farm.

However, let’s assume you want to review your modern day SharePoint Online hierarchy every once in a while, and make sure your sites, libraries and lists are aligning to best practices as far as the depth of the site levels? Or, you just want to know what sort of sites exist in your site collection, we still have a manual way of getting those information out using a simple PowerShell script. This may not be the best sophisticated way of getting a handy report which can probably be obtained using a 3rd party tool.

Unless its a test environment, we rarely notice any Office 365 tenant without MFA enabled, so this script is Modern-Auth friendly and supports MFA. You can generate a basic report of all sites, libraries and lists in a specific site collection by defining the site collection name and CSV path to save it.

###Function to Get Lists and Libraries of a web
Function Get-SPOSiteInventory([Microsoft.SharePoint.Client.Web]$Web)
{
    Write-host -f Yellow "Getting Lists and Libraries from site:" $Web.URL
 
    ###Get all lists and libraries
    $SiteInventory= @()
    $Lists= Get-PnPList -Web $Web
    foreach ($List in $Lists)
    {
        $Data = new-object PSObject
        $Data | Add-member NoteProperty -Name "Site Name" -Value $Web.Title
        $Data | Add-member NoteProperty -Name "Site URL" -Value $Web.Url
        $Data | Add-member NoteProperty -Name "List Title" -Value $List.Title
        $Data | Add-member NoteProperty -Name "List URL" -Value $List.RootFolder.ServerRelativeUrl
        $Data | Add-member NoteProperty -Name "List Item Count" -Value $List.ItemCount
        $Data | Add-member NoteProperty -Name "Last Modified" -Value $List.LastItemModifiedDate
        $SiteInventory += $Data
    }
 
    ###Get All Subwebs
    $SubWebs = Get-PnPSubWebs -Web $Web
    Foreach ($Web in $SubWebs)
    {
        $SiteInventory+= Get-SPOSiteInventory -Web $Web
    }
    Return $SiteInventory
}
 
###Config Variables
$SiteURL = "https://sitename.sharepoint.com/sites/PWA"
$CSVFile = "C:\temp\filename.csv"
 
###Get Credentials to connect
 
Try {
    #Connect to PNP Online
    Connect-PnPOnline -Url $SiteURL -UseWebLogin
 
    ###Get the Root Web
    $Web = Get-PnPWeb
 
    ###Call the function and export results to CSV file
    Get-SPOSiteInventory -Web $Web | Export-CSV $CSVFile -NoTypeInformation
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red

As you execute it, you’ll be prompted for credentials and the report will be generated (duration might depend on the number of site collections and the weight of each)

Original script used in this scenario was published in this article of SharePoint Diary

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"

Activate a SharePoint Online feature across multiple sites using PnP PowerShell

Unlike SharePoint On-premise, the Online SharePoint platform has a limitation when it comes to Manageability. However, with new PnP capabilities, you have more power than before. One of the capability I recently used across few client tenant is “Enable-PnPFeature”.

SharePoint Patterns and Practices (a.k.a PnP) contains a library of PowerShell commands (PnP PowerShell) that allows you to perform complex provisioning and artifact management actions using CSOM towards SharePoint Online (SPO) and On-Premise. This unified management capability addresses the gap between On-Premise and Online SharePoint backend.

This following short script enables an specific feature across multiple sites (as defined in the CSV file). Make sure you replace the file path and feature ID to match your requirements.

Prerequisites:

• You must have PnP PowerShell module installed in your PC and ready to connect
• Retrieve the feature ID list and choose the right one
• Keep the list of sites in CSV format stored in the folder path given the script

#Activate Feature for multiple sites 

$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:\Tools\SitesList.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’

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

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.

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*

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

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

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

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

Retrieve and export Office 365 Group Members (Part02)

This is the 2nd part of the article series “Retrieve and export Office 365 Group Members”. We are covering up the second part in this post.

1. Retrieve and export members of an specific Office 365 group (Part01)
2. Retrieve and export members of all Office 365 groups (Part 02)

The best tool to run these kind of scripts is the PowerShell ISE. Copy the following code and paste it in to PowerShell ISE and make sure that you have run it as the Admin.

### All users of all groups 

$CSVPath = "C:\Exports\AllGroupMembersList.csv"
 
### Get Credentials
$Credential = Get-Credential
   
### Create Session
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Credential -Authentication Basic -AllowRedirection
   
### Import Session
Import-PSSession $Session -DisableNameChecking

### Remove the CSV file if already exists
If(Test-Path $CSVPath) { Remove-Item $CSVPath}

### Retreive all Office 365 Groups
$O365Groups=Get-UnifiedGroup
ForEach ($Group in $O365Groups) 
{ 
    Write-Host "Group Name:" $Group.DisplayName -ForegroundColor Green
    Get-UnifiedGroupLinks -Identity $Group.Id -LinkType Members | Select DisplayName,PrimarySmtpAddress
 
    ### Get Group Members and export to CSV
    Get-UnifiedGroupLinks -Identity $Group.Id -LinkType Members | Select-Object @{Name="Group Name";Expression={$Group.DisplayName}},`
         @{Name="User Name";Expression={$_.DisplayName}}, PrimarySmtpAddress | Export-CSV $CSVPath -NoTypeInformation -Append
}
  
#Remove the session 
Remove-PSSession $Session

A closer look would be like this once you paste it. Ensure to replace the <CSVPath> parameter value before you run it.

Just hit the play button to run the whole thing or you can highlight a specific line to run that only.

If all went well, you would not get any prompts or errors except the credentials insertion prompt.

And the group members list with the respective group name will be listed right on the PowerShell result pane just like below.

And if you go back to your export folder, the CSV will also sit there just for you to open and see.

DISCLAIMER NOTE: This is an enthusiast post and is not sponsored by Microsoft or any other vendor. Please do not copy/duplicate the content of the post unless you are authorized by me to do so.