Microsoft Power Automate (Microsoft Flow)

In this blog post I will show you how you can quickly build a public facing file upload tool using Dropzone.js and the Power Automate action When a HTTP Request is Received (Premium license required). Files dropped in by a browser can easily be uploaded to OneDrive or a SharePoint document library.

Introduction

You could use this tool to allow customers or partners to easily transfer files into your environment.

The flow includes some basic file type checking to ensure that certain file types are not uploaded. If you would like to try a demo of the client side of the flow, check out this page. Here is a screenshot of an example of how it would look.

Files are simply dropped on to the page and then they are uploaded to a SharePoint document library, immediately after. This flow consists of two parts and the trigger for both of them is When a HTTP Request is received:

• A HTTP Server that serves the HTML and Javascript to the browser
  (Can be served from Power Automate or any other web server).
• Another HTTP Server Flow which receives the uploaded files from the client and uploads them to SharePoint.

The Server Component

The first step is to build the server component. This will ingest the uploads from the client and the flow for this is very simple, here is a screenshot:

Server Side Flow Code and Implementation

To make it as easy as possible for you to build this flow, I have put all of the actions into a scope which you can copy into one of your own flows, so to implement this for yourself, follow these steps:

• Create a new flow and use When a HTTP request is received as the trigger.
• Modify the HTTP method to POST.
• Copy the scope code below and paste it into your flow from My Clipboard.
• Modify the settings compose action to include your SharePoint site and document library path.
• Optional: Modify the UnsafeFileTypes array to your liking.
• Save the Flow and then copy the HTTP POST URL from the first step, you will need it in the next step.

{
  "id": "6c9bd70a-d149-40b6-ad46-a6eb-cfa45e3a",
  "brandColor": "#8C3900",
  "connectionReferences": {
    "shared_sharepointonline": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
      }
    }
  },
  "connectorDisplayName": "Control",
  "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
  "isTrigger": false,
  "operationName": "FileUpload",
  "operationDefinition": {
    "type": "Scope",
    "actions": {
      "Settings": {
        "type": "Compose",
        "inputs": {
          "siteAddress": "https://accendo1.sharepoint.com/sites/PowerAutomateText",
          "documentLibrary": "/Web Uploads"
        },
        "runAfter": {},
        "description": "Used to configure the settings for the remainder of the flow",
        "trackedProperties": {
          "filename": "@{replace(substring(triggerBody()['$multipart'][0]['headers']['Content-Disposition'],add(indexOf(triggerBody()['$multipart'][0]['headers']['Content-Disposition'], 'filename=\"'), 10)), '\"', '')}"
        }
      },
      "UnsafeFileTypes": {
        "type": "Compose",
        "inputs": [
          ".BAT",
          ".CHM",
          ".CMD",
          ".COM",
          ".CPL",
          ".CRT",
          ".EXE",
          ".HLP",
          ".HTA",
          ".INF",
          ".INS",
          ".ISP",
          ".JS",
          ".JSE",
          ".LNK",
          ".MSC",
          ".MSI",
          ".MSP",
          ".MST",
          ".PCD",
          ".PIF",
          ".REG",
          ".SCR",
          ".SCT",
          ".SHB",
          ".SHS",
          ".URL",
          ".VB",
          ".VBE",
          ".VBS",
          ".WSC",
          ".WSF",
          ".WSH"
        ],
        "runAfter": {
          "Settings": [
            "Succeeded"
          ]
        },
        "description": "List of file types to prohibit from upload"
      },
      "UnsafeAttachmentFilter": {
        "type": "Query",
        "inputs": {
          "from": "@outputs('UnsafeFileTypes')",
          "where": "@endswith(toUpper(actions('settings')?['trackedProperties']['filename']), item())"
        },
        "runAfter": {
          "UnsafeFileTypes": [
            "Succeeded"
          ]
        }
      },
      "Condition": {
        "type": "If",
        "expression": {
          "equals": [
            "@length(body('UnsafeAttachmentFilter'))",
            0
          ]
        },
        "actions": {
          "Create_file": {
            "type": "OpenApiConnection",
            "inputs": {
              "host": {
                "connectionName": "shared_sharepointonline",
                "operationId": "CreateFile",
                "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline"
              },
              "parameters": {
                "dataset": "@outputs('Settings')['siteAddress']",
                "folderPath": "@outputs('Settings')['documentLibrary']",
                "name": "@actions('settings')?['trackedProperties']['filename']",
                "body": "@if\r\n(\r\n  contains(triggerBody()['$multipart'][0]['body'], '$content'),\r\n  base64ToBinary(triggerBody()['$multipart'][0]['body']['$content']),\r\n  triggerBody()['$multipart'][0]['body']\r\n)"
              },
              "authentication": "@parameters('$authentication')"
            },
            "runAfter": {},
            "runtimeConfiguration": {
              "contentTransfer": {
                "transferMode": "Chunked"
              }
            }
          }
        },
        "runAfter": {
          "UnsafeAttachmentFilter": [
            "Succeeded"
          ]
        },
        "description": "Check if the file has an unsafe extension"
      },
      "Response": {
        "type": "Response",
        "kind": "http",
        "inputs": {
          "statusCode": "@if(equals(length(body('UnsafeAttachmentFilter')), 0), 200, 403)",
          "body": "@if(equals(length(body('UnsafeAttachmentFilter')), 0), 'File Upload Succesful', 'File Type Not Allowed')"
        },
        "runAfter": {
          "Condition": [
            "Succeeded"
          ]
        }
      }
    },
    "runAfter": {}
  }
}

Implement the Browser Facing Interface

The page the client sees can be served either from a HTTP flow in Power Automate, or a static page.

Create a Static HTML Page

To make a Static Page you can:

• Copy the HTML source code below.
• Modify line 48 so that it contains the URL to the flow created in the previous section.
• Upload to your web server.
• Optional
  • Modify Line 73 to change the header.
  • Modify or remove lines 93-96 to change the reference to this blog post.

That is all there is to it.

<!doctype html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="Example of how to use Dropzone to upload files to a SharePoint document library with Power Automate">
  <title>Upload file to SharePoint with Power Automate and dropzone</title>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
  <style>
    body {
      background: #f3f4f5;
      height: 100%;
      color: #646c7f;
      line-height: 1.4rem;
      font-family: Roboto, "Open Sans", sans-serif;
      font-size: 20px;
      font-weight: 300;
      text-rendering: optimizeLegibility
    }

    h1 {
      text-align: center
    }

    .dropzone {
      background: #fff;
      border-radius: 5px;
      border: 2px dashed #0087f7;
      border-image: none;
      max-width: 500px;
      margin-left: auto;
      margin-right: auto
    }
  </style>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/dropzone/5.9.2/basic.min.css" integrity="sha512-MeagJSJBgWB9n+Sggsr/vKMRFJWs+OUphiDV7TJiYu+TNQD9RtVJaPDYP8hA/PAjwRnkdvU+NsTncYTKlltgiw==" crossorigin="anonymous" />
