Power Platform

In this post I will show you how to export data from Power Automate, to an Excel file. This would seem to be a straightforward requirement, but until recently has required the use of apply to each loops which are slow and can potentially use many API actions.

Video Demonstration

In this demonstration I am exporting 2,000 records from a SharePoint list to an Excel document and the flow takes only 5 seconds to execute:

Create an Excel Export Template

The first step in this process is to create an Excel that contains a table for your exported data to be inserted into. Format the cells and add formulas as per your requirements and then save the file. In my example, the file looks like this:

As you can see from the screenshot, the table is called InvoiceExport and it has four fields. The fields containing values have had number formatting applied and there are some formulas to sum the Invoice Amount and count the number of records.

Make a note of what you have called the table, you will need it for your flow.

Convert the Template to Base64

Once you are happy with your Excel template. Head to Base 64 Guru and upload your template file. Base64 Guru will create a base64 representation of your file, like this:

Click on the copy button above the Base64 representation of your file to copy it to your clipboard.

Flow Overview

To give you an idea of how simple this flow is to build, here is a screenshot of the entire thing, it consists of only 5 actions:

Use the Select Action to Reshape your export Data

In my example, the data source is SharePoint, but any data source accessible to Power Automate will work. It could be SQL Server, Dataverse, SharePoint or even another Excel file. The purpose of the select action is to reshape the data down to just the columns that you want to export.

Within the select action create a raw array of values that match the order of the columns of your Excel template. We do not want to name the columns.

The code for my select action looks like this:

[
  @{item()?['Title']},
  @{item()?['Customer']},
  @{item()?['Invoice_x0020_Amount']},
  @{item()?['Date']}
]

Create the Excel File and Populate with your Data

Next step is to create your Excel file and populate it with the data from your Select action:

First, create a Compose action called ExcelDoc and paste in the Base64 representation of your template file.

Next, use a Create File action, name the file as per your requirement and for the file content use the expression:

base64ToBinary(outputs('ExcelDoc'))

Finally, create add a new action by searching for HTTP and you should see this action:

At the time of writing this post, this is a preview action and it is not available in all Office 365 Tenants. If you do not see it in your list of actions, simply copy the code below:

{
  "id": "c78ee521-4c62-4f34-84b1-8ef7-98516f0f",
  "brandColor": "#EB3C00",
  "connectionReferences": {},
  "connectorDisplayName": "Office 365 Groups",
  "icon": "https://connectoricons-prod.azureedge.net/releases/v1.0.1504/1.0.1504.2515/office365groups/icon.png",
  "isTrigger": false,
  "operationName": "Send_a_HTTP_request",
  "operationDefinition": {
    "type": "OpenApiConnection",
    "inputs": {
      "host": {
        "connectionName": "shared_office365groups",
        "operationId": "HttpRequest",
        "apiId": "/providers/Microsoft.PowerApps/apis/shared_office365groups"
      },
      "parameters": {
        "Uri": "",
        "Method": "GET",
        "ContentType": "application/json"
      },
      "authentication": {
        "type": "Raw",
        "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
      }
    },
    "runAfter": {
      "Create_file": [
        "Succeeded"
      ]
    }
  }
}

And then add a new action to your flow, go to My Clipboard and press CTRL-V. The new action will then appear in your clipboard and you can add it to your flow:

Once the new action is in place, configure it as follows:

URI:

https://graph.microsoft.com/v1.0/me/drive/items/@{substring(outputs('Create_file')?['body/Id'], add(indexOf(outputs('Create_file')?['body/Id'], '.'),1))}/workbook/tables/InvoiceExport/rows/add

Notice that I have put the name of the table into the URL:

/workbook/tables/InvoiceExport/rows/add

You will need to update this to reflect the name of your table.

Method: POST

Body:

{
	"values": @{outputs('select')['body']}
}

Export to Excel Done!

With this new action it is possible to add many rows to an Excel file very quickly and use only a single API action to do so. I tested this with 2,000 rows and it took 5 seconds to complete.

Once the export is complete you can take whatever action you want to with the file. For example to email it you could do a Get File Content and an email action:

This is a really fast, and easy way to export data from Power Automate to Excel. Please let me know if in the comments if you get stuck or found this useful.

In this post, I will explain how to create a Zip file in Power Automate without the use of any third party connectors or premium actions.

Introduction

Power Automate includes functionality to extract data from Zip files. The Zip file contents can be extracted to a OneDrive or SharePoint folder:

