Feeds:
Posts
Comments

Archive for the ‘SharePoint Online’ Category

As SharePoint Developers, we are often challenged with requests to sort list item data seemingly arbitrarily. In these cases, none of the field data can be used to sort ascending or descending and get the results we are seeking. Often, we end up creating a Number column and setting a value so we can sort on that value. The trouble here is that you must manually update all of the other items. This can be painful if you’re trying to move an item with Order = 50 to Order = 1: You must change the Order value on 49 other items!

Links Lists have a handy link in the Items menu labelled “Change Item Order.” The page this link takes you to allows you to change the order of items in the Links List just like you would change the order of fields in a list view. SharePoint then stores the numerical sort order in a hidden field on the list which the Links Lists view uses with an orderBy.

So, excellent, there is something out of box to do what we want to do. Hooray! Now, how do we make use of it? Turns out, if you add a column to your custom list that is identical to the hidden column used in the Links List, you can take advantage of the “Change Item Order” page for your custom list! Here’s how I did it:

Using SharePoint REST API, I discovered the SchemaXml property for the hidden Order field in the Links List is:

<Field ID=\"{ca4addac-796f-4b23-b093-d2a3f65c0774}\" ColName=\"tp_ItemOrder\" RowOrdinal=\"0\" Name=\"Order\" DisplayName=\"Order\" Type=\"Number\" Hidden=\"TRUE\" SourceID=\"http://schemas.microsoft.com/sharepoint/v3\" StaticName=\"Order\" FromBaseType=\"TRUE\" />

I then simply generated a POST using the REST API once again to create a field on my custom list using the SchemaXml above. You can, of course, use whatever app you wish to do the POST. My favorite is Postman. Here’s how I setup the request to send:

  1. POST to [your site URL]/_api/web/lists/getbytitle(‘[title of your custom list]‘)/fields/createfieldasxml
  2. Headers:
    1. Accept: application/json
    2. Content-Type: application/json
    3. X-RequestDigest: [a current request digest]
  3. Body:
    {
        "parameters": {
           "__metadata": {
              "type": "SP.XmlSchemaFieldCreationInformation"
           },
           "SchemaXml": "&lt;Field ID=\"{ca4addac-796f-4b23-b093-d2a3f65c0774}\" ColName=\"tp_ItemOrder\" RowOrdinal=\"0\" Name=\"Order\" DisplayName=\"Order\" Type=\"Number\" Hidden=\"TRUE\" SourceID=\"http://schemas.microsoft.com/sharepoint/v3\" StaticName=\"Order\" FromBaseType=\"TRUE\" /&gt;"
        }
    }

This adds a hidden Order field to your custom list. The only thing left to do is access the “Change Item Order” page for your list and start setting item order. Here’s the URL:

[your site URL]/_layouts/15/Reorder.aspx?List=[your list GUID]

If you want to get the ordering data for your client-side app, here’s an important tip: The SharePoint REST API does not return the Order field when you simply execute a call to _api/web/Lists…/items. If you add Order to a $select option—like _api/web/Lists…/items?$select=Order—the item ordering set by the “Change Item Order” page will be returned.

Happy ordering! Let me know below if you find any issues doing this.

Update/Note, 11/7/2017: The “Change Item Order” page only supports up to 100 items at a time. It pages items in groups of 100. Thus, it gets a little tricky to move an item around when you’re dealing with hundreds of items or more.

Advertisements

Read Full Post »

We have several SharePoint farms in our control spanning the range of SP2010 to SP2013 to SharePoint Online (SPO). When it comes to providing a unified view of all of the site collections usage data to their owners, it can be a slight challenge. To meet those needs, I created some SharePoint timer jobs last year that iterate through all of the site collections and–using CSOM–store their reporting information (such as URL, title, owner, hits, unique users, and other classification information) in one list. From that list, we generate reports for the site owners and anyone else in need of the information.

When it came time to add SharePoint Online into the mix, I ran into a slight problem: How to get usage hits and unique users? The data is in there. You can find it in an Excel workbook under Site Settings > Site Collection Administration - Popularity and Search Reports > Usage. However, none of the APIs would return me anything but big fat zeroes!

I’ve asked around on several of the Microsoft social channels (such as TechNet–“Client UsageInfo ‘implementation specific period’“–and no less than three Yammer groups), but have yet to get any answers (guess I’m not very socially noticeable on this topic–we’ll see how this post does). As a last resort, I turned to teaching my timer job how to behave like a person on a browser. Let me show you what I mean…

First, let’s start with the timer job itself. This will be quick. I’m not going to cover much here because it is well documented in the OfficeDev PnP Solution: Core.TimerJobs.Samples. If you need help creating your timer job, look at the samples.

