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





Building modern public sites (JAM Stack) with Microsoft Flow

/

Web technologies evolve, and we are on the edge of a new stack, it is called JAMStack.

  • Javascript

  • API

  • Markup

In the world of JAM Stack, we create serverless public sites by hosting them directly from a CDN.

Now there are plenty of frameworks that will move to help developers to create this type of applications right now and next.

What I’m writing about is how citizen developers can take advantage of the same pattern to build public websites

Episode 0

  • Define JAM Stack

  • Set up data source (SharePoint)

  • Set up API (Flow)

  • Set up Storage (Azure Storage)

  • Set up CDN (Cloud Flare)

  • Set up dynamic trigger


Whether you are a Professional Developer or a Citizen Developer - you need to understand the basics of this stack. This will save you a LOT of money.

How we can use Microsoft Flow (Power Automate) to build public websites. This is a how-to video that involves defining the JAM Stack, Microsoft Flow, Azure B...

Redirecting SharePoint list's NewFormUrl and EditFormUrl to Power Apps

/

In this scenario, we have a Power Apps app that takes a query param() “AssetID” and determines whether the app should create a new item or update an existing item.

We want to change the SharePoint default New or Edit items on a modern SharePoint list to go to our App. The app is a landscape canvas application and not an integrated portrait application (so we can’t use SharePoint form setting to switch this).

Because SharePoint’s New and Edit forms must be server relative pages, we can create a redirect page.

Steps

  1. Use Param in Power Apps

  2. Create a simple redirect page

  3. Change SharePoint’s NewFormUrl and EditFormUrl to this redirect page

  4. Test: Redirect to PowerApps

  5. References

Use Param in Power Apps

https://powerapps.microsoft.com/en-us/blog/powerapps-deep-linking/

Create a simple redirect page

Create a redirect ASPX (HTML) page and upload it into the SitePage library. I saved this as "AssetEditForm.aspx"

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<%@ Page Language="C#" %>
<%@ Register tagprefix="SharePoint" namespace="Microsoft.SharePoint.WebControls" assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta name="WebPartPageExpansion" content="full" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Asset Form</title>
<meta http-equiv="X-UA-Compatible" content="IE=10" />
<SharePoint:CssRegistration Name="default" runat="server"/>
<script type="text/javascript">

function getQueryVariable(variable) {
    var query = window.location.search.substring(1);
    var vars = query.split('&');
    for (var i = 0; i < vars.length; i++) {
        var pair = vars[i].split('=');
        if (decodeURIComponent(pair[0]) == variable) {
            return decodeURIComponent(pair[1]);
        }
    }
//    console.log('Query variable %s not found', variable);
}
var play = "https://apps.powerapps.com/play/e3fdef18-2be5-4d25-ba04-3edf6411c0aa?tenantId=afa78dff-85bb-46f3-b036-b43bcf79c497";
var id = getQueryVariable("ID");
if (id) {
    window.location.replace(play+"&AssetID="+id);
}
else {
    window.location.replace(play);
}
</script>
</head>
<body>
<form id="form1" runat="server">
</form>
</body>
</html>


Change SharePoint’s NewFormUrl and EditFormUrl with PnP-PowerShell

Connect-PnPOnline -url https://johnliu365.sharepoint.com/
$list = Get-PnPList "Asset"
$list.ContentTypes[0]

# assuming list content type 0 == Item
# otherwise, you need to set this on the correct list content type

$list.ContentTypes[0].EditFormUrl = "SitePages/AssetEditForm.aspx"
$list.ContentTypes[0].NewFormUrl = "SitePages/AssetEditForm.aspx"
$list.ContentTypes[0].Update($false)

# no update child content types
# you may have to set $true if you have content types in your list

$context = Get-PnPContext
$context.ExecuteQuery()


Redirecting Magic

This will change the “New”, “Edit” behaviour on the SharePoint modern or classic list. Also, this will work in the edit menu. So if you want to redirect your users to the new Power Apps experience, this is one way to change the link everywhere.

Reference Reading

Please also read April Dunnam’s two part series on how to customize SharePoint via modern ListView JSON

One Flow to handle them all - Part 2 figuring out the changes

/

I had previously wrote a method of using Microsoft Flow to subscribe to all the lists in a SharePoint site - and how to have them all call a second Request flow to execute on File changed in any of these lists.

http://johnliu.net/blog/2019/3/one-flow-to-handle-them-all-how-to-subscribe-to-multiple-sharepoint-lists-with-one-flow

In that blog post, as all good trilogies go - we finished at a spot where we have gotten a webhook event call, but did not proceed to continue working out which items actually changed.

The full Get Changes Method

In the long delay between my #FlowNinja tweet and the blog posts being written, my friend @ISSPDEV couldn’t wait for the next part he went ahead with this. He wrote in much detail.

https://gist.github.com/zplume/1baf04cc05927b57a5da248454b15dcc 

Implementing the best practices from the SharePoint team

https://docs.microsoft.com/en-us/sharepoint/dev/apis/webhooks/get-started-webhooks


The Short Cut Method

I provide a second method, perhaps one that is much shorter for a Power User to implement. I worked on this with @ArtsyPowerApper and figured out a simpler method.

First upload a file, then copy the entire trigger body JSON.

