Getting the Reply Count for a SharePoint Discussion Using the REST API

The other day I was creating a custom UI for a SharePoint 2013 Discussion list. To me, the out-of-the-box UI in SharePoint 2013 is definitely a step forward from previous versions. It’s still pretty rudimentary, though, and my client wanted something “more like other forums”.

SharePoint’s Discussion lists are sort of like Document Sets, in that the original post is a Discussion Content Type which inherits from Folder and the replies are the Message Content Type, which inherits from Item. So there aren’t any Documents involved, but Discussions are once again glorified Folders.

Since I’m using KnockoutJS on this project, I can make the UI look like pretty much anything. One thing we wanted to display was a reply count per thread. This seemed easy enough, but it could get expensive to retrieve all of the replies just to count them for the UI. Unfortunately, there was no obvious column (like “Replies” maybe?) to give me the answer.

Trolling around SharePoint StackExchange, I ran across a post by my friend Rothrock entitled How to get ItemChildCount of DocumentSet (folder) using REST Api asking a pretty similar question, but in his case it was about Document Sets. There ought to be a field like ItemChildCount or something, but he couldn’t find it, either. We’re used to a field like that using SPServices and SOAP, but there didn’t seem to be anything analogous in REST.

Luckily, someone who goes by ECM4D answered Rothrock with this example. (There were typos, but this was the idea.)

/_api/web/lists/getbytitle('your_list')/items?
  $select=ID,Title,Folder/ItemCount
  &$expand=Folder/ItemCount
  &$filter=FSObjType eq 1

By expanding the Folder, we can get at an ItemCount easily, just as we’d like to expect. This is yet another example where the documentation for the REST services simple doesn’t go deep enough to help us. Because much of the documentation is example-based – and this isn’t in any of the examples – we’re out of luck.

I ended up with something pretty similar, and it works great. Note that the ItemCount includes *all* replies, which means replies to the original post as well as replies to replies. If you wanted just replies to the original post, you’d need some other method.

MyProject.Promises.Discussions = $.ajax({
  url: _spPageContextInfo.webAbsoluteUrl +
    "/_api/web/lists/getbytitle('Discussion')/items?" +
    "$select=ID,Title,FileRef,IsFeatured,Created,Author/Title,Folder/ItemCount" +
    "&$expand=Author,Folder",
  method: "GET",
  headers: {
    "Accept": "application/json; odata=verbose"
  }
});

This call – using jQuery’s $.ajax function – gets me the basic info about the Discussion items I need:

  • ID and FileRef let me provide links for the user to use to go deeper into the threads
  • Title is the Subject of the original post
  • IsFeatured tells me if it’s a featured post so that I can highlight it in some way
  • Created and Author tell me who started the thread and when
  • Folder/ItemCount is that mysterious count of replies I was looking for

All pretty easy, really, once I found that thread on SharePoint StackExchange. One of the best ways to learn the ins and out of the REST endpoints is to troll those public forums. Be forewarned, though: you’re just as likely to find something that doesn’t work as something that does.

Get all SharePoint Document Library Files and Folders at a ServerRelativeUrl in One REST Call

Recently, I was building a directory tree view of a Document Library for a client. Yes, you can say this shouldn’t be necessary. We can just tag the documents with metadata and we won’t need folders at all. Unfortunately, that’s not always the way people want to work.

To build this, I started by calling the _api/Web/Lists/getbytitle('Document Library Name')/items endpoint. I figured I’d just get all the documents in the library and sort out the display from the array. I got this working pretty well and in my test environment with a few hundred documents, it worked great.

Then – boom. There was a little requirement I didn’t know about: quite a few of the Document Libraries where we wanted to use this had more than 5000 documents. I stupidly hadn’t thought of that possibility. In my experience, it’s pretty unusual to see Document Libraries with that many documents, though it definitely happens.

Why does 5000 documents matter? Well, as of SharePoint 2010, we went from being able to request as many items as we wanted to an item limit of 5000. I’ll admit it’s not usually a great idea to request that many items from the client, but sometimes we need to.

I had two choices:

  • Add some paging logic to the call. This would mean that if there were more than 5000 items, I’d simply make Math.ceil(documentCount / 5000) calls to get them. In smaller Document Libraries, it would still be one call; as the number of documents went up, it would take more calls.
  • Be smart about it. Just request the objects (files and folders) in the root of the Document Library, and then on-demand, only request what I needed as the user expanded each folder.

The former would have been a little easier, but with larger libraries those calls could get pretty slow. The latter idea was really the “enterprise” way to do it. The problem was that the _api/Web/Lists/getbytitle('Document Library Name')/items endpoint didn’t really give me the right info to do it well.