</head>

<body class="text-center">
  <script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
  <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/dropzone/5.9.2/min/dropzone.min.js" integrity="sha512-VQQXLthlZQO00P+uEu4mJ4G4OAgqTtKG1hri56kQY1DtdLeIqhKUp9W/lllDDu3uN3SnUNawpW7lBda8+dSi7w==" crossorigin="anonymous"></script>
  <script type="text/javascript">
	Dropzone.autoDiscover = false;
	$(document).ready(function () {
		$("#demo-upload").dropzone({
		  url: "https://prod-93.westeurope.logic.azure.com:443/workflows/8b289c3a0a29469aaae2410175f212ac/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=hiEI5Dwv-bWRVUB-h5gvvC2LdDBD9WavpLBUm4JGIvM",
		  previewTemplate: document.querySelector('#preview-template').innerHTML,
		  parallelUploads: 1,
		  thumbnailHeight: 200,
		  thumbnailWidth: 200,
		  maxFilesize: 10,
		  filesizeBase: 1000,
		  thumbnail: function(file, dataUrl) {
			if (file.previewElement) {
			  file.previewElement.classList.remove("dz-file-preview");
			  var images = file.previewElement.querySelectorAll(
				"[data-dz-thumbnail]");
			  for (var i = 0; i < images.length; i++) {
				var thumbnailElement = images[i];
				thumbnailElement.alt = file.name;
				thumbnailElement.src = dataUrl;
			  }
			  setTimeout(function() {
				file.previewElement.classList.add("dz-image-preview");
			  }, 1);
			}
		  }
		});
	});
  </script>
  <h1>File Upload Demo using DropzoneJS and Power Automate</h1>
  <SECTION>
    <DIV id="dropzone">
      <FORM id="demo-upload" 
	        class="dropzone needsclick" 
			action="/power_automate/" 
			enctype="multipart/form-data" 
			method="post">
        <DIV class="dz-message needsclick">
          Drop files here or click to upload.<BR>
          <SPAN class="note needsclick"> Selected files are uploaded to a
            SharePoint document library</SPAN>
        </DIV>
      </FORM>
    </DIV>
  </SECTION>

  <br />
  <hr size="3" noshade color="#F00000">

  <div style="font-size: 0.8em;">
    <p>For more details on how this works, please check out the blog post <a href="https://www.tachytelic.net/2021/06/power-automate-file-upload-dropzonejs" target="_blank">the blog post</a>, which provides details on
      how to configure the Power Automate Flow</p>
  </div>
  <DIV id="preview-template" style="display: none;">
    <DIV class="dz-preview dz-file-preview">
      <DIV class="dz-image"><IMG data-dz-thumbnail=""></DIV>
      <DIV class="dz-details">
        <DIV class="dz-size"><SPAN data-dz-size=""></SPAN></DIV>
        <DIV class="dz-filename"><SPAN data-dz-name=""></SPAN></DIV>
      </DIV>
      <DIV class="dz-progress"><SPAN class="dz-upload" data-dz-uploadprogress=""></SPAN></DIV>
      <DIV class="dz-error-message"><SPAN data-dz-errormessage=""></SPAN></DIV>
      <div class="dz-success-mark">
        <svg width="54px" height="54px" viewBox="0 0 54 54" version="1.1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:sketch="http://www.bohemiancoding.com/sketch/ns">
          <title>Check</title>
          <desc>Created with Sketch.</desc>
          <defs></defs>
          <g id="Page-1" stroke="none" stroke-width="1" fill="none" fill-rule="evenodd" sketch:type="MSPage">
            <path d="M23.5,31.8431458 L17.5852419,25.9283877 C16.0248253,24.3679711 13.4910294,24.366835 11.9289322,25.9289322 C10.3700136,27.4878508 10.3665912,30.0234455 11.9283877,31.5852419 L20.4147581,40.0716123 C20.5133999,40.1702541 20.6159315,40.2626649 20.7218615,40.3488435 C22.2835669,41.8725651 24.794234,41.8626202 26.3461564,40.3106978 L43.3106978,23.3461564 C44.8771021,21.7797521 44.8758057,19.2483887 43.3137085,17.6862915 C41.7547899,16.1273729 39.2176035,16.1255422 37.6538436,17.6893022 L23.5,31.8431458 Z M27,53 C41.3594035,53 53,41.3594035 53,27 C53,12.6405965 41.3594035,1 27,1 C12.6405965,1 1,12.6405965 1,27 C1,41.3594035 12.6405965,53 27,53 Z" id="Oval-2" stroke-opacity="0.198794158" stroke="#747474" fill-opacity="0.816519475" fill="#FFFFFF" sketch:type="MSShapeGroup"></path>
          </g>
        </svg>
      </div>
      <div class="dz-error-mark">
        <svg width="54px" height="54px" viewBox="0 0 54 54" version="1.1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:sketch="http://www.bohemiancoding.com/sketch/ns">
          <title>error</title>
          <desc>Created with Sketch.</desc>
          <defs></defs>
          <g id="Page-1" stroke="none" stroke-width="1" fill="none" fill-rule="evenodd" sketch:type="MSPage">
            <g id="Check-+-Oval-2" sketch:type="MSLayerGroup" stroke="#747474" stroke-opacity="0.198794158" fill="#FFFFFF" fill-opacity="0.816519475">
              <path d="M32.6568542,29 L38.3106978,23.3461564 C39.8771021,21.7797521 39.8758057,19.2483887 38.3137085,17.6862915 C36.7547899,16.1273729 34.2176035,16.1255422 32.6538436,17.6893022 L27,23.3431458 L21.3461564,17.6893022 C19.7823965,16.1255422 17.2452101,16.1273729 15.6862915,17.6862915 C14.1241943,19.2483887 14.1228979,21.7797521 15.6893022,23.3461564 L21.3431458,29 L15.6893022,34.6538436 C14.1228979,36.2202479 14.1241943,38.7516113 15.6862915,40.3137085 C17.2452101,41.8726271 19.7823965,41.8744578 21.3461564,40.3106978 L27,34.6568542 L32.6538436,40.3106978 C34.2176035,41.8744578 36.7547899,41.8726271 38.3137085,40.3137085 C39.8758057,38.7516113 39.8771021,36.2202479 38.3106978,34.6538436 L32.6568542,29 Z M27,53 C41.3594035,53 53,41.3594035 53,27 C53,12.6405965 41.3594035,1 27,1 C12.6405965,1 1,12.6405965 1,27 C1,41.3594035 12.6405965,53 27,53 Z" id="Oval-2" sketch:type="MSShapeGroup"></path>
            </g>
          </g>
        </svg>
      </div>
</body>

</html>

Create a Power Automate Flow

If you do not have a web server you can use to serve the static page, then you can use a flow to provide the browser interface instead. Here is how to create it:

• Create a new flow and use When a HTTP request is received as the trigger.
  • Change the method to GET
• Copy the scope code below and paste it to your flow.
• Modify the settings action to suit your requirements. You must update the URL parameter to the URL of the server flow defined in the previous section.

