Required SharePoint Metadata Multiple Files
This is part 2. In SharePoint, there used to be a way to have fields/columns that were required for documents, and when a document was uploaded, the user was prompted to fill in those required fields. As you may have noticed, in SharePoint Online, documents can be easily uploaded and bulk uploaded without anything prompting you or preventing upload/check in if the required fields aren’t filled out. The fields do show in yellow in views, but there isn’t really a way to enforce those required fields. In this set of posts, the next option that you have in SharePoint online to accomplish this. See part 1 in my previous post, which was a relatively simple method.
The biggest problem with classic SharePoint, was that when someone uploaded multiple files at once, and there were required fields, there was never a prompt to fill in the fields. The files all just went into this “checked out” state where the user then had to know to go into each file’s metadata, update it, and check each file in.
The solution in this post gives you a way to create an interface where users fill in required metadata AND can upload multiple files, and all of those files go into SharePoint. This is done using Microsoft Forms and Power Automate.
1. Go to https://forms.microsoft.com or click Microsoft Forms in your waffle menu at the top left in Microsoft 365.
2. Create a new form. I’m calling mine “Demo Upload Files”. Note that When you use the “upload files” functionality in a form under “My Forms”, it puts the file in your own OneDrive.
3. Add all of the fields (questions) that you’ll need people to fill in, and make them required if necessary.
4. Be sure and add an Upload files question.
5. Name your Upload files question and set the number of files you’d like them to be able to upload all at once. The max is ten. Also, you may want to make this a required field. I named mine “Upload SOW”.
6. Note that when you use the Upload files option in a Microsoft Form, you won’t be able to allow this to be an anonymous form. In the Settings, you’ll notice that it will be set in the who can fill out this form section, to Only people in my organization can respond. Be sure to give your form a name. When I click Preview, see that mine has only two questions (required) and an upload file button.
When they fill out the form, where do the uploaded files go? When you’ve got a “My Form”, it goes to your own OneDrive, in this folder structure:
When you create a Group form, the file goes into that group’s SharePoint site. It goes into the default Documents library, in this folder structure:
Also, notice that and underscore and the submitter’s name will automatically be appended to the end of each file name.
The next step is to create the flow that will put the files where you want them. Now, this part can be widely varying, per your requirements, but the idea is that you want to put the files in SharePoint and then have the flow set the metadata on those files, per the questions you asked in the form. The thing about the group forms is that they’re nice because they already put the files in SharePoint, but not with the metadata that you need from the form they filled out.
7. Create a flow, with the Microsoft Forms trigger When a new response is submitted. Select the name of the form you just created.
8. Create an action to initialize a variable. Set it as an Array, with the name varArrayFiles
9. Add an action to Get response details. Pick the name of the form you just created, and the response Id from the trigger.
10. Add an action to Set variable. The list of uploaded files from the filled out form is an array of files. For the value in the array, use an expression. Type json() and put your cursor in between the parentheses. Then, click the name of your file upload field in the response details. Mine is called Upload SOW.
11. The result is this. Click Update. The reason I’m not posting the entirety of the code, is because it uses the unique ID of the field from the form, and yours will have a different ID than mine.
12. Add an Apply to each loop. In the output, select the varArrayFiles.
13. In the loop, add the action Get File Content. If you are using a “My forms” form (from step 2), use OneDrive get file content, and if you’re using “Group forms” use the SharePoint get file content action.
Use an expression here as follows
This expression uses the ID of the file that it’s looping through. For each file that was uploaded, it gets the file contents. Note that this will always be an array with a loop, even if you are only allowing one file.
14. Add a SharePoint action in the same loop, Create file. Select your site and library where you’d like the file to be placed. For the file name, use this expression:
15. For the file content, select the file content from the previous action.
16. Add another SharePoint action in the loop, Update file properties. Use the ItemID from the previous action.
This is where you’ll need to place the values of all of the metadata fields from the Microsoft Form that was filled out, from your Get response details action. This part is a huge pain because it’s hard to tell which fields are which:
This may need more arrays if you have more complex fields that involve multiple choices, etc, but that’s the gist of it. To me, trial and error was the best way to tell which field is which, by trying it and selecting different questions to put in the fields on that last step. After the flow has run at least once, you can look at the raw data in the get response details action, to be able to see which question GUID goes with which responses. The end result is that all of the files that they upload to the form will end up in SharePoint, and the metadata questions that they answered will be populated in columns that you’ve created in your library. So, yes if you hadn’t already created columns in your library, those will need to exist, as you see with my “customer” and “hours” columns in the screenshot above.
Note that the files are going to appear as “created by” you, in SharePoint, you the flow creator. If you’d like to capture the name of the person who uploaded the file (besides just in the file name), I recommend creating an additional “person” column in the library, and populate it with “responder’s email” from the dynamic content of the response details.
I’ve detailed this whole process in this video, starting at 23:38.
Here’s the whole flow:
I was hoping to allow people outside of my organization to be able to fill out the form and upload files. Microsoft Forms are great, but there appears to be no way to have anonymous access and allow uploading of files. I suppose this could be a problem if a hacker tried to upload too many files.
Hi! Could be possible to add a word template derived document to an attachment field in a powerapps form (e.g invoice) and editing it adding some more information, working in Word, and then saving it updating the attachment field? If Yes, could be possible to manage the authorization to this attachment document (read only, read/write), base on the status of workflow?
This could be an argument for next power hour videos? Thank you very much!
I don’t know if there’s a way to do that.