So I turned to a different endpoint: _api/Web/GetFolderByServerRelativeUrl('folderRelativeUrl'). This is a newer endpoint and is designed for doing stuff like this. We can pass in the relative URL – maybe something like “/sites/SiteA/SubSiteA/LibraryName/TopFolder/SubFolderA/SubFolderB” – and get back just the files and folders for that relative path.

It takes two calls for this, though:

  • _api/Web/GetFolderByServerRelativeUrl('folderRelativeUrl')/Folders
  • _api/Web/GetFolderByServerRelativeUrl('folderRelativeUrl')/Files

That would work, but it seemed a bit inefficient. Wouldn’t it be better to get the files and folders at the same time?

Off I went to Bingle. Luckily, I found a post on SharePoint StackExchange pretty quickly from jkr asking the same thing: Get all Files and Folders in one call. Vadim Gremyachev replied with the trick.

_api/Web/GetFolderByServerRelativeUrl('folderRelativeUrl')?$expand=Folders,Files

With this one call, we can get the info about the file and the folders together in one complex object.

Figure 1: Complex object returned from _api/Web/GetFileByServerRelativeUrl()/$expand=Files,Folders

Figure 1: Complex object returned from _api/Web/GetFileByServerRelativeUrl()/$expand=Files,Folders

As I said, this endpoint is perfect for building something like a directory tree.

There’s not a lot of good documentation for this endpoint (surprise!). You can find some examples of calls on the MSDN page Files and folders REST API reference, but no examples of the results. If you download the SharePoint 2013 REST Syntax (wall posters) you get some more clues.

The Files result provides results like those shown in Figure 2. As far as I can tell, there’s no way to control what fields you get back, as using $select has no effect.

Figure 2: Complex object returned from _api/Web/GetFileByServerRelativeUrl()/Files

Figure 2: Complex object returned from _api/Web/GetFileByServerRelativeUrl()/Files

The Folders result provides results like those shown in Figure 3. As far as I can tell, there’s no way to control what fields you get back here either, as using $select has no effect.

Figure 3: Complex object returned from _api/Web/GetFileByServerRelativeUrl()/Folders

Figure 3: Complex object returned from _api/Web/GetFileByServerRelativeUrl()/Folders

Note that in the Folders results, there are also Files and Folders objects, so the idea of recursion is there, though the objects are deferred. Because each folder has a ServerRelativeUrl value, you can dig as deep as you need to.

If you know you only need to go a few layers deep, you can also do things like:

_api/Web/GetFolderByServerRelativeUrl('folderRelativeUrl')?

$expand=Folders,Folders/Folders,Folders/Folders/Folders

or

_api/Web/GetFolderByServerRelativeUrl('folderRelativeUrl')?

$expand=Files,Folders/Files,Folders/Folders/Files

Both of these calls will get you three folders deep, which may be enough for some things you might want to do. I could also see using a call like these latter ones to get a bit ahead of your user to reduce the “chatter” on the line. That would make your array processing on the client side a little more complex, but could be worth it.

With some spiffy recursion in your framework of choice, you can build some very nice user interfaces with data like this. But that’s for another post…

Uploading Attachments to SharePoint Lists Using REST

In a previous post, I walked through Uploading Attachments to SharePoint Lists Using SPServices. Well, in the “modern” world, I want to use REST whenever I can, especially with SharePoint Online.

Add an attachmentI ran into a challenge figuring out how to make an attachment upload work in an AngularJS project. There are dozens of blog posts and articles out there about uploading files to SharePoint, and some of them even mention attachments. But I found that not a single one of the worked for me. It was driving me nuts. I could upload a file, but it was corrupt when it got there. Images didn’t look like images, Excel couldn’t open XLSX files, etc.

I reached out to Julie Turner (@jfj1997) and asked for help, but as is often the case, when you’re not as immersed in something it’s tough to get the context right. She gave me some excellent pointers, but I ended up traversing some of the same unfruitful ground with her.

Finally I decided to break things down into the simplest example possible, much like I did in my earlier post with SOAP. I created a test page called UploadTEST with a Content Editor Web Part pointing to UploadText.html, below:

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.3/jquery.js"></script>

<input id="my-attachments" type="file" fileread="run.AttachmentData" fileinfo="run.AttachmentInfo" />

<script type="text/javascript" src="/sites/MySiteCollection/_catalogs/masterpage/_MyProject/js/UploadTEST.js"></script>

