Fixing the “Bad Request – Request Too Long” Error with Office 365 in Chrome

Bad Request - Request Too Long

This one really bugs me when it happens, and it’s pretty frequent. If you try to navigate to your Office 365 Settings or the Admin Portal in Office 365 with Chrome, you may well get this error instead of arriving at your regularly scheduled destination. I believe you may have a similar experience in Firefox as well.

I’ve gone Bingling for clues more than once on this one and I find lots of complaints about it happening, but the only solution seems to be “delete your cookies”. This is not an acceptable answer by any stretch of my imagination, but apparently it actually does work.

Eagle-eyed Twitter follower Harold Gale (@1wisegeek – aptly named!) spotted my compaint about it today and came to the rescue with a post with a very targeted fix.

In the hopes that it will help a few more people (and maybe rise a little higher on the Bingling charts so that they can find it!), here are the steps to the solution, temporary as it may be.

  • Go to the the Chrome Settings – usually this will be under the three vertical dots (sideways elipsis? leaky |?) in the top right of your window.

  • At the bottom of the page, click “Show advanced settings…”
  • Under Privacy, click the “Content Settings” button
  • Click on the “All cookies and site data…” button

  • In the search box in the upper right, search for “login.microsoft”

 

I found all these cookies after that search. Note the *5* “testcookie” entries. Enterprise-class!

You can also search for “office365” and/or “office.com” and delete some of those cookies as well.

Note that I have no idea what each of those cookies does, and you will want to be thoughtful about what you delete!

My hope is that this post gets few reads because Microsoft solves the issues here, but the Office 365 login “experience” has been less than stellar for many years now. I’m not going to hold my breath, and at least I know I have this post to help future me out more easily.

Advertisements

Dear Microsoft: Please Fix Retrieving SharePoint Lookup Columns with REST When the Lookup List is in Another Web

I love SharePoint. I really do. I especially love writing client side code to build awesome applications for my clients.

Today’s annoyance, though, comes while I am in the process of rewriting an application I built on SharePoint 2007, porting it to SharePoint Online in Office 365. This ought to feel like a huge leap forward technologically, and in some ways it does. I’m changing all my SOAP calls with SPServices to REST calls. I’m switching from KnockoutJS to AngularJS, which will simply perform better given the profile of the applications. (KnockoutJS was the right choice years ago when I first built the applications, but the data and feature requirements have outgrown it.)

Unfortunately, I’m running into a simple constraint that makes my life a lot harder. When I first started building these applications five years ago, I created what I’ve got to say is a very solid information architecture. It’s withstood shifting needs and requirements in the interim, and I stand by it. One of the aspects of this good information architecture is storing commonly used reference lists in the root site of the Site Collection. By creating a Site Column which is a lookup into each reference list, I can reuse those common reference values throughout my subsites.

This works great in SharePoint 2007 with SOAP calls. When I retrieve items with one of these lookup Site Columns from a list in a subsite, I simply get the ID and Title values, separated by a “;#”. However, when I try to do the same thing with “modern” REST calls, I get an error like this:

I’ve been a good team player, and I’ve suggested they fix this on the SharePoint User Voice in my suggestion Enable support for lookup columns in other webs in the REST API. The votes are up, and it’s been a while.

There’s a workaround, but it’s not very pleasant. (The easiest workaround is to simply stick with SOAP calls and SPServices – I’ve done that in several cases in other projects. But SOAP is officially “deprecated”, so…)

Here’s a specific example. The client I’m working with is in financial services, and they issue recommendations on securities. Those recommendations are very standard, and predictable: Hold, Buy, Sell, etc. In other words, perfect to store in a list in the root site called Recommendations. Why not a Managed Metadata column, you might ask? Well, I also wanted to store several other columns in the Recommendations list, like Description (e.g., “The analyst expects the security to outperform their coverage universe.”), a SortOrder value so I could rearrange the values in dropdowns using SPArrangeChoices, and several other fields which drive configuration of some reports. In other words: great information architecture. The values are all consistent across the various subsites, I store them once, etc. Nice setup.

