Edit in grid view button missing – how to resolve

In this post we take a look at the common causes for the edit in grid view button to not be visible in SharePoint Online & Microsoft Lists.

The problem

Edit in grid view was made generally available to all Microsoft customers in February 2021 and is available for lists and document libraries in SharePoint Online or Microsoft Lists. I recently had an issue reported to me that the edit in grid view button was missing from the ribbon in a SharePoint Online document library.

After taking a look myself, sure enough this was the case and the option wasn’t present. The first thing to note about this particular document library was that the default view had grouping enabled on a particular column. In trying to replicate the issue, at first when I created a new document library the edit in grid view button was present:

I noticed that when I applied the same grouping to the view the edit in grid view button disappeared!

Applying a grouping to a library view causes the edit in grid view button to disappear.

The solution

The solution for this is more of a workaround as this appears to be a Microsoft bug. I decided the best way to get around this was to create a specific view that defaults into grid view mode when selected. To do this:

  • With your library open > press the cog > library settings
  • Scroll down to views > press create view
  • Select datasheet view
  • Give the view a name > select the columns you wish to display > press OK

Now you have a view that defaults to grid view without users having to select it!

Other ways around this issue would be to:

  • Remove the grouping for the view in question to allow edit in grid view
  • Create a new view with the grouping removed and show users how to find it to edit in grid view

Unfortunately this seems to be an bug that although it has been raised with Microsoft, the SharePoint UserVoice has since been shut down so it’s unclear if it is being worked on or not. You can raise feature requests through the Microsoft feedback portal.

Bonus – free history lesson!

Out of curiosity I wanted to see if this was an issue in SharePoint 2010 as I was sure I would have come across it by now. As expected, it wasn’t and datasheet view works fine when views have groupings within them.


How to break permissions inheritance on large libraries/ lists in SharePoint

This post describes a long-standing issue with managing permissions for large libraries or lists in SharePoint Online and gives a workaround for how you can break permission inheritance.

Intro

If you have ever tried to migrate a large volume of data into SharePoint libraries or lists you will have likely encountered an issue with trying (and failing) to break permissions inheritance on lists/ libraries following the migration.

I was recently dealing with this limitation myself as part of a migration project. We had migrated terabytes of data from on-premise file servers into SharePoint Online libraries and as we were beginning to break permissions inheritance on the library saw this error:

The issue

Faced with the problem of needing to secure lots of data that, based on the error above didn’t seem possible – I decided to refresh my memory on just what the limits of lists/ libraries are in SharePoint Online:

A list can have up to 30 million items and a library can have up to 30 million files and folders. When a list, library, or folder contains more than 100,000 items, you can’t break permissions inheritance on the list, library, or folder. 

Microsoft docs – SharePoint limits

With the above being true, the error mentions the list view threshold – which is the way SharePoint throttles and limits resources that govern the amount of data and throughput that can be managed. The list view threshold is set to approximately 5000 items by Microsoft and cannot be changed.

Now the issue with not being able to change the list view threshold, although unique to SharePoint Online isn’t a new thing – in fact it seems to have been a problem for lots of people for a long time. So if we are unable to change the list view threshold, what options do we have?

Well as it turns out the list view threshold error appears to be a bit of a red herring. I had taken a look at Microsoft guidance for managing large lists and libraries – creating indices within the library, but this had no affect as the total number of items in my library exceeded 100,000.

An example of a library that had over 100,000 files/ folders in SharePoint Online.

Workaround

The answer for me came from an inspired post on the SharePoint Stack Exchange! I give full credit for the simple, yet brilliant way to get this to work to Kasper Bo Larsen, who suggested that you should just delete enough stuff to get under 100,000 > break permissions inheritance > then restore the deleted items.

This works a treat!

Now it’s not perfect, nor would I call this a fix but if you have already migrated your data then this certainly will work for you.

Things to note

#1 Remember to break inheritance before any migration tasks begin!

I know this is a super obvious one and hindsight is always 20/20, but I’ve learnt from this mistake and built it into any future migration runbooks to break permissions inheritance before starting any migration tasks. It will save you a lot of hassle I promise…

#2 Trying to break permissions inheritance via PowerShell will yield same results

I had the thought during my investigations of this that maybe trying to break permissions inheritance via PowerShell might supersede some of the restrictions deployed to us mere GUI administrators – this was not the case and the PowerShell route fails just the same.

