zplume/Retrieving list item changes with the SharePoint Online Folder.GetListItemChanges REST API in Microsoft Flow.md

## Retrieving list item changes with the SharePoint Online Folder.GetListItemChanges REST API in Microsoft Flow.md

      
    Raw
  

              Retrieving list item changes with the SharePoint Online Folder.GetListItemChanges REST API in Microsoft Flow.md
            
          
    Retrieving list item changes with the SharePoint Online Folder.GetListItemChanges REST API in Microsoft Flow

About

This post demonstrates how to retrieve SharePoint list item changes in Microsoft Flow using the SharePoint - Send an HTTP request to SharePoint action and the SharePoint Online Folder.GetListItemChanges REST API.
Why

This is something I started looking at after reading this set of tweets by John Liu:
#FlowNinja still here!  OK need to talk about why this is important:
1. 12+ lookups prevent your SharePoint trigger working?  OK.
2. Want to handle batch changes per item?  Perform Group-By on change batch and emit latest event.
3. want to attach Flow to 500 libraries? OK https://t.co/TH8i8zoJER
— John LIU (@johnnliu) 2 June 2018
John's tweets demonstrate that it's possible (and relatively simple) to use Flow to register (and maintain) SharePoint list webhooks and also handle the corresponding events in a way that scales effortlessly.
To expand on some of John's points:
Duplicating a non-trivial Flow each time you want to apply it to a new list instance does not scale well - any change you make in the original Flow now has to be replicated in the Flow copies (see Don't Repeat Yourself).
While you could work around the above issue with the nested Flow pattern (e.g. several 'When a list item is created' list instance Flows call a single Http Trigger Flow which contains the business logic and actually does the work), this still doesn't scale well beyond a handful of lists.
What if you want to automatically handle events in new sites/libraries without additional Flows?
What if you want to handle events that aren't covered by existing Flow triggers, such as Rename, Move, Restore?
What if you want to process list events on a scheduled interval rather than as they happen?
What if you want to handle multiple item events (e.g. Add, Update, Update, Update, Delete) in one place and de-duplicate / throttle your event handling?
As John demonstrated, webhooks are an effective way to register an event handler which gets notified when something happens in a list, but the webhook handler needs to do some additional work to discover exactly what events took place in the list. This post explains how to retrieve that list of events.


List.GetChanges vs Folder.GetListItemChanges

At the time of writing, the MS SharePoint webhooks documentation points to the List.GetChanges API for retrieving a list of changes in your webhook handler. In my testing, this API did not return values for the following properties (they were blank):

Editor
EditorEmailHint
ServerRelativeUrl

The above are quite useful properties to have when processing a set of changes.
Luckily the Folder.GetListItemChanges API does return values for these properties, so this post focuses on that API.


Creating the Flow

Http Request Trigger


Create a blank Flow.
Select the When a HTTP request is received trigger and copy/paste the following JSON as the Request body JSON schema:

{
    "type": "object",
    "properties": {
        "siteUrl": {
            "type": "string"
        },
        "listId": {
            "type": "string"
        },
        "changeTokenPropertyName": {
            "type": "string"
        }
    }
}


Why use the HTTP request trigger?

Using the When a HTTP request is received trigger allows calling this Flow from other Flows (A.K.A. the 'nested Flow' pattern) and makes the Flow reusable.
For example: this Flow could be called from a scheduled Flow (if change processing should happen at designated intervals) or webhook handler Flow (if change processing should happen as soon as possible).


ChangeType enum


Add a Data Operations - Compose action, rename it to Set ChangeType and copy/paste the following Inputs value:

[
    "NoChange",
    "Add",
    "Update",
    "DeleteObject",
    "Rename",
    "MoveAway",
    "MoveInto",
    "Restore",
    "RoleAdd",
    "RoleDelete",
    "RoleUpdate",
    "AssignmentAdd",
    "AssignmentDelete",
    "MemberAdd",
    "MemberDelete",
    "SystemUpdate",
    "Navigation",
    "ScopeAdd",
    "ScopeDelete",
    "ListContentTypeAdd",
    "ListContentTypeDelete",
    "Dirty",
    "Activity"
]

The above values were taken from the SharePoint Online CSOM ChangeType enum documentation, but at the time of writing this appears to be unavailable (possibly related to documentation moving from MSDN to docs.microsoft.com).


Set Headers


Add a Data Operations - Compose action, rename to Set Headers and copy/paste the following JSON:

{
    "Accept": "application/json; odata=nometadata",
    "Content-Type": "application/json"
}
So far your Flow should look like something like this:


Change tokens

The GetListItemChanges API returns a change token with each change, which represents the date and time of the change. Including one of these change tokens with subsequent requests to the API allows you to retrieve only changes that happened since that change token, allowing you to skip changes that you have already processed.
In order to make use of this, each time the Flow runs it checks the list's root folder property bag for a previously saved change token to include in the GetListItemChanges request.


Get last change token


Add a SharePoint - Send an HTTP request to SharePoint action and rename to Get Change token start from property bag.


Configure as shown below with properties from the When a HTTP request is received and Set Headers actions:


Name
Value


Site Address
siteUrl


Method
GET


Uri
_api/web/lists('listId')/RootFolder/Properties?$select=changeTokenPropertyName


Headers
Output (Set Headers)


Set change token start


Add a Variables - Initialize variable action and rename to Set Change token start.
Set the Name property to ChangeTokenStart.
Set the Type property to Object.
Copy/paste the following JSON into the Value property:

{
    "StringValue": "@{body('Get_Change_token_start_from_property_bag')?[triggerBody()?['changeTokenPropertyName']]}"
}

The action should resemble the following image:


This ChangeTokenStart JSON object will be used in the following GetListItemChanges request.


Compose Get list item changes body


Add a Data Operations - Compose action and rename to Compose Get list changes body.


Copy/paste the following JSON as the Inputs value:
{
    "query": {
        "Add": true,
        "Update": true,
        "SystemUpdate": false,
        "DeleteObject": true,
        "Move": true,
        "Rename": true,
        "Restore": true,
        "Item": true,
        "File": true,
        "RecursiveAll": true,
        "ChangeTokenStart": @{if(empty(body('Get_Change_token_start_from_property_bag')), 'null', variables('ChangeTokenStart'))}
    }
}


The above JSON represents an instance of the ChangeQuery class.
See the MSDN ChangeQuery class reference for the full list of query properties.
If a change token was retrieved from the list root folder property bag in the previous actions, this is assigned to the ChangeTokenStart property. If not, the property value is set to null.
SystemUpdate shouldn't usually be used as an event trigger. SystemUpdate is often used by event handlers that need to update the item that triggered them without triggering additional events (as this would create an infinite loop). You may want to leave SystemUpdate set to true in the change query for debugging purposes and filter out SystemUpdate events in the calling Flow (as it's quite likely you would want to filter changes to the relevant event types in the calling Flow anyway) or you can set it to false here as shown above.
On a related note: Andrew Koltyakov has written a great blog on List Items System Update options in SharePoint Online if you're interested in triggering a SystemUpdate from Flow.
The Update change event occurs as a result of quite a few different actions, such as list item field updates, file content changes and other actions that are also covered by more specific change events - e.g. rename (as shown in the example at the end of this post).
RecursiveAll: true in combination with calling GetListItemChanges on the root folder of the list means that the query will return items from anywhere in the target list / library. You could set this to false and target a particular folder if you only wanted to retrieve changes at that scope.