It’s strange that there is not a matching create archive action. An idea was posted on the community forum in 2017, but it has not been added, nor received many votes. Encodian provide this function, but many people prefer not to use external connectors.

SharePoint provides this functionality through the user interface, and I wondered if this API endpoint could be accessed from directly from Power Automate.

I quickly found that it is possible to create Zip files directly from Power Automate!

The key to unlocking this functionality is in the SharePoint API action RenderListDataAsStream. I used this API action in a previous post, easy way of getting Totals from SharePoint lists. This API action can return some useful information, such as:

• The Access Token required to access the items.
• The size of each file.
• The full item URL.
• The media URL – which is used for converting and manipulating files.

By collecting this information it is possible to formulate a new request to the media conversion enpoint which can generate a Zip File.

Flow Detail

Before we get into the details, here is a screenshot of a flow which zips an entire folder:

Although not as convenient as a built-in action, this flow isn’t very complicated. At the end of the flow, the zip file is written to a SharePoint Document Library and a OneDrive folder.

Flow Code and Implementation

To implement this flow for yourself, you need to do the following:

• Copy and paste the Scope Code below into one of your own flows.
• Update the references to SharePoint and OneDrive to match your own environment.
• Change the values in the compose action called settings
  • libraryPath is the path to the root of the SharePoint site.
  • zipFolderPath is the relative path of the folder that you want to Zip.
• Update or delete the final storage actions appropriately for your implementation.

Scope Code:

{
  "id": "b7af8f36-c669-4d9f-bb7d-bf14-63edb83a",
  "brandColor": "#8C3900",
  "connectionReferences": {
    "shared_onedriveforbusiness": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_onedriveforbusiness/connections/shared-onedriveforbu-05f1da5e-297d-4a80-8df7-cb78-b654b8a3"
      }
    },
    "shared_sharepointonline": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/de645f5680b74c47bbce762c8e2d06ac"
      }
    }
  },
  "connectorDisplayName": "Control",
  "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
  "isTrigger": false,
  "operationName": "CreateZip",
  "operationDefinition": {
    "type": "Scope",
    "actions": {
      "accessToken": {
        "type": "Compose",
        "inputs": "@outputs('SharePointHTTP')?['body']['ListSchema']['.driveAccessToken']",
        "runAfter": {
          "SharePointHTTP": [
            "Succeeded"
          ]
        },
        "description": "Collects the Access Token required to access the files"
      },
      "Select": {
        "type": "Select",
        "inputs": {
          "from": "@body('SharePointHTTP')['ListData']['Row']",
          "select": {
            "name": "@item()['FileLeafRef']",
            "size": "@item()['SMTotalSize']",
            "docId": "@{item()['.spItemUrl']}&@{outputs('accessToken')}",
            "isFolder": "@if(equals(item()['FSObjType'],'1'), true, false)"
          }
        },
        "runAfter": {
          "accessToken": [
            "Succeeded"
          ]
        },
        "description": "Reformats the output of SharePoint HTTP action ready for submission to the Zip endpoint"
      },
      "Attachment_Items": {
        "type": "Compose",
        "inputs": {
          "items": "@body('Select')"
        },
        "runAfter": {
          "Select": [
            "Succeeded"
          ]
        },
        "description": "Additional formatting of the Select array"
      },
      "downloadZip": {
        "type": "OpenApiConnection",
        "inputs": {
          "host": {
            "connectionName": "shared_sharepointonline",
            "operationId": "HttpRequest",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline"
          },
          "parameters": {
            "dataset": "@body('SharePointHTTP')['ListSchema']['.mediaBaseUrl']",
            "parameters/method": "POST",
            "parameters/uri": "/transform/[email protected]{body('SharePointHTTP')['ListSchema']['.callerStack']}",
            "parameters/headers": {
              "Content-Type": "application/x-www-form-urlencoded"
            },
            "parameters/body": "zipFileName=test.zip&[email protected]{guid()}&provider=spo&[email protected]{encodeUriComponent(outputs('Attachment_Items'))}&oAuthToken="
          },
          "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
          }
        },
        "runAfter": {
          "Attachment_Items": [
            "Succeeded"
          ]
        },
        "description": "Submits the request to the media server and downloads the Zip file"
      },
      "StoreZip": {
        "type": "OpenApiConnection",
        "inputs": {
          "host": {
            "connectionName": "shared_onedriveforbusiness",
            "operationId": "CreateFile",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_onedriveforbusiness"
          },
          "parameters": {
            "folderPath": "/__PA_Test/Filled Word Docs",
            "name": "testFile.zip",
            "body": "@base64ToBinary(body('downloadZip')['$content'])"
          },
          "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
          }
        },
        "runAfter": {
          "downloadZip": [
            "Succeeded"
          ]
        },
        "description": "Stores the received Zip file in OneDrive",
        "runtimeConfiguration": {
          "contentTransfer": {
            "transferMode": "Chunked"
          }
        }
      },
      "settings": {
        "type": "Compose",
        "inputs": {
          "libraryPath": "/sites/PowerAutomateText/Shared Documents",
          "zipFolderPath": "/DeclarationTemplateFilled"
        },
        "runAfter": {},
        "description": "Set library path to the location of the SharePoint document library that contains the documents for compression. Set zipFolderPath to the relative path of the Folder that you want to archive."
      },
      "SharePointHTTP": {
        "type": "OpenApiConnection",
        "inputs": {
          "host": {
            "connectionName": "shared_sharepointonline",
            "operationId": "HttpRequest",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline"
          },
          "parameters": {
            "dataset": "https://accendo1.sharepoint.com/sites/PowerAutomateText",
            "parameters/method": "POST",
            "parameters/uri": "_api/web/GetListUsingPath([email protected])/[email protected]=%[email protected]{encodeUriComponent(outputs('settings')['libraryPath'])}%27&[email protected]{encodeUriComponent(concat(outputs('settings')['libraryPath'], outputs('settings')['zipFolderPath']))}",
            "parameters/body": "{\"parameters\": {\"RenderOptions\": 4103}}"
          },
          "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
          }
        },
        "runAfter": {
          "settings": [
            "Succeeded"
          ]
        },
        "description": "Retrieves the file and folder information required to create a Zip file"
      },
      "Create_file": {
        "type": "OpenApiConnection",
        "inputs": {
          "host": {
            "connectionName": "shared_sharepointonline",
            "operationId": "CreateFile",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline"
          },
          "parameters": {
            "dataset": "https://accendo1.sharepoint.com/sites/PowerAutomateText",
            "folderPath": "/Shared Documents",
            "name": "testWordFile.docx",
            "body": "@base64ToBinary(body('downloadZip')['$content'])"
          },
          "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
          }
        },
        "runAfter": {
          "StoreZip": [
            "Succeeded"
          ]
        },
        "description": "Stores the received Zip file in a SharePoint document library",
        "runtimeConfiguration": {
          "contentTransfer": {
            "transferMode": "Chunked"
          }
        }
      }
    },
    "runAfter": {},
    "description": "A scope to create a Zip file in Power Automate without the use of 3rd Party Connectors or Premium Actions"
  }
}