#3 There are no service limits or boundaries for the SharePoint recycle bin

I tried to find out if there were any limits to how much data you can restore from the SharePoint recycle bin in one go – I couldn’t find anything. With that said, I was able to restore over 60,000 files/ folders in one sitting back to it’s original location after breaking permissions inheritance so I don’t believe that will be a blocker.


How to remove commas from number columns in Lists

This article describes how you can remove commas (thousand separator) from number columns in Microsoft/ SharePoint Lists.

Introduction

You may have noticed a comma or thousand separator appearing when using the number column in Microsoft/ SharePoint Lists. Commas appearing in number columns in SharePoint have been a long-standing issue, going back as far as SharePoint 2010 and beyond.

In classic SharePoint, a common way to solve this problem was to convert the number column into a text column and validate it to remove commas using a formula such as =ISNUMBER([ColumnName]+0). However, with modern SharePoint, plus the advent of Microsoft Lists it’s now become easier to manipulate columns using column formatting to achieve the desired result.

I personally encountered this issue when importing data from excel into a modern SharePoint list. In my situation, the preview offered when importing from Excel did not show the commas in my data. It was only once it was imported into SharePoint that the commas appeared.

Remove commas from number columns

  • Left-click on your number column
  • Under Column Settings > select Format this column
  • Ensure the Format Columns tab is selected, scroll down and press Advanced mode
  • Remove the text from the box and add the following JSON:
{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField > 0,'', '')"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

  • Press Save
How to add JSON formatting to number columns to remove commas.

Note

Microsoft added roadmap item 68716 in October 2020 for Microsoft/ SharePoint lists to include support for thousand separator in number columns. As this time of writing, the roadmap states this feature is due for release in June 2021 which will allow List owners/ members to choose if the comma should appear or not.

Hide a SharePoint list or library from view all site contents

Have you ever been asked to hide a list or library from a SharePoint site? If so, you go straight for selecting ‘no’ to displaying the list or library on the Quick Launch or removing it from the navigation. However, your eagle eyed users notice the handy view all site contents option and see that it is still listed there – they want it gone!

Luckily, all you need is SharePoint Designer and it is as simple as a click of a button…

(These steps were created using SharePoint Server 2010)

  • Open the site that where list or library resides in SharePoint Designer
  • Under Lists and Libraries – Select the list or library you wish to hide
  • On the main list settings page – find the Settings section
    SPD
  • Check the Hide from browser option
    hidefrombrowser

Thats it! when you option the view all site content page now, that list or library will no longer be showing. Also, if you want to re-instate it at a later date, just un-check the box and it will re-appear.

This also works for SharePoint 2013, 2016 and SharePoint Online, under the site contents page.

Page declared a record or placed on hold and is read-only

If you’ve ever been in the situation where you try to edit a page in SharePoint and you see this seemingly unchangeable message appear at the top of the page stating:

“Page has been declared a record or placed on hold and is read-only”

Fear not! I’ve seen this message appear and it usually occurs when a user (or service account) is operating as a system account. This could have been set manually by said user if they have access to the web application via central admin:

If this is the case, you can overwrite the system account check in via SharePoint Designer. You’ll obviously need the correct permissions to access SPD beforehand! In the following example I’m using a SharePoint 2010 environment. To do this:

  1. Connect to your web application in SharePoint Designer
  2. Navigate to All Files – Pages
  3. Right-click on the page that is currently locked
  4. Undo checkout

NOTE: You won’t be able to view the individual pages within each Page Library if you navigate through Lists and Libraries. This space is used to view and manage the settings for each list type you have. All Files takes you to the ‘root’ of your web application, where you can see everything that sits under the web application.

The SharePoint date format problem

I know what you’re thinking, what date format problem? Well this is something that’s cropped up for me time and time again so let me explain. Most requests I get for new SharePoint lists will usually contain a variety of custom date columns and said list will also require that the default display form shows a unique view of this data.

On the default display form, you will notice that the custom date columns will be showing data that looks like this:

bad date format

Well this just won’t do, will it? The good news is we can fix it!

To be able to customise the display form, you will need to be able to connect to your SharePoint environment using SharePoint Designer, if you don’t have it you can download it from Microsoft here:

SharePoint Designer 2010 (32 bit)