I created a Site Column back in the beginning called Recommendation, which is a lookup into the title column of the Recommendations list (Hold, Buy, Sell, etc.). I used that Site Column in many Content Types defined on the subsite level. Those Content Types are mainly used in a list I’ll call Notes.

In SOAP with SPServices, I can make this [simplified] call:

This retrieves the items and returns nice JSON for me. Because Recommendation is a lookup column, it comes back as something like “1;#Buy” and that’s easy to turn into a JSON object like:

Easy, peasy.

However, when I try the analogous call in REST:

I get the error:

In other words, there’s no way to $expand the Recommendation column because it comes from an other Web, even though that is ideal information architecture!

The workaround, which André Lage (@aaclage) pointed out in my UserVoice suggestion (but I clearly didn’t get at the time), is to simply ask for the Recommendation column’s ID instead. This isn’t obvious at all:

This doesn’t follow the syntax we’d expect: we need to append “Id” to the end of the lookup column’s InternalName. Of course, this just gets us the ID of the item in the Recommendations list; it doesn’t fetch us the Title value, which is what we really want. Because of this, I need to do a *separate* REST call to get the items from the Recommendations list and merge the values in my client side code.

Now, one could argue that this is more efficient. I don’t ask the server to $expand the values across thousands of notes (yes, there are way more than 5000; I’ve written enough about that lately – I may have mentioned it here and here and here and here), so it gets a break. Retrieving the 5-10 values in the reference list (in this case) is no big deal.

But I have a half dozen or so of these lookup columns to deal with in this application, which means a half dozen extra REST calls, plus the code to merge the values. More work for me, but more importantly a longer wait for the application user when they load the page. I believe that poor UX is what has doomed many a SharePoint roll out, and I loathe creating a poor UX myself. In this case, I’ll make it work, but I’d really like to see this change.

Yes, Virginia, You Can Get More than 5000 SharePoint Items with REST

If you haven’t been paying any attention, you might not know that I loathe the 5000 item limit in SharePoint. I may have mentioned it here and here and here and a bunch of other places, but I’m not sure. But given it’s there, we often need to work around it.

No 5000 item limit

I’ve written this little function before, but alas today I needed it again but couldn’t find any previous incarnations. That’s what this blog is for, though: to remind me of what I’ve already done!

In this case, I’m working with AngularJS 1.x. We have several lists that are nearing the 5000 item level, and I don’t want my code to start failing when we get there. We can get as many items as we want if we make repeated calls to the same REST endpoint, watching for the __next link in the results. That __next link tells us that we haven’t gotten all of the items yet, and provides us with the link to get the next batch, based on how we’ve set top in the request.

Here’s an example. Suppose I want to get all the items from a list which contains all of the ZIP codes in the USA. I just checked, and that’s 27782 items. That’s definitely enough to make SharePoint angry at us, what with that 5000 item limit and all. Let’s not get into an argument about whether I need them all or not. Let’s just agree that I do. It’s an example, after all.

Well, if we set up our requests right, and use my handy-dandy recursive function below, we can get all of the items. First, let’s look at the setup. It should look pretty similar to anything you’ve done in an AngularJS service. I set up the request, and the success and error handlers just like I always do. Note I’m asking for the top 5000 items, using "&$top=5000" in my REST call.

If there are fewer than 5000 items, then we don’t have a problem; the base request would return them all. Line 32 is what would do that “normal” call. Instead, I call my recursive function below, passing in the request only, even though the function can take two more arguments: results and deferred.

The recursive function simply keeps calling itself whenever it sees that the __next attribute of the response is present, signifying there is more data to fetch. It concatenates the data into a single array as it goes. In my example, there would be 6 calls to the REST endpoint because there are 27782 / 5000 = 5.5564 “chunks” of items.