Get list item changes


Add a SharePoint - Send an HTTP request to SharePoint action, rename to Get list changes and configure with the values shown below:


Name
Value


Site Address
siteUrl


Method
POST


Uri
_api/web/lists('listId')/RootFolder/GetListItemChanges?$Expand=RelativeTime&$top=1000


Headers
Output (Set Headers)


Body
Output (Compose Get list changes body)


The $top=1000 query string parameter means that up to 1000 changes will be returned. This is the default, but can be reduced if you want limit results to smaller batches or test paging over a set of results with a small number of changes.
If you're running this Flow on a schedule and/or expect more than 1000 changes each time the Flow runs, you'll want to adjust things so that the Get list changes action executes in a do until loop which updates the ChangeTokenStart variable (to the last change token) at the end of each iteration and loops until there are no more changes to process.


Your Get list changes action should look like this:


Select ID ChangeToken and ChangeType


Add a Data Operations - Select action and rename to Select ID ChangeToken and ChangeType.
Set the From property of the action to the following Expression value:

body('Get_list_changes')?['value']

The value property of the GetListItemChanges response contains the array of changes.


Switch the Map property to text mode by clicking the icon to the right of the Enter key and Enter value fields.
Set the Map property to the following Expression value:

setProperty(setProperty(item(), 'ChangeType', outputs('Set_ChangeType')[item()['ChangeType']]), 'ChangeToken', item()?['ChangeToken']?['StringValue'])

