Working with Microsoft Graph to access o365 Planner

I’m currently working on a project where I have to migrate classic SharePoint tasks lists to Planner in O365. It cannot be done using CSOM, but there is a Planner REST API you can use.

This post does not contain new stuff, but I had to read trough hundreds of posts to get it all together. Therefore, I decided to write a post for whom it may be helpful.

I wrote a simple MVC-based Azure web site to do the work, nothing fancy, as it is just to migrate existing tasks lists. But you cannot communicate with your O365 tenant through Graph using SharePointOnlineCredentials. Therefore, I first registered an Azure App to have a ClientID/ClientSecret combination:

The Redirect URL is the URL to where I deployed my Azure web site (which is not described in this blog post)

Planner is part of O365 groups, so you have to give this Azure App at least read permissions to the O365 groups in your tenant. Via the Required permissions setting, I can give access to my application to as many APIs as available.

I selected the Graph API, and gave my application the permission to read from site collections and to read/write O365 groups:

I also had to click the Grant Permissions button. As an you then consent to an application’s delegated permissions on behalf of all the users in your tenant.

Read more Azure App registrations.

This gives me the permission to request data about all O365 groups in my tenant.

Now it is coding time 🙂

First I need to  get an access token based on the client ID and client secret of the Azure App I just registered:

private async Task<string> GetAccessToken(string resourceId)
{
    try
    {
        var authority = ConfigurationManager.AppSettings["ida:AuthorizationUri"] + ConfigurationManager.AppSettings["ida:TenantId"];
        var clientCredential = new ClientCredential(ConfigurationManager.AppSettings["ida:ClientId"],
        ConfigurationManager.AppSettings["ida:ClientSecret"]);

        AuthenticationContext ac = new AuthenticationContext(authority);
        AuthenticationResult result = await ac.AcquireTokenAsync(resourceId, clientCredential);

        return result.AccessToken;
     }
     catch (Exception ex)
     {
         // TODO: log the exception
         return null;
     }
 }

• The AuthorizationUri is https://login.windows.net/
• The TenantId is the ID of your O365 tenant. If you don’t know the ID, you can find it in your Azure portal: navigate to Azure Active Directory > Properties, and in the Map ID, you’ll find your tenant ID
• The ResourceId is the Graph URI: https://graph.microsoft.com

Tip: You can verify if you have a valid access token by going to https://jwt.io/

Next step is to retrieve the O365 group:

 string restUrl = string.Format("https://graph.microsoft.com/v1.0/kboske.com/groups?$filter=displayname eq '{0}'", groupName);
 Task<string> getGroupTask = Task<string>.Run(() => GetResponse(restUrl, accessToken));
 getGroupTask.Wait();

If you get a successful response, you will get something like this:

My GetResponse method is very basic, but I add it in here for completeness:

private async Task<string> GetResponse(string restUrl, string accessToken)
{
    string jsonresult = null;

    try
    {
       using (HttpClient client = new HttpClient())
       {
          var accept = "application/json";
          client.DefaultRequestHeaders.Add("Accept", accept);
          client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken);
          using (var response = await client.GetAsync(restUrl))
          {
              if (response.IsSuccessStatusCode)
              {
                  jsonresult = await response.Content.ReadAsStringAsync();
              }
              else
              {
                   throw new Exception("Error getting data: " + response.StatusCode.ToString());
              }
          }
       }
    }
    catch (Exception ex)
    {
       // TODO: handle the exception
    }
    return jsonresult;

}

Once I get the group Id, I can get to the plan, using the following REST call, no?

restUrl = string.Format("https://graph.microsoft.com/v1.0/recomatics.com/groups/{0}/planner/plans", groupId);

No, you can not!! You get a

But when I try to access Planner, I get a 401 – Unauthorized error instead.

It seems you can only access the plans by using user credentials because you can only access the plans to which you have permissions.

I changed the GetAccessToken method, in order to authenticate with my user credentials:

private async Task<string> GetAccessToken(string resourceId, string userName, string password)
{
    try
    {
        var authority = ConfigurationManager.AppSettings["ida:AuthorizationLoginUri"] + ConfigurationManager.AppSettings["ida:TenantId"];
        var authContext = new AuthenticationContext(authority);
        var credentials = new UserPasswordCredential(userName, password);
        var authResult = await authContext.AcquireTokenAsync(resourceId, ConfigurationManager.AppSettings["ida:ClientIdNativeClient"], credentials);

        // Get the result
        return authResult.AccessToken;
    }
    catch (Exception ex)
    {
       // TODO: handle the exception
       return;
    }
 }

But then I get a different error:

"AADSTS70002: The request body must contain the following parameter: 'client_secret or client_assertion'."

That’s because my Azure App only accepts client id / client secret combination because I registered it as a Web service. To be able to authenticate using my user credentials, I have to register an Azure App as a Native client:

When I now call the REST endpoint to get to the plan of my O365 group, I get a successful response:

Tip: try out your REST calls in the Graph explorer

Helpful posts:

• Integrating applications with Azure Active Directory
• Understand the Microsoft Graph with a Console App of Simon Jaeger

New version of the CAML Designer Released!

Andy Van Steenbergen and I finally released a new version of the CAML Designer; and this is a version we are very proud of!

This version of the CAML Designer now works on SharePoint 2007, SharePoint 2010, SharePoint 2013, SharePoint 2016 and SarePoint Online; AND it supports multi-factor authentication for SharePoint Online!

 

As you can see we changed the way you can connect to SharePoint: there are only 2 ways to connect:

• through the client-side object model (CSOM) for SharePoint versions 2010, 2013, 2016 and O365
• through the web services to be able to connect to SharePoint 2007

Yes, you read it correctly! The CAML Designer now works on ALL versions of SharePoint (except for SharePoint 2001)! We didn’t test the connection to SharePoint 2003, but I bet it will also work.

Another cool and much desired functionality we added to the CAML Designer, is the possibility to connect to SharePoint Online through multi-factor authentication.

The snippets are still generated for:

• raw CAML
• Server object model
• CSOM
• REST
• Web Services
• PowerShell

You can download the tool from the BIWUG site. There you will find a link to download the application.

A detailed explanation on how to use the tool can be found here.

Have fun with it and please, don’t hesitate to give us your feedback at camlfeedback@biwug.be !!

What if your sandbox solution contains a Visual WebPart?

As already mentioned in previous post, Microsoft decided to stop support for coded sandbox solutions on SharePoint Online. Developers will have to do the necessary effort to migrate the impacted sandbox solutions into solutions that comply with the recommended coding practices. You can find a lot of code samples and recommendations on the PnP site.

If your sandbox solutions contains a Visual WebPart, you will have to take action, because a Visual WebPart generates an assembly.In general, a Visual Web Part consists of the following:

• an .ascx file with UI elements like HTML controls and ASP.NET controls
• an .ascx.cs code behind file with some business logic that interacts with the UI and other data in the SharePoint site
• a .webpart file that contains the definition of the web part (i.e. title of the web part, description, full name of the assembly, etc)

You could try to replace all ASP.NET controls by HTML controls, and develop the business logic in JavaScript and JSOM or REST. Now you don’t need that DLL anymore, right? Now you can get rid of the the assembly by setting the Include Assembly in Package project property to false in Visual Studio, right?

Wrong! You can even try to remove the <%@Assembly> directives and the <%@Register> directives from the .ascx control:

Even if your Visual WebPart contains nothing else than HTML and JavaScript, that assembly is generated. This is because a Visual WebPart consists of an .ascx control. In SharePoint on premises environment, an .ascx control gets deployed to the /TEMPLATE/CONTROLTEMPLATES folder; but this is not the case with sandbox solutions. The .ascx control gets compiled into the DLL, meaning that all UI elements defined on the .ascx control are generated in code.

Trying to force things by removing the metadata element from the .webpart file, will result in an import error when the page loads:

In case of a complex Visual WebPart, your best option is to create a SharePoint Add-in Part:

• A SharePoint hosted add-in consisting of HTML, JavaScript and using JSOM or REST to communicate with the host SharePoint site
• A provider hosted add-in represented by an MVC application using CSOM to communicate with SharePoint

In case of less complex Visual WebPart, you could choose to replace it by embedding JavaScript

