Column Formatting in SharePoint Online Rolling Out!

Every once in a while, something comes to Office 365 where no one has anything negative to say. (Is that a back-handed complement?)

The fact that this tweet got 22 retweets and 34 likes (as of this writing) and lots of eyeballs means this is one of those things.

Over the years we’ve had several ways to change the look of a column in a list view. Early on – say in the SharePoint 2007 and 2010 days – we often used jQuery in the page to do what we needed. This was WAY before it was fashionable to write client side code for SharePoint. It worked just fine, but had pretty decent barriers to entry. You had to know how to write JavaScript, get it into the page, use CSS3-like selectors in jQuery, etc.

In SharePoint 2013, we got something called jsLink, which was part of what most people refer to as Client Side Rendering, or CSR. This was a pretty good mechanism, and also used JavaScript, but with some fairly arcane functions and structure. However, it allowed us to customize both individual fields and entire rows.

Announced at Ignite, we now are getting a new capability to format columns in “modern” list views. It doesn’t seem to have a snazzy name other than “Column Formatting“, but that doesn’t matter.

There’s a great page with documentation and examples which we can tweak to get what we need: Use column formatting to customize SharePoint. This new mechanisms use what’s called declarative customization, meaning we don’t actually write code – though it may feel like code to many of you. Rather than code, we write JSON, which stands for JavaScript Object Notation. JSON is basically a way to store data.

Let’s look at one of the examples from that page.

Say we want to color a column’s value if that value is less than 70. (The text on the page says color it red, but the little image is yellow, so I’m not sure they have it right at the moment – especially since the number is exactly 70.)

In any case, the JSON data below says the following:

If @currentField < 70 Then add the CSS class = sp-field-severity–warning.

which gives us something like this (with the above caveat):

While the techies will tell you this isn’t code, it really sort of is. The condition we want to apply is stored as JSON – which is data – but tells SharePoint how to render the field. The fact that we use operators, operands, etc. to make this happen means that we are effectively writing code. That said, I expect we will see a great number of “recipes” arise out in the community, just like we did with the great SPCSR repo in Github. In fact, I challenge all of you to start sharing your recipes as soon as you start using this feature!

As soon as we can all get our hands on this new capability (watch for it in First Release by the end of October), I expect to start adding examples to a new Github repo I just created: SPColumnFormatting. If you don’t understand Github, don’t fret. You’ll be able to just copy the recipes and alter them to meet your needs. If you do understand Github and would like to contribute, please do with a pull request. The faster we build up a set of recipes people can use, the more productive the citizen developer army out there will be!


Enabling the “Anyone” Sharing Setting in and Office 365 Site

Julie (@jfj1997) and I are working hard on SPS New England coming up on October 28 in Burlington, MA. I wanted to set up an easy place for the speakers to drop their slide decks. We have a perfectly good Office 365 tenant, so I figured I’d just create a Document Library somewhere and share the link with the speakers so they could do so.