This expression replaces the ChangeType number value returned by the GetListItemChanges API with the corresponding (human readable) ChangeType enum string (e.g. Add or Update) and replaces the ChangeToken object with it's StringValue property value (unwrapping the value makes the list of changes a bit easier to read).


Your select action should look like this:


Handle no changes


This Flow needs to handle the scenario where there are no new changes to process (if nothing has happened since the change token that you pass to the GetListItemChanges API).


Add a condition and rename to If there are no new changes, return an empty array and exit the Flow.


Set the first condition value to the following expression:


length(body('Select_ID_ChangeToken_and_ChangeType'))


Set the comparison dropdown to is equal to and the second condition value to 0.


In the Yes branch, add a Request - Response action, rename to Response - empty array and configure as follows:


Name
Value


Status Code
200


Headers
Content-Type: application/json


Body
[]


Response Body JSON schema
See below.


Copy/paste the following JSON into the Response Body JSON schema property value:


{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "RelativeTime": {
                "type": "string"
            },
            "SiteId": {
                "type": "string"
            },
            "Time": {
                "type": "string"
            },
            "Editor": {
                "type": "string"
            },
            "EditorEmailHint": {
                "type": "string"
            },
            "ItemId": {
                "type": "integer"
            },
            "ListId": {
                "type": "string"
            },
            "ServerRelativeUrl": {
                "type": "string"
            },
            "SharedByUser": {},
            "SharedWithUsers": {},
            "UniqueId": {
                "type": "string"
            },
            "WebId": {
                "type": "string"
            },
            "ChangeType": {
                "type": "string"
            },
            "ChangeToken": {
                "type": "string"
            }
        },
        "required": [
            "RelativeTime",
            "SiteId",
            "Time",
            "Editor",
            "EditorEmailHint",
            "ItemId",
            "ListId",
            "ServerRelativeUrl",
            "UniqueId",
            "WebId",
            "ChangeType",
            "ChangeToken"
        ]
    }
}

Add a Terminate action and rename to Terminate - no changes.
Set the Status to Succeeded.
Your condition should look like the following image:


Parse last change


Add a Data Operations - Parse JSON action and rename to Parse last change details.
Set the Content property to the following JSON value:

{
    "lastChangeToken": "@{last(body('Select_ID_ChangeToken_and_ChangeType'))['ChangeToken']}",
    "siteId": "@{last(body('Select_ID_ChangeToken_and_ChangeType'))['SiteId']}",
    "webId": "@{last(body('Select_ID_ChangeToken_and_ChangeType'))['WebId']}"
}

This retrieves the ChangeToken, SiteId and WebId properties from the last change item returned in the GetListItemChanges request response.


Set the Schema property to the following JSON value:

{
    "type": "object",
    "properties": {
        "lastChangeToken": {
            "type": "string"
        },
        "siteId": {
            "type": "string"
        },
        "webId": {
            "type": "string"
        }
    }
}

Your Parse last change details action should resemble the following image:


Get folder ID


The list root folder's UniqueId property is required for updating the root folder's property bag (in the subsequent action).


Add a SharePoint - Send an HTTP request to SharePoint action and rename to Get folder ID


Update the action properties as listed below:


Name
Value


Site Address
siteUrl


Method
GET


Uri
_api/web/lists('listId')/RootFolder?$select=uniqueId


Headers
Output (Set Headers)


Body
(Empty)


Parse folder ID


Add a Data Operations - Parse JSON action and rename to Parse Get folder ID


Update the action properties as follows:


Name
Value


Content
Body (Get folder ID)


Schema
See below.


Set the action Schema property to the following JSON value:


{
    "type": "object",
    "properties": {
        "UniqueId": {
            "type": "string"
        }
    }
}

Set change token