Now, let’s get to the meat. As I stated in my TechNet post, I’ve resorted to using a POST WebRequest to pull down the Excel document that contains the hits and unique users information (Site Settings > Popularity and Search Reports > Usage) and DocumentFormat.OpenXml to read it. I’m not very fond of this solution, though, because the ASPX pages don’t support OAuth and, thus, I can’t use app permissions to do it. I’d much rather be using an API than pretending to be a user with a browser but, as I said, the APIs always return zero (0) and I’ve not found any other alternative.

“So, how do I programmatically retrieve the usage data from SharePoint Online?” you ask? Below is a walkthrough to show how I did it in Visual Studio 2013. In this walkthrough, I’m going to “put the cart before the horse.” In other words, I’m going to build up the supporting methods before I walk you through building the method that uses them.

  1. Add a reference to .NET Framework 4.0’s WindowsBase assembly.
  2. Add the following NuGet packages:
    1. App for SharePoint Web Toolkit (for SharePoint Online)
    2. OfficeDevPnPCore16 (this will also install Microsoft.AspNet.WebApi.Client, Microsoft.AspNet.WebApi.Core, Microsoft.SharePointOnline.CSOM, and Newtonsoft.Json)
    3. DocumentFormat.OpenXml
  3. Add the following using statements:
    1. using System.Net
    2. using System.Security
    3. using Microsoft.SharePoint.Client
    4. using DocumentFormat.OpenXml.Packaging
    5. using DocumentFormat.OpenXml.Spreadsheet
  4. Since OAuth is not supported, we will need to get SharepointOnlineCredentials. This method takes a password in the form of a SecureString. In this walkthrough, I use a simple getter method called MyPassword with my password hard-coded as a string. You, of course, will want to replace or modify this method with something a bit more secure.
    unsafe private SecureString MyPassword
    {
      get
      {
        char[] chars = "myPassword".ToCharArray();
        SecureString myPassword;
        fixed (char* pChars = chars)
        {
          myPassword = new SecureString(pChars, chars.Length);
        }
        return myPassword;
      }
    }

    Note that you will have to check Allow unsafe code in the project’s build properties in order to compile with this method.

  5. The next two methods, GetCellValue and GetCellValues<T> are used to read the Excel spreadsheet we get back from SharePoint. The bulk of this code came from a Microsoft sample. I’ve modified it to reduce the overhead involved in opening the Stream object so we don’t have to do it for every cell we want to read.
    /// &lt;summary&gt;
    /// Retrieve the value of a cell, given a file stream, sheet name, and address name.
    /// &lt;/summary&gt;
    private string GetCellValue(System.IO.Stream stream, string sheetName, string addressName)
    {
      string value = null;
    
      // Open the spreadsheet document for read-only access.
      using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
      {
        // Retrieve a reference to the workbook part.
        WorkbookPart wbPart = document.WorkbookPart;
    
        // Find the sheet with the supplied name, and then use that 
        // Sheet object to retrieve a reference to the first worksheet.
        Sheet theSheet = wbPart.Workbook.Descendants&lt;Sheet&gt;().Where(s =&gt; s.Name == sheetName).FirstOrDefault();
    
        // Throw an exception if there is no sheet.
        if (theSheet == null)
        {
          throw new ArgumentException("sheetName");
        }
    
        // Retrieve a reference to the worksheet part.
        WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    
        // Use its Worksheet property to get a reference to the cell 
        // whose address matches the address you supplied.
        Cell theCell = wsPart.Worksheet.Descendants&lt;Cell&gt;().Where(c =&gt; c.CellReference == addressName).FirstOrDefault();
    
        // If the cell does not exist, return an empty string.
        if (theCell != null)
        {
          value = theCell.InnerText;
    
          // If the cell represents an integer number, you are done. 
          // For dates, this code returns the serialized value that 
          // represents the date. The code handles strings and 
          // Booleans individually. For shared strings, the code 
          // looks up the corresponding value in the shared string 
          // table. For Booleans, the code converts the value into 
          // the words TRUE or FALSE.
          if (theCell.DataType != null)
          {
            switch (theCell.DataType.Value)
            {
              case CellValues.SharedString:
    
                // For shared strings, look up the value in the
                // shared strings table.
                var stringTable = wbPart.GetPartsOfType&lt;SharedStringTablePart&gt;().FirstOrDefault();
    
                // If the shared string table is missing, something 
                // is wrong. Return the index that is in
                // the cell. Otherwise, look up the correct text in 
                // the table.
                if (stringTable != null)
                {
                  value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                }
                break;
    
              case CellValues.Boolean:
                switch (value)
                {
                  case "0":
                    value = "FALSE";
                    break;
                  default:
                    value = "TRUE";
                    break;
                }
                break;
            }
          }
        }
      }
      return value;
    }
    
    private List&lt;T&gt; GetCellValues&lt;T&gt;(System.IO.Stream stream, string sheetName, string[] addressNames)
    {
      var vals = new List&lt;T&gt;();
    
      // Open the spreadsheet document for read-only access.
      using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
      {
        // Retrieve a reference to the workbook part.
        WorkbookPart wbPart = document.WorkbookPart;
    
        // Find the sheet with the supplied name, and then use that 
        // Sheet object to retrieve a reference to the first worksheet.
        Sheet theSheet = wbPart.Workbook.Descendants&lt;Sheet&gt;().Where(s =&gt; s.Name == sheetName).FirstOrDefault();
    
        // Throw an exception if there is no sheet.
        if (theSheet == null)
        {
          throw new ArgumentException("sheetName");
        }
    
        // Retrieve a reference to the worksheet part.
        WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    
        foreach (string addressName in addressNames)
        {
          // Use its Worksheet property to get a reference to the cell 
          // whose address matches the address you supplied.
          Cell theCell = wsPart.Worksheet.Descendants&lt;Cell&gt;().Where(c =&gt; c.CellReference == addressName).FirstOrDefault();
    
          // If the cell does not exist, return an empty string.
          if (theCell != null)
          {
            var value = (T)Convert.ChangeType(theCell.InnerText, typeof(T));
            vals.Add(value);
          }
        }
      }
      return vals;
    }
    
  6. Now comes the method which drives it all: GetSPOUsageStats. This method takes a site collection URL and two references which it will populate: one for hits and one for unique users.I give specific cell references and do some math in lines 202 through 212. These cells refer to the last three full months of data. In my case, I want the sum of the last three full months of hits and an average daily usage for the last three full months (you may note that an individual unique user is probably being counted up to three times since there is no way to filter this individual as one visit over the sum of the three months). I never use the current month’s data because the time period it represents varies in length. You will likely want to modify lines 202 through 212 to suite your needs.
    private void GetSPOUsageStats(string siteUrl, ref long hitsCnt, ref double userAvg)
    {
      Uri siteUri = new Uri(siteUrl);
      ClientContext clientContext = new ClientContext(siteUri);
    
      var creds = new SharePointOnlineCredentials("me@mytenant.onmicrosoft.com", MyPassword);
      var authCookie = creds.GetAuthenticationCookie(siteUri);
    
      string postData = "__EVENTTARGET=__Page&__EVENTARGUMENT=ReportId%3D6bbf6e1c-d79a-45da-9ba0-d0c3332bf6e2";
      byte[] byteArray = Encoding.UTF8.GetBytes(postData);
    
      HttpWebRequest webRequest = HttpWebRequest.CreateHttp(siteUrl + "/_layouts/15/Reporting.aspx?Category=AnalyticsSiteCollection");
      webRequest.Method = "POST";
      webRequest.Accept = "text/html, application/xhtml+xml, */*";
      webRequest.ContentType = "application/x-www-form-urlencoded";
      webRequest.ContentLength = byteArray.Length;
      webRequest.CookieContainer = new CookieContainer();
      webRequest.CookieContainer.Add(new Cookie("SPOIDCRL",
            authCookie.TrimStart("SPOIDCRL=".ToCharArray()), //This is to remove the prefix from the cookie's value
            String.Empty,
            siteUri.Authority));
    
      using (System.IO.Stream dataStream = webRequest.GetRequestStream())
      {
        // Write the data to the request stream.
        dataStream.Write(byteArray, 0, byteArray.Length);
        // Close the Stream object.
        dataStream.Close();
        WebResponse webResponse = null;
        try
        {
          webResponse = webRequest.GetResponse();
        }
        catch (Exception ex)
        {
          Console.WriteLine("----------------------------------");
          Console.WriteLine("ERROR processing GetSPOUsageStats for {0}", siteUrl);
          Console.WriteLine();
          Console.WriteLine(ex.Message);
          Console.WriteLine();
          Console.WriteLine(ex.StackTrace);
          Console.WriteLine("----------------------------------");
        }
        if (null != webResponse)
        {
          using (webResponse)
          {
            byte[] buffer = new byte[1024];
            System.IO.MemoryStream memStream = null;
            try
            {
              memStream = new System.IO.MemoryStream();
              using (System.IO.Stream input = webResponse.GetResponseStream())
              {
                //total = input.Length;
    
                int size = input.Read(buffer, 0, buffer.Length);
                while (size &gt; 0)
                {
                  memStream.Write(buffer, 0, size);
                  //received += size;
    
                  size = input.Read(buffer, 0, buffer.Length);
                }
                input.Flush();
              }
    
              var hits = GetCellValues&lt;long&gt;(memStream, "Views", new string[] { "B54", "B55", "B56" });
              hitsCnt = hits.Sum();
    
              var uniqueUsers = GetCellValues&lt;int&gt;(memStream, "Views", new string[] { "C54", "C55", "C56" });
              var months = GetCellValues&lt;double&gt;(memStream, "Views", new string[] { "A54", "A56" });
              var dateStart = new DateTime(1900, 1, 1).AddDays(months[0]);
              dateStart = new DateTime(dateStart.Year, dateStart.Month, 1);
              var dateEnd = new DateTime(1900, 1, 1).AddDays(months[1]);
              dateEnd = new DateTime(dateEnd.Year, dateEnd.Month + 1, 1);
              var days = (dateEnd - dateStart).Days;
              userAvg = Math.Round((double)uniqueUsers.Sum() / days, 4);
            }
            catch (Exception ex)
            {
              Console.WriteLine("----------------------------------");
              Console.WriteLine("ERROR reading GetSPOUsageStats for {0}", siteUrl);
              Console.WriteLine();
              Console.WriteLine(ex.Message);
              Console.WriteLine();
              Console.WriteLine(ex.StackTrace);
              Console.WriteLine("----------------------------------");
            }
            finally
            {
              if (null != memStream)
              {
                memStream.Flush();
                memStream.Close();
                memStream.Dispose();
              }
            }
          }
        }
      }
    }
    
  7. Now, all we have to do is call GetSPOUsageStats. In a console application, this looks something like this:
    static void Main(string[] args)
    {
      Program p = new Program();
      long hitsCnt = 0;
      double userAvg = 0;
      p.GetSPOUsageStats("https://mytenant.sharepoint.com/sites/testsite", ref hitsCnt, ref userAvg);
      Console.WriteLine("Last three full months hits: {0}", hitsCnt);
      Console.WriteLine("Average unique users per day for last three full months: ", userAvg);
      Console.WriteLine("Press any key to quit.");
      Console.ReadKey();
    }
    

