RLV Blog

Hacking DocX files to fix Quick Parts in Word Online

Posted on December 18, 2020 by Dan

Microsoft Word documents can have Quick Parts connected to SharePoint fields, so that the value of the field is displayed inside the document. But Word Online (still) does not fully support this. It will display Quick Parts with a a placeholder text instead of the actual value. If the SharePoint list value changes, the document must be opened in the desktop client for the placeholder text to update and show correctly in Word Online.

This caused trouble for me when I was migrating thousands of files into SharePoint Online. I had a script that uploaded documents and tagged them with important metadata. This metadata was supposed to be displayed in the document, but in Word Online the field name was shown instead (as a placeholder). This was not acceptable, especially since Word Online is default when opening a Word document in SharePoint. I tried to use the Word Automation COM API to make a script that updates these values, but it does not allow editing these fields. What to do?

Here I present a solution to update the placeholder values from PowerShell. It works by unzipping the DocX files to directly modify the XML files. Hacky, but it works :-)

This is not a generic script. It was written for my own needs. Modify and adapt the script to suit your needs before running. For example, it does not show how to get the field values to replace the placeholders with. In my case I had a separate CSV file with migration data. Below I just use a simple key/value structure to map placeholders (e.g. [Author]) to a value. A colleague told me he actually found the “real” values elsewhere in the DocX file. I have not verified this, but perhaps it is possible to read these values instead.

Note: Due to a bug in Compress-Archive make sure to first update it or else Word Online will think the documents are corrupt and only open them in view mode: Install-Module Microsoft.PowerShell.Archive -MinimumVersion 1.2.3.0 -Repository PSGallery -Force -AllowClobber

Update: Forgot to use HtmlEncode() to avoid corrupting the XML

[System.Reflection.Assembly]::LoadWithPartialName("System.web")

$docIn = "d:\temp\quickparts.docx"
$docOut = "d:\temp\quickparts-fixed.docx"
$quickparts = @{
	"[Author]" = "John Doe";
	"[Company]" = "Contoso";
	"[Area]" = "Sales";
}

Write-Host "Start processing quickparts in $docIn"

$workDir = "$($env:TEMP)\quickparts"

if([System.IO.Directory]::Exists($workDir) -eq $false) { 
	New-Item -Path $workDir -ItemType Directory | out-null
} else {
	Remove-Item -Path "$workDir\*" -Recurse -Force
}

Copy-Item -Path $docIn -Destination "$workDir\in.zip"
Expand-Archive -Path "$workDir\in.zip" -DestinationPath "$workDir\unzipped"

# This list of xml files to process is based where placeholders were located in my files. There may be other locations too.
$xmlFiles = @(
	"$workDir\unzipped\word\header1.xml"
	"$workDir\unzipped\word\header2.xml"
	"$workDir\unzipped\word\header3.xml"
	"$workDir\unzipped\word\glossary\document.xml"
	"$workDir\unzipped\word\footer1.xml"
	"$workDir\unzipped\word\footer2.xml"
	"$workDir\unzipped\word\footer3.xml"
	)

$anyModified = $false

foreach($file in $xmlFiles) {

	Write-Host "Processing XML file: $([System.IO.Path]::GetFileName($file))..."

	if([System.IO.File]::Exists($file) -eq $true) {
		$file_content = [System.IO.File]::ReadAllLines( $file, [System.Text.Encoding]::UTF8 )
		$modified = $false

		foreach($quickpart in $quickparts.GetEnumerator()) {
			$find = "<w:t>$($quickpart.Key)</w:t>" # Found by examining XML files. There may be other tags.
			if([system.String]::Join(" ",$file_content).Contains($find) -eq $true) {
				$replace =  "$($quickpart.Value)`n"
				$replace = $replace.TrimEnd("`n").Replace("`n", ", ")
				$replace = [System.Web.HttpUtility]::HtmlEncode($replace)
				Write-Host "`tReplaced $($quickpart.Key) in $([System.IO.Path]::GetFileName($file)) with $replace"
				$replace = "<w:t>$replace</w:t>"
				$file_content = $file_content.Replace($find, $replace)
				$modified = $true
			}
		}

		if($modified -eq $true) {
			$anyModified = $true
			[System.IO.File]::WriteAllLines( $file, $file_content, [System.Text.Encoding]::UTF8 )
		}
		
	}

}