Like I said, I wanted to keep it simple. Here’s what I’m doing:

  • In line 1, I load a recent version of jQuery from cdnjs. On the theory that Angular just adds another layer of complexity, I wanted to try just jQuery, which is useful for its $.ajax function, among many other things.
  • In line 3, I’ve got an input field with type=”file”. In “modern” browsers, this gives us a familiar file picker.
  • In line 5, I’m loading my script file called UploadTest.js, below:
$(document).ready(function() {

 var ID = 1;
 var listname = "UploadTEST";

 $("#my-attachments").change(function() {

  var file = $(this)[0].files[0];

  var getFileBuffer = function(file) {

   var deferred = $.Deferred();
   var reader = new FileReader();

   reader.onload = function(e) {
    deferred.resolve(e.target.result);
   }

   reader.onerror = function(e) {
    deferred.reject(e.target.error);
   }

   reader.readAsArrayBuffer(file);

   return deferred.promise();
  };

  getFileBuffer(file).then(function(buffer) {

   $.ajax({
    url: _spPageContextInfo.webAbsoluteUrl +
     "/_api/web/lists/getbytitle('" + listname + "')/items(" + ID + ")/AttachmentFiles/add(FileName='" + file.name + "')",
    method: 'POST',
    data: buffer,
    processData: false,
    headers: {
     "Accept": "application/json; odata=verbose",
     "content-type": "application/json; odata=verbose",
     "X-RequestDigest": document.getElementById("__REQUESTDIGEST").value,
     "content-length": buffer.byteLength
    }
   });

  });

 });
});

Here’s how this works – and yes, it does work!

  • I’ve got everything wrapped in a $(document).ready() to ensure the page is fully loaded before my script runs.
  • Lines 3-4 just set up some variables I could use for testing to keep things simple.
  • In line 6, I bind to the change event for the input field. Whenever the user chooses a file in the dialog, this event will fire.
  • In line 8, I’m getting the information about the file selected from the input element.
  • Lines 10-26 is the same getFileBuffer function I used with SOAP; it’s where we use a FileReader to get the contents of the selected file into a buffer.
  • The function in lines 28-42 runs when the file contents have been fully read. The getFileBuffer function returns a promise, and that promise is resolved when the function has gotten all of the file contents. With a large file, this could take a little while, and by using a promise we avoid getting ahead of ourselves. Here we make the REST call that uploads the attachment.
    • The URL ends up looking something like: “/my/site/path/_api/web/lists/getbytitle(‘UploadTEST’)/items(1)/AttachmentFiles/add(FileName=’boo.txt’)”
    • The method is a POST because we’re writing data to SharePoint
    • The data parameter contains the “payload”, which is the buffer returned from the getFileBuffer function.
    • The headers basically tell the server what we’re up to:
      • Accept doesn’t really come into play here unless we get a result back, but it says “send me back json and be verbose”
      • content-type is similar, but tells the server what it is getting from us
      • X-RequestDigest is the magic string from the page that tells the server we are who it thinks we are
      • content-length tells the server how many bytes it should expect

This worked for me on a basic Custom List (UploadTEST). So now I knew it was possible to upload an attachment using REST.

I took my logic and shoved it back into my AngularJS page, and it still worked! However, when I switched from $.ajax to AngularJS’s $http, I was back to corrupt files again. I’m still not sure why that is the case, but since I’m loading jQuery for some other things, anyway, I’ve decided to stick with $.ajax for now instead. If anyone reading this has an idea why $http would cause a problem, I’d love to hear about it.

Once again, I’m hoping my beating my head against this saves some folks some time. Ideally there would be actual documentation for the REST endpoints that would explain how to do things. Unfortunately, if there is for this, I can’t find it. I’d also love to know if /add allows any other parameters, particularly whether I can overwrote the attachment if one of the same name is already there. Maybe another day.

Sympraxis Is Growing: A Huge Welcome to Julie Turner!

Sympraxis LogoWell, it’s official: Julie Turner (@jfj1997) will be joining me at Sympraxis in mid-May. This is very exciting news for me.

If you’ve been following me at all, you may know that I have my own little company called Sympraxis Consulting LLC. (One of the first things Julie is going to make me do is bring that Web site into the second decade of the twenty-first century!) I started it with my good friend Peter Sterpe back in 2008. Starting a business at the end of 2008 was – well – let’s call it bad timing. Pete and I had a slow go of it for a while, and he left to take a real job. I was solo for about 6 years until Pete rejoined me last year. A few months ago Pete got the opportunity to take a full time teaching role at Boston College, and I couldn’t be happier for him.

I’ve never had grand plans for Sympraxis. I wanted to go out on my own mainly to see if I could pull it off. I also was tired of “working for the man”; anyone who knows me understands that office politics and bureaucracy are not my thing – I just want to get stuff done. I was extremely lucky to have a little idea that became SPServices and that little library has opened many doors for me. It seems like it’s worked: eight years later I still love what I’m doing and I’m able to make money doing it.