There isn't currently a REST API for setting list folder property bag values. The best workaround I'm aware of is capturing the request that is sent to SharePoint by the CSOM API when setting a list folder property bag value (e.g. using PnP PowerShell and Telerik's Fiddler debugging proxy) and then replicating the captured request from Flow, with the relevant variables replaced.
Persisting the last change token retrieved from the GetListItemChanges API in the list root folder property bag means that subsequent requests to this Flow (with the same changeTokenPropertyName parameter) can continue from that change token (returning only new changes) rather than returning changes that have already been processed by the calling Flow.


Add a SharePoint - Send an HTTP request to SharePoint action and rename to Set Change token start property bag value.


Update the action properties as follows:


Name
Value


Site Address
siteUrl


Method
POST


Uri
_vti_bin/client.svc/ProcessQuery


Headers
Content-Type: text/xml


Body
See below.


Copy/paste the following XML into the action's Body property property:


<Request AddExpandoFieldTypeSuffix="true" SchemaVersion="15.0.0.0" LibraryVersion="16.0.0.0" ApplicationName="SharePoint PnP PowerShell Library" 
    xmlns="http://schemas.microsoft.com/sharepoint/clientquery/2009">
    <Actions>
        <Method Name="SetFieldValue" Id="42" ObjectPathId="37">
            <Parameters>
                <Parameter Type="String">@{triggerBody()?['changeTokenPropertyName']}</Parameter>
                <Parameter Type="String">@{body('Parse_last_change_details')?['lastChangeToken']}</Parameter>
            </Parameters>
        </Method>
        <Method Name="Update" Id="43" ObjectPathId="27" />
</Actions>
    <ObjectPaths>
        <Property Id="37" ParentId="27" Name="Properties" />
        <Identity Id="27" Name="ab8c6d9e-3059-5000-c7a7-1c96cb3c9e84|740c6a0b-85e2-48a0-a494-e0f1759d4aa7:site:@{body('Parse_last_change_details')?['siteId']}:web:@{body('Parse_last_change_details')?['webId']}:folder:@{body('Parse_Get_folder_ID')?['UniqueId']}" />
</ObjectPaths>
</Request>

Parse set change token response


Add a Data Operations - Parse JSON action.


Set the Content property to the Body output of the Set change token start property bag value action.
Copy/paste the following JSON into the Schema property:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "SchemaVersion": {
                "type": "string"
            },
            "LibraryVersion": {
                "type": "string"
            },
            "ErrorInfo": {
                "type": [
                    "object",
                    "null"
                ],
                "properties": {
                    "ErrorMessage": {
                        "type": "string"
                    },
                    "ErrorValue": {},
                    "TraceCorrelationId": {
                        "type": "string"
                    },
                    "ErrorCode": {
                        "type": "number"
                    },
                    "ErrorTypeName": {
                        "type": "string"
                    }
                }
            },
            "TraceCorrelationId": {
                "type": "string"
            }
        },
        "required": [
            "SchemaVersion",
            "LibraryVersion",
            "ErrorInfo",
            "TraceCorrelationId"
        ]
    }
}

The CSOM API that the previous action mimics does not return an HTTP error code if something goes wrong with the request, but returns the error details in a 'success' (HTTP 200) response.


Get error message


This action retrieves the error message from the Set Change token start property bag value request response, if there is one.


Add a Variables - Initialize variable action.


Update the properties of the actions as follows:


Name
Value


Name
Set property bag error


Type
String


Value
See below.


Update the Value property of the action to the following Expression:


body('Parse_Set_property_bag_value_response')[0].ErrorInfo?.ErrorMessage


Handle set change token response


The final step in the Flow is to return the retrieved list item changes, but if there was an error persisting the change token, this should be returned instead.


Add a condition and rename to Handle Set property bag value response.
Set the first condition value to the Set property bag error variable.
Set the comparison type to is equal to.
Leave the second condition value empty.


Response - return changes


Add a Request - Response action to the Yes branch and rename to Response - return changes. As the name suggests, this action will return the set of changes to the calling Flow.
Set the Status Code to 200.
Add a Content-Type header with the value application/json.
Set the Body value to the Output of the Select ID ChangeToken and ChangeType action.
Copy/paste the following JSON into the Response Body JSON Schema field (this is the same schema used by the Response - empty array action earlier in the Flow):

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "RelativeTime": {
                "type": "string"
            },
            "SiteId": {
                "type": "string"
            },
            "Time": {
                "type": "string"
            },
            "Editor": {
                "type": "string"
            },
            "EditorEmailHint": {
                "type": "string"
            },
            "ItemId": {
                "type": "integer"
            },
            "ListId": {
                "type": "string"
            },
            "ServerRelativeUrl": {
                "type": "string"
            },
            "SharedByUser": {},
            "SharedWithUsers": {},
            "UniqueId": {
                "type": "string"
            },
            "WebId": {
                "type": "string"
            },
            "ChangeType": {
                "type": "string"
            },
            "ChangeToken": {
                "type": "string"
            }
        },
        "required": [
            "RelativeTime",
            "SiteId",
            "Time",
            "Editor",
            "EditorEmailHint",
            "ItemId",
            "ListId",
            "ServerRelativeUrl",
            "UniqueId",
            "WebId",
            "ChangeType",
            "ChangeToken"
        ]
    }
}

Response - return error


