Copying Microsoft Forms Attachments to a SharePoint List Item using Power Automate

hi my name is jeff rhodes and this is another video on microsoft 365 and inspired by my book that you can see here so what we're going to talk about today is going from microsoft forms attaching a file or one or more files and then having that automatically get copied to sharepoint but then rather than just the individual files we want to use power automate to make a sharepoint list item for each one and then attach put the attachment in there on the list item so kind of cool in real world had to do this recently uh and wanted to share with you so let me first give credit to a guy named norm young so you can see his post here so most of what i am showing you i learned from this post i had to change it a little bit norm's post talked about going into uh getting it out of onedrive for business and uh i want it to go to sharepoint so i'll make this a group form and then that instead of putting in one drive it puts the uh both the excel file which i'm not using but the attachment into sharepoint which i think is more appropriate in most cases like to teach people that onedrive's for your personal files and team sharepoint are for organizational files which would normally be like the results of a question so let's get started but thanks to norm for getting me going so we're going to start in forms here and what i want to do is make it right as a group form okay so i've got a couple groups and two of them could name black canyon so i already kind of found the right one hopefully that i'm gonna go into so i'm gonna go into group and say new group form that just keeps me from having to make a personal and then move it to a group and does another step so i'm just going to call this attachment test and what i want is i literally only going to have one question so i'm going to call it do a text long answer i'll just say describe the document and i'll make it required and then i'm just going to add a file upload and i'll limit it to three files i'll make it i'm not going to make this required so it's sometimes nice to test it without a file so maybe we don't need that if i go to my settings i want to go ahead and record the name so that i know you know i'm going to use that and put it in my list so that looks good i'm going to just preview it for now so that we can see what it's going to look like so i'm just going to say christmas picture and i'm going to upload the file and there's a christmas file right there and submit so let me go over to my sharepoint site that's a good sign it created this so notice i'm in the documents library and it created apps microsoft forms test the question and then the list i'm a little bit surprised it said question there so maybe i didn't let me go look at it again it's kind of oh that's because i didn't it didn't save my lit work there so i should have said uh please provide up to three uploads or three documents so that's an oversight so let's do this again test two i'll upload that file again and submit and if we go over to sharepoint let's refresh it and it's still called a question which i don't really like so let me see if i can get rid of this and do it again so let's delete this and we'll try it again oops preview it it's called christmas test again my christmas tree oops ah let me pause for a minute while i figure that out all right i'm back and i learned something so i recreated the form and same thing is it it made it so i still had apps microsoft forms i called the new one upload attachments but it's still called a question so i thought when i did this before it would name the question but it doesn't really matter it gave me my attachment so we'll we'll go ahead and move on so uh that works out well now but again i don't want it this way i want to make a list so let's go ahead and just put it in the same site for simplicity so i'm going to go to site contents and i'll say new list blank list and i'll just call it attachments like that and then i like to go to the old school list settings so i'll do it there notice attachment i mean the title is always there so i'm going to call this email address with the idea that this will be the person who entered the form and then i will add one more all i really need is the description see i've used that before i'll make that multiple lines i don't care about plain enhanced text i'll make it plain text and then i don't need to add anything for attachment because the sharepoint attachment can already be there you know so in other words it already has an attachment so let's move on let's go to office and let's go to power automate now so let me go to all apps power automate and my flows and i'll create a new automated cloud flow and i'll just say copy attachment and responses from forms that's a nice long name and you see it's remembered i've done this before so i'm going to trigger it when a new response is submitted now one of the things that's a challenge here is it's got my old thing i was playing with but it doesn't have that upload attachment so what i have to do is i need to enter do a custom value so it can be a little challenging to get the to get the form id is what we need there but if i look at the url see right here i've got the form id so i'm going to hit shift and end and see if i can get that that whole bit right there is the form id so the minute i make that a group form then i need that so let's use that and paste it in just press for you whoops press return like say see new steps there we go so now it's kind of founded okay and then i need forms again i get response details so it again wants that form so i'll enter custom value put the form id in and then the res the identifier we can just pull it like that all right so what we want to do next is we want to create a list item so we type in sharepoint here and we say create item and we see if it remembers actually i don't remember what that one is so let's go over here and just make sure we have the right one that's where our list is there we go oops so it looks like it's that one cites black canyon and then attachments list let's make sure that's the right one yep attachment list and then what i love about power automate is it gives me the choices so i'm going to say okay my email is actually the responder's email and then the description is please describe the files and i save so let's do this part for now because what we need to do in a minute is to get our hands on the json of the attachment so we want to leave it there and then run it so let's go back over to this form if i can find it so i can do it again so we'll call it uh response2 and very importantly i'm going to upload a file again let's see if i can find some document what is well boarding pass of mine there we go submit all right so let's go look at power automate and see if it ran successfully which it did which is great so we got our details and we're able to create the item so so far so good it's got some of our stuff there we go got some things in there all right good deal so let's go check our list see if it got added it did didn't but notice if i edit this so there's no attachment here so that's what we want to do next is to get our hands on the attachment so so far so good so let's go back and edit this some more so what we want to do next is we want to we want to add a condition so let's go in here and condition and say okay we'll want to just check if there is an attachment so if we come in here say [Music] so notice we've got like that's all the the sharepoint stuff but what we want is to please load the files so if that's not equal to blank then if it's yes then we do something called compose so we do a data operation compose and again we'll grab that out of the list of this please upload that so let's save it again because what this is going to be is some json that we got to see what it looks like let's cancel we want to save that first so we'll go back here and submit another response we'll just call it response 3. upload something else oh who knows what is going on maybe i better go back to pictures again actually i know my d drive we got a bunch of pictures there we go let's look at me there we go picture me my dad way back for win so that'd be kind of fun and submit so let's go look at our flow again there we go succeeded actually i don't see the that's not the right one oh yeah i need to get the newest one there we go yeah and so what we want to do is we want to get this output or get this json right here so we get it so what we want to do is copy this whole thing let me just fire up notepad real quick and we'll i'll show you what the whole thing looks like let me move that to the right spot so we bring that over here and if we copy in that json see it gives us so if we look this is the name of the file and this is the whole url and then an id and some other stuff all right so let's go back to our flow all right so we go back over here and we add another action and we want to parse the json like that and what we want to grab is the output so notice that nice and we want to the schema we want to generate from sample so we can just paste in that and say okay we're done and what happens is it's nice and it gives us all the things that we wanted so we're at that name and link and id it just gives us everything we want including the size and the reference and so forth so that's exactly what we want so we do another one and it's a little bit involved but we want to do control and we want to apply to each because what's going on there is that we have one of these for each one of the files that they upload so i'm going to pick this again and i'm going to again grab the body but notice it gives me all the other things which we're going to use in a minute so what we want to do now is we want to go back to sharepoint and we want we're looking for i'll just search for it get file content file content using path and this is the part that's different than that article i mentioned because this one we want to do in sharepoint and not in onedrive all right so what it wants is the site address okay so that's relatively easy we have that from here so we're going to get just all the way up to the address so we'll put that in and then it wants the the path so that one whoops paste that in again so it wants me to select the site and then it wants the file now this one's a little bit challenging because we gotta we gotta get the uh we wanna use a variable but we can come in here and click it and then find shared documents apps forms question and then notice it's got my one so i'll just pick one of those through now what i like to do is just copy that whole thing and we had some trouble with doing this before to see if i can get it to put the right spot in but we'll try it because what we want is we want to go back to the thing and get the name of the file ah yeah so it kind of worked but we got to get rid of this part because it still had it so remember when i thought this might be different than question but it seems like it's all working so that's the dynamic path to the file okay so that almost there and then once we have that then we're going to go sharepoint again [Music] and we'll search in oh there it was add attachment okay so we again let's see if i still have that in the clipboard nope see if i can find it i'll just go over and get it just to be sure so we have that still to copy pick the list which is attachment list all right so now we got to get the id so again we're looking so so this is kind of from the most current so we're going to get the id that's of the list item that we just found okay and then the file name we'll call it the same file name as was the user gave it so we got to scroll this down and again use the file name from the json and then the content is what we read so that basically came both of these came from this get file content used in the past so it basically read the file and that was it so kind of a lot of stuff but let's let's save it and let's try it again so we're go back and we'll run it so we'll i'll just call this the real mccoy the idea this is the first one that hopefully will actually have that so we'll do another oh let's find another picture there we go do a picture here's what i look like in high school i don't like that so i can buy dates here all right so we'll submit all right let's go over let's see what what uh power automate has to say it says it's running so let's go let's reload it again let's take it a minute so we'll ah it succeeded so that's good it took 16 seconds so it took a little longer than normal but it ran through here said applied to each got the content you can see it got its path so that looks got the name of the file correctly got the download added as attachment the whole url so so far so good looking pretty good so let's check it out let's go over to this i'mma refresh this for a while so it should be there each one of them got the email and the description but let's go look and see if this one has an attachment oh that's good sign there's the attachment if we click on it all right there's that old mug from a long time ago so we're able to go through and get the attachment as well so what ends up in the end is that we have both copies so if we kind of go back and look so now going forward every time this gets answered it'll put the attachment in and put the the data in in this case just the user and the description if i go back to documents though it's also got this list so it's for whatever reason the name of the file is is untitled so i'm not entirely sure why that is maybe i didn't yeah set the form so it had noticed that it gives the name of the attachment here in the form so i could find it if i want to use the excel because it has the name of the of the file and then uh must be in the actual name of the document i call this upload attachment so yeah you'll have to play with that and see why it said un unnamed untitled file but it does have the answers here and then if i go back then inside here it's got there's got the name upload attachment and then it's got all the files in one directory so let's do one more test on this just to make sure it works for multiple ones so let's do i'll do the complete one let's share it i'll make a shorter url copy it let's put it in the browser and i'll just say test of multiple attachments so i'll put a picture of my dad there i'll upload let's go back to christmas and let me upload one more i'll upload that boarding pass we were doing before my wife's there we go and submit so we'll go over to power automate just see how long it takes so we figure it's probably longer than 16 seconds because that's what it took while it was just doing one file so there's 18 seconds ah so it took about 18 seconds let's go over we got those we got another copy notice that a should have a different name so notice i put the number one on that since i uploaded the same file and that could happen people could upload the same file uh you know multiple people named it you know travel voucher one or something like that so let's go over to the list which i didn't put that on the bar there so let's go to attachments list here there's test of multiple attachments let's edit that ah good sign it did each one let's try the christmas picture there we go that's what our christmas uh tree looks like so that's it all right well i know kind of a long video hope that it was useful and have a great rest this holiday season and we'll see you next time

Channel: Jeff Rhodes

Views: 12,177

Keywords: Attachments, JSON, Microsoft Forms, Power Automate, SharePoint

Id: 5IyCVHqvIXA

Length: 26min 43sec (1603 seconds)

Published: Thu Dec 30 2021