Here is the scope code:

{
  "id": "14df8e31-7d3a-4dbc-9151-4a25-f8d8b4e5",
  "brandColor": "#8C3900",
  "connectionReferences": {
    "shared_sendmail": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_sendmail/connections/shared-sendmail-510245c4-46f9-4771-9ab0-d4d8-f8faacd5"
      }
    }
  },
  "connectorDisplayName": "Control",
  "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
  "isTrigger": false,
  "operationName": "DropzoneBrowserInterface",
  "operationDefinition": {
    "type": "Scope",
    "actions": {
      "settings": {
        "type": "Compose",
        "inputs": {
          "headerText": "File Upload Demo using DropzoneJS and Power Automate",
          "url": "https://prod-93.westeurope.logic.azure.com:443/workflows/8b289c3a0a29469aaae2410175f212ac/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=hiEI5Dwv-bWRVUB-h5gvvC2LdDBD9WavpLBUm4JGIvM",
          "thumbnailHeight": "200",
          "thumbnailWidth": "200",
          "maxFilesize": "10"
        },
        "runAfter": {}
      },
      "HTML": {
        "type": "Compose",
        "inputs": "<!doctype html>\n<html lang=\"en\">\n\n<head>\n  <meta charset=\"utf-8\">\n  <meta name=\"viewport\" content=\"width=device-width, initial-scale=1, shrink-to-fit=no\">\n  <meta name=\"description\" content=\"Example of how to use Dropzone to upload files to a SharePoint document library with Power Automate\">\n  <title>Upload file to SharePoint with Power Automate and dropzone</title>\n  <link rel=\"stylesheet\" href=\"https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css\" integrity=\"sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh\" crossorigin=\"anonymous\">\n  <style>\n    body {\n      background: #f3f4f5;\n      height: 100%;\n      color: #646c7f;\n      line-height: 1.4rem;\n      font-family: Roboto, \"Open Sans\", sans-serif;\n      font-size: 20px;\n      font-weight: 300;\n      text-rendering: optimizeLegibility\n    }\n\n    h1 {\n      text-align: center\n    }\n\n    .dropzone {\n      background: #fff;\n      border-radius: 5px;\n      border: 2px dashed #0087f7;\n      border-image: none;\n      max-width: 500px;\n      margin-left: auto;\n      margin-right: auto\n    }\n  </style>\n  <link rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/dropzone/5.9.2/basic.min.css\" integrity=\"sha512-MeagJSJBgWB9n+Sggsr/vKMRFJWs+OUphiDV7TJiYu+TNQD9RtVJaPDYP8hA/PAjwRnkdvU+NsTncYTKlltgiw==\" crossorigin=\"anonymous\" />\n</head>\n\n<body class=\"text-center\">\n  <script src=\"https://code.jquery.com/jquery-3.4.1.slim.min.js\" integrity=\"sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n\" crossorigin=\"anonymous\"></script>\n  <script src=\"https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js\" integrity=\"sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo\" crossorigin=\"anonymous\"></script>\n  <script src=\"https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js\" integrity=\"sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6\" crossorigin=\"anonymous\"></script>\n  <script src=\"https://cdnjs.cloudflare.com/ajax/libs/dropzone/5.9.2/min/dropzone.min.js\" integrity=\"sha512-VQQXLthlZQO00P+uEu4mJ4G4OAgqTtKG1hri56kQY1DtdLeIqhKUp9W/lllDDu3uN3SnUNawpW7lBda8+dSi7w==\" crossorigin=\"anonymous\"></script>\n  <script type=\"text/javascript\">\n    Dropzone.autoDiscover = false;\n    $(document).ready(function () {\n        $(\"#demo-upload\").dropzone({\n          url: \"@{outputs('settings')['url']}\",\n          previewTemplate: document.querySelector('#preview-template').innerHTML,\n          parallelUploads: 1,\n          thumbnailHeight: @{outputs('settings')['thumbnailHeight']},\n          thumbnailWidth: @{outputs('settings')['thumbnailWidth']},\n          maxFilesize: @{outputs('settings')['maxFilesize']},\n          filesizeBase: 1000,\n          thumbnail: function(file, dataUrl) {\n            if (file.previewElement) {\n              file.previewElement.classList.remove(\"dz-file-preview\");\n              var images = file.previewElement.querySelectorAll(\n                \"[data-dz-thumbnail]\");\n              for (var i = 0; i < images.length; i++) {\n                var thumbnailElement = images[i];\n                thumbnailElement.alt = file.name;\n                thumbnailElement.src = dataUrl;\n              }\n              setTimeout(function() {\n                file.previewElement.classList.add(\"dz-image-preview\");\n              }, 1);\n            }\n          }\n        });\n    });\n  </script>\n  <h1>@{outputs('settings')['headerText']}</h1>\n  <SECTION>\n    <DIV id=\"dropzone\">\n      <FORM id=\"demo-upload\" \n            class=\"dropzone needsclick\" \n            action=\"/power_automate/\" \n            enctype=\"multipart/form-data\" \n            method=\"post\">\n        <DIV class=\"dz-message needsclick\">\n          Drop files here or click to upload.<BR>\n          <SPAN class=\"note needsclick\"> Selected files are uploaded to a\n            SharePoint document library</SPAN>\n        </DIV>\n      </FORM>\n    </DIV>\n  </SECTION>\n\n  <br />\n  <hr size=\"3\" noshade color=\"#F00000\">\n\n  <div style=\"font-size: 0.8em;\">\n    <p>For more details on how this works, please check out <a href=\"https://www.tachytelic.net/2021/06/power-automate-file-upload-dropzonejs\" target=\"_blank\">the blog post</a>, which provides details on\n      how to configure the Power Automate Flow</p>\n  </div>\n  <DIV id=\"preview-template\" style=\"display: none;\">\n    <DIV class=\"dz-preview dz-file-preview\">\n      <DIV class=\"dz-image\"><IMG data-dz-thumbnail=\"\"></DIV>\n      <DIV class=\"dz-details\">\n        <DIV class=\"dz-size\"><SPAN data-dz-size=\"\"></SPAN></DIV>\n        <DIV class=\"dz-filename\"><SPAN data-dz-name=\"\"></SPAN></DIV>\n      </DIV>\n      <DIV class=\"dz-progress\"><SPAN class=\"dz-upload\" data-dz-uploadprogress=\"\"></SPAN></DIV>\n      <DIV class=\"dz-error-message\"><SPAN data-dz-errormessage=\"\"></SPAN></DIV>\n      <div class=\"dz-success-mark\">\n        <svg width=\"54px\" height=\"54px\" viewBox=\"0 0 54 54\" version=\"1.1\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" xmlns:sketch=\"http://www.bohemiancoding.com/sketch/ns\">\n          <title>Check</title>\n          <desc>Created with Sketch.</desc>\n          <defs></defs>\n          <g id=\"Page-1\" stroke=\"none\" stroke-width=\"1\" fill=\"none\" fill-rule=\"evenodd\" sketch:type=\"MSPage\">\n            <path d=\"M23.5,31.8431458 L17.5852419,25.9283877 C16.0248253,24.3679711 13.4910294,24.366835 11.9289322,25.9289322 C10.3700136,27.4878508 10.3665912,30.0234455 11.9283877,31.5852419 L20.4147581,40.0716123 C20.5133999,40.1702541 20.6159315,40.2626649 20.7218615,40.3488435 C22.2835669,41.8725651 24.794234,41.8626202 26.3461564,40.3106978 L43.3106978,23.3461564 C44.8771021,21.7797521 44.8758057,19.2483887 43.3137085,17.6862915 C41.7547899,16.1273729 39.2176035,16.1255422 37.6538436,17.6893022 L23.5,31.8431458 Z M27,53 C41.3594035,53 53,41.3594035 53,27 C53,12.6405965 41.3594035,1 27,1 C12.6405965,1 1,12.6405965 1,27 C1,41.3594035 12.6405965,53 27,53 Z\" id=\"Oval-2\" stroke-opacity=\"0.198794158\" stroke=\"#747474\" fill-opacity=\"0.816519475\" fill=\"#FFFFFF\" sketch:type=\"MSShapeGroup\"></path>\n          </g>\n        </svg>\n      </div>\n      <div class=\"dz-error-mark\">\n        <svg width=\"54px\" height=\"54px\" viewBox=\"0 0 54 54\" version=\"1.1\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" xmlns:sketch=\"http://www.bohemiancoding.com/sketch/ns\">\n          <title>error</title>\n          <desc>Created with Sketch.</desc>\n          <defs></defs>\n          <g id=\"Page-1\" stroke=\"none\" stroke-width=\"1\" fill=\"none\" fill-rule=\"evenodd\" sketch:type=\"MSPage\">\n            <g id=\"Check-+-Oval-2\" sketch:type=\"MSLayerGroup\" stroke=\"#747474\" stroke-opacity=\"0.198794158\" fill=\"#FFFFFF\" fill-opacity=\"0.816519475\">\n              <path d=\"M32.6568542,29 L38.3106978,23.3461564 C39.8771021,21.7797521 39.8758057,19.2483887 38.3137085,17.6862915 C36.7547899,16.1273729 34.2176035,16.1255422 32.6538436,17.6893022 L27,23.3431458 L21.3461564,17.6893022 C19.7823965,16.1255422 17.2452101,16.1273729 15.6862915,17.6862915 C14.1241943,19.2483887 14.1228979,21.7797521 15.6893022,23.3461564 L21.3431458,29 L15.6893022,34.6538436 C14.1228979,36.2202479 14.1241943,38.7516113 15.6862915,40.3137085 C17.2452101,41.8726271 19.7823965,41.8744578 21.3461564,40.3106978 L27,34.6568542 L32.6538436,40.3106978 C34.2176035,41.8744578 36.7547899,41.8726271 38.3137085,40.3137085 C39.8758057,38.7516113 39.8771021,36.2202479 38.3106978,34.6538436 L32.6568542,29 Z M27,53 C41.3594035,53 53,41.3594035 53,27 C53,12.6405965 41.3594035,1 27,1 C12.6405965,1 1,12.6405965 1,27 C1,41.3594035 12.6405965,53 27,53 Z\" id=\"Oval-2\" sketch:type=\"MSShapeGroup\"></path>\n            </g>\n          </g>\n        </svg>\n      </div>\n</body>\n\n</html>",
        "runAfter": {
          "settings": [
            "Succeeded"
          ]
        }
      },
      "Response": {
        "type": "Response",
        "kind": "http",
        "inputs": {
          "statusCode": 200,
          "headers": {
            "Content-Type": "text/html; charset=UTF-8"
          },
          "body": "@outputs('HTML')"
        },
        "runAfter": {
          "HTML": [
            "Succeeded"
          ]
        }
      }
    },
    "runAfter": {}
  }
}