Next, after the validation steps we had in the initial handler flow - we add a Parse JSON.

Use the trigger body JSON to create the schema.

By telling SharePoint to give us files updated in that library in the last 10 seconds, we fetch the changed items directly.

Thoughts

In my tests - I have tried 5 seconds and it seems good enough, but I extended it to 10 seconds in case there’s an large upload or delay.

Short cut method has a small risk of firing an event twice on the same item if it was somehow updated rapidly twice.

Also, unlike the complete Get Changes method, we can’t see System Update, Deletion, Rename, Permission Update events. So this method is only good for Item/File Created and Updated events.

But the steps is far simpler with a Parse JSON and one Get Files (with an OData filter)


A Power User friendly method of connecting hundreds of sites, list and libraries to a single Flow

/
Photo by Sebastian Boring on Unsplash

Photo by Sebastian Boring on Unsplash

We need to get this disclaimer out of the way first - this approach uses SharePoint Designer workflow as a glue - the main reason for this is because SharePoint Designer workflows (being old generation technology) can be deployed across site collections via PowerShell scripting without too much trouble, as long as they remain small and don’t need regular maintenance.

This approach isn’t necessary for deploying a Flow to a dozen sites. But if we are talking potentially hundreds of project sites. Those become scenarios that aren’t currently covered well, and would need workarounds such as this one.

Let me know what you think in the comments.

The Problem

There are many ways we can view this problem of “deploying Flows across many lists, libraries, sites and site collections”.

This problem has several issues we may not need to consider:

  • First, we have Export and Import, as well as Send Flow as a Copy. These are simple, manual steps. That can’t be automated.

  • But at some point - we will hit the 600 Flow limit (this isn’t per environment, this is per account)

  • We will need to reconfigure URLs specific to each Flow.

  • As these copied Flows are customized from the master version which makes updating and maintaining them very tricky.

  • When redeploy a Flow - existing connections must be maintained.

  • When deploying Flow to a new site - new connections must be configured (or existing connections re-used).

  • If we are ever in a situation where we need to deploy a new update an existing Flow but now with additional new Connections. God have mercy.

  • If we think perhaps just delete existing Flows and re-deploy as new, we’d kill any existing runs.

So there are alternate approaches as well… what if we just don’t deploy hundreds of copies of the same Flow?


A new solution?

The method in this blog post is a different idea - we will borrow SharePoint Designer workflow’s various tools for deploying cross site collections and have it invoke the same Flow to perform the main task.

This was posted as #FlowNinja hack 91

https://twitter.com/johnnliu/status/1121055884749053953


Let’s begin

Warning: this hack involves the use of SharePoint Designer. Now before you all run off screaming, there's a good reason why we want to try this method: mass deployments. So here we go

1. Make a Request trigger Flow. With a simple compose.
2. make SPd 2013 reusable workflow

3. first action - add SPd HTTP web request to the Flow request trigger
4. need to configure both request body and request header

5. If we don't blank out authorization this happens DirectApiAuthorizationRequired: The request must be authenticated only by Shared Access scheme

Add SPd Workflow to a Library and a List

6. now publish and add this to a List and a Document library
7. upload a file see SPd workflow trigger
8. see HTTP Request flow trigger

The Test is successful. Next copy the JSON we received in Flow

9. add Request schema from the previous successful run
10. change Compose to match request body

11. add a new item to a sharepoint list (that has the same SPd workflow attached)
12. the same Flow re-triggers.

Take a breather, have some tea

13. at this point, there's several more things to do - consider whether we want excessive logging. SPd workflow is simple - once it is working there's not a lot more to tweak, perhaps we want more context values. While still within this site collection, republish will update it.

Package the SPd Workflow for cross site collection

Let's package this workflow for cross site collection warp jump.

14 remove workflow association to list Achievements - because we don't have that list everywhere, only Documents. 15 export WSP to desktop

16 go to new site collection - this can be SharePoint or group sites or project sites.
17 site settings - upload solution to gallery - activate solution
18 site settings - site features -activate feature (this associates with Documents)

19 test with upload new file in new site collection
20 flow triggers, context of Flow is new site url.

21. note there was an error with the SPd workflow after it called HTTP - because I didn’t activate the SharePoint Workflow feature, Workflow History list isn’t available in the new site collection.

Consider Step #13 and whether you’d want to delete all the logging steps before exporting Flow

21. now we are in Flow. Re-fetch the item and go crazy.

Yes, in Step #4 we deliberately chose the context values that will allow us to re-configure Flow to pick up the item

Conclusion

Con:

  • The pain of writing a SharePoint Designer workflow again

  • Difficult to update deployed SPd workflows - but the one we have here is very simple (two build dictionary and a HTTP request)

Pros:

  • A simple lightweight SharePoint Designer workflow can be deployed across many site collections via PnP-PowerShell, or as part of PnP-Provisioning.

    Activate the solution, and then activate the feature (this will create the workflow associations)

  • All the events from associated SharePoint libraries will call a single Microsoft Flow - this is where we can customize to our heart’s content.

I don’t think this approach should be written off - it should be evaluated and may suit your situation well. I also think in a possible future when cross-site Flow deployments can be done easier - we may have different Flow triggers invoking our one Flow. So there exists an upgrade path forward.