Finally someone that tested there code and syntax before posting a video or blog!! The only issue I had was that I was getting the existing file name and then adding more info to it. One of the existing file names has an apostrophe in it, so the code above throws an error about an expected comma. Basically if you change the part after 'FileLeafRef': to have regular doulble quotes around it, it works great. 'FileLeafRef':"whatever stuff you need to put here"
Good spot. It is not common to use double-quotes in JSON but as you say it is the best way in this circumstance. Time for me to do a JSON 101 video. If only RUclips allowed us to update videos without losing views.
Excellent video from an excellent teacher. I am in debt to many who share their knowledge through videos but you, sir, are an excellent teacher that explains not just what to do but what is happening behind the scenes. That's a gift. I would much rather know what I'm doing and why as opposed to just following steps blindly. This is the best video I've seen on the topic. Now I just need to tweak this for renaming folders rather than files. Thank you particularly for building on the the _api bit showing how we can drill down into the lists. That will save lots of troubleshooting. Priceless.
This was awesome. I struggled an entire day to find that it was this easy. It is not just file name the HTTP request can handle a lot more than that. Thank you so much.
All I want to say is,,, Thank you very much :) You made it really easy . Very well explained.. and no BS.. all crisp , straight and simple to understand.
You're not wrong about it probably not working first time. Just spent an hour trying to figure it out till I realised I had a colon instead of a semi-colon in the line application/json;odata=verbose. Nice video, thanks
Sad to see you abandoned this channel! I've been struggling with this rename and although my use case was quite different because you taught us to figure stuff out I got it! If you have any suggestions of videos to watch to continue this learning path Please let me know!
BRILLIANT THANK YOU. Worked first time! I was hoping to eventually rename files based on the values displayed in lookup columns. But I've had lots of trouble with Lookup Columns in the past so thought this might just be the first step but WOW this Flow did it for me. The Values presented themselves as options and IT JUST WORKED. My only issue is the file Extension. I want it independent of file type I don't want every file to have a .docx extension!. My solution was to append the Dynamic Content "File name with extension" to my new file name. So I'm appending to the existing file name. And that's fine. Then ... Scrolling through the comments I see instructions to use Split to get just the extension of the existing file stripping out the old file name. I used compose to get the extension BRILLIANT. Now I'm thinking I want the option "Append to front of existing file OR replace". BUT I'm not even sure I want to yet. I can always do two flows, that would be much easier. Many, many, and lots more thanks John
This is an excellent presentation, concise but thorough enough that a novice like me can follow along. I am relatively new to Power Automate and know nothing of XML or JSON, yet I was able to automate a task to rename several files we are migrating to Sharepoint. I placed this in an Apply to Each loop to rename all files in the library. The library contains mixed file types, so I will try using text manipulation on the current filename with extension to extract the extension portion and append it to the new filename.
You uncovered a lot more than renaming files for me. I was not aware the SharePoint files and column data are in separate places. This explains a lot. Will you cover more "behind the scenes" of SharePoint libraries?
Hi John. Nice share ! Thank you so much. What would I need to change in order to rename files with different extensions (docx, pdf, xlsx...) ? Keep up with the good work 👍🏽
Create a variable called FileExt for example. Set the variable with an expression that gets the last period / full-stop used in the filename by splitting the name at that point. For example you would want to add code something like this: last(split(outputs('Get_file_properties')?['body/{FilenameWithExtension}'], '.')) Finally, you will want to remove the docx in the example and replace with the variable FileExt, just like it shows the CourseRef at time 14:48 in the video . Do not remove the period / full stop before the file extension. I hope this helps.
Dear kind sir, how can we execute this workflow if we want to rename files in a subfolder? like for example, how can we do this in a subfolder in "course outlilnes"? What would the uri be?
John, this was very useful, thank you for taking the time to do it. I'm trying to create a triggered flow that kicks off when a file is added to a folder (using When a file is created in a folder) and then I added a next step to Get File Properties, but when I go to add the ID there isn't anything in the Dynamic Content (it states No Dynamic Content Available). I've tried using different folders/Libraries within SharePoint but cannot get past this problem. Any advice or ideas why it would be missing?
I finally figured it out after 6 long hours... I ended up using a "When a file is created or modified (properties only)" first as a trigger when the file arrives, then a "Get File Properties" step to get the ID, then the "Send HTTP request to SharePoint" step as you show in the video. Works well! Thanks again for the video.
I cannot answer your issue directly without seeing the flow but commonly, "No dynamic content available" happens when you do not specifiy the correct settings in the trigger. Make sure it recognises your site (select a site from the list or click "Enter custom site", and type or paste the site in), and check you are selecting a valid library.
Thanks for the excellent presentation. In my use case the files are of multiple different file types (and extensions), how could one use this method to update the file name but not the extension?
So grabbing the file extension was in another RUclips video of mine but let me give you the answer here. After the CourseRef field I inserted at the end of the HTTP request block, I would type a period / full stop (.) followed by the following code which gets the last block of text after the last period found in the file name. last(split(outputs('Get_file_properties')?['body/{FilenameWithExtension}'],'.'))
Hey John, great video! I wanted to ask how you would go about deleting all spaces in a SharePoints files and folders names. For example from “hello world” to “helloworld” I have an enormous SharePoint that needs more file name space and I’m a bit lost on how to go about it.
John, Excellent video. how can you achieve this for all the files in that document library instead of one selected file? Does this work if the Doc Library holds upto 4000 pdfs for example.
- Create a manual trigger flow. - Use a Get files rather than Get file. - Create a ForEach loop group action and place all the rename actions inside it. This will get it to loop through all the files. Make this better by adding a Status control that looks for rows that have the status value Pending, and then during the flow update the item and add to the status column "Complete"
Hi John, great videos I have to say. Just used this for adding document numbers to file names. What I would like to do is also add the version number so i end up with "Doc No+Version No+filename". you can see the problem, as soon as i rename the version alters. Also if I alter the document and save, version alters, i run the rename flow and it adds to the already renamed file. I cant get out of the loop thankfully set this up with manual trigger.
I would advise against this. One of the golden rules I teach in document management training is "A file name never changes throughout its entire lifecycle" Never include anything in the file name that will change during the lifecycle of the document such as revision dates or version numbers. Your document and is 512 versions are managed as one file but are actually stored as individual files which you can find by appending /_layouts/Versions.aspx?list= {list guid}&ID= {item id} at the end of your SharePoint Site library path and replacing list GUID with the GUID of your library and item ID with the ID found in the ID column for the document.
@@JohnDayQA hi John, the problem is I need to issue revised documents as part of the project lifecycle. What I am doing is copying the file name to a hidden column when it is created. I then have a column for manually adding a revision number ie 001. when I publish a new revision I manually alter 001 to 002 and use publish to trigger renaming the file with the filename from the hidden column plus the rev number. Would be interested to discuss some sharepoint design work/training with you if interested.
Document properties are a change and therefore will increase the version number. As part of content management what you are looking to record is the release version prior to approval. 1: For to the library settings and enable Draft / minor versions and require content approval. 2: Get your power automate to capture the current major version (Use the first and split commands to grab the whole number before the period / full stop . then add 1. 3:You have now stored the version number of the next approved stage whilst in draft. 4: When you approve the document it will increase to the next major version matching what you stored. If you want to contact me, use the email address mentioned in the video and we can proceed from there.
I'm not good at sharepoint, can you give me some clue regarding this. This is to work around power apps delegation issue, I need to update a new column called 'year' based on the filename. For example, all my files starts with year '20210101-' (Around 5000 files), I want to update 'year' column from the filename by checking if it starts with 2021 / 2019/ 2020
If you want to check each file, you want to extract the 4 Left-most characters using the Left function from the file name, convert them into a Float value and plop the result into your new column. If you want something to do this to every item in your SharePoint list or library, you'll find it better in Power Automate with a For Each control. If you need a video covering this, I can happily do one. If you need domething more specific than what I have given gere, let me know and I will work on it.
Thanks John, really well explained, great pace too! I have a topic you might be able to help with. I need to take a row of data from a tab in an excel spreadheet andd copy into an existing Excel spreadsheet. Each time, the target sheet is a fresh file with just headers, as this is load sheet for SAP (so has clever stuff to allow a user to connect and import directly from Excel). Have you done this, or can you put together a video on this. I have subscribed, so keep up the great work!
Hi Tom. Your solution can be completed with Power Automate, VBA, Office Scripts, you could probably do most of it with a Macro and tweak it. Did you have a preference of how you want this solution to be done? Let me know and I will put a video together that best suits your needs.
Great walk-through! I learned a lot. Is there an easy way to instead of using the property CourseRef have a user making a manual input of the file name?
Hi John, thank you this is really helpful. I know this could be a noob question, but I'm a noob in Power Automate so I'm just wondering if you can explain how to do it to a file that was generated in the same flow with the action "Copy File" since I'm having a lot of problems to get the "ID" in the action "Get File Properties" to generate the name of the file based on the file properties. 😅
I've been "using" the HTTP request in a few of my Flows, but never understood it until this video. However, little curveball - what if the file name you are renaming to already exists? How do you chose to overwrite or append?
This will return an error to the Power Automate action. So, add a "parallel branch" and add a new action underneath the Send HTTP Request to resolve the issue as you prefer. With the new action, click settings and "Configure run after" to bet set when previous action has failed. What you choose to don in this case is totally up to you. You could email a user to request a file name, or you create a loop that continues to add an incremented number to the end of the file name until it no longer returns an error.
@@JohnDayQA hmmm, I was hoping to actually overwrite the files. I'll have to work out how to get the old file and delete it. It's not that simple, as the file created splits into 28 folders.
@@mikedodds1321 The reason why this is complicated is becasue the files are not stored and identified uniquely by their file name, but their GUID (Global Unique IDentifier). This is their primary key, if you wish. The documents are stored separately to the column data you see which governs it (the file is not actually stored in the library).
You "used to" be able to do it with the "Create file" action in Power Automate, but apparently they updated the action in 2020 to prevent this becasue it caused chaos. If you have a Create File you created over a year ago, you could copy and paste it into this flow (actions retain the version of it), as your parallel action, copy all the data to it and remove the old one. Personally, I would never use this method as it goes completely against content management methodology and compliance. You would not only lose all audit, version history and currently assigned permissions in the current file but also in the old one you are renaming, and replaced with a blank slate. It sounds like a better solution for you in this case would be to create a list of items, each item contains the file as attachment. You can then update the item and overwrite the file. In this case you can multiple files with the same name but each has their own unique associated information. You can also have folders in lists if you need them. Downside: No Check Out, No minor versions, no way to add column properties into word documents.
Can this method also be used to change a file name if it is located at the site contents root directory (ie. not in a library/folder on the site)? I am trying to get to the target endpoint just using "_api/web/lists/getbytitle('NAME')" and keeping everything else in the HTTP Request the same as shown in this video. The flow runs but the FileLeafRef property does not change.
Not sure what you are attempting here but have you tried grabbing the site content lists by removing the list or library name from the URL. This will list all lists and libraries including your templates, user directory, site masters, themes, and other content.
This was very helpful and works great. My challenge is I want it to update the filename "when a file is created or modified". Thought it was working awesome until I looked at the run history. Every time it processes the HTTP post action, SharePoint sees that as yet another "modification" and re-triggers the flow. A vicious loop. Any ideas? Ony thing I can come up with is at start of the flow, have it check the modification date/time and if it is less than x-minutes old, terminate the flow.
Same applies to folders. Send HTTP request is the best method. FileLeafRef is the localised name identity for files and folders. Just use "Get Folder metadata" instead of "Get File"
Can you please explain how we would be able to rename a workSHEET by sending HTTP request? Looking to use this same action on power automate to do that as part of my flow. Thank you! and great video! Thumbs up !
The Excel Online does not have a rename sheet action and there is no API function available to do it. However Power Automate can run Excel scripts. If you place this script inside the excel workbook, you can call teh script from Power Automate. You can use and pass variables with the new sheet name. function main(workbook: ExcelScript.Workbook) { getsheetname = workbook.getWorksheet("name_of_sheet"); // Rename worksheet to "new_name_of_sheet" lVDR.setName("new_name_of_sheet"); } Hopefully renaming a sheet is next on the Excel data connector.
Well done, your solution fixes what you need but is not the problem. You obviously do not need any of the underlining metadata. This would be in effect the difference between using the Get file (Properties only) action of SharePoint, and Get file metadata. If you are using a list rather than a library then you do not need the metadata call as it exists for files. If you are working on a library, for now this may suffice, but if you start calling or relying on underlining file metadata, you are no longer calling it through the API.
Is there a way to pull in the subfolder name into the new file name? For Example: Library = Vendor Docs. Subfolder = XYZVendor. Filename = taxform.pdf. Desired Output for new filename: "XYZVendor-taxform"
Create a variable called Subfolder for example. Set the variable with an expression that gets the last folder name after the last forward slash "/". For example you would want to add code something like this: last(split(outputs('Get_file_properties')?['body/{path}'], '/')) Place this before the CourseRef at time 14:48 in the video .
Thanks for this video, very helpful. Now new challenge for you because I did not find how to do it : how do you upload an excel file in a sharepoint when you have not the sharepoint connection in powerautomate for desktop using a http request ?
You'll need a "For Each action" to go through each file in your selection. If you want Power Automate to go through all the files or selected files, create a SharePoint View to filter the files you need, then use an Instant manual trigger to start the flow. Connect to the library using that view and then use a "For Each" action to loop through each file renaming them as you see fit. Hope that helps. Contact me if you get stuck.
Excellent tutorial. I do have something a little more complicated that would love to see how its done.. so .. Email has a zip attachment. Extract to Sharepoint, delete the Zip and rename the file addiing a datetime. Help would be appreciated!
Fortunately, OneDrive for Business has a lot of built-in calls to its APIs including convert to PDF and Extract ZIP. You put the file in a OneDrive folder, extract the zip and then move the result files into SharePoint. So you will need an API that can read zip file content and be able to extract it. Alternatively you can write the API code yourself. Fortunately OneDrive has an API call called "Extract to Archive" Add an action to your Power Automate and connect to OneDrive for Business, then search ZIp to find the Extract archive to folder action.
Hi John! Im trying to replicate this to all items, renamint them by their IDs, nevertheless, Automate is changing the name of the folders. How can strictly change the name of the files and not the folders?
You could add a Trigger condition in the Settings of the trigger at the top, to compare the 'Name' to the 'FileNameWithExtension' property. If For a File these will be different Name: Report 2022 File Name with Extension: Report 2022.xlsx - If FALSE it is a File Whereas a Folder this will be the same value Name: MyStuff FileNameWithExtension: MyStuff - If TRUE it is a folder So your expression may look something like this @equals(outputs('Get_file_properties')?['body/{Name}'],outputs('Get_file_properties')?['body/{FilenameWithExtension}'])
This is exactly what I was looking for! I followed through and it all made sense but I can't get it to work. I keep getting the Error "Action 'Send_an_HTTP_request_to_SharePoint' failed". Not even sure where to start troubleshooting. Any ideas?
I don't understand how the file was saved and the metadata in the document library was automatically captured, i.e., 'CourseRef', 'Subject', 'Title', etc. ??
Many thanks for your tutorial. somehow i struggle with a 400 error the __metadata parameter does not exist in the getbyid method .... somehow i am stuck. Do you have a idea where i can finde some information to fix this problem?
Many factors affect this - you are pointing to a list not a library (in which case remove the __metadata : SP call) - you have not "verbosed" the call correctly. It might be able to help if you pasted the code inside the HTTP request large box to me.
We are getting barcode images in .jpg format, multiple images of single barcode like 123 a, 123 b etc. I want to rename them like 123_1, 123_2, 123_3 like this for all the different barcodes at one go, can you please help me on this? Thanks in advance
Hi - This works for a file whose name is static, but what about if i have a file that comes into SharePoint 4x a day and each time it has a new date/time stamp in the file name? What i'm trying to do is rename a file whose name is ever changing ex: all_sales_orders 2023-09-14 09-16.csv where the last 21 characters will need to be removed if the file name contains "all_sales_orders"...is this a possibility?
Thanks for the great tutorial! Unfortunately I always get the error message "BadGateway". The flow takes about 20min until the error message is displayed. Do you know this problem or do you have a solution?
If this is a 502 badgateway this is common where you have used a column that allows multiple values. To resolve this you need to get items not get item. you can then return the first value or filter on the value you want. If this is not the case, please specify the error code number. We may need to dig a little deeper to resolve this.
@@liangyang9144 He means if you use a column such as a Choice type column where you have enabled "allow multiple selections" This is exactly what I had and after turning off multiple selections it has fixed the problem! Thnx John.
Hi John, Thank you, it works for me. I manage to use multiple property columns connected with ‘& ‘ to rename the file, but I cannot figure out how to rename the file by keeping its original file name and add appending file name with additional metadata from multiple property columns? For example original file name is ‘John is expert.docx’, I want to rename it to ‘John is expert & Taiwan.docx’, Taiwan is coming from the property data column ‘country’. How to write FileLeafRef command line?
Add the new file name into a variable first and concatenate the old file name with your column data, then place the variable inside the SEND HTTP Request as the value for the FileLeafRef.
Check the If-Match property, the box onthe right must only contain an asterisk (*). If this is correct the item does not have an eTag, this is true if you are connecting to a list not a library. Still struggling. Comment back, maybe it needs another pair of eyes to check it over.
I shouldn't be shocked this did not work for me. Trying to rename all my sharepoint site pages to remove the engl- placed in each name. 500+ pages i will have to rename by hand and this is only the first site of 30
1: I would recommend creating a view (use the classic views settings page) and under the Folders option, select "Show all items without folders" now all your documents are in one entire list. 2: Change the trigger at the top to a instant one so you can call it when you need it. 3: Use a Get files (plural) 4: No you need to add a Apply to Each container that loops through every document 5: Place the Send HTTP request inside the Apply to Each container
Yes, the Power Automate flows from any list work on a single selection. To make a multiple selection, you are better off creating a Power App form connected to the list on one screen with checkbox options to select items. Then, have a button that runs a Power Automate flow that uses "For Each" loop to go through all selected items.
Yep, You can use a variable to get, store even create the folder prior to renaming it. Somewhere in this description I went into detail on a similar question (if you can find it). If you need further help, contact me on facebook.com/johndayqanda
This is the worst programming language I have encountered in my life and I will be happy to never use it again after this project. But I can tell this is a very good tutorial.
Hi there, I tried following your instructions for renaming a folder automatically, but I got an error message saying Action 'Send_an_HTTP_request_to_SharePoint' failed. The details say : The expression "lists/GetByTitle('Student Folders')/Items(%2fstudent+folder%2fDemo)" is not valid. clientRequestId: 485e3132-01ca-4224-8df6-7fcf69346c3c serviceRequestId: a2dc9ca0-f0b2-6000-3afb-344bb164bc27 I was trying to rename the folder Demo to 21-22 Demo but I wasn't quite sure how to do it when I was writing the code in the Body section. Could you please help?
Finally someone that tested there code and syntax before posting a video or blog!! The only issue I had was that I was getting the existing file name and then adding more info to it. One of the existing file names has an apostrophe in it, so the code above throws an error about an expected comma. Basically if you change the part after 'FileLeafRef': to have regular doulble quotes around it, it works great. 'FileLeafRef':"whatever stuff you need to put here"
Good spot. It is not common to use double-quotes in JSON but as you say it is the best way in this circumstance. Time for me to do a JSON 101 video. If only RUclips allowed us to update videos without losing views.
Legend; exactly the issue i was having! Thanks
Excellent video from an excellent teacher. I am in debt to many who share their knowledge through videos but you, sir, are an excellent teacher that explains not just what to do but what is happening behind the scenes. That's a gift. I would much rather know what I'm doing and why as opposed to just following steps blindly.
This is the best video I've seen on the topic. Now I just need to tweak this for renaming folders rather than files. Thank you particularly for building on the the _api bit showing how we can drill down into the lists. That will save lots of troubleshooting. Priceless.
I have spent so long trying to do this. Your explanation was perfect. Thank you for going into detail and showing how this can be done. Amazing job!
This was awesome. I struggled an entire day to find that it was this easy. It is not just file name the HTTP request can handle a lot more than that. Thank you so much.
Glad it helped!
All I want to say is,,, Thank you very much :)
You made it really easy . Very well explained.. and no BS.. all crisp , straight and simple to understand.
You're not wrong about it probably not working first time. Just spent an hour trying to figure it out till I realised I had a colon instead of a semi-colon in the line application/json;odata=verbose. Nice video, thanks
I have saying when delivering VB, Python or any other language course,
"If it works first time, there's something wrong with it." 😁
Sad to see you abandoned this channel! I've been struggling with this rename and although my use case was quite different because you taught us to figure stuff out I got it! If you have any suggestions of videos to watch to continue this learning path Please let me know!
I've been looking for something like this for a long time. Thanks!!
Brilliant! This will save my bacon! Keep these tips and tricks coming. I've been sharing your link out! :-D
Awesome, thank you!
The best tutorial I never saw on that subject. Great job. Thank you
BRILLIANT THANK YOU. Worked first time!
I was hoping to eventually rename files based on the values displayed in lookup columns. But I've had lots of trouble with Lookup Columns in the past so thought this might just be the first step but WOW this Flow did it for me. The Values presented themselves as options and IT JUST WORKED.
My only issue is the file Extension. I want it independent of file type I don't want every file to have a .docx extension!. My solution was to append the Dynamic Content "File name with extension" to my new file name. So I'm appending to the existing file name. And that's fine. Then ...
Scrolling through the comments I see instructions to use Split to get just the extension of the existing file stripping out the old file name. I used compose to get the extension BRILLIANT. Now I'm thinking I want the option "Append to front of existing file OR replace". BUT I'm not even sure I want to yet. I can always do two flows, that would be much easier.
Many, many, and lots more thanks John
This is an excellent presentation, concise but thorough enough that a novice like me can follow along. I am relatively new to Power Automate and know nothing of XML or JSON, yet I was able to automate a task to rename several files we are migrating to Sharepoint. I placed this in an Apply to Each loop to rename all files in the library. The library contains mixed file types, so I will try using text manipulation on the current filename with extension to extract the extension portion and append it to the new filename.
Superb. I'm hoping to put some more tips out on PAUT as soon as I can, so anything in particular. I'll do my best to cover it.
You are amazing. Been looking for this specific info for a while and I love how you explain every detail. Amazing!
You uncovered a lot more than renaming files for me. I was not aware the SharePoint files and column data are in separate places. This explains a lot. Will you cover more "behind the scenes" of SharePoint libraries?
You are a legend John, this method also works for renaming folders 👏👏👏
This is a great video @John Day Q&A, thank you for sharing. Would love to see more on this very topic and the PowerAutomate SharePoint HTTP action.
Absolutely Brilliant. The best explanation available. Solved my problems. Love to fool proof method for obtaining the variable link contexts.
Great video John, nicely explained and very helpful. Cheers!
Superb. Fixed a lot of issues for me.
Thank you John - great demonstration, I was successful in dynamically updating a file name. This will be a nice time saver!
Amazing video, thank you so much! Happy to see in a video which other things we can do through an HTTP request...cheers :)
Glad it was helpful!
Excellent!!! The tips and side notes are extremely helpful. My new Favorite Guru 🙂
As it was said: Excellent video!
Thanks for your help!!! You saved my day. Best tutorial about this topic I've ever found. Thank you very much.
Hi John. Nice share ! Thank you so much. What would I need to change in order to rename files with different extensions (docx, pdf, xlsx...) ? Keep up with the good work 👍🏽
Create a variable called FileExt for example. Set the variable with an expression that gets the last period / full-stop used in the filename by splitting the name at that point.
For example you would want to add code something like this:
last(split(outputs('Get_file_properties')?['body/{FilenameWithExtension}'], '.'))
Finally, you will want to remove the docx in the example and replace with the variable FileExt, just like it shows the CourseRef at time 14:48 in the video . Do not remove the period / full stop before the file extension.
I hope this helps.
You legend, this helped me solve a problem that was really bugging me 🙌
Tqvm Jon.
This is a good one.
Thank you, works great.
Dear kind sir, how can we execute this workflow if we want to rename files in a subfolder? like for example, how can we do this in a subfolder in "course outlilnes"? What would the uri be?
Thank you so much sir for explaining in detail.
Thanks, really well explained. Super helpful!
Extremely helpful video. Thanks John :D
Thanks to you, I did it!!!!!🎉
Great detailed explanation. Thanks!
John, this was very useful, thank you for taking the time to do it. I'm trying to create a triggered flow that kicks off when a file is added to a folder (using When a file is created in a folder) and then I added a next step to Get File Properties, but when I go to add the ID there isn't anything in the Dynamic Content (it states No Dynamic Content Available). I've tried using different folders/Libraries within SharePoint but cannot get past this problem. Any advice or ideas why it would be missing?
I finally figured it out after 6 long hours... I ended up using a "When a file is created or modified (properties only)" first as a trigger when the file arrives, then a "Get File Properties" step to get the ID, then the "Send HTTP request to SharePoint" step as you show in the video. Works well! Thanks again for the video.
I cannot answer your issue directly without seeing the flow but commonly, "No dynamic content available" happens when you do not specifiy the correct settings in the trigger. Make sure it recognises your site (select a site from the list or click "Enter custom site", and type or paste the site in), and check you are selecting a valid library.
You saved me 5 hours of bug hunting
Thanks for the excellent presentation. In my use case the files are of multiple different file types (and extensions), how could one use this method to update the file name but not the extension?
So grabbing the file extension was in another RUclips video of mine but let me give you the answer here.
After the CourseRef field I inserted at the end of the HTTP request block, I would type a period / full stop (.) followed by the following code which gets the last block of text after the last period found in the file name.
last(split(outputs('Get_file_properties')?['body/{FilenameWithExtension}'],'.'))
Hey John, great video! I wanted to ask how you would go about deleting all spaces in a SharePoints files and folders names. For example from “hello world” to “helloworld”
I have an enormous SharePoint that needs more file name space and I’m a bit lost on how to go about it.
Hi Riley S.
I would use the replace function in an expreession, for example;
replace(variable or property,' ','')
This is amazing!!!! Thank you! You saved my day!
John, Excellent video. how can you achieve this for all the files in that document library instead of one selected file? Does this work if the Doc Library holds upto 4000 pdfs for example.
- Create a manual trigger flow.
- Use a Get files rather than Get file.
- Create a ForEach loop group action and place all the rename actions inside it.
This will get it to loop through all the files. Make this better by adding a Status control that looks for rows that have the status value Pending, and then during the flow update the item and add to the status column "Complete"
Hi John, great videos I have to say. Just used this for adding document numbers to file names. What I would like to do is also add the version number so i end up with "Doc No+Version No+filename". you can see the problem, as soon as i rename the version alters. Also if I alter the document and save, version alters, i run the rename flow and it adds to the already renamed file. I cant get out of the loop thankfully set this up with manual trigger.
I would advise against this. One of the golden rules I teach in document management training is "A file name never changes throughout its entire lifecycle" Never include anything in the file name that will change during the lifecycle of the document such as revision dates or version numbers. Your document and is 512 versions are managed as one file but are actually stored as individual files which you can find by appending /_layouts/Versions.aspx?list= {list guid}&ID= {item id} at the end of your SharePoint Site library path and replacing list GUID with the GUID of your library and item ID with the ID found in the ID column for the document.
@@JohnDayQA hi John, the problem is I need to issue revised documents as part of the project lifecycle. What I am doing is copying the file name to a hidden column when it is created. I then have a column for manually adding a revision number ie 001. when I publish a new revision I manually alter 001 to 002 and use publish to trigger renaming the file with the filename from the hidden column plus the rev number. Would be interested to discuss some sharepoint design work/training with you if interested.
Document properties are a change and therefore will increase the version number. As part of content management what you are looking to record is the release version prior to approval.
1: For to the library settings and enable Draft / minor versions and require content approval.
2: Get your power automate to capture the current major version (Use the first and split commands to grab the whole number before the period / full stop . then add 1.
3:You have now stored the version number of the next approved stage whilst in draft.
4: When you approve the document it will increase to the next major version matching what you stored.
If you want to contact me, use the email address mentioned in the video and we can proceed from there.
Thanks man, solved my problem perfectly
Thank you so much, I needed this
incredible stuff, thank you
I'm not good at sharepoint, can you give me some clue regarding this.
This is to work around power apps delegation issue, I need to update a new column called 'year' based on the filename.
For example, all my files starts with year '20210101-'
(Around 5000 files), I want to update 'year' column from the filename by checking if it starts with 2021 / 2019/ 2020
If you want to check each file, you want to extract the 4 Left-most characters using the Left function from the file name, convert them into a Float value and plop the result into your new column. If you want something to do this to every item in your SharePoint list or library, you'll find it better in Power Automate with a For Each control.
If you need a video covering this, I can happily do one. If you need domething more specific than what I have given gere, let me know and I will work on it.
Thanks John, really well explained, great pace too! I have a topic you might be able to help with. I need to take a row of data from a tab in an excel spreadheet andd copy into an existing Excel spreadsheet. Each time, the target sheet is a fresh file with just headers, as this is load sheet for SAP (so has clever stuff to allow a user to connect and import directly from Excel). Have you done this, or can you put together a video on this. I have subscribed, so keep up the great work!
Hi Tom.
Your solution can be completed with Power Automate, VBA, Office Scripts, you could probably do most of it with a Macro and tweak it. Did you have a preference of how you want this solution to be done?
Let me know and I will put a video together that best suits your needs.
Great walk-through! I learned a lot.
Is there an easy way to instead of using the property CourseRef have a user making a manual input of the file name?
Hello! Thanks for sharing. Just one query, can this be use for multiple document rename? What if i wanted specific name for every file?
Hi John, thank you this is really helpful. I know this could be a noob question, but I'm a noob in Power Automate so I'm just wondering if you can explain how to do it to a file that was generated in the same flow with the action "Copy File" since I'm having a lot of problems to get the "ID" in the action "Get File Properties" to generate the name of the file based on the file properties. 😅
I've been "using" the HTTP request in a few of my Flows, but never understood it until this video. However, little curveball - what if the file name you are renaming to already exists? How do you chose to overwrite or append?
This will return an error to the Power Automate action. So, add a "parallel branch" and add a new action underneath the Send HTTP Request to resolve the issue as you prefer. With the new action, click settings and "Configure run after" to bet set when previous action has failed.
What you choose to don in this case is totally up to you. You could email a user to request a file name, or you create a loop that continues to add an incremented number to the end of the file name until it no longer returns an error.
@@JohnDayQA hmmm, I was hoping to actually overwrite the files. I'll have to work out how to get the old file and delete it. It's not that simple, as the file created splits into 28 folders.
@@mikedodds1321 The reason why this is complicated is becasue the files are not stored and identified uniquely by their file name, but their GUID (Global Unique IDentifier). This is their primary key, if you wish. The documents are stored separately to the column data you see which governs it (the file is not actually stored in the library).
You "used to" be able to do it with the "Create file" action in Power Automate, but apparently they updated the action in 2020 to prevent this becasue it caused chaos. If you have a Create File you created over a year ago, you could copy and paste it into this flow (actions retain the version of it), as your parallel action, copy all the data to it and remove the old one.
Personally, I would never use this method as it goes completely against content management methodology and compliance. You would not only lose all audit, version history and currently assigned permissions in the current file but also in the old one you are renaming, and replaced with a blank slate.
It sounds like a better solution for you in this case would be to create a list of items, each item contains the file as attachment. You can then update the item and overwrite the file. In this case you can multiple files with the same name but each has their own unique associated information. You can also have folders in lists if you need them.
Downside: No Check Out, No minor versions, no way to add column properties into word documents.
Can this method also be used to change a file name if it is located at the site contents root directory (ie. not in a library/folder on the site)? I am trying to get to the target endpoint just using "_api/web/lists/getbytitle('NAME')" and keeping everything else in the HTTP Request the same as shown in this video. The flow runs but the FileLeafRef property does not change.
Not sure what you are attempting here but have you tried grabbing the site content lists by removing the list or library name from the URL. This will list all lists and libraries including your templates, user directory, site masters, themes, and other content.
This was very helpful and works great. My challenge is I want it to update the filename "when a file is created or modified". Thought it was working awesome until I looked at the run history. Every time it processes the HTTP post action, SharePoint sees that as yet another "modification" and re-triggers the flow. A vicious loop. Any ideas? Ony thing I can come up with is at start of the flow, have it check the modification date/time and if it is less than x-minutes old, terminate the flow.
looking for how to change a sharepoint folder name, is this the same method you would use for that or is there better method?
Same applies to folders. Send HTTP request is the best method. FileLeafRef is the localised name identity for files and folders. Just use "Get Folder metadata" instead of "Get File"
Mil gracias por este video. Saludos.
Can you please explain how we would be able to rename a workSHEET by sending HTTP request? Looking to use this same action on power automate to do that as part of my flow. Thank you! and great video! Thumbs up !
The Excel Online does not have a rename sheet action and there is no API function available to do it. However Power Automate can run Excel scripts. If you place this script inside the excel workbook, you can call teh script from Power Automate. You can use and pass variables with the new sheet name.
function main(workbook: ExcelScript.Workbook) {
getsheetname = workbook.getWorksheet("name_of_sheet");
// Rename worksheet to "new_name_of_sheet"
lVDR.setName("new_name_of_sheet");
}
Hopefully renaming a sheet is next on the Excel data connector.
Thanks John great video. I get this error "The property '__metadata' does not exist on type..." A search didn't turn up why this might be happening.
Searching impies that not using the odata=verbose can cause this, but I have that in there.
OK so I deleted out section of
"'__metadata':
{'type': 'SP.Data.xxxx.....,"
and it worked
Well done, your solution fixes what you need but is not the problem. You obviously do not need any of the underlining metadata. This would be in effect the difference between using the Get file (Properties only) action of SharePoint, and Get file metadata. If you are using a list rather than a library then you do not need the metadata call as it exists for files.
If you are working on a library, for now this may suffice, but if you start calling or relying on underlining file metadata, you are no longer calling it through the API.
Is there a way to pull in the subfolder name into the new file name? For Example: Library = Vendor Docs. Subfolder = XYZVendor. Filename = taxform.pdf. Desired Output for new filename: "XYZVendor-taxform"
Create a variable called Subfolder for example. Set the variable with an expression that gets the last folder name after the last forward slash "/".
For example you would want to add code something like this:
last(split(outputs('Get_file_properties')?['body/{path}'], '/'))
Place this before the CourseRef at time 14:48 in the video .
HI John, really helpfull video, is there a way to move a file using a HTTP request as well?
Thanks for this video, very helpful. Now new challenge for you because I did not find how to do it : how do you upload an excel file in a sharepoint when you have not the sharepoint connection in powerautomate for desktop using a http request ?
Sorry, one more question John. How to Power Auto Rename multiple files in one run?
You'll need a "For Each action" to go through each file in your selection. If you want Power Automate to go through all the files or selected files, create a SharePoint View to filter the files you need, then use an Instant manual trigger to start the flow. Connect to the library using that view and then use a "For Each" action to loop through each file renaming them as you see fit.
Hope that helps. Contact me if you get stuck.
Huge help. Thank you!
Excellent tutorial. I do have something a little more complicated that would love to see how its done.. so .. Email has a zip attachment. Extract to Sharepoint, delete the Zip and rename the file addiing a datetime. Help would be appreciated!
Fortunately, OneDrive for Business has a lot of built-in calls to its APIs including convert to PDF and Extract ZIP. You put the file in a OneDrive folder, extract the zip and then move the result files into SharePoint. So you will need an API that can read zip file content and be able to extract it. Alternatively you can write the API code yourself. Fortunately OneDrive has an API call called "Extract to Archive" Add an action to your Power Automate and connect to OneDrive for Business, then search ZIp to find the Extract archive to folder action.
Hi John!
Im trying to replicate this to all items, renamint them by their IDs, nevertheless, Automate is changing the name of the folders.
How can strictly change the name of the files and not the folders?
You could add a Trigger condition in the Settings of the trigger at the top, to compare the 'Name' to the 'FileNameWithExtension' property.
If For a File these will be different
Name: Report 2022
File Name with Extension: Report 2022.xlsx
- If FALSE it is a File
Whereas a Folder this will be the same value
Name: MyStuff
FileNameWithExtension: MyStuff
- If TRUE it is a folder
So your expression may look something like this
@equals(outputs('Get_file_properties')?['body/{Name}'],outputs('Get_file_properties')?['body/{FilenameWithExtension}'])
This is exactly what I was looking for! I followed through and it all made sense but I can't get it to work. I keep getting the Error "Action 'Send_an_HTTP_request_to_SharePoint' failed". Not even sure where to start troubleshooting. Any ideas?
Is it possible to get a subject, title or some other word / variable from the text in the word documents to create a filename?
Yes, you can amend the fileLeafRef property by assigning after the colon (:) any field you want. Just plop it in from the Dynamic content box.
I don't understand how the file was saved and the metadata in the document library was automatically captured, i.e., 'CourseRef', 'Subject', 'Title', etc. ??
Many thanks for your tutorial. somehow i struggle with a 400 error the __metadata parameter does not exist in the getbyid method .... somehow i am stuck. Do you have a idea where i can finde some information to fix this problem?
Many factors affect this
- you are pointing to a list not a library (in which case remove the __metadata : SP call)
- you have not "verbosed" the call correctly.
It might be able to help if you pasted the code inside the HTTP request large box to me.
We are getting barcode images in .jpg format, multiple images of single barcode like 123 a, 123 b etc. I want to rename them like 123_1, 123_2, 123_3 like this for all the different
barcodes at one go, can you please help me on this? Thanks in advance
great, to the point content
Hello Sir, Thanks for the tutorial. Is there a way we can run the power automate but it does not change the Modified Time ? Thank you!!
Hi - This works for a file whose name is static, but what about if i have a file that comes into SharePoint 4x a day and each time it has a new date/time stamp in the file name? What i'm trying to do is rename a file whose name is ever changing ex: all_sales_orders 2023-09-14 09-16.csv where the last 21 characters will need to be removed if the file name contains "all_sales_orders"...is this a possibility?
Thanks so much for this.
Good Work. Thank you
Welcome
What about folder names?
Thanks for the great tutorial!
Unfortunately I always get the error message "BadGateway".
The flow takes about 20min until the error message is displayed.
Do you know this problem or do you have a solution?
same problem!!!
If this is a 502 badgateway this is common where you have used a column that allows multiple values. To resolve this you need to get items not get item. you can then return the first value or filter on the value you want.
If this is not the case, please specify the error code number. We may need to dig a little deeper to resolve this.
@@JohnDayQA HI what do you mean that i have used a column that allows multiple values? Do you mean the file properties?
@@liangyang9144 He means if you use a column such as a Choice type column where you have enabled "allow multiple selections"
This is exactly what I had and after turning off multiple selections it has fixed the problem! Thnx John.
Hello there! thank you so much, my automate cant change the name of the file but is possible to change everything else, can someone know why?
p e r f e c t !
Hi John, Thank you, it works for me. I manage to use multiple property columns connected with ‘& ‘ to rename the file, but I cannot figure out how to rename the file by keeping its original file name and add appending file name with additional metadata from multiple property columns? For example original file name is ‘John is expert.docx’, I want to rename it to ‘John is expert & Taiwan.docx’, Taiwan is coming from the property data column ‘country’. How to write FileLeafRef command line?
Add the new file name into a variable first and concatenate the old file name with your column data, then place the variable inside the SEND HTTP Request as the value for the FileLeafRef.
how to update document library fields (Created Date, Modified Date, Created By, Modified By) power automate?
great exercise
I keep getting this error:
The request ETag value '' does not match the object's ETag value
Check the If-Match property, the box onthe right must only contain an asterisk (*). If this is correct the item does not have an eTag, this is true if you are connecting to a list not a library. Still struggling. Comment back, maybe it needs another pair of eyes to check it over.
Have you managed to fix this error?
I`m getting the same one and don`t really know why
thank you!!!
I shouldn't be shocked this did not work for me. Trying to rename all my sharepoint site pages to remove the engl- placed in each name. 500+ pages i will have to rename by hand and this is only the first site of 30
Hello, thanks for this video, how I can possibly to apply this to rename all files in my library to all subfolders in library ?
1: I would recommend creating a view (use the classic views settings page) and under the Folders option, select "Show all items without folders" now all your documents are in one entire list.
2: Change the trigger at the top to a instant one so you can call it when you need it.
3: Use a Get files (plural)
4: No you need to add a Apply to Each container that loops through every document
5: Place the Send HTTP request inside the Apply to Each container
Is it possible to rename MULTIPLE items using this flow? For me, selecting multiple items make the button disappear.
Yes, the Power Automate flows from any list work on a single selection. To make a multiple selection, you are better off creating a Power App form connected to the list on one screen with checkbox options to select items. Then, have a button that runs a Power Automate flow that uses "For Each" loop to go through all selected items.
@@JohnDayQA
Thank you!
can we do it by sub folders in Document library
Yep, You can use a variable to get, store even create the folder prior to renaming it. Somewhere in this description I went into detail on a similar question (if you can find it). If you need further help, contact me on facebook.com/johndayqanda
Skip to 7:10
LOL.... I had "Boris Johnson" do the voice over.
This is the worst programming language I have encountered in my life and I will be happy to never use it again after this project. But I can tell this is a very good tutorial.
still does not exist in 2024 lol
Hi there, I tried following your instructions for renaming a folder automatically, but I got an error message saying Action 'Send_an_HTTP_request_to_SharePoint' failed. The details say :
The expression "lists/GetByTitle('Student Folders')/Items(%2fstudent+folder%2fDemo)" is not valid.
clientRequestId: 485e3132-01ca-4224-8df6-7fcf69346c3c
serviceRequestId: a2dc9ca0-f0b2-6000-3afb-344bb164bc27
I was trying to rename the folder Demo to 21-22 Demo but I wasn't quite sure how to do it when I was writing the code in the Body section. Could you please help?