Additional Information

This flow works by sending an array of files or folder names to the media conversion endpoint as form data. A sample array looks like this:

{
  "items": [
    {
      "name": "_rels",
      "size": "1168",
      "docId": "https://accendo1.sharepoint.com:443/_api/v2.0/drives/b!wGO-FC/items/01KW24YP...?version=Published&access_token=eyJ0eXAi...",
      "isFolder": true
    },
    {
      "name": "docProps",
      "size": "2861",
      "docId": "https://accendo1.sharepoint.com:443/_api/v2.0/drives/b!wGO-FC/items/01KW24YP...?version=Published&access_token=eyJ0eXAi...",
      "isFolder": true
    },
    {
      "name": "word",
      "size": "477746",
      "docId": "https://accendo1.sharepoint.com:443/_api/v2.0/drives/b!wGO-FC/items/01KW24YP...?version=Published&access_token=eyJ0eXAi...",
      "isFolder": true
    },
    {
      "name": "[Content_Types].xml",
      "size": "2703",
      "docId": "https://accendo1.sharepoint.com:443/_api/v2.0/drives/b!wGO-FC.../items/01KW24YP...?version=Published&access_token=eyJ0eXAi...",
      "isFolder": false
    }
  ]
}

This array is created in the Select action based on the output of the SharePointHTTP action. This example flow is designed to zip an entire folder but you might have more specific requirements.

If you want to filter this array you have a choice of two places in which to do it:

• By modifying the URI in the SharepointHTTP action to filter what it returns in the request (Examples here).
• By adding a filter array action after the Select action and then using that filter as the input for the Compose action called Attachment Items.

Filtering the Select action is the easiest option, but less efficient.

Conclusion

I’ve been looking for a way to a create Zip file in Power Automate for a while (in order to be able to modify Word documents) so it is great to get this done.