But all these approaches require you to go to each page that hosts the old Visual Web Part to replace it by the new development. This can be quite a challenge for large O365 tenants.

Another approach is to keep your .webpart file and change it to point to a JavaScript file. The advantage of this approach is that pages hosting your old Visual WebPart will now automatically host your new web part.

What are the steps you have to take to make this approach work?

1. Write the JavaScript to replace the logic of your existing Visual Web Part
2. Point the .webpart file to this JavaScript file
3. Deactivate and remove the old sandbox solution
4. Upload your JavaScript file into the Style Library of your site collection
5. Upload the new .webpart file into the WebPart Gallery

Following image shows how you can modify the .webpart file:

• Change the <metadata> tag to point to the ScriptEditorWebPart

    <metaData>
      <type name="Microsoft.SharePoint.WebPartPages.ScriptEditorWebPart, Microsoft.SharePoint, Version=16.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
      <importErrorMessage>Cannot import the migrated Demo Visual Web Part.</importErrorMessage>
    </metaData>

• Add a <property> element with name Content
• Add a reference to your JavaScript file
• Add a <div> element that will be the container of the UI that you have to develop in the JavaScript file

In your JavaScript you will have to retrieve this <div> element, to be able to embed the rest of the UI.

You can even automate steps 4 and 5. You can upload the JavaScript into the Style Library using the following CSOM code:

        private static void UploadFileToStyleLibrary(ClientContext ctx, string sourcePath, string styleLibFolder, string jsFileName)
        {
            // get the Style Library
            List styleLibrary = ctx.Site.RootWeb.GetList(ctx.Url + "/Style%20Library");
            ctx.Load(styleLibrary);
            ctx.ExecuteQuery();

            // upload the js file
            string fullSourcePath = sourcePath + jsFileName;
            if (!styleLibFolder.EndsWith("/")) { styleLibFolder += "/"; }
            string fullTargetPath = "/Style%20Library" + styleLibFolder + jsFileName;
            
            var fileCreationInfo = new FileCreationInformation
            {
                Content = System.IO.File.ReadAllBytes(fullSourcePath),
                Overwrite = true,
                Url = fullTargetPath
            };

            Microsoft.SharePoint.Client.File uploadFile = styleLibrary.RootFolder.Files.Add(fileCreationInfo);
            ctx.Load(uploadFile);
            ctx.ExecuteQuery();
        }

You can upload the new .webpart file into the WebPart Gallery using the following CSOM code:

        private static void UploadWebPartFile(ClientContext ctx, string sourcePath, string wepartFileName)
        {
            // get the WebPart Gallery
            List wpGallery = ctx.Site.RootWeb.GetCatalog((int)ListTemplateType.WebPartCatalog);
            ctx.Load(wpGallery);
            ctx.ExecuteQuery();

            // check if there is a file with the same name
            try
            {
                Microsoft.SharePoint.Client.File file = wpGallery.RootFolder.Files.GetByUrl(wepartFileName);
                ctx.Load(file);
                ctx.ExecuteQuery();

                // delete the file if it already exists
                file.DeleteObject();
                ctx.ExecuteQuery();
            }
            catch { }

            // updload the webpart file
            var fileCreationInfo = new FileCreationInformation
            {
                Content = System.IO.File.ReadAllBytes(sourcePath + wepartFileName),
                Overwrite = true,
                Url = wepartFileName
            };

            Microsoft.SharePoint.Client.File uploadFile = wpGallery.RootFolder.Files.Add(fileCreationInfo);
            ctx.Load(uploadFile);
            ctx.ExecuteQuery();
        }

If you prefer to work with a SharePoint Add-in to provision your files, you could follow the PnP approach for App Script Parts.

Good luck!

Do not use SPUtility.CreateNewDiscussions(SPListItemCollection, title) but use SPUtility.CreateNewDiscussions(SPList, title) method instead

Internally the method SPUtility.CreateNewDiscussions(SPListItemCollection, title) executes the SPListItemCollection.Add method to add a new discussion item to the list.

The SPListItemCollection.Add method is know for loading the whole list item collection into memory before adding a new Discussion list item. This can cause poor performance and even throttling for large lists.