if($anyModified -eq $true) {
	Write-Host "Compress and save processed document as $docOut"
	Compress-Archive -Path "$workDir\unzipped\*" -DestinationPath "$workDir\out.zip"
	Copy-Item -Path "$workDir\out.zip" -Destination $docOut
} else {
	Write-Host "No quick part placeholders found in $docIn"
}

Remove-Item -Path $workDir -Recurse -Force

Write-Host "Ready!"

[System.Reflection.Assembly]::LoadWithPartialName("System.web")

$docIn = "d:\temp\quickparts.docx"

$docOut = "d:\temp\quickparts-fixed.docx"

$quickparts = @{

"[Author]" = "John Doe";

"[Company]" = "Contoso";

"[Area]" = "Sales";

}

Write-Host "Start processing quickparts in $docIn"

$workDir = "$($env:TEMP)\quickparts"

if([System.IO.Directory]::Exists($workDir) -eq $false) {

New-Item -Path $workDir -ItemType Directory | out-null

} else {

Remove-Item -Path "$workDir\*" -Recurse -Force

}

Copy-Item -Path $docIn -Destination "$workDir\in.zip"

Expand-Archive -Path "$workDir\in.zip" -DestinationPath "$workDir\unzipped"

# This list of xml files to process is based where placeholders were located in my files. There may be other locations too.

$xmlFiles = @(

"$workDir\unzipped\word\header1.xml"

"$workDir\unzipped\word\header2.xml"

"$workDir\unzipped\word\header3.xml"

"$workDir\unzipped\word\glossary\document.xml"

"$workDir\unzipped\word\footer1.xml"

"$workDir\unzipped\word\footer2.xml"

"$workDir\unzipped\word\footer3.xml"

)

$anyModified = $false

