How to provision SharePoint list and resources with only standard Microsoft Flow using ExecuteSiteScriptAction

/

I wanted to blog about an interesting technique I was testing - how to call ExecuteSiteScriptAction from Microsoft Flow - and using that to provision SharePoint site.

We’ll do this all with Microsoft Flow and it is all part of the Flow / Office-Seeded license so there’s no extra cost.

Steps

  • YouTube: How to create custom SharePoint list in one action using ExecuteSiteScriptAction and Flow

  • Documentation links for Site Design and Scripting

  • ExecuteSiteScriptAction seems to be something new allowing us to directly execute site script without saving it as a site script first

  • Future ideas

I did a recording of this on YouTube last week, this blog post will cover the steps and goes a bit deeper into the technical details.

Documentation links for Site Design and Scripting

  • https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/site-design-json-schema

  • https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/get-started-create-site-design

ExecuteSiteScriptAction

ExecuteSiteScriptAction is a REST command available on the API, although doesn’t seem to be mentioned in the REST documentation (yet)

  • https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/site-design-rest-api#rest-commands

I actually found out about this from reverse engineering the new SharePoint “Create List from Excel Spreadsheet” feature. I was reading how it was done in JavaScript, and came across this end point.

I did a search for ExecuteSiteScriptAction and finds a reference in PnPJS.

  • https://pnp.github.io/pnpjs/sp/site-scripts/#execute-site-script-action

  • https://github.com/pnp/pnpjs/issues/1096


What does this all mean?

ExecuteSiteScriptAction allows us to execute site script directly, without first saving it as a site script and then execute the site script.

So it’s ideal to provision a complex SharePoint resource.

Previously, I do this either via a series of REST calls, or use REST with $batch or call Azure Functions, but all those methods are more complex. Using Site Script is a much easier way to quickly ask SharePoint to provision the resource on the server side, without multiple trips of communicating with the server.

To do this in Flow (watch the video):

Use this in the first compose - create a JSON of the site script to createSPList with several subactions.

{
  "verb": "createSPList",
  "listName": "Customer_Tracking",
  "templateType": 100,
  "subactions": [
    {
      "verb": "setTitle",
      "title": "Customer Tracking"
    },
    {
      "verb": "setDescription",
      "description": "List of Customers and Orders"
    },
    {
      "verb": "addSPField",
      "fieldType": "Text",
      "displayName": "Customer Name",
      "isRequired": false,
      "id": "c532fcb9-cdb3-45c6-8247-c784dcd58e1a",
      "internalName": "customer_name",
      "addToDefaultView": true
    },
    {
      "verb": "addSPField",
      "fieldType": "Text",
      "displayName": "Customer Name 2",
      "isRequired": false,
      "id": "c532fcb9-cdb3-45c6-8247-c784dcd58e1b",
      "internalName": "customer_two",
      "addToDefaultView": true
    },
    {
      "verb": "addSPFieldXml",
      "schemaXml": "<Field ID=\"{c532fcb9-cdb3-45c6-8247-c784dcd58e1c}\" Type=\"Choice\" DisplayName=\"Customer Category\" Required=\"FALSE\" Format=\"Dropdown\" StaticName=\"customer_category\" Name=\"customer_category\"><Default>Operations</Default><CHOICES><CHOICE>Operations</CHOICE><CHOICE>IT</CHOICE><CHOICE>Legal</CHOICE><CHOICE>Engineering</CHOICE></CHOICES></Field>"
    },
    {
      "verb": "addSPField",
      "fieldType": "Text",
      "displayName": "Text Field",
      "isRequired": false,
      "addToDefaultView": true
    },
    {
      "verb": "addSPField",
      "fieldType": "Number",
      "displayName": "Number Field",
      "internalName": "ElectricSlide",
      "addToDefaultView": true,
      "isRequired": true
    }
  ]
}


Then create a second JSON

{
  "actionDefinition": "@{string(outputs('createSPList-definition'))}"
}

Finally, send that to SharePoint via REST

// _api/Microsoft.Sharepoint.Utilities.WebTemplateExtensions.SiteScriptUtility.ExecuteSiteScriptAction()


{
  "accept": "application/json; odata.metadata=minimal",
  "content-type": "application/json;charset=utf-8"
}

That’s it - all the actions are standard, and doesn’t require calling out to a webservice.


Future ideas / Homework

  1. Imagine Flows now can easily call this to create needed SharePoint list if it doesn’t exist.

  2. Or to upgrade SharePoint lists across many sites (use variable for site url).

  3. A new PowerApps can call an accompanied Flow to create the list that it needs.

  4. We can also use many of the other features within Site Script to modify permissions, set theme and colours etc.

  5. We can even use AddSPFieldXML to add complex fields via XML definition.

  6. As a final thought - I think the Site Script definition is a much easier way to define provisioning steps, since it’s really designed to be low code.

  7. Being able to use one technique (Site Script) to provision assets during:

    • Create Sites using out of box experience

    • Invoke with Power Automate (Flow)

    • And we can also invoke Site Script from SPFx in JavaScript through PnPJS

    • This kind of shared skill re-use, and with Microsoft extending site script makes this a great technique to learn and be familiar with.

  8. Daniel Laskewitz reached out and told me that we can add the new SharePoint REST API for creating new sites to completely automate creating new sites as well as provisioning the scripts all using Power Automate. Here’s the doc link he sent me. Thank you Daniel!

    https://docs.microsoft.com/en-us/sharepoint/dev/apis/site-creation-rest