Saturday, July 22, 2017

Call any SharePoint REST API from Microsoft Flow

EDIT:
There is a much better way to call the SharePoint REST APIs now. A new action has been added to Microsoft Flow to do just that easily. See https://sergeluca.wordpress.com/2018/05/03/assign-unique-permissions-to-a-document-with-the-new-send-an-http-request-to-sharepoint-action-how-to-use-the-sharepoint-rest-api-in-flow/ for more information.

Case:
You want to do an action on SharePoint Online from a Flow. But the action, that is supported in the SP REST API, is not yet available as an easily configurable action inside Microsoft Flow.
So you think, I should be able to use the HTTP action for this.

Well, you're right!

But how do you then authenticate to SharePoint Online? I tried to get a token from the supported SharePoint actions in Flow but these turn out to have been *sanitized*, ie. not usable in other actions.

I tried a bunch of things including using the OAuth authentication setting on the HTTP actions. But that required filling in the rather meaningless (to me at least) parameter Audience. I suppose this should be the App ID URI of microsoft flow in your AAD Tenant, but I wasn't able to find it.

Thank god for this article that I (re)found after useless trial and error: Access Sharepoint Online Using Postman by Shantha Kumar

The article explains in great deal how to authenticate and perform a call to any REST API endpoint inside SharePoint.

This is the general gist:
1. Register an App in SharePoint through the pages /_layouts/15/appregnew.aspx and appinv.aspx
This will give you a client-id, client-secret and on the page /_layouts/15/appprincipals.aspx you can find your tenant id (the guid after the @ sign)
2. Perform a HTTP post to https://accounts.accesscontrol.windows.net/<tenantid_guid>/tokens/OAuth/2
with headers
 Content-Type:application/x-www-form-urlencoded
