Webhook – Sentiment of comments given on a SharePoint page – Part 2

This is the continuation of a previous post called ‘Sentiment of comments given on a SharePoint page‘.

The scenario:

1. The sentiment of the comments of the page is analyzed and updated in the list (previous post)
2. A webhook is triggered by the change in the list and a notification is sent to an Azure Function to validate the endpoint and this function puts a message on a Service Bus Queue
3. Another Azure Function is triggered when a message is added to the queue and this function triggers back to SharePoint to get the changes

Webhook subscription

To add a webhook to a list a subscription is needed by sending a POST request to
/_api/web/lists(‘list guid’)/subscriptions
with the subscription object in the payload.

The subscription object consists of:

• resource – identifies the list the webhook is added for
• notificationUrl – location of the webhook url/Azure Function to send the notifications
• expirationDateTime – expiration date of the webhook
• clientState – string passed back to the client on all notifications, usable for eg tagging subscriptions

The subscription can be created by eg using Postman or SP Editor (Chrome) Extension.

SP Editor

The far most easy way to create a subscription is using the SP Editor extension.

Figure 1 – create a subscription using SP Editor

Select ‘Webhooks’ in the menu; select the appropriate list; set eventually clientstate and fill in the notification url.

Postman

To create a subscription using Postman a POST request has to be created to https://<sitecollectionurl>/_api/web/lists(‘<listguid>’)/subscriptions

With headers:
Accept: application/json
Content-Type: application/json
Authorization: <Bearer token>

And body:
{
“resource”: ” https://<sitecollectionurl>/_api/web/lists(‘<listguid>’)“,
“notificationUrl”: “publicly accessible url”,
“expirationDateTime”: “2018-12-01T15:00:00+00:00”
}

When developing and running Azure Functions locally in Visual Studio the url will be pointing to localhost. Such an url can’t be registered  as notificationUrl. A great solution is to use ngrok to use a forwarding https address as a service proxy for SharePoint to send the requests. Keep in mind that with a Free plan of ngrok the forwarding url changes at every restart.

Once the subscription is in place, SharePoint can and will call this custom webhook.

Validation

First SharePoint will validate the endpoint specified in the notificationUrl by sending a validation string to it. SharePoint expects a response (the validation string sent and HTTP 200 OK) within 5 seconds from the Azure Function. If this succeeds an HTTP 201 Created message is returned and the newly created subscription object with the ID of the new subscription and other properties. This ID can be used to delete or update the subscription. Subscriptions will expire after 180 days by default.

Once the validation has occurred this same function is used to be notified by SharePoint when a change has occurred in the list.

Service bus Queue

Once a change occurs a message will be put on the queue.
Notification messages do not pass any event information. The notification object consist of the following properties:

{“subscriptionId”:”<guid>”,
“clientState”:”optional string”,
“expirationDateTime”:”2018-11-15T15:50:28.4190000Z”,
“resource”:”<list guid>”,
“tenantId”:”<guid>”,
“siteUrl”:”<relative site url”,
“webId”:”<web guid>”}

Be aware this a way to be notified of a done change, -ed events (Added, Updated, CheckedOut and more), not -ing events

The code

public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get","post", Route = null)]HttpRequestMessage req, TraceWriter log)
{
  log.Info("A request is received");
  // parse query parameter
  string validationToken = req.GetQueryNameValuePairs()
  .FirstOrDefault(q => string.Compare(q.Key, "validationtoken", true) == 0)
  .Value;

  if (!string.IsNullOrEmpty(validationToken))
  {
    log.Info($"Validation token received: {validationToken}");

    //return the appropriate response
    var response = req.CreateResponse(HttpStatusCode.OK);
    response.Content = new StringContent(validationToken);
    return response;
  }

  log.Info("SharePoint triggered this webhook");

  var content = await req.Content.ReadAsStringAsync();
  log.Info($"Received following payload: {content}");

  //add message to Service Bus Queue
  var logMessage = string.Empty;
  var connectionString = ConfigurationManager.AppSettings["ServiceBusConnection"];
  var queueName = ConfigurationManager.AppSettings["QueueName"];

  if (string.IsNullOrEmpty(connectionString) || string.IsNullOrEmpty(queueName))
  {
    logMessage = "AppSettings not found.";
    log.Error(logMessage);
    return req.CreateErrorResponse(HttpStatusCode.NotFound, logMessage);
  }

  var client = QueueClient.CreateFromConnectionString(connectionString, queueName);
  BrokeredMessage msg = new BrokeredMessage(content);
  await client.SendAsync(msg);

  log.Info("Message added to queue");

  return req.CreateResponse(HttpStatusCode.OK);
}