Your flow should look like this:

Conclusion

There are many ways the HTTP action can be used and this is a neat little flow that provides a super simple method to upload files into your Office 365 environment for external partners and customers. I’d be really interested to hear if you implement this and what you are using it for.

This post is part of a series of blog posts which demonstrate how to use the SharePoint Batch API in Power Automate. In this post I will demonstrate how to create SharePoint items in batches of up to 1,000. The main advantages of using the batch API are:

• Drastically improved performance.
• Potentially 1,000 times reduction in the use of Power Automate API actions.

This Flow is very similar to the post I wrote on batch deleting items from a SharePoint list.

Example Data

In this flow I am going to import an Excel file containing 3 columns and 15,000 rows of data to a SharePoint list with the same configuration:

The data source is unimportant. Any JSON array containing the data that you want to insert into SharePoint will be fine.

The Standard Power Automate Method

The most often used method to import rows of data into SharePoint with Power Automate is to use an apply to each loop to iterate through a JSON array using a “Create Item” action within the loop. It would look something like this:

The great thing about this method is that it is very simple to do, so if you are new to Power Automate it’s a great way to get data from a variety of sources into a SharePoint list.

For small datasets, this method is perfect. But it does have two problems:

• Performance is quite slow. A large dataset will take many hours to complete.
• A large number of rows will consume many API actions from your daily allowance.

I tested this flow using the first 1,000 items from the Excel table and it took 7 minutes and 15 seconds to complete. So to do all 15,000 rows would have taken approximately 1 hour and 50 minutes.

I’m not going to go into too much detail about the method above, because if you’re reading this, you’re probably trying to find a faster method!

Batch Create SharePoint List Items

It is possible, using the SharePoint $batch API to bundle up to 1,000 actions to SharePoint in a single HTTP request. This provides drastically increased performance and reduces the number of API actions required by a factor of 1,000.

However, it is much more complicated to implement. So if you need to increase performance, it is worth the effort.

To create exactly the same 1,000 rows of data using the batch method took only 1 minute and 46 seconds. So the total time to do all 15,000 records would be approximately 26 minutes. Around 1.5 Hours faster than the standard method.

Batch API Flow Overview

As per my method for deleting items from a SharePoint list, I have created a flow which should be simple for you to modify. Here is a screenshot of the flow:

The Flow Follows this Process:

• Data is retrieved from the data source
• A variable named LoopControl is defined which is used to determine when the Do Until loop should end.
• The Scope Create SharePoint Items begins:
  • A compose action called Settings defines some settings for the flow.
  • A Select action is used to reformat the incoming data to shape it correctly for the list.
  • A template is defined for the change actions.
  • A do until loop iterates through all of the JSON data in batches until no more remains.
  • A select action is used to reformat the array items.
  • A compose action is used to join the output of the select.
  • The output of the compose is sent to SharePoint and contains the batch of actions to be completed.

Still not fast enough?

The flow above, although much more complicated than the standard method provides a great performance benefit. What I noticed, is that the performance bottleneck has shifted from Power Automate to SharePoint.