foreach($file in $xmlFiles) {

Write-Host "Processing XML file: $([System.IO.Path]::GetFileName($file))..."

if([System.IO.File]::Exists($file) -eq $true) {

$file_content = [System.IO.File]::ReadAllLines( $file, [System.Text.Encoding]::UTF8 )

$modified = $false

foreach($quickpart in $quickparts.GetEnumerator()) {

$find = "<w:t>$($quickpart.Key)</w:t>" # Found by examining XML files. There may be other tags.

if([system.String]::Join(" ",$file_content).Contains($find) -eq $true) {

$replace = "$($quickpart.Value)`n"

$replace = $replace.TrimEnd("`n").Replace("`n", ", ")

$replace = [System.Web.HttpUtility]::HtmlEncode($replace)

Write-Host "`tReplaced $($quickpart.Key) in $([System.IO.Path]::GetFileName($file)) with $replace"

$replace = "<w:t>$replace</w:t>"

$file_content = $file_content.Replace($find, $replace)

$modified = $true

}

if($modified -eq $true) {

$anyModified = $true

[System.IO.File]::WriteAllLines( $file, $file_content, [System.Text.Encoding]::UTF8 )

}

if($anyModified -eq $true) {

Write-Host "Compress and save processed document as $docOut"

Compress-Archive -Path "$workDir\unzipped\*" -DestinationPath "$workDir\out.zip"

Copy-Item -Path "$workDir\out.zip" -Destination $docOut

} else {

Write-Host "No quick part placeholders found in $docIn"

}

Remove-Item -Path $workDir -Recurse -Force

Write-Host "Ready!"

Posted in Development, Tips | Tagged microsoft word, powershell, quick parts, sharepoint online, word online, xml, zip | Leave a comment

Migrating data from Access to SQL Server

Posted on April 30, 2020 by Dan

Suppose that you have a simple Microsoft Access database that you need to migrate to another database such as MS SQL Server. You model the tables and relationships in the new database, and now you need to export the actual data from Access and import it into the new database. This is how I did it:

I began by exporting the data as CSV. I found the easiest way was to simply use copy/paste. First though, open each tables in Design View. There is a tab named “Lookup” at the bottom. Ensure that any lookup field is set to display “Value List” instead of “Table/Query”. Then you can view the table normally and copy all data to the clipboard (Ctrl+A, Ctrl+C) and paste it into a .csv text file saved with UTF-8 encoding.

Next I used PowerShell to transform the CSV data into to SQL statement. While doing this I also adapted the structure of the data. For example, some fields were renamed, and I also had multiple fields that I wanted to merge into one. Here is a generic version of the script I put together that you can modify to your own needs:

$dir = Split-Path $MyInvocation.MyCommand.Path
$data = Import-Csv "$dir\Access\data.csv" -Delimiter "`t" -Encoding UTF8 
$out = [System.IO.StreamWriter]::new( "$dir\provisioning.sql" )

$out.WriteLine("SET IDENTITY_INSERT Chemicals ON")
$data | ForEach-Object {
    $sql = 
        "INSERT INTO MyTable (Id, Name, Link, DateModified) values ({0}, '{1}', '{2}', getutcdate() )" -f 
        $_.Id, 
        $_.Name, 
        $_.WebLink;

    $out.WriteLine($sql)
}
$out.WriteLine("SET IDENTITY_INSERT Chemicals OFF")

$out.close()

$dir = Split-Path $MyInvocation.MyCommand.Path

$data = Import-Csv "$dir\Access\data.csv" -Delimiter "`t" -Encoding UTF8

$out = [System.IO.StreamWriter]::new( "$dir\provisioning.sql" )

$out.WriteLine("SET IDENTITY_INSERT Chemicals ON")

$data | ForEach-Object {

$sql =

"INSERT INTO MyTable (Id, Name, Link, DateModified) values ({0}, '{1}', '{2}', getutcdate() )" -f

$_.Id,

$_.Name,

$_.WebLink;

$out.WriteLine($sql)

}

$out.WriteLine("SET IDENTITY_INSERT Chemicals OFF")

$out.close()

Note: Normally you let SQL Server generate the ID of key fields. In this case though, we already have ID:s from the Access database. Since these ID:s specify the primary-foreign key relations, it is important to keep intact. To allow insertion of keys like that, IDs SET IDENTITY_INSERT Chemicals ON is used.

Finally, open the generated SQL script (provisioning.sql) in SQL Server Management Studio (or similar) and run it to provision your data!

Posted in Development, Tutorials | Tagged access, csv, data migration, database, ms sql server, powershell, sql | 1 Comment

Applying SharePoint Retention Labels Programmatically

Posted on February 10, 2020 by Dan

So you’ve created and published Retention Labels in Office 365 and now you want to apply labels by CSOM code. It’s not very well documented. The following (crude) code should help you get started applying labels to both individual items and to set as default for the whole library:

First some boilerplate initialization…

using Microsoft.SharePoint.Client.CompliancePolicy;
...
var ctx = GetAppOnlyClientContext("https://mytenant.sharepoint.com/sites/myaswesomesite");
var rootWeb = ctx.Site.RootWeb;
var list = rootWeb.Lists.GetByTitle("My List");
var listRootFolder = list.RootFolder;
ctx.Load(rootWeb);
ctx.Load(list);
ctx.Load(listRootFolder);
await ctx.ExecuteQueryAsync();

using Microsoft.SharePoint.Client.CompliancePolicy;

...

var ctx = GetAppOnlyClientContext("https://mytenant.sharepoint.com/sites/myaswesomesite");

var rootWeb = ctx.Site.RootWeb;

var list = rootWeb.Lists.GetByTitle("My List");

var listRootFolder = list.RootFolder;

ctx.Load(rootWeb);

ctx.Load(list);

ctx.Load(listRootFolder);

await ctx.ExecuteQueryAsync();

Get all labels available on the site:

var availableLabels = SPPolicyStoreProxy.GetAvailableTagsForSite(ctx, "https://mytenant.sharepoint.com/sites/myawesomesite");
await ctx.ExecuteQueryAsync();

Console.WriteLine("All available Retention Labels:");
foreach (var label in availableLabels)
{
    Console.WriteLine("\t" + label.TagName);
}

var availableLabels = SPPolicyStoreProxy.GetAvailableTagsForSite(ctx, "https://mytenant.sharepoint.com/sites/myawesomesite");

await ctx.ExecuteQueryAsync();

Console.WriteLine("All available Retention Labels:");

foreach (var label in availableLabels)

{

Console.WriteLine("\t" + label.TagName);

}

Get the default label for a list:

var currentLabelApplied = SPPolicyStoreProxy.GetListComplianceTag(ctx, listRootFolder.ServerRelativeUrl);
await ctx.ExecuteQueryAsync();
if(currentLabelApplied.Value != null)
{
    Console.WriteLine("List Default Label: " + currentLabelApplied.Value.TagName);
}

var currentLabelApplied = SPPolicyStoreProxy.GetListComplianceTag(ctx, listRootFolder.ServerRelativeUrl);

await ctx.ExecuteQueryAsync();

if(currentLabelApplied.Value != null)

{

Console.WriteLine("List Default Label: " + currentLabelApplied.Value.TagName);

}

Get labels for all items in a list:

for(var i=1; i<=list.ItemCount; i++)
{
    var item = list.GetItemById(i);
    ctx.Load(item);
    await ctx.ExecuteQueryAsync();
    Console.WriteLine($"Item '{item.FieldValues["Title"]}' has label '{item.FieldValues["_ComplianceTag"]}'");
}

for(var i=1; i<=list.ItemCount; i++)

{

var item = list.GetItemById(i);

ctx.Load(item);

await ctx.ExecuteQueryAsync();

Console.WriteLine($"Item '{item.FieldValues["Title"]}' has label '{item.FieldValues["_ComplianceTag"]}'");

}

Write label to a list item:

var itemToLabel = list.GetItemById(1);
ctx.Load(itemToLabel);
await ctx.ExecuteQueryAsync();
itemToLabel.SetComplianceTag("My retention Label", false, false, false, false);
itemToLabel.Update();
await ctx.ExecuteQueryAsync();

var itemToLabel = list.GetItemById(1);

ctx.Load(itemToLabel);

await ctx.ExecuteQueryAsync();

itemToLabel.SetComplianceTag("My retention Label", false, false, false, false);

itemToLabel.Update();

await ctx.ExecuteQueryAsync();

Set the default label for a list:

var applyToExistingItems = true;
SPPolicyStoreProxy.SetListComplianceTag(ctx, listRootFolder.ServerRelativeUrl, "My Label", false, false, applyToExistingItems);
await ctx.ExecuteQueryAsync();

var applyToExistingItems = true;

SPPolicyStoreProxy.SetListComplianceTag(ctx, listRootFolder.ServerRelativeUrl, "My Label", false, false, applyToExistingItems);

await ctx.ExecuteQueryAsync();

SetComplianceTag and SetListComplianceTag have parameters (isTagPolicyHold, isTagPolicyRecord and isEventBasedTag) that are not documented what they actually do. Above I have set them to false. If you know what they do, please leave a comment!

Posted in Development | Tagged c#, compliance, csom, office 365, programming, retention, retention labels, retention policies, sharepoint, sharepoint online | 4 Comments

If Snipping Tool freezes and blocks your screen…

Posted on January 17, 2020 by Dan

Note: Be sure to read the comments for more help!

Today when I used the Snipping Tool in Windows 10 it stopped working. I could select an area over and over again, but it never closed after that as it should. The screen was dimmed and I could not get to any other program because Snipping Tool was on top of everything, even Task Manager! I almost gave up doing a hard reset, when I found a way to get rid of it:

Alt+Tab still worked though. There is a small X that appears if you hover an application thumbnail with the mouse. Using this you can close Snipping Tool!

Phew…

Posted in Tips | Tagged freezing, snipping tool, windows | 96 Comments

How to create searchable user profile properties in SharePoint Online

Posted on November 21, 2019 by Dan

In this post I will go through the steps of creating a custom user profile attribute that is searchable and shows on the user profile in Delve. It’s not very hard but involves quite a few steps to keep track of. This is written for SharePoint Online, but the basics apply to SharePoint 2013/2016/2019 as well.

Add a new User Profile Property

Go to SharePoint Admin > User Profiles > Manage Users Properties then click on New Property. Configure the basic settings such as names and type. (Remember that you can’t changes the type after it has been created)

To make the property show up on Delve profiles, ensure the following settings:

Default Privacy Setting: Everyone
Show in the Profile Properties section of the user’s profile page: Checked
Show on the Edit Details page: Checked
Indexed: Checked

Ensure the field is crawled

Now we need to wait until the property has been crawled by search. This will not happen unless we fill out the field on at least one user profiles. So start by editing a profile or two.

Then go to SharePoint Admin > Search > Manage Search Schema > Crawled Properties and wait until your new property shows up. Have patience as this may take a little while (anything from 15 minutes to four hours in my experience).

Map to a managed property

Go to SharePoint Admin > Search > Manage Search Schema > Manage Properties. Create a new Managed Properties with the following settings:

Type: Text
Searchable: Checked
Queryable: Checked
Retrievable: Checked
Click Advanced Searchable Settings and select PeopleIdx in the popup window
Click the blue button “Add a Mapping” and find the crawled property we just created

Finish up

Now we wait again to let search crawl pick up the new managed property. This may take quite a while, and you never know when it happens in SharePoint Online. I typically leave it during the night and check in the morning…

If you have done everything correct, you will now have a custom user property that you can freely search for all over Office 365 to find people, and that you can view on peoples Delve profiles!

Notes & tips

If you have business requirements to only allow a limited set of values to enter into the property, you can create a term set in the Term Store with allowed values. Then, when creating the new property, select string as type and tick the “Configure a Term Set to be user for this value” checkbox.
The properties you create are visible in Delve under “Additional Information” at the bottom of user’s profile pages. If you allow users to edit the properties themselves, they can do so on the SharePoint Profile page. To find it, go to https://tenant-my.sharepoint.com/_layouts/15/editprofile.aspx
Managed properties that you create yourself in SharePoint Online can not be sorted or refined in search results. If you need to do this, you can add another managed property, but instead of creating it yourself use one of the predefined RefinableString properties.
To easily update properties in multiple user profiles, check out the Bulk User Profile Update Api for SharePoint Online.
There is no way in SharePoint Online to control when search will crawl user properties. This script may help ensuring that your user profiles are re-indexed: https://github.com/wobba/SPO-Trigger-Reindex

Posted in Tutorials | Tagged crawled properties, delve, managed properties, office 365, sharepoint, sharepoint online, sharepoint onprem, sharepoint search, user profiles | 1 Comment

Popup opens as new browser window in Edge and Internet Explorer

Posted on November 20, 2019 by Dan

Problem: Opening a popup with JavaScript in Edge or Internet Explorer results in a new browser window instead of a popup window. For example:

var popup = window.open("https://www.duckduckgo.com/", "DDG", "height=640,width=960,toolbar=no,menubar=no,scrollbars=no,location=no,status=no");

1	var popup = window.open("https://www.duckduckgo.com/", "DDG", "height=640,width=960,toolbar=no,menubar=no,scrollbars=no,location=no,status=no");

Communication between the new window and originating site is not possible either, since both popup (above) and window.opener (in the new window) are undefined.

Solution: This can happen if the originating site’s URL and popup URL are not in the same security zone. Ensure that both are in the Trusted Sites zone in Internet Options!

Posted in Development | Tagged development, edge, internet explorer, javascript, popups, programming | Leave a comment

How to quickly get the Group ID for an Office 365 Group

Posted on November 19, 2019 by Dan

Microsoft Graph typically requires us to use the Group ID when working with O365 groups and modern sites. But Group ID can be tricky to find since it is rarely displayed. Here are some ways I found to quickly get hold of it during when experimenting with MS Graph:

1. The group ID is made available on our trusty old friend _spPageContextInfo in the group’s SharePoint site. Knowing this we can easily find it by going to the site and type _spPageContextInfo.groupId in the JS console. _spPageContextInfo is however not available on modern pages, but you can go to a page that is not modern such as Site Settings to find it.

2. Using Graph Explorer we can query for the group. By default mailNickname is used for the site URL. So if the site URL is https://tenant.sharepoint.com/sites/MyTeamSite then GET https://graph.microsoft.com/v1.0/groups?$filter=mailNickname eq 'MyTeamSite'&$select=id will give you the Group ID. Note that mailNickname is not guaranteed to be the same as the site URL, for example if you have changed the site URL.

3. Group ID is also hidden deep in the page source of modern sites. To find it you can view the page source (CTRL+U) and search for spPageContextInfo. Triple click the paragraph you found to select it, copy the paragraph to the clipboard, open the JS console and paste it between two brackets { CTRL+V } and press enter. Now you can easily inspect the object and find the group id.

4. We can actually get to the page context object on modern pages too, but it is hidden deep down: window.spModuleLoader._bundledComponents["b6917cb1-93a0-4b97-a84d-7cf49975d4ec"].PageManager._instance.pageContext.legacyPageContext.groupId (FYI: The GUID here refers to the web scoped feature for “modern” pages.). I made a bookmarklet to make it easier to access: javascript:void%20function(){alert(window.spModuleLoader._bundledComponents[%22b6917cb1-93a0-4b97-a84d-7cf49975d4ec%22].PageManager._instance.pageContext.legacyPageContext.groupId)}();

Posted in Tips | Tagged group id, microsoft graph, modern sites, office 365, sharepoint online | 3 Comments

Excluding external users from search results in SharePoint Online

Posted on January 31, 2019 by Dan

Search in SharePoint Online returns both internal and external users by default. This may not be desirable. Here’s how to exclude external users from the search results.

External users have account names containing #ext# which makes them easy to filter out. Typically when you search for people you will query against the Local People Results result source. In SharePoint Online we can’t modify this result source like On Premises, but we can clone it and add our own custom filters:

Open SharePoint Admin
Go to search settings and select Manage Result Sources
Copy Local People Results (click the down arrow to find the copy command)
Edit your new result source and find Query Transform
Add -AccountName:"#ext#" to the query
Verify that it works using the Query Builder
Save

Remember that you will need to configure search pages and other solutions to use this new result source instead of the default Local People Results!

Posted in Tips | Tagged sharepoint online, sharepoint search | 3 Comments

Try this if sound in Windows stops working

Posted on August 19, 2018 by Dan

Several of my computers have had problems with sound suddenly not working. Typically this seems to happens when switching context, such as going from plugged in to battery, but sometimes it seems random. At work we use Skype for Business. When it encounters sound problems it tends to freeze up. I’ve found the following to work at restoring sound (and S4B) again:

Right click on the sound icon in system tray. Select “Playback Devices”.
Double click on “Speakers / Headphones”. Go to the “Advanced” tab in the dialog that opens:
Click on “Restore Defaults” or the “Test” button:

Depending on the problem you have, one of these buttons tends to restore sound playback again!

Posted in Tips | Tagged fix, repair, restore, skype 4 business, sound, sound cards, windows | Leave a comment

Enable Mometum on an Alps Glidepoint Touchpad

Posted on August 15, 2018 by Dan

My old computer has a touchpad from Synaptics with a feature called “Momentum”. (I’ve also seen this called “Inertia” och “Inertial Scrolling”.) It causes the mouse pointer move a little bit before stopping after you’ve released your finger, as if friction is slowing it down. I find this makes using a touchpad more enjoyable, since it allows you to flick your finger when moving the mouse.

My new computer came with an Alps Glidepoint Touchpad. I was disappointed when I could not find any momentum settings in the settings. So I started digging through the registry to see if I could find some hidden settings, and I did!

Simply find this key using RegEdit and set it to 1 :

Computer\HKEY_CURRENT_USER\Software\Alps\Apoint\Gesture\EnableMoment

1	Computer\HKEY_CURRENT_USER\Software\Alps\Apoint\Gesture\EnableMoment

You can easily test the new setting by toggling the touch pad on/off in the settings dialog:

Sadly I find that the Alps implementation of momentum applies too much “friction”, making the mouse stop too fast. I have not been able to find any settings controlling this. If you do, please post a comment!

Posted in Tips | Tagged drivers, mouse, regedit, touchpad, windows | 1 Comment

Hacking DocX files to fix Quick Parts in Word Online

Migrating data from Access to SQL Server

Applying SharePoint Retention Labels Programmatically

If Snipping Tool freezes and blocks your screen…

How to create searchable user profile properties in SharePoint Online

Popup opens as new browser window in Edge and Internet Explorer

Excluding external users from search results in SharePoint Online

Try this if sound in Windows stops working

Enable Mometum on an Alps Glidepoint Touchpad

About RLV Blog

Recent Posts

Categories

Popular Posts

Archives

Meta