Facebook @workplace integration with SharePoint using Microsoft Flow
Table of contents:
Building an intranet using modern SharePoint I was asked by a customer, if this is possible to show information from their facebook @workplace portal. I started digging and reading the documentation only to realize, that no matter if I were using modern or classic approach, still there are no ready-to-use scripts from facebook, allowing to copy-paste and then be able to display ex. information from Newsfeed.
Solution
I wanted very much to not use custom programming, rather to focus on ootb possibilities. After reading facebook API’s documentation on custom integrations (source) I decided to create a solution working in a following scenario:
- @workplace uses webhooks to call Microsoft Flow (source1: webhooks getting started, source2: webhooks in @workplace)
- Microsoft Flow receives a call, parses it and stores data in a SharePoint list
- Dedicated web part in SharePoint shows data from the list
Prerequisites
Custom integration in @workplace
To even be able, to use a webhook, first it needs to be defined. To do that you need to go to “Integrations” page (being a @workplace administrator): https://[your-company].facebook.com/work/admin/?section=apps&ref=bookmarks and then to create new custom integration:
Then you have to define its name and optionally a description.
Now you have to select what permissions does the integration need (in my case this is only “Read group content”), then select groups, to which it will have access (in my cases this is “All groups”) and finally configure the webhooks.
[tds_info] Remember, that you can only subscribe one URL per webhook topic, but you may use the same URL for multiple topics.[/tds_info]
In my case I only configured webhooks for the “Groups”. How? Read below.
Webhook verification
To save a webhook it has to have verified connection with the callback URL. The callback URL is in fact a URL of the Microsoft Flow, which is going to receive and parse data.
[tds_info] The tricky thing is, that the callback verification must be done using GET request, whereas further webhooks’ requests are done using POST calls.[/tds_info]
To do that simply create a Flow with a “Request” action as the trigger. Then parse the header parameters, using the “Parse JSON” action:
- Content: triggerOutputs()[‘queries’]
- Schema:
{
"type": "object",
"properties": {
"hub.mode": {
"type": "string"
},
"hub.challenge": {
"type": "string"
},
"hub.verify_token": {
"type": "string"
}
}
}
Finally add the “Response” action, and use “hub.challenge” value as a Body:
Do not forget, to change how the Flow can be triggered – set the method to “GET” (1) in trigger action (it’s under the “advanced options”), publish your workflow and finally copy its URL (2):
Now paste that URL in a “Callback URL” field in webhook configuration and type any “Verify Token” value (it should be used to check, if the GET request really comes from your webhook) and hit “Save”:
Now you’re ready to go – you will notice in your Flow a new run, completed successfully and your new “Custom integration” will be saved.
[tds_info] Whenever you decide to change ANYTHING in your Custom Integration configuration, you will need to verify Callback URL again – so again to send a GET request to your Flow.[/tds_info]
Building a Flow
Remember to keep the actions for GET response within your workflow. I did it by adding a condition action, with a condition: “1 is equal to 2”, so that Flow always goes the other way. I am changing its behavior between receiving GET and POST requests, which is impossible to automate right now. So when I modifies my @workplace Custom integration settings, I change the method to “GET” and the condition to “1 is equal 1”. After verification passes, I am turning them back to “POST” and “1 is equal to 2”.
Request body schema
In my “POST” branch, the first action is “Parse JSON”, used to parse the request body. Before I made a valid schema I did dozens of calls from @workplace to Flow, to see how specific actions are represented in JSON. I have named the following scenarios:
- Membership – when a new user joins a group
- Comment – when a new comment is created
- Post – when a new post is written
- Post with a single photo
- Post with multiple photos
- Post without photo (a status)
- Event
- all others
So the schema looks like below (it contains properties for all scenarios, set to not be mandatory):
{
"type": "object",
"properties": {
"entry": {
"type": "array",
"items": {
"type": "object",
"properties": {
"changes": {
"type": "array",
"items": {
"type": "object",
"properties": {
"field": {
"type": "string"
},
"value": {
"type": "object",
"properties": {
"from": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
}
}
},
"member": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
}
}
},
"update_time": {
"type": "string"
},
"verb": {
"type": "string"
},
"community": {
"type": "object",
"properties": {
"id": {
"type": "string"
}
}
},
"actor": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
}
}
},
"attachments": {
"type": "object",
"properties": {
"data": {
"type": "array",
"items": {
"type": "object",
"properties": {
"url": {
"type": "string"
},
"subattachments": {
"type": "object",
"properties": {
"data": {
"type": "array",
"items": {
"type": "object",
"properties": {
"url": {
"type": "string"
},
"media": {
"type": "object",
"properties": {
"image": {
"type": "object",
"properties": {
"src": {
"type": "string"
},
"width": {
"type": "number"
},
"height": {
"type": "number"
}
}
}
}
},
"type": {
"type": "string"
},
"target": {
"type": "object",
"properties": {
"url": {
"type": "string"
},
"id": {
"type": "string"
}
}
},
"title": {
"type": "string"
}
}
}
}
}
},
"media": {
"type": "object",
"properties": {
"image": {
"type": "object",
"properties": {
"src": {
"type": "string"
},
"width": {
"type": "number"
},
"height": {
"type": "number"
}
}
}
}
},
"type": {
"type": "string"
},
"description": {
"type": "string"
},
"target": {
"type": "object",
"properties": {
"url": {
"type": "string"
},
"id": {
"type": "string"
}
}
},
"title": {
"type": "string"
}
}
}
}
}
},
"type": {
"type": "string"
},
"target_type": {
"type": "string"
},
"comment_id": {
"type": "string"
},
"post_id": {
"type": "string"
},
"created_time": {
"type": "string"
},
"message": {
"type": "string"
},
"permalink_url": {
"type": "string"
}
}
}
}
}
},
"id": {
"type": "string"
},
"time": {
"type": "number"
}
},
"required": [
"changes",
"id",
"time"
]
}
},
"object": {
"type": "string"
}
}
}
Step by step
After parsing request body using the schema, Flow is doing the following steps:
- Get operation field – whether post is a “Comment”, “Membership” or “Posts”
I do it using the “Compose” action and the following expressions:body('Parse_request_body')?['entry']?[0]?['changes']?[0]?['field'] - Switch branches based on the operation type.
- For each type I am getting a proper ID (ex. comment_id for “Comments” or post_id for “Posts”)
- Then I am querying SharePoint to check, whether there is already a record with that ID. If there is, then obviously workflow ends its execution.
- If this is a “Post” type, then I am looking an internal operation type property using the following expression:
body('Parse_request_body')?['entry']?[0]?['changes']?[0]?['field'] - Based on the outcome, Flow switches between:
- Photo
- Status
- Event
- and other requests (haven’t seen anything “other” yet)
- Then for “Photo” type it also checks, whether there is only one or multiple photos (gallery) checking, if the type is “album” by evaluating the below expression:
body('Parse_request_body')?['entry']?[0]?['changes']?[0]?['value']['attachments']['data']?[0]?['type'] - Finally, for each message type it is also trying to match user with an existing one, by using action “Office 365 Get user profile (V2)” and concatenating users first and last name together with company’s domain as a UPN:
concat(trim(replace(trim(body('Parse_request_body')?['entry']?[0]?['changes']?[0]?['value']['from']['name']), ' ', '.')), '@the-company.com') - Obviously, if user is not found, the action triggers error, so for that case the next action, which is “Create list item” has configured “run after” settings also to “has failed”, so that no matter what happens, Flow wont fail for that reason:
- In the end Flow is creating new item on a list, combining all information together:
Structure of Flow’s actions, on a medium level of details, looks as below for the described solution:
Icons (i) visible next to arrows mark relations, in which the next action is executed even if the previous one ends up with failure.
Structure of a single block, used to save data from @workplace into a SharePoint list, looks as on the example below (it shows saving data for a new post):
Data structure
For storing comments I am using a single SharePoint list built from the following columns:
- Title – holds type of the information.
- Author – a text column to keep name and last name of an author.
- Date – when the event occurred (from webhook).
- Post/ Comment – a multiline text field, allowing HTML formatting, to keep body of the message.
- Image – again, a multiline text field, allowing HTML formatting, to store <img> tag with the URL of the image, attached to a message – this is because Flow does not support “Picture/ Hyperlink” fields having set type to “Picture”.
- SourceURL – a “Picture/ Hyperlink” field with a type set to “Hyperlink”.
- AuthorPPL – again, an author field, but this time as a “Person or group” field. My Flow is trying to map Author data to an existing SP User.
- ItemId – an identifier of the message: comment_id, post_id or a member_id.
The list, filled up with data, looks as below:
Things to remember
During that project I learnt the following things. Please, keep them in mind when trying to follow my steps:
- @workplace will send GET request to FLOW every time a “Custom integration” is modified and saved.
- @workplace will be sending requests for a single event as long as it won’t receive a “200 OK” response – be prepared to verify if the content has already been added.
- @workplace is not always waiting for the response. I see dozens of failed flows just because @workplace abandoned waiting for the response. I have no idea why, it seems to be really random. Anyway – in case it fails waiting, it will try to send the same data again and again until receiving “200 OK”. Only the frequency will be lower and after couple of tries it will stop.
- Using the “run after” configuration in Flow’s actions can be really useful – both for overcoming missing information or actions which can end up with errors which we don’t really care about, as well as doing conditional blocks. In my Flow I was using this configuration very often.
Thanks for reaching this sentence. I hope you find it useful. Don’t hesitate to contact me in case you have any questions or leave your comment below!
Kevin Annfield
Hi Tomasz.
Thanks for posting this up, appreciated. I have a question though, I’m trying to verify a FB Messenger webhook with the Flow HTTP Request action, but its not working for me. The issue seems to be with the Content of the ParseJSON action as the verification request doesn’t seem to contain the ‘queries’ content. I’ve tried to use the Headers, Path Parameters and Body, but none of them contain the value to use in the hub.challenge response.
Do you have any suggestions? Maybe the FB Messenger verification process is different to the Workplace process?
Thanks
Tomasz Poszytek
Are you sure you are doing this right? The verification must be done using GET call, therefore your Flow should be set to be triggered by a GET query. Once having it configured and after you put Flow URL as a callback URL in your integration definition, you should then save it and when saving, Facebook should send the confirmation request, using GET, to your Flow.
Kevin Annfield
I’m pretty sure I’m doing it right. I’ve got my Flow configured the same as yours. I’ll get someone else to have a look too.
It makes complete sense what is meant to be happening, for some reason I’m not seeing the same parameters out of the Request action as you.
Tomasz Poszytek
I would need to take a look. If you’re exactly following my steps then everything should look equal. Otherwise maybe Facebook has changed something.
Isaac
Hello,
For some reasons I can’t get the content trigger for the ‘Parse Json’ after the ‘Http Trigger’ to show “Queries”. I only get 3 options; Body, Headers, and Path Parameters.
What should I do in this case?
Tomasz Poszytek
Try with Path Parameters.
Isaac
Okay it seems you must add
@
before
triggerOutputs()[‘queries’]
so its
@triggerOutputs()[‘stuff’]
so that it shows the green box…. but it doesn’t work, and pathparameters doesn’t work either.
(I checked the view code of pathparameters and noticed that it had an @ sign).
It timed out. Must I try a couple of times?
Tomasz Poszytek
No, without “@”. Try putting just triggerOutputs() in a Compose action to see if there is such a property.
Just asking – is your trigger action for this case configured to accept “GET” requests? Are you sending any query parameters to that URL? Maybe if there are no, then the property doesn’t exist.
Simba
Hi Tomasz
This is a brilliant post i have managed to follow through most of it up until the point the compose actions to get the comments body, the comments_id etc. I have only recently started playing with Power-automate and am struggling to understand the expressions you are using to get the value of the comments body for example and also comments id from the request body to filter items from the SharePoint list then writing that into the SharePoint list. Can you please explain how you are getting a comment into the list maybe step by step if you can, it would be much appreciated.
Looking forward to your response and
Great Post once again
Tomasz Poszytek
Hi, I don’t have an access to that Flow anymore, but basically once you do the “Parse JSON” then whatever you want to get out from that code is simply defined as a path, eg. body(‘Parse_request_body’)?[‘entry’]?[0]?[‘comments’]?[0]?[‘commentId’] etc… Have you tried it? So once you get the JSON from the Facebook request look at its structure and then try to figure out a path to specific data you want to get.
Simba
Hi Tomasz,
Thank you, i eventually figured that to get almost any value one is traversing the Request Body until you get to the specific item you want.
A different issue i am running into is regarding photo posts.
1) How do i get the image to display as a preview in the sharepoint list instead of a string URL
2) If it is a post with multiple images how are you handling that on the sharepoint side. I am getting the following error on writting to SharePoint.
Unable to process template language expressions in action ‘Add_new_photo_update,_1st_photo’ inputs at line ‘1’ and column ‘2807’: ‘The template language expression ‘body(‘Parse_request_body’)?[‘entry’]?[0]?[‘changes’]?[0]?[‘value’][‘attachments’][‘data’]?[0]?[‘media’][‘image’][‘src’]’ cannot be evaluated because property ‘image’ cannot be selected. Because the request body multiple images.
Thanks and looking forward to your feedback
Simba
Hello again Tomasz,
Below is the Expression i am using to get the URL of the image in the post
body(‘Parse_request_body’)?[‘entry’]?[0]?[‘changes’]?[0]?[‘value’][‘attachments’][‘data’]?[0]?[‘media’][‘image’][‘src’]
In my SharePoint list multi line field with rich text enabled it still renders as a URL. Any thoughts
Thanks
Tomasz Poszytek
I think in such case I was taking only the first image, not all, since that was an array.
Regarding how to display images – if you use multiline text field with richHTML option enabled, then put the URL in the proper “<IMG>” tag, e.g.: <img src=”body(‘Parse_request_body’)?[‘entry’]?[0]?[‘changes’]?[0]?[‘value’][‘attachments’][‘data’]?[0]?[‘media’][‘image’][‘src’]” />
Jeffrey Bernard
I’d like to comment here that the issue people are experiencing with the authentication of webhooks is due to a typo in triggerOutputs()[‘queries’]. You used apostrophe rather than single parenthesis. Here is the correct trigger output that works. triggerOutputs()[‘queries’]
Tomasz Poszytek
Can’t find the typo, but thanks for careful lecture 🙂
Jeffrey Bernard
Adding this here. To help others with the webhook validation.
https://youtu.be/2YPtR-mOgH8