So I modified the flow, which adds a small amount of extra complexity, but doubles the effective speed:

Method	Time to Create 1k List Items	Difference
Standard Apply/Each Loop	435 seconds
Batch API	106 seconds	-329
Batch API Threaded	54 seconds	-383

This is achieved by splitting each batch of 1,000 into two simultaneous batches of 500 and running them in an apply to each loop with concurrency enabled. The apply to each loop uses an expression as its source:

if
(
  equals(variables('LoopControl'), 0),
  json('[]'),
  createArray
  (
    take(body('Select'), div(length(body('Select')), 2)),
    skip(body('Select'), div(length(body('Select')), 2))
  )
)

This expression does a couple of things:

• Checks if there are any remaining items in the dataset, and if not returns an empty array.
• If there are items remaining it determines the length of the items and splits the array into two halves.
• The two halves are then run in parallel which doubles the speed of the SharePoint batch command.

Flow Code and Implementation

Because this flow is somewhat complicated I am providing a number of options for you to download and implement it.

Sample Data and Flow Exports

Download my sample Excel Data File and Exports of the flows to import into your environment here:

• Sample Excel Data
• Create SharePoint Items Batch API Method
• Threaded Create SharePoint Items Batch API Method

To work with my sample data you will need to put the Excel file into your OneDrive and modify the List Rows Present in a Table action accordingly.

After Importing the flow you will need to update the settings compose action at the beginning of the scope with your SharePoint site address and list name:

Which version is right for you depends on how many items you have and what your performance demands are. I recommend choosing the non threaded version unless you require the added performance of the threaded version.

Paste the Scope into a Flow

If you prefer to build your own flow you can do the following:

• Create a variable called LoopControl.
• Copy the Scope code below and paste it into your flow.
• Update the settings compose action.
• Change the Select action GenerateSPData to draw from your own data source, but retain the first column.

The scope code for the non-threaded version:

{
  "id": "8869cabe-b30e-400c-b0f2-3d8a-d53fb36c",
  "brandColor": "#8C3900",
  "connectionReferences": {
    "shared_excelonlinebusiness": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-917cacf6-0b56-4b3f-ae9f-e703589cabec"
      }
    },
    "shared_sharepointonline_1": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
      }
    }
  },
  "connectorDisplayName": "Control",
  "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
  "isTrigger": false,
  "operationName": "Create_SharePoint_Items",
  "operationDefinition": {
    "type": "Scope",
    "actions": {
      "settings": {
        "type": "Compose",
        "inputs": {
          "siteAddress": "https://accendo1.sharepoint.com/sites/PowerAutomateText/",
          "listName": "Invoices Development Test",
          "batchSize": 1000
        },
        "runAfter": {},
        "description": "list to batch delete items from",
        "trackedProperties": {
          "meta": {
            "type": "[email protected]{outputs('settings')?['listName']}ListItem"
          },
          "batchGUID": "@{guid()}",
          "changeSetGUID": "@{guid()}"
        }
      },
      "GenerateSPData": {
        "type": "Select",
        "inputs": {
          "from": "@body('List_rows_present_in_a_table')?['value']",
          "select": {
            "__metadata": "@json(concat('{\"type\":\"SP.Data.', replace(outputs('settings')['listName'], ' ', '_x0020_'), 'ListItem\"}'))",
            "Customer": "@item()?['Customer']",
            "InvNum": "@int(item()?['Invoice Number'])",
            "Invoice_x0020_Amount": "@float(item()?['Invoice Amount'])"
          }
        },
        "runAfter": {
          "settings": [
            "Succeeded"
          ]
        }
      },
      "batchTemplate": {
        "type": "Compose",
        "inputs": "[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nPOST @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items HTTP/1.1\nContent-Type: application/json;odata=verbose\n\n|RowData|\n",
        "runAfter": {
          "GenerateSPData": [
            "Succeeded"
          ]
        }
      },
      "Do_until": {
        "type": "Until",
        "expression": "@equals(variables('LoopControl'), 0)",
        "limit": {
          "count": 60,
          "timeout": "PT1H"
        },
        "actions": {
          "Select": {
            "type": "Select",
            "inputs": {
              "from": "@take(skip(body('GenerateSPData'), mul(outputs('settings')['batchSize'], iterationIndexes('Do_until'))), outputs('settings')['batchSize'])",
              "select": "@replace(outputs('batchTemplate'), '|RowData|', string(item()))"
            },
            "runAfter": {}
          },
          "Set_variable": {
            "type": "SetVariable",
            "inputs": {
              "name": "LoopControl",
              "value": "@length(body('Select'))"
            },
            "runAfter": {
              "Select": [
                "Succeeded"
              ]
            }
          },
          "batchData": {
            "type": "Compose",
            "inputs": "@join(body('Select'), decodeUriComponent('%0A'))",
            "runAfter": {
              "Set_variable": [
                "Succeeded"
              ]
            }
          },
          "SendBatch": {
            "type": "ApiConnection",
            "inputs": {
              "host": {
                "connection": {
                  "name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
                }
              },
              "method": "post",
              "body": {
                "method": "POST",
                "uri": "/_api/$batch",
                "headers": {
                  "X-RequestDigest": "digest",
                  "Content-Type": "multipart/mixed;[email protected]{actions('settings')?['trackedProperties']['batchGUID']}"
                },
                "body": "[email protected]{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('batchData'))}\nContent-Transfer-Encoding: binary\n\[email protected]{outputs('batchData')}\[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\[email protected]{actions('settings')?['trackedProperties']['batchGUID']}--"
              },
              "path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest",
              "authentication": {
                "type": "Raw",
                "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
              }
            },
            "runAfter": {
              "batchData": [
                "Succeeded"
              ]
            },
            "limit": {
              "timeout": "P1D"
            },
            "metadata": {
              "flowSystemMetadata": {
                "swaggerOperationId": "HttpRequest"
              }
            }
          },
          "Results": {
            "type": "Compose",
            "inputs": "@base64ToString(body('sendBatch')['$content'])",
            "runAfter": {
              "SendBatch": [
                "Succeeded"
              ]
            }
          }
        },
        "runAfter": {
          "batchTemplate": [
            "Succeeded"
          ]
        }
      }
    },
    "runAfter": {
      "Initialize_variable": [
        "Succeeded"
      ]
    }
  }
}

Here is the scope code for the threaded version:

{
  "id": "79273084-e0e1-450a-a878-1dda-e7a2b8a4",
  "brandColor": "#8C3900",
  "connectionReferences": {
    "shared_excelonlinebusiness": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-917cacf6-0b56-4b3f-ae9f-e703589cabec"
      }
    },
    "shared_sharepointonline_1": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
      }
    }
  },
  "connectorDisplayName": "Control",
  "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
  "isTrigger": false,
  "operationName": "Create_SharePoint_Items",
  "operationDefinition": {
    "type": "Scope",
    "actions": {
      "settings": {
        "type": "Compose",
        "inputs": {
          "siteAddress": "https://accendo1.sharepoint.com/sites/PowerAutomateText/",
          "listName": "Invoices",
          "batchSize": 1000
        },
        "runAfter": {},
        "description": "list to batch delete items from",
        "trackedProperties": {
          "meta": {
            "type": "[email protected]{outputs('settings')?['listName']}ListItem"
          },
          "batchGUID": "@{guid()}",
          "changeSetGUID": "@{guid()}"
        }
      },
      "GenerateSPData": {
        "type": "Select",
        "inputs": {
          "from": "@body('List_rows_present_in_a_table')?['value']",
          "select": {
            "__metadata": "@json(concat('{\"type\":\"SP.Data.', replace(outputs('settings')['listName'], ' ', '_x0020_'), 'ListItem\"}'))",
            "Customer": "@item()?['Customer']",
            "InvNum": "@int(item()?['Invoice Number'])",
            "Invoice_x0020_Amount": "@float(item()?['Invoice Amount'])"
          }
        },
        "runAfter": {
          "settings": [
            "Succeeded"
          ]
        }
      },
      "batchTemplate": {
        "type": "Compose",
        "inputs": "[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nPOST @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items HTTP/1.1\nContent-Type: application/json;odata=verbose\n\n|RowData|\n",
        "runAfter": {
          "GenerateSPData": [
            "Succeeded"
          ]
        }
      },
      "Do_until": {
        "type": "Until",
        "expression": "@equals(variables('LoopControl'), 0)",
        "limit": {
          "count": 60,
          "timeout": "PT1H"
        },
        "actions": {
          "Select": {
            "type": "Select",
            "inputs": {
              "from": "@take(skip(body('GenerateSPData'), mul(outputs('settings')['batchSize'], iterationIndexes('Do_until'))), outputs('settings')['batchSize'])",
              "select": "@replace(outputs('batchTemplate'), '|RowData|', string(item()))"
            },
            "runAfter": {}
          },
          "Apply_to_each": {
            "type": "Foreach",
            "foreach": "@if\r\n(\r\n  equals(variables('LoopControl'), 0),\r\n  json('[]'),\r\n  createArray\r\n  (\r\n    take(body('Select'), div(length(body('Select')), 2)),\r\n    skip(body('Select'), div(length(body('Select')), 2))\r\n  )\r\n)",
            "actions": {
              "batchData": {
                "type": "Compose",
                "inputs": "@join(item(), decodeUriComponent('%0A'))",
                "runAfter": {}
              },
              "SendBatch": {
                "type": "ApiConnection",
                "inputs": {
                  "host": {
                    "connection": {
                      "name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
                    }
                  },
                  "method": "post",
                  "body": {
                    "method": "POST",
                    "uri": "/_api/$batch",
                    "headers": {
                      "X-RequestDigest": "digest",
                      "Content-Type": "multipart/mixed;[email protected]{actions('settings')?['trackedProperties']['batchGUID']}"
                    },
                    "body": "--batch_@{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: \nContent-Transfer-Encoding: binary\n\[email protected]{outputs('batchData')}\[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\[email protected]{actions('settings')?['trackedProperties']['batchGUID']}--"
                  },
                  "path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest",
                  "authentication": {
                    "type": "Raw",
                    "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
                  }
                },
                "runAfter": {
                  "batchData": [
                    "Succeeded"
                  ]
                },
                "limit": {
                  "timeout": "P1D"
                },
                "metadata": {
                  "flowSystemMetadata": {
                    "swaggerOperationId": "HttpRequest"
                  }
                }
              },
              "Results": {
                "type": "Compose",
                "inputs": "@base64ToString(body('sendBatch')['$content'])",
                "runAfter": {
                  "SendBatch": [
                    "Succeeded"
                  ]
                }
              }
            },
            "runAfter": {
              "Set_variable": [
                "Succeeded"
              ]
            },
            "runtimeConfiguration": {
              "concurrency": {
                "repetitions": 10
              }
            }
          },
          "Set_variable": {
            "type": "SetVariable",
            "inputs": {
              "name": "LoopControl",
              "value": "@length(body('Select'))"
            },
            "runAfter": {
              "Select": [
                "Succeeded"
              ]
            }
          }
        },
        "runAfter": {
          "batchTemplate": [
            "Succeeded"
          ]
        }
      }
    },
    "runAfter": {
      "Initialize_variable": [
        "Succeeded"
      ]
    }
  }
}

To copy the Scopes above simply copy the code into your clipboard, like this:

Conclusion

This is a great way to improve the performance of creating items in a SharePoint list. I have made my best attempt to make it as simple as possible for you to duplicate and I hope you find it useful. Please do let me know in the comments if you have managed to speed up your flows using this method. I’d be interested to know how long it took before and how much time you saved.

The standard actions in Power Automate make it simple to perform CRUD operations against a SharePoint list. But they are designed to be easy to use and do not focus on performance. This post will explain how to delete many items from a SharePoint list using the $batch API.

The standard Power Automate method

A flow to delete items from a SharePoint list is very simple to create and might look something like this:

This flow will work fine, and on a small list it would be perfect, but it has a number of problems:

• If the list contains more than 5,000 items, the Get Items action will have to be placed in a loop.
• It will consume a lot of API actions and may cause you to exceed your daily limit.
• It is very slow.

This flow took four minutes to complete on a small list containing only 500 items.

A few people from my YouTube Channel and others that have commented on the method I used to get more than 5,000 items from a SharePoint list have been looking for a faster and more efficient method.

The SharePoint Batch Method

It is possible to make batch requests of actions for SharePoint to execute using the OData $batch query option. There are many applications for this method.

Because of the increased complexity of this flow, I suggest that you would only use it if you want to speed up the process of deleting items or reduce the number of API actions that you are using.

This flow took 47 seconds to complete on the same list containing 500 items.

To build this flow I used the excellent documentation about the batch method from this website. The batch is a request to the SharePoint API that is formatted in a particular way. Here is an example request which would delete two items:

--batch_9ad2bc1f-9117-4ecf-ae24-d543ac65edda
Content-Type: multipart/mixed; boundary="changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a"
Content-Length: 166999
Content-Transfer-Encoding: binary

--changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a
Content-Type: application/http
Content-Transfer-Encoding: binary

DELETE  https://accendo1.sharepoint.com/sites/PowerAutomateText/_api/web/lists/getByTitle('Invoices')/items(2603) HTTP/1.1
Content-Type: application/json;odata=verbose
Accept: application/json;odata=nometa
IF-MATCH: *

--changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a
Content-Type: application/http
Content-Transfer-Encoding: binary

DELETE  https://accendo1.sharepoint.com/sites/PowerAutomateText/_api/web/lists/getByTitle('Invoices')/items(2604) HTTP/1.1
Content-Type: application/json;odata=verbose
Accept: application/json;odata=nometa
IF-MATCH: *

--changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a--

--batch_9ad2bc1f-9117-4ecf-ae24-d543ac65edda--

The API is quite fussy and if the request is not delivered in exactly the expected format it will fail.

Flow Detail

