One of the cool things about Power Automate is that it is simply an API caller, like a robo version of Postman. And that means if you have the right API you can do anything.
Often we have to deal with zip files, with the need to edit the contents but keep it in a zip, and that unfortunately is not as easy as it should be. We have the unzip action but no zip action, or do we.
You can get some paid third party actions (but I'm to tight for that 😎). But guess who does handle zip files for bulk downloads, and has a fantastic API, that's right, SharePoint. So with the right API call and the SharePoint HTTP connector we can zip our our files back up.
In my example I have a zip file with one text file saying 'hello world', and I want to update it to 'goodbye world' (Imaginative I know 😎). I upload that file on a button press and want the output file created in SharePoint.
There are 3 main things to do:
- Unzip
- Read/Edit the file
- Zip
1. Unzip
This is the easy one, as we have the out of the box unzip SharePoint or OneDrive action. We use the solution as the input and it will create a folder with the unzipped contents.
2. Read/Edit the file
In my example its a txt file so no manipulation need (binary/base64 etc), things like json/xml and html are the same.
To edit our files first we must filter the files from the newly created unzipped folder to get the one we want to edit.
Next we get the content of the first item from the filtered folder array. Then we create a new file that replaces the old file, but with a replace()
expressions.
File Content
replace(body('Get_file_content'),'hello','goodbye')
incase you wonder I used below expression to get the destination path from the extract folder input
actions('Extract_folder').inputs.parameters.destination
3. Zip
Now this is the hard one and also the cool bit 😎, how do we use SharePoint to create a zip file, well to be honest I don't understand it fully but basically we are going:
- Get folder data
- Create our zip payload
- Send zip request
Set library data request
So the first step is the request, this returns all the key data we need to create our second http request, we are going to use the _api/web/GetListUsingPath
api aimed at the SharePoint site your zip contents is located.
We need to pass 2 queries to the url, one is the site and library path (but not including top level), and second the full path (site and folder to zip path).
E.g we want to zip the 'zipFolder' in the 'YourLibrary' library:
SiteURL: wwww.YourSP.com/sites/YourSite/YourLibrary/zipFolder
Site and library path: /sites/YourSite/YourLibrary
Full path: /sites/YourSite/YourLibrary/zipFolder
On top that they need to be encodeUriComponent()
the values.
_api/web/GetListUsingPath(DecodedUrl=@a1)/RenderListDataAsStream?@a1=%27@{encodeUriComponent('/sites/YourSite/YourLibrary')}%27&RootFolder=@{encodeUriComponent('/sites/YourSite/YourLibrary/zipFolder'))}
Finally the body needs below:
{"parameters": {"RenderOptions": 4103}}
4103 is the schema for returned data, in this case it includes some key information we need:
- ListSchema
- .driveAccessToken
- ListData
- FileLeafRef
- SMTotalSize
- spItemUrl
- FSObjType
- .mediaBaseUrl
- .callerStack
Create our zip payload
From the http request we need to transform the response data, first we use a compose to convert the token. Im not 100% sure why we can't pass the token directly in, but it fails if we do. One of the cool things about a compose is it automatically gets the right type (e.g string/integer/json), so I Im guessing its that.
outputs('SharePointHTTP')?['body']['ListSchema']['.driveAccessToken']
Next we need to build the array of the zips contents, and to do this we use a select:
body('SharePointHTTP')['ListData']['Row']
We need the
- file name -
item()['FileLeafRef']
- file size -
item()['SMTotalSize']
- doc id -
item()['.spItemUrl']&outputs('token')
- if it is a folder -
if(equals(item()['FSObjType'], '1'), true, false)
Send zip request
Finally we are going to send the request that will return our zip file.
Instead of our SharePoint site we are going to use one of Microsofts. The URL will be in our first HTTP request response:
body('SharePointHTTP')['ListSchema']['.mediaBaseUrl']
And normally looks something like this 'https://southcentralus1-mediap.svc.ms'
Our Uri will be:
/transform/zip?cs=body('SharePointHTTP')['ListSchema']['.callerStack']
The body will require a zip name (anything will do as we are not using it to create the file, so I use test.zip), and guid and the array of files to zip.
The guid we can use the guid()
expression, and for the array we need to do a little bit of transforming.
First it needs to be in an object with item as the key, so we need to create the object using the json
expression.
json(
concat(
'{"items":'
,
'body('Select')
,
'}'
)
)
Finally we need to convert the json using encodeUriComponent()
expression again.
zipFileName=test.zip&guid=@{guid()}&provider=spo&files=@{encodeUriComponent(
json(
concat('{"items":',body('Select'),'}')
)
)}&oAuthToken=
Create/Replace Zip File
Last we need to create the new updated zip file, here we just use a create file action and the base64ToBinary()
expression on the http response.
base64ToBinary(body('downloadZip')['$content'])
Our zip is now updated and this process can work on any zip file and any secret zip files. Whats a secret zip file, well lots of file extensions are actually zips but just pretending not to be.
Word .docx files, a zip, Excel .xlsx files, yep another zip file. All you need to do is rename them with .zip file extension and name them back after editing. I've even create a flow that can remove workbook and worksheet passwords using the same tricks as above as .xlsx contain xml (with a little help from an Office Script Regex).
A copy of the solution can be found here
Top comments (2)
I didn't even know you can do this wiht SharePoint API. Very smart solution.
This post in my pocket 😉
"Intelligence is a privilege, and it needs to be used for the greater good of people.”– Dr. Octopus
😁 Just Saying 😈