and in the body (remember the request is URL encoded, so it's just one big string, not JSON) a string like this:
grant_type=client_credentials&client_id=<apponlyclientid>@<tenantidguid>&client_secret=<clientsecret>&resource=00000003-0000-0ff1-ce00-000000000000/<yoursubdmain>.sharepoint.com@<tenantidguid>
3. Get the access token for the output of the previous HTTP action. Use a compose action, and enter something like this:
"@outputs('HTTP_2').body.access_token" where 'HTTP_2' is the name of your previous HTTP action with spaces replaced by underscores.
4.
Use the output of the HTTP compose (that contains the Bearer token) as input for any REST API call into sharepoint.
For example, Perform a HTTP Post to https://<yoursubdomain>.sharepoint.com/sites/Provisioning/_api/web/lists/getbytitle('Documents')/items(2)/roleassignments/addroleassignment(principalid=23,roledefid=1073741827)
with the following headers:
Accept:application/json;odata=verbose
Authorization:Bearer [OutputFromComposeAction]
This will add List item permissions on a list item (if you have broken role inheritance on this item already and are passing valid values for list item id, principalid and roledefid)

Here are some screenshots how to do that in Microsoft Flow:
Before




After



58 comments:

  1. Hi,
    Nice post. I tried to follow, but I am missing something, I guess.
    I've generated the client-id and client-secret, granted FullControl permission to the App, but the Flow fails with a 403 exception for the last HTTP request, even when I do a simple GET.
    I get this spooky error message there too: "X-MSDAVEXT_Error: 917656; Access+denied.+Before+opening+files+in+this+location%2c+you+must+first+browse+to+the+web+site+and+select+the+option+to+login+automatically."

    Any idea what the problem might be?

    Thanks

    ReplyDelete
    Replies
    1. So the problem was that I used Scope="http://sharepoint/content/sitecollection/web", but it needs to be Scope="http://sharepoint/content/sitecollection".

      Tanks

      Delete
    2. Hi, can you confirm it is still working for you? I am trying to make it work but I am stuck at the 403 error for the last request

      Delete
    3. It still works for me. You can check if your token works by doing a simple call for the web's title: https://yourtenant.sharepoint.com/sites/asite/_api/web?$select=Title

      Delete
    4. this article may help https://sharepointmadeeasy.blogspot.de/2018/03/microsoft-flow-get-access-denied-for.html

      Delete
  2. Thank You for the detailed description. Will the above identified steps work in the case of Sharepoint Online with AllowAppOnly policy ? I am able to get an access token but is not able to call the Rest API in Sharepoint as I am getting Access denied error. Please suggest

    ReplyDelete
  3. Are you sure your app has the correct permissions granted to do the call you want to do? The above steps also work with app credentials instead of user. How did you register your app to obtain the client id and secret? Maybe https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/app-only-elevated-privileges-sharepoint-add-in will help in clarifying things. Let me know.

    ReplyDelete
  4. It has worked. Thank You !

    ReplyDelete
  5. Hello, I using a GET method to return list of groups from SP Site. however, I would like to store the Title of group to Compose action and I am not able to do achieve that. can you please help?

    ReplyDelete
    Replies
    1. Steps to take if I understand you correctly:
      1. Do a HTTP call like above to https://yourtenant.sharepoint.com/sites/yoursite/_api/web/sitegroups
      You will get back JSON that includes the group titles inside an array (in body.d.results ). (you can see the JSON that comes back from the HTTP call in the logs if you run it once)
      2.Add a compose action with the expression: actionOutputs('HTTP').body?.d?.results
      3. I put the output of step 2 into a variable of type Array.
      4. Add an apply to each on the output of 3.
      5. Inside the apply to each, you can get the title of each group in the site with the expression:
      item().Title

      Hope this helps. I have the flow working in my tenant.

      Delete
  6. Is there a way i Can add user(s) to existing sharepoint group?

    ReplyDelete
    Replies
    1. Yes, see the SP REST API documentation here: https://msdn.microsoft.com/en-us/library/office/dn531432.aspx#bk_UserCollectionRequestExamples

      Delete
    2. Thanks for the response, I have seen this, however, It doesnot work. keeps giving error in postman Access Denied, though i am the owner of the site.

      Delete
    3. If you're using above method with the App only token, the rights of your own account dont matter. You need to specify the rights your App gets when you create the app on appregnew.aspx.

      Delete
    4. Hi, Yes, I am using the token from HTTP request where the app has the Trust parameter to 'Full Control' even instead of 'Read'. not sure if that makes any difference, but i still get access denied. will it be possible for you to create a blog on the steps with this requirement, as I am not able to find any article which actually has a working solution for adding Users in a Group via Postman or REST API via browser. or even MS Flow.

      Thanks,

      Delete
    5. This post had the answer:
      https://social.technet.microsoft.com/Forums/en-US/09f3fe56-5471-4075-b321-4d5b965fa67e/adding-users-to-group-using-rest-apijsom?forum=sharepointdevelopment

      You need to POST a json string such as this:
      {"__metadata":{"type":"SP.User"},"LoginName":"i:0#.f|membership|jurgen.wiersema@yourtenant.com"}
      to /_api/web/sitegroups(8)/users where the ID is the ID of the group you want to add the users to.

      Delete
    6. I had seen this article and tried it as well. in Postman itself it fails with SP.user type not found error.

      Delete
    7. Did you use a Compose JSON action to store the JSON above, and then used the string() function to send it in the body of the HTTP Post, and the Content-Type of the HTTP Post set to application json verbose like in the blog article?
      I think I had the same error the first time and that solved it. Just keep at it, I have it working in my tenant. Otherwise, you can also email me at the email address you can probably guess (on gmail).

      Delete
    8. I dont see compose JSON action, there is Compose Parse Json. I just stored the above Json in String variable and passed in the body, its giving me Access Denied permission. It is Ok. I have spent lot of time on this already. thank you very much for your help and responses so far.

      Delete
  7. Hi :),

    I tried your method, but I receive: "Unauthorized". I changed to Postman with the same info and receive my token. You know what happened with in flow?

    ReplyDelete
    Replies
    1. I am getting same error for one of my flows. please let me know if you found an answer. strangely enough one of my other flow just works fine and one fails.

      Delete
  8. Hi

    Iam getting all body as an option to select when i add Compose Action. How can i filter out only access_token, and not the all body?

    ReplyDelete
    Replies
    1. That is this step:
      "@outputs('HTTP_2').body.access_token" where 'HTTP_2' is the name of your previous HTTP action with spaces replaced by underscores.

      Delete
  9. Has this been blocked on SharePoint Online? Works on Postman, but not on MS Flow. Possible Apps permission issue. Will check.

    ReplyDelete
    Replies
    1. Same here - specific error is "Invalid client secret is provided"

      Delete
    2. Hi,

      You need to encode your client secret and resource parameter.

      Then it will work. That was same case with me.

      Delete
  10. Hi All,

    Whoever facing unauthorized error, You need to encode your client secret and resource parameter and then pass it into body.Then it will work. That was same case with me. Let me know if you still face issue, i will help you out.

    Kind Regards.

    ReplyDelete
    Replies
    1. Hi Suresh, thanks for you help. When you set the contenttype to application/x-www-form-urlencoded the client (in this case Flow) should encode the HTTP request automatically (this is what a browser does). But it's a good thing to try and see if the output is different if you manually encode it in case of problems.

      Delete
  11. this article will help.

    http://sharepointmadeeasy.blogspot.de/#!/2018/02/microsoft-flow-http-rest-api-invalid.html

    ReplyDelete
  12. Hi, I've just tried to create a Flow to be able to call Sharepoint ODATA API, however I'm having a problem:
    I've successfully retrieved the access token and when I want to perform the Sharepoint call (for example to https://.sharepoint.com/sites/future/_api/web/lists ) I'm having error 400 - InvalidHostName

    I really have no idea why this is happening, the headers look right and the request is so simple. Which is the reason?

    Thanks you

    ReplyDelete
    Replies
    1. Of course I've deleted the tenant name, but the query is working fine directly in browser.

      Delete
    2. This is of course hard for me to say without being able to see the flow. Did you create the token HTTP request with the following body:
      grant_type=client_credentials&client_id=@&client_secret=&resource=00000003-0000-0ff1-ce00-000000000000/.sharepoint.com@

      Delete
  13. Hi, unfortunately I cannot make this work. With Postman I get the access_token, but trying the same request from Flow I always get the following:

    {"error":"invalid_request","error_description":"AADSTS90014: The request body must contain the following parameter: 'grant_type'.\r\nTrace ID: 8f5e37d8-37fb-4f0a-8b07-3b5a4bca6300\r\nCorrelation ID: a52996a7-2793-4c2b-af87-a2971e7127d0\r\nTimestamp: 2018-04-23 07:43:45Z","error_codes":[90014],"timestamp":"2018-04-23 07:43:45Z","trace_id":"8f5e37d8-37fb-4f0a-8b07-3b5a4bca6300","correlation_id":"a52996a7-2793-4c2b-af87-a2971e7127d0"}
    I have tried to encoded the client secret/resource/both but none of them helped. Could you help me out? Thank you

    ReplyDelete
    Replies
    1. Apparently the grant_type you are passing is incorrect. Did you set the content type of the request for an access token to
      Content-Type:application/x-www-form-urlencoded ?
      And are you passing a body of the form:
      grant_type=client_credentials&client_id=@&client_secret=&resource=00000003-0000-0ff1-ce00-000000000000/.sharepoint.com@

      Delete
    2. Yes, I've made sure that the header and the body are correct. They were ok, because in postman I got the access_token. I've spent hours with that problem and finally the solution was to create the flow in Internet Explorer instead of Google Chrome, then it worked like charm. Thank you very much for the article and for your help.

      Delete
  14. Hi getting access denied error while calling rest api with apponly sitecollection full control permission.

    ReplyDelete
  15. I;ve updated the article to point everyone to the new MS Flow action for all your SP REST API needs. See https://sergeluca.wordpress.com/2018/05/03/assign-unique-permissions-to-a-document-with-the-new-send-an-http-request-to-sharepoint-action-how-to-use-the-sharepoint-rest-api-in-flow/

    ReplyDelete
  16. Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating mulesoft online training Hyderabad

    ReplyDelete
  17. Everything is going great up until I try to do the actual sharepoint request:
    api/web/roleassignments/addroleassignment(principalid=,roledefid=1073741826)

    This used to give me an "Unauthorized Error", which is what lead me to this article, but when I follow your steps and put in the Authorization header, I get a bad gateway error:

    POST
    api/web/roleassignments/addroleassignment(principalid=,roledefid=1073741826)
    {
    "Accept": "application/json; odata=verbose",
    "Authorization": "Bearer "
    }

    ----------------

    {
    "error": {
    "code": 500,
    "source": "flow-apim-msmanaged-na-northcentralus-01.azure-apim.net",
    "clientRequestId": "",
    "message": "BadGateway",
    "innerError": {
    "status": 500,
    "message": "Cannot add value because header 'Authorization' does not support multiple values.\r\nclientRequestId: ",
    "source": "https://sharepointonline-ncus.azconn-ncus.p.azurewebsites.net/datasets//httprequest",
    "errors": []
    }
    }
    }

    ------------------

    Any ideas as to what's going on here?

    ReplyDelete
    Replies
    1. Sorry, the blog stripped out my placeholder tokens, so for clarity I'm going to repost this:

      Everything is going great up until I try to do the actual sharepoint request:
      api/web/roleassignments/addroleassignment(principalid=[MyGroupID],roledefid=1073741826)

      This used to give me an "Unauthorized Error", which is what lead me to this article, but when I follow your steps and put in the Authorization header, I get a bad gateway error:

      POST
      api/web/roleassignments/addroleassignment(principalid=[MyGroupID],roledefid=1073741826)
      {
      "Accept": "application/json; odata=verbose",
      "Authorization": "Bearer [MyAccessToken]"
      }

      ----------------

      {
      "error": {
      "code": 500,
      "source": "flow-apim-msmanaged-na-northcentralus-01.azure-apim.net",
      "clientRequestId": "[MyClientRequestId]",
      "message": "BadGateway",
      "innerError": {
      "status": 500,
      "message": "Cannot add value because header 'Authorization' does not support multiple values.\r\nclientRequestId: [MyClientRequestId]",
      "source": "https://sharepointonline-ncus.azconn-ncus.p.azurewebsites.net/datasets/[MySite]/httprequest",
      "errors": []
      }
      }
      }

      Delete
    2. Ah, disregard this. I was mistakenly using the Sharepoint HTTP action and not the generic HTTP action. Everything is working as it should. Thank you for the excellent guide.

      Delete
  18. I think REST API and Power BI usage can actually help in solving every kind of complex IT problem.

    powerbi read rest

    ReplyDelete
  19. I am so proud of you and your efforts and work make me realize that anything can be
    done with patience and sincerity. Well I am here to say that your work has inspired me without a doubt. Here is i want to share
    about mulesoft training online with Free Bundle videos .

    ReplyDelete
  20. Thank you for sharing such a great information.Its really nice and informative.hope more posts from you. I also want to share some information recently i have gone through and i had find the one of the best mulesoft 4 training videos

    ReplyDelete
  21. I just loved your article on the beginners guide to starting a blog. Thank you for this article. mulesoft training india
    and mulesoft training videos with highly experienced faculty.

    ReplyDelete
  22. Hi I am trying to add user into Sharepoint group. I am able to get all my sitesgroup with GET request but when I try with POST not able to achine can someone help me on this
    my URL : http://test.sharepoint.com/sites/oegsupport/_api/Web/SiteGroups/GetById(13)/Users

    Body Data :

    {
    "__metadata":{"type":"SP.User"},
    "LoginName":"i:0#.f|membership|dnyanesh@dnyaneshtechno.onmicrosoft.com"
    }

    ReplyDelete
  23. Ah,so beautiful and wonderful post!An opportunity to read a fantastic and imaginary blogs.It gives me lots of pleasure and interest.Thanks for sharing.
    Data Science Training In Chennai

    Data Science Course In Chennai

    ReplyDelete