Parse Json from HTTP Request to SharePoint POST

[kmw1130]

I am trying to get the siteURL from my HTTP Request to SharePoint (POST) after creating a new subsite.  My flow does not error, but it is not updating my list with the URL.  I was following the example from this site:  Modern Team Site Creation Automation With Power Automate (c-sharpcorner.com)

[eliotcole]

From my memory it's not a straightforward call, but I think I have actually done this with a site manager implementation I made for a company previously.

Annoyingly it was before I learned to start developing stuff in my own environment before presenting a proof of concept.

However, I have a feeling I might still have something laying around, if I do I'll update you shortly. 👍

[kmw1130]

@eliotcole Thanks that would be awesome.

[eliotcole]

Alright, then, @kmw1130 , buckle up. 😉

First Answer is:

Have You Looked The Site Creation Request?

Firstly, sometimes some HTTP data isn't somehow found in JSON Parses. So, are you 100% certain that your previous flow actions are not providing the Site URL in their body / data sections? Sometimes there are fields available in HTTP responses that are not shown in JSON Parses. It's odd, yes, but it's just the way this thing works.

Secondly, I took a look at the link you gave, and it seems a bit old, and a bit complex.

I have just used the SiteManager API to create a site, and it provides the following information upon completion.

{
  "d": {
    "Create": {
      "__metadata": {
        "type": "Microsoft.SharePoint.Portal.SPSiteCreationResponse"
      },
      "SiteId": "aaa111aaa-22bb-3c33-d4dd-ee5ee555ee5e",
      "SiteStatus": 2,
      "SiteUrl": "https://DOMAIN.sharepoint.com/sites/TestSiteMaker"
    }
  }
}

I didn't use the super complicated method that c-sharpcorner seems to use, though, I used the following:

HTTP Request to SharePoint

Site: Root Site - domain.sharepoint.com

Method: POST

URI: _api/SPSiteManager/create

Headers:

{
  "accept": "application/json; odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Body:

{
  "request": {
    "Title": "TestSiteMaker",
    "Url": "https://domain.sharepoint.com/sites/TestSiteMaker",
    "Lcid": 1033,
    "ShareByEmailEnabled": false,
    "Description": "Description",
    "WebTemplate": "SITEPAGEPUBLISHING#0",
    "SiteDesignId": "6142d2a0-63a5-4ba0-aede-d9fefca2c767",
    "Owner": "eliot@domain.com",
    "WebTemplateExtensionId": "00000000-0000-0000-0000-000000000000"
  }
}

So task one, take a look at a run that created a site, and look at the response in the SharePoint, perhaps it will give you the data you want after all.

Second task, do you need to create the site the way that c-sharp has instructed? The method above doesn't create an accompanying 365 group, but ... baby steps, y'know?

So ... perhaps try this simplified approach to creation, first, perhaps it'll give you more information upon creation.

[eliotcole]

OK, this is where that seatbelt you fastened will come in handy.

This wasn't the way I remember hacking it before, but it's a method that certainly rang a bell, and it got results for me.

Four caveats up front:

1. Whilst I can't guarantee that this will get the exact result each time, it *should* get it ... I am just aware that you're using the Search API, and one would assume that it's an inexact methodology.
2. It should be noted that the web widely acknowledges that the SharePoint API doesn't have a method for retrieving site data via the ID. The accepted way to do it is via the Graph API, but (quite frankly) that is quite difficult to set up and get working regularly.
3. It is specific about the type of site, here. You will need to work with the search API and site types to hone this.
4. I'm tired, so apologies for the singular image, here ... but this was a bit of a slog.

Also, this can all be condensed a lot, if / when you are comfortable with your expression writing, and sliding logic in to those expressions. Just remember one of the reasons for Flows, especially in workplaces, is to avoid the personal elitism created by an individual's insane excel formulas, so when you do use expressions, ensure they're explained somewhere.

Right then ... in to it.

Retrieving a Site URL via the SharePoint Search API

Flow To Find SharePoint Site URL from Site ID

So, once your API has created the site, and for some reason the Site URL data isn't available to you, then you can find it using the ID via the following method.

1. Put the ID in a Variable - Firstly, ensure however that ID you're getting is being placed into a variable. In my flow I've called it siteIdVAR (I defined mine manually).
2. Create Other Variables - You need a URL variable (urlVAR) and an array variable (arrayVAR), I've also made one to house the query (queryVAR).
3. Define the Query - My query follows this list. I will say that the "SiteId:" part may be pointless, but belt and braces, eh?
4. Set Up The HTTP Request - I've detailed it below, this will be your search request to the system.
5. Set Up The first Parse JSON - Not detailed below. Run the HTTP Request query once, and paste the results body in to generate the schema.
6. Set The Array Variable - Detailed below. This uses a very specific expression that digs into the data that you've pulled so far, and creates a new set of information.
7. Set Up The second Parse JSON - Detailed below. Note that I've inserted null types into the schema to account for blank responses.
8. Filter This Array - You only need the data that corresponds to the path. So filter the Key field on the value "Path" ...
9. Pick The URL into the URL Variable - Detailed below. This expression selects the item from the array.

Query String

contentclass:STS_Site AND SiteId:@{variables('siteIdVAR')}

HTTP Request to SharePoint

Site: Root Site - domain.sharepoint.com

Method: POST

URI:

_api/search/query?querytext='@{variables('queryVAR')}'&selectproperties='Title,Path'&rowlimit=10

Headers:

{
  "accept": "application/json; odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Body:

Set Array Variable:

array(body('Parse_JSON')?['PrimaryQueryResult']['RelevantResults']['Table']['Rows'][0]?['Cells'])

This will look at the 'PrimaryQueryResult' field, and then pull the 'RelevantResults' field from that, then selecting the 'Table' field, and lastly the 'Rows' field from the Table. Once it has the rows, it will select the first entry in the rows ("[0]") and finally, it will populate our array variable with the 'Cells' array.

Second Parse JSON

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Key": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "Value": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "ValueType": {
                "type": [
                    "string",
                    "null"
                ]
            }
        },
        "required": [
            "Key",
            "Value",
            "ValueType"
        ]
    }
}

Filter the Second Parse JSON Body Data

@equals(item()['Key'], 'Path')

Set the URL Variable

body('Filter_array')[0]?['Value']

This just picks the first item in the filter array, and selects only the Value field, avoiding the need for pesky looping.

[kmw1130]

@eliotcole Wow, that is a lot of information, which I'm a very grateful for.  To answer the question about creating an O365 group....No I don't need one.  The sites I am creating are subsites based on a Site Template I created.  I have it creating the subsite based on a Business Unit the is selected in my Site Creation List and it creates the subsite under that BU site.    I'm going to read thru all these wonderful instructions and give it a try for my Json to update my Site Creation list with the newly created URL.  I wasn't able to get to it yesterday because my laptop crashed and had to get a new one.  

Thanks again!!!