Download Program.cs DOCX for a source example used to generate this walkthrough. This Word document contains a copy of the Program.cs file.

Extra! Extra! Office 365 User Voice has helped shed some light on the dark side. Just a few days ago, the O365 Feedback Team told us, “We are working on many of these reports areas right now.” Check out the full thread here.

Read Full Post »

I’ve been working on Apps quite a lot this month and learning all sorts of things like: the difference between Office 365 D (Dedicated) and Office 365 MT (Multi-Tenant); how to setup Access Control Service (ACS); and when to use F5 in Visual Studio and when to package and deploy. It’s been a roller coaster of confusion and trial-and-error as I’ve attempted to wade through all of the How-To’s created over the last couple of years and merge them with all of the changes made available to the products during that same period. This week has brought a lot more clarity and understanding; in short, I think I’m finally starting to grasp the hang of it.

At the end of the day yesterday, I ran into an interesting phenomenon I thought would be worth sharing. Several of the documents I’ve read regarding app event receivers have said they are not supported from an on-premise install of SharePoint (see Where can I use app event receivers? in the MSDN App event receivers FAQ). Thus, online being my “only” option, I was banging my head trying to get event receivers to work in SharePoint Online for a provider-hosted app. No matter what I tried, my attached W3SVC wouldn’t stop on the breakpoint for the IRemoteEventService's ProcessEvent.

Having lost nearly all hope, I shelved my efforts and moved on to something else in the project. Upon installing the app to my on-premise SharePoint server, Visual Studio surprisingly hit my breakpoint in IRemoteEventService's ProcessEvent! Whoa! What’s going on here?

Well, it turns out that my host web (the IIS site hosting the service being called by the event) is on a Dev server. Naturally, it isn’t available publicly. As such, when SharePoint Online went to call the service, it couldn’t establish a connection. Of course, our on-premise SharePoint can connect to the Dev server because they’re on the same side of the firewall. So, what about all the documentation implying this can’t be done? Well, most of the documentation out there doesn’t consider that you can now run ACS from an on-premise server (many were written before ACS was a known option for on-premise).

Notice how the MSDN article doesn’t say it’s not possible; it only indicates that it’s not supported. I suppose such configuration as I have is just too new to support as of the last update of that article (December 31, 2014)–that more testing is needed. I for one am glad app event receivers for provider-hosted apps work when you have an accessible host web and other configurations (like ACS), either way!

Let me know if you encounter any issues using app event receivers with provider-hosted apps in an on-prem SharePoint app web and/or host web.

Read Full Post »