Add a Request - Response action to the No branch and rename to Response - return error.
Set the Status Code to 500.
Add a Content-Type header with the value application/json.
Set the Body field value to the following Expression:

body('Parse_Set_property_bag_value_response')[0].ErrorInfo

Copy/paste the following JSON into the Response Body JSON Schema field:

{
    "type": [
        "object",
        "null"
    ],
    "properties": {
        "ErrorMessage": {
            "type": "string"
        },
        "ErrorValue": {},
        "TraceCorrelationId": {
            "type": "string"
        },
        "ErrorCode": {
            "type": "number"
        },
        "ErrorTypeName": {
            "type": "string"
        }
    }
}

Terminate - error


Add a Control - Terminate action to the No branch and rename to Terminate - error.
Set the Status to Failed.
Set the Code to 500.
Set the Message to the Set property bag error variable.
The finished condition should look like this:


Calling this Flow from another Flow

This Flow can be called from another Flow using the HTTP - HTTP action with the following properties:


Name
Value


Method
POST


Uri
The HTTP POST URL property of the When a HTTP request is received trigger.


Headers


Key
Value


Content-Type
application/json


Body JSON example

{
    "siteUrl": "https://tenant.sharepoint.com/sites/siteName",
    "listId": "00000000-0000-0000-0000-000000000000",   
    "changeTokenPropertyName": "ScheduledFlowChangeToken"
}
Body parameters


Name
Description


siteUrl
The absolute URL of the site (web) containing the list.


listId
The GUID of the list.


changeTokenPropertyName
The list root folder property bag property name that will be used to store the last change token retrieved from the GetListItemChanges API. This will be created if it doesn't already exist. This should be unique to the calling Flow.


Example output

The following JSON is an example of the list item change events returned from this Flow after renaming a file. For this single user action, both the Update and Rename events are triggered:
[
    {
        "RelativeTime": "1|0|1",
        "SiteId": "3f37b0d9-b37a-40d4-b1bf-eaf463dec366",
        "Time": "2018-08-12T10:21:20Z",
        "Editor": "Leo Siddle",
        "EditorEmailHint": "user@tenant.onmicrosoft.com",
        "ItemId": 132,
        "ListId": "1510ef2b-9d29-460d-a16e-ec68ef14d0dc",
        "ServerRelativeUrl": "/sites/SiteName/Shared Documents/fileName.PNG",
        "SharedByUser": null,
        "SharedWithUsers": null,
        "UniqueId": "252d4c5d-eb73-4113-a737-809cb9c849b8",
        "WebId": "a5783ce5-c53d-4e4d-af38-e3bede99c712",
        "ChangeType": "Update",
        "ChangeToken": "1;3;1510ef2b-9d29-460d-a16e-ec68ef14d0dc;636696660804600000;249848385"
    },
    {
        "RelativeTime": "1|0|1",
        "SiteId": "3f37b0d9-b37a-40d4-b1bf-eaf463dec366",
        "Time": "2018-08-12T10:21:21Z",
        "Editor": "Leo Siddle",
        "EditorEmailHint": "user@tenant.onmicrosoft.com",
        "ItemId": 132,
        "ListId": "1510ef2b-9d29-460d-a16e-ec68ef14d0dc",
        "ServerRelativeUrl": "/sites/SiteName/Shared Documents/newFileName.PNG",
        "SharedByUser": null,
        "SharedWithUsers": null,
        "UniqueId": "252d4c5d-eb73-4113-a737-809cb9c849b8",
        "WebId": "a5783ce5-c53d-4e4d-af38-e3bede99c712",
        "ChangeType": "Rename",
        "ChangeToken": "1;3;1510ef2b-9d29-460d-a16e-ec68ef14d0dc;636696660805800000;249848386"
    }
]
Name	Value
Site Address	`siteUrl`
Method	GET
Uri	_api/web/lists('`listId`')/RootFolder/Properties?$select=`changeTokenPropertyName`
Headers	`Output` (`Set Headers`)
Name	Value
Status Code	200
Headers	Content-Type: application/json
Body	`[]`
Response Body JSON schema	See below.
Name	Value
Method	POST
Uri	The `HTTP POST URL` property of the `When a HTTP request is received` trigger.
Name	Description
`siteUrl`	The absolute URL of the site (web) containing the list.
`listId`	The GUID of the list.
`changeTokenPropertyName`	The list root folder property bag property name that will be used to store the last change token retrieved from the GetListItemChanges API. This will be created if it doesn't already exist. This should be unique to the calling Flow.