Although this solution works well, It’s clear that a native function to do this wouldn’t be much work, so please vote the idea up if you would like to see this.

I’ve already written a couple of posts about how to sum arrays in Power Automate, but this post is specifically about how to sum a column from a SharePoint list. If you want to sum an array that has not originated from a SharePoint list, check out my other posts:

• Instantly sum an array with Power Automate
• How to Sum an Array of Numbers in Power Automate

It has always surprised me that there isn’t an easy way to do this built into Power Automate, but I stumbled across a simple solution. I was working with a list that had a view with a Total applied to it and it occurred to me that if the total is available in a view, then it must also be available via an API.

This same method can be used to perform many different types of aggregations on SharePoint lists.

So the first step in this process is to modify your SharePoint List view so that it has a total applied to it.

Get the Colum Total from the SharePoint List View

Once you have modified your view to have a total you need to collect three bits of information:

• The Site Path.
• The List Name.
• The View ID.

Once you have that information create a compose action called Settings and populate it with the information you collected above. Here is the code from my example:

{
  "sitePath": "/sites/PowerAutomateText/",
  "listName": "Invoices",
  "viewID": "576393ba-5827-4100-a157-7d901b344ca8"
}

and a screenshot of the action:

Next add a Send an HTTP request to SharePoint action, select the site address and set the method to POST, in the Uri field paste the following code:

_api/web/lists/GetByTitle('@{outputs('Settings')['listName']}')/[email protected]{outputs('Settings')['viewID']}

As you can see, I am using the RenderListDataAsStream API action, which you can do a lot with and it is worth reading the documentation.

In the headers section add a header named accept with the value of application/json;odata=nometa

The action should look like this:

This action will bring you back everything that you would see in the browser when looking at the same view. It is important to understand that this will bring view results, not the standard list of items that you would receive with the Get Items action.

The output of this action produces an array called Row which in addition to the contents of the view shows the aggregated total on each row. Here is a sample of the output produced by my action:

{
	"Row": [{
		"ID": "27694",
		"PermMask": "0x7ffffffffffbffff",
		"FSObjType": "0",
		"UniqueId": "{7CF06512-7444-441F-9D6B-08610D1BB415}",
		"ContentTypeId": "0x0100EA3A251D88BC5941A590C41BA4B80EE700F04B73CD6F513D44943FDF3FE121DF77",
		"FileRef": "/sites/PowerAutomateText/Lists/Invoices/27694_.000",
		"FileRef.urlencode": "%2Fsites%2FPowerAutomateText%2FLists%2FInvoices%2F27694%5F%2E000",
		"FileRef.urlencodeasurl": "/sites/PowerAutomateText/Lists/Invoices/27694_.000",
		"FileRef.urlencoding": "/sites/PowerAutomateText/Lists/Invoices/27694_.000",
		"Attachments": "0",
		"SMTotalSize": "218",
		"_CommentFlags": "",
		"_CommentCount": "",
		"InvNum": "1",
		"InvNum.": "1.00000000000000",
		"Customer": "Ledner Inc",
		"Invoice_x0020_Amount": "804.39",
		"Invoice_x0020_Amount.": "804.390000000000",
		"Date": "6/25/2015",
		"Date.": "2015-06-25T07:00:00Z",
		"ItemChildCount": "0",
		"FolderChildCount": "0",
		"owshiddenversion": "1",
		"Restricted": "",
		"Invoice_x0020_Amount.SUM": "5,027,707.52"
	}]
}

As you can see, on line 27 of the output there is a an object element named Invoice_x0020_Amount.SUM which we can get with a simple compose action:

Because the result from SharePoint is an array, the compose action just takes the first result and retrieves the Total from there, here is the code I used as reference. Obviously you will need to update it to reflect whatever your field name is:

float(outputs('Send_an_HTTP_request_to_SharePoint')?['body']['Row'][0]['Invoice_x0020_Amount.SUM'])

Other Possible Uses

This technique can be used in a number of other ways to get results that would otherwise require much more effort. The standard aggregations in a SharePoint view are:

• Count
• Average
• Maximum
• Minimum
• Sum
• Std Deviation
• Variance

But in addition to those aggregations you can also perform group by operations. So if I was to create a view on the same list, but group it by customer name, I could get the Total Invoice value on a customer by customer basis.

Conclusion

I think this is the easiest way to calculate the sum of a SharePoint column. My other post on using the xpath method is also good, but not specific to SharePoint columns

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.