Julie is someone I’ve heard about for years, mainly through my friends who worked together at Knowledge Management Associates (KMA). (As is so often the case in this busy world, KMA is no longer extent as a standalone entity, having been acquired by Sentri, and then by Polycom.) The info I heard about Julie was universally positive over the years. When people like Sadie Van Buren (@sadalit), Chris McNulty (@cmcnulty2000), and Mike Gilronan (@mikegil) say good things about someone, it means a lot.

There’s a strong synergy between what I love to do in the SharePoint space and what Julie loves to do. We both do a lot of client side development, we both are passionate about implementing solutions that work extremely well with an excellent user experience, we both believe in the promise of effective knowledge management driving organization performance improvement, and the list goes on and on. We don’t believe in technology just for the sake of technology; it has to solve specific business needs.

I’ve been extremely impressed by the work that Julie has done with Bob German (@bob1german) on their Widget Wrangler (WW), which is now a part of the Office Development Patterns and Practices repo (OfficeDev/PnPSamples / ww) on GitHub. The approach that Bob and Julie have taken in the WW is incredibly well-aligned with the approaches I’ve been advocating for development on SharePoint for years. Without deploying any server-side code, we can build extremely robust solutions on top of SharePoint, and the thinking Bob and Julie have done with the WW takes it even further than I’ve been able to go as a solo artist.

I expect to learn a *lot* from Julie – and I’m hoping she can learn some things from me. Julie brings server side development skills I’ve never even tried to master, and those skills complement the client side work extremely well. Need a new REST service? Julie can do that. Need an AngularJS app done fast? Julie can do that, too. In my mind, Julie is technically ambidextrous: she’s equally comfortable writing code on the server and the client. She has a reputation as being extremely fast and good. Julie is also funny, smart as a whip, and believes in a good work/life balance. Having Julie aboard will greatly expand Sympraxis’ capacity to do more of what I’ve been doing all along, and bring her highly complementary capabilities into the mix. Who could want more?

Running a small business like Sympraxis is definitely a family affair. We discussed Julie joining Sympraxis in my kitchen after she had met my wife Melanie and son Calder. I look forward to meeting Julie’s husband Ken and her awesome son Evan. Some people say that being an entrepreneur is all about sacrifice, but it doesn’t have to be. It’s going to be great to have Julie join the Sympraxis family.

Julie Turner with her husband Ken and son Evan

Looping Through Content in a SharePoint 2013 Site Workflow – Part 4 – Get Authorization Info

This entry is part 4 of 4 in the series Looping Through Content in a SharePoint 2013 Site Workflow

In this installment, we’ll focus on the Get Authorization info step. We need to do this step first so that we’re set up to write data later in the workflow.

Get Authorization Info

Remember that we were actually trying to create a workflow? If you’ve gotten this far, I’ll forgive you if you’ve already forgotten what the point of the exercise is! But planning is important.

If you got ahead of yourself by creating the workflow first (like I did), you’ll need to shut down SharePoint Designer and reopen it so that it will notice that you’ve enabled all of the stuff above.

Now that we have the heavy cabling in place, we need to build the workflow so that it has the permissions it needs. For this, we need to make the REST calls into other sites/subsites in an App Step. (It’s App Step in SharePoint Designer, and since SharePoint Designer is at a dead end, it may always be, never becoming an Add-In Step.)

App Step

What we will be doing is wrapping any REST calls that reach into other Webs (sites) in an App Step. Once you’ve done this, you’ll get this prompt when you publish the workflow. I’d call this the “Are you really, really, really sure?” prompt. You’ll see this every time you publish the workflow from now on – pretty much (it seems sort of sporadic).

App Step Warning

REST Considerations

There’s a real chicken and egg issue with using REST calls in SharePoint Designer workflows. If you haven’t made REST calls before – outside SPD workflows – the REST calls themselves will be like black magic to you. Even if you have done REST calls before, the way it works in SPD workflows is pretty clunky. So what should you learn about?

Well, you can follow a cookbook approach like in this series of posts as well as the references I provide, or you can go whole hog and learn how it really works. It’s really up to you. If you think you’ll want to make REST calls in other contexts, learning how to do it in the context of an SPD workflow is probably a bad way to go about it. If you learn about it in this context, it may make less sense outside SPD workflows. Your call.

Set Up the App Step

When you first create your Site Workflow, you’ll have a Stage named Stage 1. As with most Microsoft tools, SharePoint Designer gives your Stage an arbitrary name; give it one that makes sense to you based on what it actually does. Mine is called Get Auth.

