excel-mobile.jpg

Freeze Column Headers in SharePoint Like in Excel

Caroline Sosebee is a Software Engineer at ThreeWill. She comes to us with 20+ years of software development experience and a broad scope of general IT support skills.

Have you ever been asked to freeze the column headers in SharePoint like you can in Excel? If so, then read on as I found there’s a nifty little way to handle this after one of our clients asked if it was possible. Their quandary was that they had a couple of lists with lots of numerical columns and it was hard for them to keep up with what they were seeing.

Of course, I started with my usual searching of the ‘net, finding various options and snippets of code that claimed to do the job. The one that stood out to me as the best bet though was an open source script called Sticky Headers. After reading up on it (and reviews of it) it looked like it would more than suit my needs.

Regular SharePoint view headers look like this – plain and fixed in location.

But with these floating headers, as soon as you scroll down far enough for the standard headers to roll off the page, they will pop up, as shown here. They remain visible until you scroll back to the top of the list when the standard headers are back on the screen.

Of course, you can tweak the css and other functionality since you have full control of the code and the styling. In my case, I chose to color the headers blue so that they were differentiated from the rest of the screen.

One caveat I did run across is that with a ‘group by’ view, you have to expand out the very first group level completely for the headers to appear. This is because the code is trying to determine the first visible row of data on the page to use with its calculations. I haven’t tried to figure it out any further than that, but hope to come back and revisit this part again

Overall, it’s a fairly easy thing to install. You need 2 files –the file you hopefully have downloaded from the Sticky Headers link above (I mean, why write it yourself?) and a jquery file, as the script is dependent on this. These scripts can be deployed to your entire site or to individual pages in a number of different ways.

  • You can deploy directly to a list view by adding a Content Editor or Script Editor Web Part to the page.

The pro to this is that it’s an easy install via the UI. The drawback is that it has to be done on a page by page basis. If users have lots of views on a list it will need to be added to each one. And then … expect a call the next time a new view is added as they will want it there as well.

  • You can deploy it to the master page, which will make it available throughout your site.

The pro to this is you only have to do it once for the site. One of the cons is that if you’re editing one of the OOTB master pages and Microsoft comes out with an update to it, your changes may be lost. It’s not really recommended to update default master pages so if you choose this option, you’ll need to do some googling to be sure you update the page correctly.

  • You can deploy the files to the entire site by using ScriptLink references. ScriptLinks are deployed to the site via User Custom Actions which makes them available throughout your site.

Pros for it are you only have to do it once for a site, you don’t have to touch the master page and you can make sure the scripts load in the order you need. The only con for this is that it is deployed for the entire site, which your users may not want. Not a real con, but might be to some.

For this particular assignment, I chose to deploy the sticky headers via ScriptLinks to the entire site. This is my preferred method most of the time for deploying scripts as it makes them available for future enhancements as well. The drawback, as many will point out, is that this means the scripts are loaded on every page whether it needs them or not, adding overhead, which could potentially cause a performance hit or other issues. I understand the caution, but so far this has not been an issue for us.

To deploy, I first went into the Site Assets library and created a folder called ‘scripts’. I took the two files (jquery.min.js and stickyHeaders.js) and manually uploaded them into the newly created folder. (I ultimately did this via a PowerShell script but you can do it manually for now.)

I then needed to add the ScriptLinks for the files. Unfortunately, there’s not a UI to use to do this, so it has to be scripted. The steps to do this are fairly simple (I’ve included the entire PS script below for your use):

  • Get the context for the site.
Add-Type -Path (Join-Path $PSScriptRoot “libs\Microsoft.SharePoint.Client.dll”)

Add-Type -Path (Join-Path $PSScriptRoot “libs\Microsoft.SharePoint.Client.Runtime.dll”)

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)

$securePwd = ConvertTo-SecureString $pwd -AsPlainText -Force

$cred = New-Object PSCredential($user, $securePwd)

$ctx.Credentials = $cred

  • Pull the current collection of user custom actions (at the site level).
$site = $ctx.Web

$site.UserCustomActions

$ctx.Load($site)

$ctx.Load($ucActions)

$ctx.ExecuteQuery()

  • Now add a new custom action to the collection and populate the appropriate properties. The Title field is the primary key for the Custom Action. It’s how you will find it again if you need to update or delete the action. ScriptSrc is the url to the file, while Sequence determines the order in which the actions will be loaded. That’s all that’s needed for a ScriptLink reference.
$ucAction = $ucActions.Add()

$ ucAction.Location = “ScriptLink”

$ ucAction.Title = “jQueryScript”

$ ucAction.ScriptSrc = “~Site/SiteAssets/scripts/jquery.min.js”

$ ucAction.Sequence = “1”

$ucAction.Update()

$ctx.Load($ucAction)

$ctx.ExecuteQuery()

Run the script from a PowerShell prompt and like magic when you refresh your page, the scripts are loaded and you should see the floating headers in action. And since they do load for every page, the headers will even show on pages like ‘People and Groups’ and ‘Site Features’.

Pretty cool, huh?

Entire create-custom-actions.ps1 PowerShell script:

[CmdletBinding()]

param(

[Parameter(Mandatory=$true)]

[string]$siteUrl,

[Parameter(Mandatory=$true)]

[string]$user,

[Parameter(Mandatory=$true)]

[string]$pwd,

[switch]$force

)

Set-StrictMode -Version “3.0”

Add-Type -Path (Join-Path $PSScriptRoot “libs\Microsoft.SharePoint.Client.dll”)

Add-Type -Path (Join-Path $PSScriptRoot “libs\Microsoft.SharePoint.Client.Runtime.dll”)

function GetContext {

[CmdletBinding()]

param()


# Get the client context to SharePoint

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)

$securePwd = ConvertTo-SecureString $pwd -AsPlainText -Force

$cred = New-Object PSCredential($user, $securePwd)

$ctx.Credentials = $cred

return $ctx

}

function AddCustomActionToSite {

[CmdletBinding()]

param($ctx, $title, $source, $seq, $deleteIfExists)

## Get the existing custom actions on the site (we also use this to add a custom action to the collection)

$site = $ctx.Web

$ucActions = $site.UserCustomActions

$ctx.Load($site)

$ctx.Load($ucActions)

$ctx.ExecuteQuery()

$ucAction = $ucActions.Add()

$ucAction.Location = “ScriptLink”

$ucAction.Title = $title

$ucAction.ScriptSrc = $source

$ucAction.Sequence = $seq

$ucAction.Update()

$ctx.Load($ucAction)

Write-Host “Creating custom action '$($ucAction.Title)'”

$ctx.ExecuteQuery()

}

## Get the Context

$ctx = GetContext

## Add site custom actions to load javascript files to the site

AddCustomActionToSite $ctx “jQueryScript” “~Site/SiteAssets/scripts/jquery.min.js” “1” $force.IsPresent

AddCustomActionToSite $ctx “StickyHeaderScript” “~Site/SiteAssets/scripts/stickyHeaders.js” “2” $force.IsPresent


Related Posts

Caroline SosebeeFreeze Column Headers in SharePoint Like in Excel