It is recommended to use the SPUtility.CreateNewDiscussions(SPList, title) method instead, as this method internally executes the SPList.ItemAdd() method.

SPList.ItemAdd() does not load all list items in memory but executes a dummy CAML query to retrieve an empty SPListItemCollection to which a new item is added.

Office Dev PnP survey

The Office Dev PnP program has done a great job on building sample code and scenarios to guide SharePoint developers away from Full Trust code solutions to the add-in model. Through the past year the guidance started to evolve to other areas like ffice 365 APIs, Office Add-ins and unified APIs. PnP program has now evolved as open source community effort with both internal and external contributors.

This program is open source and driven by the community, with both internal and external contributors.

Every SharePoint developer gains from this initiative, and therefore I’m a big fan. and therefore I want to ask for your cooperation to fill out the survey http://aka.ms/officedevpnpsurvey.

Thanks for your cooperation!

 

Repair content type retention policies

Recently I was at a customer who implemented retention policies on content types. They have about 20 content types and a site collection with hundreds of sub sites with thousands of documents.

Becky Bertram wrote a nice detailed article on how to define retention policies for SharePoint 2010.

Problem description

When retention policies are applied on content types, there are 2 timer jobs that run (by default during the weekend):

• Information management policy timer job: by default, runs on friday 11 PM. The job goes through libraries that have policies applied. It calculates the expiration date for every item.
• Expiration policy timer job: by default, runs on saturday 11 PM. This job executes the action part of the retention policy. For example, if the action is to move expired documents to the recycle bin, expired documents will be deleted; if the action is set to move the documents to a send-to location, the expired documents will be moved.

My customer explained that when the  timer jobs ran for the first time, they ran for several hours and then just stopped running. As of then the jobs ran weekly, but with a lot of similar errors in the ULS logs:

"Error processing expiration of content in list <list name> in site <url to sp site>. 
 Error: Invalid field name. {b0227f1a-b179-4d45-855b-a18f03706bcb}".

"Error processing expiration of content in list <list name> in site <url to sp site>. 
 Error: Invalid field name. {acd16fdf-052f-40f7-bb7e-564c269c9fbc}".

From this post you can see that these guids refer to out of the box SharePoint fields:

Exempt from Policy	b0227f1a-b179-4d45-855b-a18f03706bcb	_dlc_Exempt
Expiration Date	acd16fdf-052f-40f7-bb7e-564c269c9fbc	_dlc_ExpireDate

When I tried to take a look at the Compliance Details of a document, I got the following error message: “column  ‘_dlc_exempt’ does not exist. It may have been deleted by another user”.

The Compliance Details menu option is only available on the context menu when retention policies are active for the specific document:

After investigation I found out that part of the sites had the retention policies correctly applied, and part of the sites had not.

In the rest of the article I will use the term “active retention policy”. It means that retention policies are defined on content types, and that these content types are in use on a document library.

Solution

I found a very interesting article on retention policies on the net to get me started, so kudos to Mike Berryman. I started my investigation to repair the site collection based on this article.

When a SharePoint web has active retention policies, it should have the following properties on its property bag:

• allowslistpolicy
• dlc_sitehaspolicy
• dlc_sitehasexpirationpolicy
• dlc_webhasexpirationpolicy

As some SharePoint sites were working correctly and some not, I corrected each SPWeb as follows:

$web.AllowUnsafeUpdates = $true
if (!($web.Properties.ContainsKey("allowslistpolicy")))
{
    $web.Properties.Add("allowslistpolicy", $true)
}
if (!($web.Properties.ContainsKey("dlc_sitehasexpirationpolicy")))
{
    $web.Properties.Add("dlc_sitehasexpirationpolicy", $true)
}
if (!($web.Properties.ContainsKey("dlc_sitehaspolicy")))
{
    $web.Properties.Add("dlc_sitehaspolicy", $true)
}
if (!($web.Properties.ContainsKey("dlc_webhasexpirationpolicy")))
{
    $web.Properties.Add("dlc_webhasexpirationpolicy", $true)
}
$web.Properties.Update()
$web.Update()

A library with active retention policies should have the following hidden fields:

• _dlc_Exempt
• _dlc_ExpireDateSaved
• _dlc_ExpireDate