Image from https://s-media-cache-ak0.pinimg.com/564x/16/bb/03/16bb034cb3b6b0bdc66d81f47a95a59f.jpg

Image from https://www.pinterest.com/pin/323062973239383839/

NOW, before a bunch of people get all angry at me about this, the Stan Lee rule applies here. If you have tens of thousands of items and you decide to load them all, don’t blame me if it takes a while. All those request can take a lot of time. This also isn’t just a get of of jail free card. I’m posilutely certain that if we misuse this all over the place, the data police at Microsoft will shut us down.

In actual fact, the multiple calls will be separated by short periods of time to us, which are almost eternities to today’s high-powered servers. In some cases, you might even find that batches of fewer than 5000 items may be *faster* for you.

In any case, don’t just do this willy-nilly. Also understand that my approach here isn’t as great at handling errors as the base case. Feel free to improve on it and post your improvements in the comments!

Unity Connect Haarlem 2016 Follow Up

Sorry it’s taken me a little while to get this post up, but I had a great time in Haarlem, The Netherlands the week before last at the Unity Connect conference. The conference is “under new management” – as it were – this year, and though I haven’t attended this particular event in the past, it’s top notch. I look forward to where George Coll (@GeorgeColl) and the other folks from Blue Whale Web (who are running the IT Unity brand now) take things.

The location was pretty amazing, as it was in the Philharmonie Haarlem. It’s a modern concert and performance venue built into a very old building (at least to us Americans!). Check out this photo of Dux Sy (@meetdux) after he presented in the main concert hall.

I delivered two sessions at the conference, and links to the two slide decks are available below.

Several people have asked about the code examples I showed. You can find the small survey in my KO SharePoint repo on Github. The Sliding Quick Launch CSS (courtesy my colleague Julie Turner [@jfj1997]) is in our Sympraxis Conference Demos repo on Github.

I also had the great pleasure of speaking at the Dutch Information Worker User Group (DIWUG) the evening before the conference started, along with Adis Jugo (@adisjugo). The slides from that talk – Creating a Great User Experience in SharePoint – are below.

20161116_214053000_ios

 

Movement on the SharePoint List 5000 Item Limit!

In October, Eric Alexander (@ejaya2) posted an idea to the SharePoint UserVoice called Prioritize large list management in SharePoint Online. Yesterday I received an alert about it because I had voted for it.

The 5000 item limit has been an albatross around our necks since SharePoint 2010. SharePoint 2007 was the wild west days: we could pile as many items into a list as we wanted and retrieve them without a problem, though perhaps at the expense of performance.

I’ve railed about this limit many times in the past, like here.

No 5000 item limit

If you search the SharePoint UserVoice for “5000“, you’ll find no fewer than 14 suggestions circling this topic. There are probably even more, but without the number 5000 in them.

Luckily, our good friends in Redmond know this is an issue for us. As of yesterday, Eric’s suggestion moved to “Working on it”, at least for SharePoint Online.

Prioritize large list management in SharePoint Online - Working on it

 

 

 

I’m looking forward to what happens here. As Eric notes in his suggestion:

If nothing can be done, then TechNet NEEDS to indicate that the actual limit of SharePoint Online lists and libraries is 5,000 items, not the current architectural limit of 30 million.

The more we want to use SharePoint as a service (SPaaS?), the more important it becomes to get past this limitation.

I’m certain that the work Dan Kogan (@kogandan) and his team are doing on the SharePoint Framework (SPFx) has made it obvious that this limit is a serious issue. (Sometimes you have to take the albatross off your neck and slap someone with it.)

Image from http://elkgrovegovernment.com/with-the-nrc-announcement-has-the-albatross-been-taken-off-elk-groves-neck/

Image from http://elkgrovegovernment.com/with-the-nrc-announcement-has-the-albatross-been-taken-off-elk-groves-neck/