For this example, we are only focusing on one custom date column, at the end of the post I will summarise how to fix this issue for multiple custom date columns.

  • Open SharePoint Designer and connect to your environment
  • Navigate to the lists and libraries and open the list you want to change
  • Within the Forms section – New Form
SharePoint New Form

(I’d recommend creating a new display form for no other reason than to avoid potentially breaking the active one).

  • Give the form a logical name so you can identify it at a glance as a display form (I usually go for DispForm2)
  • Press OK
  • The code behind the display form will open, don’t worry too much about this we are only interested in a small portion of the code.
  • In the Ribbon – under the Home Tab – Press Advanced Mode
Advanced button on ribbon
  • If it’s not already, at the bottom of the form – change the view to split view
SharePoint split view
  • Scroll through the design view of your form until you find the custom date field – select it
  • In the code view, make sure the following line is highlighted:
    <xsl:value-of select="@Start_x0020_Date"/>
    (NOTE: the bits after the @ will be the name of your date field)
  • If you amend this line of code to look like this:
    <xsl:value-of select="ddwrt:FormatDate(string(@Start_x0020_Date), 2057, 3)"/>
  • you should see something like this:
    11 June 2014
  • Save the display form
  • Press Preview in browser to double check the format date function is working as expected
  • Close preview browser, close the display form
  • In the forms section of the list – select your form
  • In the Ribbon – Set as Default
set as default

That’s all there is too it! It might seem like quite a few steps but it isn’t too bad once you get comfortable with the forms code view.

If you have more than one custom date column that you need to format, just amend the code of each custom date column using the example above as a guide (just make sure if you copy/paste the code that each @ name is different.

FormatDate function and locales explained

What our updated code is doing is inserting a FormatDate function, which allows us to add the locale parameters to the end of our line code (the 2057, 3). The locale parameters control what the output of the FormatDate function will be, here are some examples of the outputs and locale parameters:

OutputLocaleFormat
3/23/200910331
3/23/2009 12:00 AM10332
Monday, March 23 200910333
12:00 AM10334
Monday, March 23, 2009 12:00 AM10337
3/23/2009 12:00:00 AM103313
Monday, March 23, 2009 12:00:00 AM103315
23/03/200920571
3/23/2009 12:00 AM20572
23 March 200920573
00:0020574
23/03/2009 00:0020575
23 March 2009 00:0020577
00:00:00205712
23/03/2009 00:00:00205713
23 March 2009 00:00:00205715

If you are looking for a list of all the available locales you can find them here:

https://msdn.microsoft.com/en-us/library/ms912047(v=winembedded.10).aspx

That’s it for now, if you’ve got anything you’d like me to cover feel free to get in touch or leave a comment!

Hiding NEW! from items in lists or libraries

This is my first ever blog post…scary! I’ll start with a really easy solution to a problem I was tasked with solving by one of our departments…

Ever wanted to completely remove any reference to the pesky NEW! icon from freshly uploaded documents or list items? Yeah me either…well if in case you ever felt so inclined to do so here are some very simple steps to remove the icons from any new documents or items on a page.

This example is a SharePoint 2010 web application with a standard publishing site collection active:

    1. Navigate to the page you want to remove the NEW! icons from and begin editing
    2. Add a content editor webpart to the page; I added it to the bottom of the page but you can add it whenever suites
    3. Click on the down arrow to open the webpart menu – Select Edit Web Part
    4. You’ll now notice the content editor webpart has changed, it will now say ‘Click here to add new content’, click here!
    5. In the ribbon – Editing Tools menu – Format Text tab – Press HTML – Edit HTML Source
    6. In the HTML editor, copy and paste this little bit of CSS:
      <style>
      IMG.ms-newgif {display:none;}
      </style>
      
    7. Press OK on the HTML editor

    Voila! The NEW! icons have vanished and we can move on with our lives…the next little bit is totally optional, but I think for completeness it makes sense to do. We’re just going to rename and hide the content editor webpart so that it’s not visible and any editors know not to touch it:

    1. Click on the down arrow to open the webpart menu – Select Edit Web Part
    2. In the content editor webpart menu – expand Appearance
    3. Change the Title to ‘Do not delete’
    4. Change the Chrome Type to None
    5. Press Apply and OK

    That is all there is to it, we have successfully removed the NEW! icons so they are no longer visible and also hidden the webpart which contains the tiny snippet of CSS that makes the change.