I have created a flow which, hopefully removes most of the complication associated with this method, which you can copy and paste into your environment. Here is a screenshot of the flow:

The Flow follows this process:

• Creates a variable used to hold the number of items found.
• A compose action defines the settings for the remainder of the flow.
  • The site address
  • The list name
• Another compose action creates a template for the change sets.
• A Do Until loop is started and continues until there are no items left.
  • Get Items collects the list items in batches of 1,000 items (this is the limit for a batch)
  • A select action creates the change set for each item.
  • A compose action joins the change set.
  • Finally a SharePoint HTTP action performs the request.

Flow Code and Implementation

To implement this flow, do the following:

• Create a new flow
• Add an Initialize variable action and name the variable itemCount.
• Copy and paste the Scope code below into your flow.
• Modify the settings action to create the correct values for your environment.
• Optional Step. Add an OData File to the Get Items action to narrow the selection of items to be deleted. Check out this excellent OData Cheat Sheet by Tom Riha for help with that.

Here is the code for the scope, which performs does the deletions.

{
	"id": "519d6f92-4bd5-4ffb-9f91-0086-c5119f9e",
	"brandColor": "#8C3900",
	"connectionReferences": {
		"shared_sharepointonline_1": {
			"connection": {
				"id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
			}
		}
	},
	"connectorDisplayName": "Control",
	"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
	"isTrigger": false,
	"operationName": "Delete_SharePoint_Items",
	"operationDefinition": {
		"type": "Scope",
		"actions": {
			"Do_until": {
				"type": "Until",
				"expression": "@equals(variables('itemCount'), 0)",
				"limit": {
					"count": 60,
					"timeout": "PT1H"
				},
				"actions": {
					"Get_items": {
						"type": "ApiConnection",
						"inputs": {
							"host": {
								"connection": {
									"name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
								}
							},
							"method": "get",
							"path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/tables/@{encodeURIComponent(encodeURIComponent(outputs('settings')['listName']))}/items",
							"queries": {
								"$top": 1000
							},
							"authentication": {
								"type": "Raw",
								"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
							}
						},
						"runAfter": {},
						"trackedProperties": {
							"ItemCount": "@{length(body('Get_items')['value'])}"
						},
						"metadata": {
							"flowSystemMetadata": {
								"swaggerOperationId": "GetItems"
							}
						}
					},
					"Select": {
						"type": "Select",
						"inputs": {
							"from": "@body('Get_items')['value']",
							"select": "@replace(outputs('Template'), '|ID|', string(item()['Id']))"
						},
						"runAfter": {
							"Set_variable": ["Succeeded"]
						},
						"description": "replace(outputs('Template'), '|ID|', string(item()['Id']))"
					},
					"BatchDelete": {
						"type": "Compose",
						"inputs": "@join(body('Select'), decodeUriComponent('%0A'))",
						"runAfter": {
							"Select": ["Succeeded"]
						},
						"description": "join(body('Select'), decodeUriComponent('%0A'))"
					},
					"Send_an_HTTP_request_to_SharePoint": {
						"type": "ApiConnection",
						"inputs": {
							"host": {
								"connection": {
									"name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
								}
							},
							"method": "post",
							"body": {
								"method": "POST",
								"uri": "/_api/$batch",
								"headers": {
									"X-RequestDigest": "digest",
									"Content-Type": "multipart/mixed; [email protected]{actions('settings')?['trackedProperties']['batchGUID']}"
								},
								"body": "[email protected]{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('BatchDelete'))}\nContent-Transfer-Encoding: binary\n\[email protected]{outputs('BatchDelete')}\[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\[email protected]{actions('settings')?['trackedProperties']['batchGUID']}--"
							},
							"path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest",
							"authentication": {
								"type": "Raw",
								"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
							}
						},
						"runAfter": {
							"BatchDelete": ["Succeeded"]
						},
						"limit": {
							"timeout": "P1D"
						},
						"metadata": {
							"flowSystemMetadata": {
								"swaggerOperationId": "HttpRequest"
							}
						}
					},
					"Results": {
						"type": "Compose",
						"inputs": "@base64ToString(body('Send_an_HTTP_request_to_SharePoint')['$content'])",
						"runAfter": {
							"Send_an_HTTP_request_to_SharePoint": ["Succeeded"]
						},
						"description": "base64ToString(body('Send_an_HTTP_request_to_SharePoint')['$content'])"
					},
					"Set_variable": {
						"type": "SetVariable",
						"inputs": {
							"name": "itemCount",
							"value": "@length(body('Get_items')['value'])"
						},
						"runAfter": {
							"Get_items": ["Succeeded"]
						}
					}
				},
				"runAfter": {
					"Template": ["Succeeded"]
				}
			},
			"Template": {
				"type": "Compose",
				"inputs": "[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nDELETE  @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items(|ID|) HTTP/1.1\nContent-Type: application/json;odata=verbose\nAccept: application/json;odata=nometa\nIF-MATCH: *\n",
				"runAfter": {
					"settings": ["Succeeded"]
				}
			},
			"settings": {
				"type": "Compose",
				"inputs": {
					"siteAddress": "",
					"listName": ""
				},
				"runAfter": {},
				"description": "list to batch delete items from",
				"trackedProperties": {
					"batchGUID": "@{guid()}",
					"changeSetGUID": "@{guid()}"
				}
			}
		},
		"runAfter": {
			"Initialize_variable": ["Succeeded"]
		},
		"description": "Delete items from SharePoint using the $batch API method"
	}
}

The first step in the scope is the settings action. Modify this action to the correct values for your environment:

{
  "siteAddress": "https://SharePointSiteName.sharepoint.com/sites/ListName/",
  "listName": "someList"
}

Conclusion

If you need to delete many items from a list in an efficient manner this flow should improve your performance and reduce the number of API actions that your flow consumes. I hope you are able to implement it into your environment easily. Please drop me a question in the comments if you need further help.

I recently did a post on how to sum an array with Power Automate which used an Office Script to calculate the sum of an array. The reason I used an Office Script is because Power Automate does not have a built in action to do this simple operation. But I have now discovered a native method of summing an array in an instant.

The Standard Method

The standard method to sum an array in Power Automate generally follows this pattern:

• Define a variable to hold the total.
• Get an array of items.
• Loop through the array of items.
• For each iteration of the loop, increment the total variable by the current array item in the loop.

This method is fine and works perfectly well. The problem is that the performance is bad. Here is an example of a flow which uses the standard method to sum an “Invoice Amount” from a list with 500 rows:

In the example above, the flow took three minutes to execute. For a simple sum operation I think this is too long.

Instantly Sum an Array – A New Method

I discovered a new, native method to sum an array which I don’t think is widely known. Using the same example list I was able to sum the entire list instantly. The pattern of the flow is:

• Get Items (SharePoint list etc)
• Select action to make a simple array of items.
• Compose action to reformat the array.
• Use the xpath function to sum the array in a single step.

Here is a screenshot of the flow:

With this method I was able to execute the entire flow in 1 second.