Processing messages from the queue

This function is a Service Bus Triggered function. Once the first Azure Function puts a message on the queue, this second Azure Function is triggered and will process the message.

The function has to connect to SharePoint Online to get the changes that happened in the list based on the information in the message on the queue. Therefor an Azure AD app for app-only access is set up based on the instructions described  in the Microsoft documentation here. To authenticate against SharePoint the PnP Authentication manager is used.

To get all the changes in the list the ChangeQuery object can be used. The code below shows how to narrow down the changes to the

• change object Item
• change action Update
• changes occurred in the last minute or since the token stored in the property bag of the list

[FunctionName("ProcessMessage")]
public static void Run([ServiceBusTrigger("%QueueName%", AccessRights.Manage, Connection = "ServiceBusConnection")]string myQueueItem, TraceWriter log, ExecutionContext executionContext)
{
  log.Info($"C# ServiceBus queue trigger function processed message: {myQueueItem}");

  Notifications<NotificationModel> notifications = JsonConvert.DeserializeObject<Notifications<NotificationModel>>(myQueueItem);

  InitData initData = new InitData(executionContext.FunctionDirectory);
  if (!initData.isValid)
  {
    log.Error(initData.ToString(), initData.exception);
    throw new ArgumentException("InitData is invalid", initData.exception);
  }

  var storedChangeToken = "storedChangeToken";

  using (ClientContext ctx = ClientContextHelper.GetClientContext(log, initData))
  {
    foreach (var item in notifications.Value)
    {
      var listId = item.Resource;
      var webId = item.WebId;

      Web web = ctx.Site.OpenWebById(new Guid(webId));
      List list = web.Lists.GetById(new Guid(listId));
      ctx.Load(list);
      ctx.ExecuteQueryRetry();

      ChangeQuery query = new ChangeQuery(false, false);
      ChangeToken currentToken = list.CurrentChangeToken;
      ChangeToken lastChangeToken = new ChangeToken();
      // get changes of the last minute
      lastChangeToken.StringValue = string.Format("1;3;{0};{1};-1", listId, DateTime.Now.AddMinutes(-1).ToUniversalTime().Ticks.ToString());
      // use the token of the propertybag of the list
      var storedToken = list.GetPropertyBagValueString(storedChangeToken, string.Empty);
      if (!string.IsNullOrEmpty(storedToken))
      {
        lastChangeToken.StringValue = storedToken;
      }

      query.ChangeTokenStart = lastChangeToken;
      query.Update = true;
      query.Item = true;

      var changes = list.GetChanges(query);
      ctx.Load(changes);
      ctx.ExecuteQueryRetry();

      foreach (var change in changes)
      {
        if (change is ChangeItem)
        {
          try
          {
            ChangeItem changeItem = (ChangeItem)change;
            ListItem listItem = list.GetItemById(changeItem.ItemId);
            ctx.Load(listItem);
            ctx.ExecuteQueryRetry();

            var score = listItem["RawSentiment"] == null ? "0" : listItem["RawSentiment"].ToString();
            var rawSentiment = float.Parse(score);
            log.Info($"Raw sentiment of listitem {changeItem.ItemId} is {rawSentiment}");

            list.SetPropertyBagValue(storedChangeToken, currentToken.StringValue);
          }
          catch (Exception ex)
          {
            log.Error(ex.ToString());
          }
        }
      }
    }
  }
}

For now the value of the RawSentiment value of the list item is shown in the console window, ready for the next step which will be filled out in the next post.

Summary

To be notified when a change occurs a webhook url/REST endpoint can be registered by a subscription. Several possibilities are available to add a webhook in SharePoint, but a real ‘webhook management’ user interface is missing.
The endpoints can be easily hosted by Azure Functions.