I used the XML definition of these fields in order to create the missing fields:

$displayName_exempt = "Exempt from Policy"
$schemaXml_exempt = "<Field ID='{B0227F1A-B179-4D45-855B-A18F03706BCB}' 
    Name='_dlc_Exempt' StaticName='_dlc_Exempt' DisplayName='_dlc_Exempt' 
    SourceID='http://schemas.microsoft.com/sharepoint/v3' Group='Document and Record Management Columns' 
    Type='ExemptField' Indexed='FALSE' Hidden='TRUE' CanToggleHidden='TRUE' 
    ShowInNewForm='FALSE' ShowInEditForm='FALSE' ShowInFileDlg='FALSE' ShowInDisplayForm='FALSE' Required='FALSE' 
    Sealed='TRUE' ReadOnly='TRUE' OverwriteInChildScopes='TRUE'/>"
 
$displayName_expireDateSaved = "Original Expiration Date"
$schemaXml_expireDateSaved = "<Field ID='{74E6AE8A-0E3E-4DCB-BBFF-B5A016D74D64}' 
    Name='_dlc_ExpireDateSaved' StaticName='_dlc_ExpireDateSaved' DisplayName='_dlc_ExpireDateSaved' 
    SourceID='http://schemas.microsoft.com/sharepoint/v3' Group='Document and Record Management Columns' 
    Type='DateTime' Indexed='FALSE' Hidden='TRUE' CanToggleHidden='TRUE' ShowInNewForm='FALSE' 
    ShowInEditForm='FALSE' ShowInFileDlg='FALSE' ShowInDisplayForm='FALSE' Required='FALSE' 
    Sealed='TRUE' ReadOnly='TRUE' OverwriteInChildScopes='TRUE' />"

$displayName_expireDate = "Expiration Date"
$schemaXml_expireDate = "<Field ID='{ACD16FDF-052F-40F7-BB7E-564C269C9FBC}' 
   Name='_dlc_ExpireDate' StaticName='_dlc_ExpireDate' DisplayName='_dlc_ExpireDate' 
   SourceID='http://schemas.microsoft.com/sharepoint/v3' Group='Document and Record Management Columns' 
   Type='DateTime' Indexed='TRUE' Hidden='TRUE' CanToggleHidden='TRUE' 
   ShowInNewForm='FALSE' ShowInEditForm='FALSE' ShowInFileDlg='FALSE' ShowInDisplayForm='FALSE' Required='FALSE' 
   Sealed='TRUE' ReadOnly='TRUE' OverwriteInChildScopes='TRUE' />"

As some SharePoint libraries were working correctly and some not, I corrected each SPList as follows:

function RepairField($lib, $fieldInternalName $fieldDisplayName, 
       $schemaXml) { 
   $field = $null
   try
   {
      $field = $lib.Fields.GetFieldByInternalName($fieldInternalName)
   }
   catch {}
            
   if ($field -eq $null)
   {
       $lib.Fields.AddFieldAsXml($schemaXml)
       $field = $lib.Fields[$fieldInternalName]
       if ($field -ne $null)
       {
            $field.Title = $fieldDisplayName
            $field.Update()
       }
   }
}       
       
RepairField($lib, "_dlc_exempt", 
    $displayName_exempt, 
    $schemaXml_exempt)

RepairField($lib, "_dlc_ExpireDateSaved", 
    $displayName_expireDateSaved, 
    $schemaXml_expireDateSaved)

RepairField($lib, "_dlc_ExpireDate", 
    $displayName_expireDate, 
    $schemaXml_expireDate)

$lib.Update()

Each document on which a retention policy applies, has the following properties in its property bag:

• ItemRetentionFormula
• _dlc_ItemStageId
• _dlc_ItemScheduleId

I corrected the items as follows:

$files = $lib.RootFolder.Files
foreach ($file in $files)
{
    $item = $file.Item
            
    # add the property ItemRetentionFormula to the property bag
       write-host ("set the retention properties  for item ID " + $item["ID"])
    $item["Modified"] = $item["Created"]

    if ($item.Properties.ContainsKey("ItemRetentionFormula") -eq $false)
    {
        $item.Properties.Add("ItemRetentionFormula", $true)            
    }
    if ($item.Properties.ContainsKey("_dlc_ItemStageId") -eq $false)
    {
        $item.Properties.Add("_dlc_ItemStageId", $true)
    }
    if ($item.Properties.ContainsKey("_dlc_ItemScheduleId") -eq $false)
    {
        $item.Properties.Add("_dlc_ItemScheduleId", $true)
    }
                    
    # clear the item stage id
    $item.Properties["_dlc_ItemStageId"] = ""
    # set the schedule type
    $item.Properties["_dlc_ItemScheduleId"] = $null
    # set the property to contain the formula
    $item.Properties["ItemRetentionFormula"] = $null
    $file.Update()  
    $item.SystemUpdate()
}

For completeness, at the end I disposed the SPWeb object:

$web.AllowUnsafeUpdates = $false
$web.Dispose()

Remarks:

• I used the server-side object model.
• As I had to work on the production environment, I had to write the code in PowerShell, but you can also do it in C#.

 

 

 

 

SharePoint 2007 out of support

Last week I received the question “Is SharePoint 2007 still supported?”

My first reaction was “euh, what??” SharePoint 2010 mainstream support has just ended, so SharePoint 2007 is surely not supported anymore. But the customer referred to this page, saying that SharePoint 2007 was under support till October 10th 2017: https://support.microsoft.com/nl-be/lifecycle/search?sort=PN&alpha=SharePoint%20Server%202007&Filter=FilterNO

My colleague Peter Loete and I took a closer look to the support page, and this is the table from which the customer deducted that SharePoint 2007 was still under support:

So, no mainstream support anymore for SP2007 SP3: it ended on September 10th 2012. Extended support, at the other side, is still available till October 10th 2017.

What’s included in extended support? You can read more about it here.

SharePoint 2010 mainstream support ends on October 13th 2015

Although a lot of my customers still are on SharePoint 2010, mainstream support ends for all SharePoint 2010 installations with SP2 on October 13th 2015. If you’re not yet on SP2, you can still download it from here.

If you are a Microsoft Premier customer, you get extended support. You can read the details here.

You can find official information on the product lifecycle of SharePoint 2010 here.

Setting the value of a lookup field using CSOM

Today I had a hard time to find out how to set the value of a lookup field using CSOM. In most of the code samples I found on the internet, the LookupId is set hard coded, and this is not what I needed. Additionally, in some cases the lookup list was situated on the root web, and in another case I had to query a different field than the Title field. I ended up writing the following method:

public static FieldLookupValue GetLookupValue(ClientContext clientContext, string value, 
   string lookupListName, string lookupFieldName, string lookupFieldType, bool onRootWeb)
{
     List list = null;
     FieldLookupValue lookupValue = null;

     if (onRootWeb)
     {
          list = clientContext.Site.RootWeb.Lists.GetByTitle(listName);
     }
     else
     {
          list = clientContext.Web.Lists.GetByTitle(listName);
     }

     if (list != null)
     {
         CamlQuery camlQueryForItem = new CamlQuery();
         camlQueryForItem.ViewXml = string.Format(@"<View>
                  <Query>
                      <Where>
                         <Eq>
                             <FieldRef Name='{0}'/>
                             <Value Type='{1}'>{2}</Value>
                         </Eq>
                       </Where>
                   </Query>
            </View>", lookupFieldName, lookupFieldType, value);

          listItemCollection listItems = list.GetItems(camlQueryForItem);
          clientContext.Load(listItems, items => items.Include
                                            (listItem => listItem["ID"],
                                             listItem => listItem[lookupFieldName]));
          clientContext.ExecuteQuery();

          if (listItems != null)
          {
              ListItem item = listItems[0];
              lookupValue = new FieldLookupValue();
              lookupValue.LookupId = Int.Parse(item["ID"].ToString());
          }             
     }

     return lookupValue;
}

This method works on both SharePoint 2010 and 2013.

I hope this code snippet can help others.

Enjoy!

 

SharePoint Saturday

SharePoint Saturday 2014 is coming near and we all start to feel the vibes. April 26th is the big day! We just made the session agenda available online. Check it out here!