Unfortunately, it wasn’t quite so simple. (What I needed was an Anyone link, but it was grayed out. The message on hover “Your organization is preventing you from selecting this option.” didn’t help me much since, well, I am my organization! (Well, I do have to answer to Julie from time to time.) I’d love for the message to say something link “Talk to your admin about turning on Anyone sharing, as described at this link.” The Learn more link didn’t really help, either. I considered using Dropbox!

Instead of using Dropbox, since I couldn’t figure out the magic incantation to light up the Anyone link in the sharing dialog, I turned to my favorite RTFM replacement, the Twitters.

I got a bunch of suggestions, and maybe I was too dense to understand them, but I wasn’t getting any joy. Luckily, one of Microsoft’s finest employees, Tom Resing (@resing), saw my tweet:

Tom’s a swell guy, so he poked around at Microsoft and asked a few people how to do this. Today he got back to me.

Before I tweeted, I had checked the settings in the following two places, so thought I should be good.

First, the Office 365 Admin center, under Security & Privacy. That looked good.

Then I had checked in the SharePoint Admin Center under sharing, and that looked good.

It turns out there is yet another setting I needed to change. This one is in the SharePoint Admin Center and is at the individual Site Collection level under Sharing:

Even though the other settings above say that anonymous links are allowed, I still needed to say that anonymous links were allowed at the Site Collection level.

Even worse, if the site is a “modern” Office 365 Group -based site, there is no UI to change this setting yet – at least until we have the new SharePoint Admin Center. You’ll need to use Powershell (IMO, the answer should never be “you need Powershell”).

After a few days of trying to figure out, I can start collecting slides! Thanks, Tom!




Detecting the Current SharePoint User’s Regional Settings

Brian McCullough (@bpmccullough) pinged me on Twitter today with a question:

Brian was asking about an old blog post of mine where I basically screen scraped the data: Detecting the Current User’s Regional Settings with jQuery. It worked fine in the old days, but I didn’t think it would work anymore on SharePoint Online.

It was bugging me that I couldn’t remember the answer, so I went digging into some SharePoint pages to see what I could see. In two different SharePoint Online tenants, I saw that the _spPageContextInfo object has the following properties:

As I recall, if the userTimeZoneData  is not null, then the user has set their time zone themselves. If it is null, then the user is going along with the webTimeZoneData. I don’t have an example, but I’m pretty sure you can also check preferUserTimeZone  to see if the user wants to have her own time set.

Note that there are lots of other goodies in  _spPageContextInfo as well. The other properties which are pertinent to time zones are:

  • userTime24  – Does the user prefer 24 hour (military ) time?
  • webTime24  – Does the Web display 24 hour (military ) time?

The TimeZoneData  properties are also quite rich, showing the time zone description, the bias from GMT, Daylight Savings Time bias (usually 60 mins, but sometimes 30 mins), when DST starts and ends.

<update date=”2017-10-04″>

Bryan Mathews (@brmathews) reminded me on Twitter that there is a REST endpoint that provides some of this information as well.

If you use the /_api/web/RegionalSettings/TimeZone endpoint, you’ll get back information like this:

This is XML, but the data you’ll get back in JSON is the same. Note that there isn’t as much details here as in the  _spPageContextInfo, but this endpoint is available to you in any page.


Note that depending on the version of SharePoint you’re running, your mileage may vary.

Does anybody really know what time it is?


My Life Is Good – Celebrating Ten Years of Sobriety

Today marks ten years to the day since I had my last drink of alcohol. That’s 3,654 days without a drink, or 87,696 hours, or 5,261,760 minutes. If you can’t live without a drink, you know how big those numbers are. I quit cold turkey on September 30, 2007. Most people can’t do that and it’s much harder for them. I was lucky that I could simply turn it off – but I had gotten to a point where if I didn’t turn it off, I might not have lived to write this post.

I tried Alcoholics Anonymous and an addiction therapy group for about six months after I quit, and for me they just didn’t feel right. I joined a men’s therapy group and spent over six years working on my demons and just normal issues to help make myself as whole as possible. You don’t have to use groups like this to get off the sauce, but for many people they can be great anchors.

It amazed me how much of my personal growth had been stunted by my alcohol abuse. I had moored myself in the age where I really got going with my drinking. The literature backs this up – many drunks stop maturing in their thoughts and actions once the booze takes over their lives. I was what many people call a “functional alcoholic”. I did my job – and not so badly – but when I got home, I had to have a drink. Over the years, I needed to drink more and more to make myself feel what I though of as “good”. The bottles became better friends to me than my actual friends, or even my wife.

When I first stopped drinking, it amazed me how embedded drinking is in our culture. Some of that embedding is simply about enjoying life to its fullest (think a French country meal without a nice glass of the local red), but far more often here in the USA, it’s about partying and going crazy. (If you’re doing a lot of the latter, you might want to think about your relationship with alcohol.) In many ways, we’re taught that alcohol = fun. Without drinking, we’re missing out.

After I quit, it amazed me:

  • That going out with friends was actually more fun in many ways
  • How good I could feel waking up in the morning
  • How much more time there was in the day – drinking is a time consuming job
  • How I no longer wrestled with my “oh, I could quit anytime if I wanted to” nonsense discussions with myself

This isn’t just a self-congratulatory post, though. (I admit – it does feel pretty good!) I want to help anyone I know out there who feels like they may have a problem. Addictions are crazy things; when we are in the middle of them, we can’t see them – once we stop, it’s like a grey cloud has been lifted.

I’m not here to preach to anyone about their own choices. For me, stopping my drinking was – I believe – a matter of life or death. But it doesn’t have to be that serious for it to be a good decision for you. Alcohol cheated me out of many good years, and probably many good relationships. When you’re a drinker, you don’t tend to blame the alcohol or yourself – you blame others.

In the last ten years, I:

  • Started my own successful business
  • Wrote an open source library used by thousands of people around the world – SPServices
  • Became a Microsoft MVP for SharePoint
  • Traveled the globe speaking about SharePoint, collaboration, and knowledge management – all my passions
  • Regained the love of my wife (despite all my other failings)
  • Tried to be the best Father I can be to our awesome son Calder (aka “The Dude)
  • Took back control of my life

Most importantly, I’ve enjoyed so much of that time. (Into every life some rain must fall.) For many years, I had thought I needed the booze to keep me grounded and happy. The image I had in my head was of Jacob Marley in A Christmas Carol. I don’t know why it was that image, but it stuck with me for years. Without the booze, I’d no longer be grounded, plus the booze was a burden I deserved. By casting off my alcohol-laden Jacob Marley chains, I have felt lighter, and breathed more clearly than I did in the time I was drinking.

Now that I look back at that thinking, I can’t believe it made any sense to me. I consider myself reasonably intelligent, yet I was able to convince myself of the value of a way of life which was totally not what I thought. That’s the insidious nature of a substance like alcohol – it rearranges the way your mind works so that you can make sense of your own behavior.

If you ever want to have a conversation with me about what my journey was like, I’m here. No one should go through the suffering addition causes without an opportunity to make big changes. But the only person who can make those changes is you. Oddly, there’s little stigma to drinking, yet considerable stigma to being an alcoholic, even if you can quit. I’m here to tell you that any amount of stigma is worth it to live the full and happy life you deserve, despite yourself.

Thank you to everyone who has been with me on this grand journey. I’m not going to list names, but many people in my life have been important parts of what amounts to my rebirth. I also want to thank all of the people who read this blog, because your thanks and knowing I’m helping some of you do better things with the technology I love to work with give me sustenance every day. If you’ve made it all the way to this point, thanks for spending a little time with me on what is – for me – an important day.

Next time you see me, let’s raise a glass of club soda together. And m. and C. – I’m here for you.

Microsoft Flow: Updating File Metadata When You Have More Than 5000 Items

It’s been way too long since I did a blog post. Let’s remedy that, shall we?

Paul Culmsee (@paulculmsee) has been doing some great work with Microsoft Flow and PowerApps. His daughter Ashlee even won a contest put on by the PowerApps team to make a fidget spinner app! When Paul digs into a technology, stand back, because he beats it into submission like few others. If there is a Clever Workaround – and we need those way too often – he’ll find it.


Paul’s latest post on this topic shows that we now can get the ID of the newly created item from the Create File step, which we can use with the newer SharePoint – Update File Properties Flow action. Thank goodness my tricks here are no longer necessary!  The (new current) best way to update SharePoint document metadata using Flow


Paul did a post back in August I’ve referred to multiple times as I’ve tried to use Microsoft Flow. My approach with Flow here is that I try to use it for anything new I need to automate in Office 365. If I hit a roadblock, then I revert to SharePoint Designer workflows. The good news is that I’m finding I revert less and less, though it still happens maybe a third of the time at this point. (I’m guessing, but that’s about what it feels like. I would love it to be 0%!) Unfortunately, that means rework down the road if we want to be fully “modern”.

Paul’s article The (currently) best way to update SharePoint document metadata using Flow shows us the unfortunately byzantine way we need to do things in order to update metadata on a file we’ve just created. The fact that we don’t get a useful “handle” back to the file we just created is sorta crazy, and the Microsoft Flow team knows this. In the meantime, Paul’s approach works. Well, it works unless you have more than 5000 items in a library.

I had exactly that situation. At one of my clients, we have an automated process which dumps files into a library I’ve inventively called __Temp. When a new file shows up, I run a Flow to copy the file into a place where we want to store it and apply metadata to the copied file. My problem arose when I tried Paul’s second step. Because my library has way over 5000 items, that step failed.

So, until we are able to get the ID or some other useful handle for the newly created file, I needed to come up with a cleaver workaround on top of Paul’s clever workaround.

The trick was to do the following:

  • Add an index to the Modified column in the destination list.
  • Set up a TimeStamp variable in my Flow to capture the startOfDay() when the Flow is running
  • In the GetItems step, use a filter based on the Modified column, and *then* the FileLeafRef to find the file I just created

Here it is in pictures…

Make sure you have an index on the Modified column in the target list. In my case, I have indices on several other columns as well.

In the flow, set up a variable to hold startOfDay(utcnow()) . You set this up in the Expression Builder. I initially tried to set the variable just before I created the new file, setting it to utcnow() . In theory, this should have worked, but I found I had about a 50% failure rate. This doesn’t make a lot of sense, but to be safe, I switched to startOfDay() , which has proved reliable.

Just like Paul’s example, I then create the new file.

In the GetItems step, I use the more complex filter:  Modified gt datetime'TimeStamp​​' and FileLeafRef eq 'Name' Because Modified is an indexed column, and it is the first filter in my expression, the result set is reduced to fewer than 5000 items and the query works.

Now everything works reliably. I can’t wait to reduce the complexity of this Flow when we get a handle to the newly created file in the Create File step!

Aren’t you proud of me? I wrote this whole post without whining about the 5000 item limit.