Changing value of hyperlink field of a list en masse using PowerShell

Forever grateful to Salaudeen Rajack for all the contributions over the years for SharePoint admin community. Recently I was tasked with changing values en masse of a column in a list of type picture/ hyperlink field in a SharePoint online list. The requirement was if you find a string in such a field replace a part of the string in all values. https://www.sharepointdiary.com/2017/08/sharepoint-online-update-hyperlink-field-using-powershell.html#ixzz6hNcutSdB
provides the main idea. But I had to marry this concept to all items in a list. This is not the most efficient way, but here is what I had to come up with quickly:

#Config Variables
$SiteURL = "Your site/ subsite where the list resides"
$ListName = "Your list name"
$counter = 0
$maxCount = 2500

 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)

#$myItem = Get-PnPListItem -List $ListName -Id $ItemID
 
#Loop through list and Update Hyperlink column 
#value using PowerShell

$listItems = Get-PnPListItem -List $ListName -Fields "Title","Column name with picture/ hyperlink column name" -PageSize 1000
   
ForEach ($item in $listItems)
{
$subCatUrl = $item["Column name with picture/ hyperlink column name"].Url.ToLower()

if ($subCatUrl.Contains("Your replacable content"))
{
    $newsubCatUrl = $subCatUrl.Replace("Your replacable content","Your desired content")

    Set-PnPListItem -List $ListName -Identity $item["ID"] -Values @{"Column name with picture/ hyperlink column name"= "$newsubCatUrl, $subCatText"}

    Write-Host $item["ID"] "Changed" -ForeGroundColor yellow

    $counter++

    If ($counter -ge $maxCount){break;}
}
else
{
    Write-Host "No Your replacable content found in" $item["ID"] -ForeGroundColor magenta
}

Write-Host "Total items processed are " $counter -ForeGroundColor green

Site Page not available in the New drop down

If you happen to stumble across a classic SharePoint online site where in the New option of site pages library drop down does not show “Site Page” but shows Wiki and Web part page and exhausted all other options please check if the site feature called:
Site Pages
Allows users to add new site pages to the site

is active.
Please remember this is a site feature and if you have a subsite, you have to enable it @ subsite level also.

View a previous version of an Excel file from SharePoint online library

Tags

So a user came to me and asked “Soumya, I want to download a previous version of an excel document in a SharePoint online document library”. I don’t want to restore it. I just want to open it or download it.
Hmmm, not as easy as it sounds. But found how to do this in word document here:
https://sharepointmaven.com/5-ways-users-can-benefit-versioning-sharepoint/
All I had to do it expand this in Excel. In Excel, you will see a File > Info option that lets you see versions.

Open the Excel file in Excel app (not in browser). Go to File > Info to see all versions on the right hand side. Then by clicking the appropriate version, you should be able to open it and if needed save a local copy.
I wonder how the same thing is done for pdf files.

Find O 365 group Id

Tags

This is a quick article. How to quickly find O 365 group Id. I am assuming you are a global admin. There are several articles online around it. https://www.rlvision.com/blog/how-to-quickly-get-the-group-id-for-an-office-365-group/ is one example. There are a few more. I also found that for me the quickest way to look this up is @ the azure portal. Go to portal.azure.com. Login with your global admin credentials. Click on Azure Active Directory > Manage > Groups. Look the group up. You will see something like below:

From here you pick up the ObjectId and you are all set.

Delete modern SharePoint Team site immediately to reclaim its URL

Tags

Requirement came from user. She wanted me to migrate https://YourTenant.sharepoint.com/sites/YourCommunicationsSite (Communications site) to https://YourTenant.sharepoint.com/sites/YourTeamSite (Team site).

Well, I thought it is easy. Open ShareGate and start migrating. Turns out she wanted https://YourTenant.sharepoint.com/sites/YourTeamSite to be a communications site. That means we have to delete the current Team site and reclaim the URL to create a new communications site with the same URL.

Problem is Team sites are tied to Office 365 groups. Even if you delete a team site from SharePoint, you won’t be able to free up the URL. So here are the steps you do:

Delete the SharePoint site from SharePoint admin center. This is UI based.

Delete the SharePoint site from recycle bin. This will need SPO PowerShell.

Connect to SPO tenant using SharePoint admin url

Remove-SPODeletedSite -Identity https://YourTenant.sharepoint.com/sites/https://YourTenant.sharepoint.com/sites/YourTeamSite

Delete the Office 365 group from AzureAD group recycle bin. For this you will need AzureAD PowerShell module installed followed by PowerShell. For this I followeed Salaudeen Rajack’s blog post here:

https://www.sharepointdiary.com/2018/04/how-to-delete-office-365-group-using-powershell.html

After the above I could immediately reclaim the URL and create a brand new communications site @ https://YourTenant.sharepoint.com/sites/YourTeamSite

Report couldn’t be loaded in PowerPlatform Admin center

I was called to provide a report on how many Flows (Power Automates) were created in the last 28 days. I thought Oh, this is easy. Start with Power Platform Admin Center and go to  Power Automate and click on analytics. And I got the following screen:
One colleague was seeing it just fine. I thought it’s got to be my license as for my life, I could not find an Environment Admin of the default environment I was in.
I found this https://docs.microsoft.com/en-us/power-platform/admin/analytics-flow#who-can-view-these-reports article. I thought I am a Global Admin too. So it must be license. It says:
Admins with the following roles and a license can view the reports in Power Automate analytics:
OK, what license? I had a P2 license. But I didn’t have a E5 license which my colleague had. As soon as the E5 license was assigned, my report started running!

Account throttled for SPO sites

Tags

Recently, I started getting redirected to a throttling page for any SharePoint Online sites I tried to get on my tenant. This was happening with my regular account. The message said Something is not right. The page you requested is temporarily unavailable…. See image below.
UserThrottledforWorkFlow
The problem was intermittent. But the problem was occurring on 90+% of the attempts.

We opened a Microsoft ticket as the above problem went on for days. Initially we thought this may be a license issue.

There are some articles in the net about this. But in our case it turned out to be that I was migrating a site that has a list with 30000+ items and workflows associated with them. About 4 that are getting triggered for each item. The migration triggered 30000+*4 workflows which pretty much made Microsoft to flag my account and have it throttled.  See image below:
ICEWflowsThrottle
Once the above workflows were terminated/ removed, everything became normal.

One more SP 2013 on-prem patching

Nowadays I am more of an O 365 person than a traditional SharePoint on-prem person. But there are always remnant on-prem existence and my company is no exception. Hence recently I have been asked to patch an old farm that has not been upgraded for a while.  I have done enough of these to know that every such patch, no matter how many you have done, may pose it’s unique set of challenge.

I end up with errors like below:
An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown. Additional exception information:
Feature (Name = [Operations Manager Dashboard Web Part], Id = [Your Id], Description = [Allows for Operations Manager Dashboards to be hosted in SharePoint], Install Location = [Your Location]) is referenced in database [Your database], but isn’t installed on the current farm. The missing feature might cause upgrade to fail. If necessary, please install any solution that contains the feature and restart upgrade. (EventID:ajxkh)

Feature (Name = [Power View Integration Feature], Id = [Your Id], Description = [Enables interactive data exploration and visual presentation against PowerPivot workbooks and Analysis Services tabular databases.], Install Location = [Your Location]) is referenced in database [Your Location], but isn’t installed on the current farm. The missing feature might cause upgrade to fail. If necessary, please install any solution that contains the feature and restart upgrade. (EventID:ajxkh)

Dug into some old notes and found this one:
https://soumyabhatta.wordpress.com/2017/05/26/delete-missing-features-dig-deeper/
Tried to remove the troubled features that I could. Another run of PSConfig left me with essentially this error:
An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown. Additional exception information:
Application Resource Web Config for this IIS site (Your site#) could not be found

I noticed that the site above is not working anyway. Hence I decided to delete the site, using Central Admin and removed it from IIS.
After this, psconfig was a success. Viola!

Configuring Application Server Role, Web Server (IIS) Role

I have done many installations of SharePoint Server 2013 on Windows Server 2008, but in my first attempt on Windows Server 2012 R2, I ran into the above. And it took days and weeks to resolve. When I Binged the Google, I came up with a few helpful ones:
https://support.microsoft.com/en-us/help/2765260/the-products-preparation-tool-in-sharepoint-server-2013-may-not-progre
http://www.someshinyobject.com/posts/server-2012-r2-and-sharepoint-2013-the-tool-was-unable-to-install-application-server-role-web/
But I had no luck with the above and the error persisted. I could not complete prereq tool successfully and eventually I had to open a Microsoft ticket. The Microsoft engineer examined the prerequisite log and recommended to reregister ASP.Net with IIS and activate it. Although I could not understand the exact reason for doing so:
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i -enable
This overcame the configure app server role issue. Then it said that it is failing to download the AppFabic from online. So I opted for an offline prerequisite installation and finally got over with it.

How to get value of a field in a DisplayForm.aspx (View form) in a SharePoint list

For any time I can think of, I didn’t know this clearly. I am eternally grateful to Salaudeen Rajack’s SharePoint Diary @
http://www.sharepointdiary.com/2013/05/get-form-field-values-from-dispform-editform-pages-in-sharepoint.html
In fact, I got the original idea from the above. Here he had a line of code var fldValue = $(‘h3:contains(“Last Name”)’).closest(‘td’).next(‘td’).text();
Another probably even more helpful post is:
https://sharepoint.stackexchange.com/questions/93388/how-can-i-set-select-a-field-in-the-display-form
However, the above post is > 3 years old and I had to check if the concept is good for my problem still. The most important line in the above post is “Using jQuery, find the nearest item with a unique identifier.”
I had a simple task. From a view (or display form) in a list, I had to pick up the value of a field. A field that is of type Single line of Text. Best way to do this is to inspect the form in Chrome with Developer Tools (the famous F12, you know!).
While doing such inspection, I found the following:
<td nowrap=”true” valign=”top” width=”113px” class=”ms-formlabel”><h3 class=”ms-standardheader”><a name=”SPBookmark_Title”></a>Claim #</h3></td><td nowrap=”true” valign=”top” width=”113px” class=”ms-formlabel”><h3 class=”ms-standardheader”><a name=”SPBookmark_Title”></a>Claim #</h3></td><td valign=”top” class=”ms-formbody” width=”350px” id=”SPFieldText”> <!– FieldName=”Claim #” FieldInternalName=”Title” FieldType=”SPFieldText”   –> 18-114</td>

Note the row:
Claim # 18-114
I wanted the value 18-114, but as you can see for different items in the list, the value will be different. I can not quite do $(#18-114). Let us go back to what will be unique. In other words, “Using jQuery, find the nearest item with a unique identifier.” In this case, the name of the field will be unique. Which is Claim # that is present in the row
Claim # 18-114
This is precisely the element I need. Now this has a class ms-formlabel. So find all tds which has the class ms-formlabel and contains the text Claim #. That is precisely done by the following:’td.ms-formlabel:contains(“Claim #”)’)
Then go through its only sibling that has the css class ms-formbody applied. Find the text and trim it to get the actual value which in this case was 18-114. That’s how you derive the final value given below:
var fldValue = $(‘td.ms-formlabel:contains(“Claim #”)’).siblings(“.ms-formbody”).text().trim();
Another beauty if developer tools is in the Console tab, you can type us the query to inspect the value, so if you type $(‘td.ms-formlabel:contains(“Claim #”)’).siblings(“.ms-formbody”).text().trim(), you can check if it’s returning the expected value.
ReadDispPlayFormField