Step by Step Explanation

Let’s go through the above example in depth and you will get a better understanding of how it works.

The Get Items action collects the items from the SharePoint list, but the data source is irrelevant. It could be any action that returns an array of results.

The Select action, which is changed to “Text” mode produces a simple array of numbers from the amount column. Example output:

[  3764.58,  2223.05,  4320.49,  544.46,  3464.76 ]

The JSON step (which is a compose action) creates an object to store the number array. Ready for the next step, which requires the array to be formatted in a way that can be converted to XML. The code, which you can copy and paste to your flow is:

{
  "root": {
    "Numbers": @{body('Select')}
  }
}

The result of this compose action is a JSON that has the following format:

{
	"root": {
		"Numbers": [
			3764.58,
			2223.05,
			4320.49,
			544.46,
			3464.76
		]
	}
}

This JSON is then passed into the XML function which transforms it to this:

<root>
  <Numbers>3764.58</Numbers>
  <Numbers>2223.05</Numbers>
  <Numbers>4320.49</Numbers>
  <Numbers>544.46</Numbers>
  <Numbers>3464.76</Numbers>
</root>

Now that the data is in XML format, the powerful xpath expression can be used. xpath has native summing functionality so it can sum the entire array in one action with the following code:

xpath(xml(outputs('JSON')), 'sum(/root/Numbers)')

The output of the action is the sum of the array.

Conclusion

Summing an array using apply to each method is fine if your array is small. But it’s really flawed:

• Even a modest sized array takes long time to process.
• It can easily consume large chunks of your daily request limits.
• It’s very inefficient.

I personally like to avoid apply to each actions whenever possible. The xpath method explained in this post is even faster than the Office Script method I previously posted, I tested it it with 100,000 items and it finished almost instantly.

This post explains how to extract text from Microsoft Word docx files using only built in actions in Power Automate. 3rd party actions exist, which are more probably more sophisticated and can certainly make this process easier.

docx files are actually zip files

The first thing to that is important to understand, is that a word docx file is actually a zip file that contains a number of folders and files. The root of the zip folder contains these files:

The word folder in the root of the zip file contains more files and folders:

Within the word folder, there is a file called document.xml (sometimes documentN.xml) which contains the actual document content, and this is the file which we will parse with Power Automate. My example word document looks like this:

The content of document.xml contains:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<w:document xmlns:wpc="http://schemas.microsoft.com/office/word/2010/wordprocessingCanvas" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:w14="http://schemas.microsoft.com/office/word/2010/wordml" xmlns:w15="http://schemas.microsoft.com/office/word/2012/wordml" xmlns:wpg="http://schemas.microsoft.com/office/word/2010/wordprocessingGroup" xmlns:wpi="http://schemas.microsoft.com/office/word/2010/wordprocessingInk" xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml" xmlns:wps="http://schemas.microsoft.com/office/word/2010/wordprocessingShape" mc:Ignorable="w14 w15 wp14">
  <w:body>
    <w:p xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordml" w:rsidP="65D7FFCD" w14:paraId="2C078E63" wp14:textId="568EF955">
      <w:pPr>
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
      </w:pPr>
      <w:bookmarkStart w:name="_GoBack" w:id="0"/>
      <w:bookmarkEnd w:id="0"/>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="648E6FBB">
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t xml:space="preserve">How to extract </w:t>
      </w:r>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="648E6FBB">
        <w:rPr>
          <w:b w:val="1"/>
          <w:bCs w:val="1"/>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t xml:space="preserve">text </w:t>
      </w:r>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="648E6FBB">
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t>from a Microsoft Word docx file</w:t>
      </w:r>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="4ECA4038">
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t>.</w:t>
      </w:r>
    </w:p>
    <w:p w:rsidR="65D7FFCD" w:rsidP="65D7FFCD" w:rsidRDefault="65D7FFCD" w14:paraId="77484B81" w14:textId="049555E8">
      <w:pPr>
        <w:pStyle w:val="Normal"/>
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
      </w:pPr>
    </w:p>
    <w:p w:rsidR="4ECA4038" w:rsidP="65D7FFCD" w:rsidRDefault="4ECA4038" w14:paraId="10A0E5FC" w14:textId="67D59CCA">
      <w:pPr>
        <w:pStyle w:val="Normal"/>
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
      </w:pPr>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="4ECA4038">
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t>This document explains how to extract text from a Microsoft Word document using standard Power Automate actions. The result isn’t perfect, but it should be good enough for basic usage.</w:t>
      </w:r>
    </w:p>
    <w:sectPr>
      <w:pgSz w:w="12240" w:h="15840" w:orient="portrait"/>
      <w:pgMar w:top="1440" w:right="1440" w:bottom="1440" w:left="1440" w:header="720" w:footer="720" w:gutter="0"/>
      <w:cols w:space="720"/>
      <w:docGrid w:linePitch="360"/>
    </w:sectPr>
  </w:body>
</w:document>

As you can see from the above, the text data is on lines 18,27,34, 41 and 66 of the XML file.

Step 1 – Extract the contents of the Word document

To be able to access the content of document.xml the docx file needs to be extracted first. Use the flow action Extract archive to folder to extract the docx file to a temporary folder. Make sure you set the overwrite option to Yes.

Note: You will not be able to select the word document from the file browser within the action because it filters the available files to show only files with a .zip extension. So you can either:

• Rename the docx file to .zip
• Put in the file path manually or use dynamic content from a previous step

In my flow, the action looks like this:

Step 2 – Filter the output of the extraction

The output of the Extract archive to folder action is an array of objects which contains information about every file extracted from the archive. This output needs to be filtered so that we can get the file Id of the document.xml. So add a filter array action and use the output of Extract archive to folder as the input for the filter. Click the edit in advanced mode link and use this filter expression:

@and(startsWith(item()['Name'], 'document'),endsWith(item()['Name'], 'xml'))

This will filter the array and narrow it down to just the file containing the document contents. Here is how my filter array looks:

Step 3 – Get the file content of document.xml

Add a Get file content action and use this expression for the file:

first(body('Filter_array'))['Id']

It should look like this:

Step 4 – Grab the content of the text elements

Finally, add a compose action and use the following expresison:

xpath(xml(outputs('Get_file_content')?['body']), '//*[name()=''w:t'']/text()')

Here is how it looks in my flow:

The xpath expression will grab each element named w:t and return an array of strings of the content found in those elements. Click here If you’d like to learn more about the structure of a word docx file. The output from my sample document produced the following array:

[
  "How to extract ",
  "text ",
  "from a Microsoft Word docx file",
  ".",
  "This document explains how to extract text from a Microsoft Word document using standard Power Automate actions. The result isn’t perfect, but it should be good enough for basic usage."
]

At this point you can either iterate through the results, or use a simple join expression to create a single string from the results. Here is a screenshot of the entire flow:

As you can see from the above, it is possible to Extract Text from a Word docx file with Power Automate quite easily, and a more sophisticated xpath expression could target specific regions of text required.