Set up workflow skeleton

As shown on the ribbon above, add an App Step to your workflow.

Set Up for Authorization in the Workflow

When you make only GET REST calls in a workflow (basically translating to read-only), you won’t need this step, but I set it up because I know I will be doing POSTs (making changes to items) down the road. The only goal of this step is to get the form digest value we’ll need to make those updates later.

The actual REST call looks something like this, which is pretty simple:

$.ajax({
    url: _spPageContextInfo.siteServerRelativeUrl +
        "/_api/contextinfo",
    method: "POST",
    contentType: "application/json;odata=verbose",
    headers: {
        "Accept": "application/json;odata=verbose"
    }
});

It’s a POST because it has to be, even though we’re simply requesting data. It’s a little simpler when we’re issuing the request like we are above. I can paste this code into a browser console (assuming jQuery is already loaded) and I get back a result that looks something like this:

{
    "d": {
        "GetContextWebInformation": {
            "__metadata": { "type": "SP.ContextWebInformation" },
            "FormDigestTimeoutSeconds": 1800,
            "FormDigestValue": "0x4FCED17AFEC66558CE8F780C0B30C0C289A2D58AF426746750288C3705FDFE822D2E0DA7E386E0E9A6E9F66FFC1DE45A7013EECC946AF56A8F75756EDE319C62,12 Apr 2016 17:19:33 -0000",
            "LibraryVersion": "16.0.5131.1207",
            "SiteFullUrl": "https://hostname.sharepoint.com",
            "SupportedSchemaVersions": {
                "__metadata": { "type": "Collection(Edm.String)" },
                "results": ["14.0.0.0", "15.0.0.0"]
            },
            "WebFullUrl": "https://hostname.sharepoint.com"
        }
    }
}

The only thing we really care about for this workflow is the value for FormDigestValue. That’s sort of like the magic key that we can use when we want to make updates in the workflow later.

To do this in the workflow, it takes a bit more setup. I’m hoping that seeing what the REST call looks like above will help make this part of the setup make a little more sense.

Get Auth App Step

Build the Dictionary

Every REST call you make in your workflow will need a dictionary (think of it as a JSON object) which contains some information about what form the request takes and how you’d like the information back.

To build a dictionary, we use the Build Dictionary action. A dictionary in a SharePoint Designer workflow is just a set of name/value pairs wrapped up in a package, sort of like the headers values in the REST call above.

In this case, you should add three name/value pairs to your dictionary:

Accept: application/json;odata=verbose
Content-Type: application/json;odata=verbose
Content-length: 255 

Create Dictionary
Give your dictionary a name; here I’ve named mine requestHeaders.

Set up the Web Service (REST) Call

Next, we use the Call HTTP web service action to set up the actual call to the SharePoint server.

The web service URL should be the path to the site where you want to make the call, followed by /_api/contextinfo. (We’re asking the server to send us some information about the current context; the form digest is a part of that context.)

The HTTP method has to be HTTP POST, as I mentioned above.

Call HTTP web service

This action is the one that actually makes the call to the server.

Parse the Results

Next we need to process the data we get back from the server. We’ve seen above what it looks like, and we have to use the tools available in Sharepoint Designer to parse it out.

The data that comes back from the call is also considered a dictionary, so we use the Get an Item from a Dictionary action.

Get dictionary value

We need to specify three things: the value we’d like to retrieve in XPath notation, the dictionary we’d like to retrieve it from, and where we’d like to store the result.

First, set the “item by name or path” to d/GetContextWebInformation/FormDigestValue. If you look at the data above, you’ll see that this is the path to the value we want.

String builder

Next, set the dictionary name to the name you used in the first step; mine is requestHeaders.

Finally, choose a variable to receive the value. I’m using digestValue. We’ll be able to use this value later in the workflow because we’ve saved it into a variable.

Log the Results

The last above is where I do a little logging to make sure that this step worked. Using the Log to History List action, I emit the responseCode from the request and the value of digestValue variable. The responseCode ought to be “OK” if all went well, and I should see the long string value for the digestValue.

Log results

Set Up the Transition

At the bottom of the stage we’ve been building, there’s a transition option, which is basically “What next?”. Set it to End of Workflow for now.

Publish and Test

This is a good point to save your workflow and test it. No, it doesn’t do anything of real value yet, but building it up step-by-step is the right way to go. If you see good results in the Workflow History Log, then you can feel comfortable moving on to the next step in the workflow.

You should see something like this:

Logging data

In the next installment in the series, I’ll show you how to set up the next step: Get Webs.