Google Forms to PDF & Email while storing data in Google Sheets

alright so in this video we're going to take a Google Form and have a user submit the form and take the information and convert that to a PDF and then if I'm not totally bored by the end of the video we'll probably also email that PDF over to the person who submits the form but we'll see how it goes so I'm gonna go pretty quickly because there's a lot to cover so I'm gonna start by doing some setup here so two folders and I'm gonna create a Google Doc and Google sheets and a Google Form you all right so all the fields that I'm going to replace I'm just gonna encapsulate them in these brackets like this so FN L n QT y and a DDR you so an empty spreadsheet finally deform you so let's just preview this our form first-name lastname shipping address and quantity which is what we're going to be using in this template to create a PDF out of this and all of that right now is located in this folder I have in my Google Drive I have a couple of empty folders here and this three files now let's go check out the documentation for google form events so that's their event objects and for Google sheet events we have form submit and that's what we can get out of that so that's good we're gonna need this now before we get to that however let's make sure we connect our form to our spreadsheet so select response destination and link to the spreadsheet and just select a spreadsheet so if you're not familiar how to do any of this stuff I have videos covering all of this how to create forms link to spreadsheets make documents right now let's just test the form really quickly after we did this so that's submitted let's go check out the form form responses and there it is so now we have a connected worksheet I'm gonna go ahead and delete this one and keep this okay good so the form is ready it's basically doing what it's supposed to do now let's get to our spreadsheet and open our script editor and start writing some code I'm gonna start by creating a function to make a PDF file so we need to set some variables you so I need IDs for our PDF folder temp folder and template document so let's go get all of those so PDF folder and all of that we're gonna get from the URL here on top and the document we gonna get from the document and we're going to make sure we just keep the IDs all right so let's start by grabbing that temporary template document and making a copy inside of our temporary folder and that will be our new temporary file so we'll need to take that temporary file make some changes to it and then save it back which is what we're going to do here but after we do all of that we're gonna then take that temporary file and make a PDF out of it and we want to put that PDF inside of that PDFs folder you now that should get it as a blob the file we have out of here and then we'll just create a file out of that in our folder with this name now we need to make sure we make some changes to that document because we don't want to just make a PDF out of this we want to replace this FN L n QT Y whatever with all the information that was sent to us now the way that information is going to come from the form is gonna be in this form or we can also get it in this form which is array with indexes so we can say 1st 2nd 3rd 4th column I'm gonna use the object like this so let's just have this as a reference so these are going to be basically names and column names in our case are basically these first-name lastname shipping etc and apparently it comes back as an array here now what we're going to do we're going to use this information in this file so let's go ahead and open this file as a document so we'll get the ID of the file and we'll open that as a document so basically in this document application set a reference to it 9 that document would basically have to get this body and replace all of these with the appropriate fields now we're gonna take that body and all the replacements now we need to replace the first name with this first name so that's going to come from this info object and that's gonna give us this array we just need the first element in that array since this is just a regular text field there are no multiple things in here so I'm gonna just repeat this for the rest of these so I believe those were the names so now I just have to match the column names from here so once we make all those replacements we'll just save this document and close it and then after all of that is done we'll make a PDF out of it which is already down here and give it a name so I'm just gonna run this to see if this works this is this function create PDF all the permissions so now if I go check my docks see I have this temporary docks with this and we should have a PDF there it is so the PDF is done the document is done maybe we could also rename the document or we can delete the document one or the other I am just going to delete the document so what I'll do here after this is done we'll just take the temporary folder remove the file which is gonna be this temp file so now that we know this function works we're just gonna convert it to a reusable function so what that means is that this we're gonna remove out of here and we're gonna send it as info and then we'll also add a file name so I'm thinking the file name we can just do from this info right away without having to send that information so let's just keep it this way so let's just make the file name something out of DS so I'll just take that the first name and combine it with a space and combine it with the last name for now that should be good enough so now we need to run this function when somebody submits the form and we need to grab this information and create that PDF right so let's create a function for that so that function is simply gonna run this function and we're gonna need this info for that function to be able to do this and get what it needs out of this this is where I'm gonna go back to that documentation so if you look here this form submit it's gonna have this event object with name values in it which should be this object that I need so what that means is that I'm just going to go here and pass the event and then my info is going to be equal to that a dot named values so now we need to make sure this function actually runs when we submit the form so we're gonna set up a trigger so edit current project triggers add a trigger and we want to run this function after form submit that's going to be a spreadsheet event and it's gonna be on Form submit if there are any errors will get daily notifications that's fine my popup blocker is not letting me do this so I'm going to allow that and set the permissions all right so hopefully that hole blocking thing didn't mess up the entire thing but it looks like we're fine so now that we set this trigger let's go and try to submit the form again to see what happens and I'm gonna do submit form so that was submitted right now let's go first of all check our spreadsheet so as you can see there's Linda Smith and all the information there let's go check what happened look at that we have Linda Smith as a PDF and all that information in that PDF so now we have a PDF from our submitted form and again all of that is using this template document so if you go and change this template that will also change the results of that PDF now this temp Docs I did delete that we also need to remove this one but we don't keep the temporary Docs we just keep the PDF you could keep the Docs all you had to do is just not remove this in the end all right so that's good I also want to try to add a timestamp to this filename so let's go ahead and add an extra space in here and then some sort of timestamp so we're gonna do a new date object and just let it convert it to string probably that should be fine let's try this and see what happens if it doesn't work we'll have to move it to a different variable and then convert it to string ourselves but I don't think it's necessary so let's go check this out that's done let's go check this out this is good so now we have our filename with a timestamp and everything okay so the PDF was made now it would be nice to also add a link to that PDF in the spreadsheet so I guess let's go and add a column here you have to be careful with this because if you're expanding your form it's gonna expand over this and it's gonna overwrite this in some cases it might be better to put this column maybe a few columns over or what I actually prefer to do most of the time is not to mess around with this spreadsheet and create another worksheet here and just make a copy of what I need from here so I can say that equals to this drop the end reference and do command shift ENTER to do an array formula so that way I have a duplicate of the same exact thing on this worksheet but here I know that I'm gonna grab those columns and if I'm changing this at least it's going to go by these columns so that's not gonna touch our data in this spreadsheet but that's up to you I'm gonna let you decide what you want to do about that now what I want to do I want to be able to come here and in this column add a link to the PDF that was created now to do that we need to make sure we get that PDF back so when we do this create PDF we make this PDF file right here and what we can do we can simply just return that PDF file or in our case we could just return a link to that PDF file I don't know so let's just return the PDF file itself so that means now this is gonna return a PDF file so let's go and take a look at that docks to see where are we gonna put the name of that PDF file or de link not the name so we need to figure out which row that needs to go to and that should be available probably in the event itself see it says range representing the cell or range of cells that were edited okay that should work so if we do that e dot range that would be the range of cells that was edited meaning when it's adding this well actually it's doing it here but it's gonna be the same thing because we're copying it right but it's gonna be this range now from this range if we just figure out what row that was we should have what we need so let's just see what the matter is called pretty simple getro so if we do this should give us the row of our entry I believe I guess we'll find out if it's not so once we figure out the row for our entry now we need to go to our people worksheet and put that in this F column in that row so that's our spreadsheet app get active spreadsheet and in that active spreadsheet we need to get the worksheet called people I think I called it and then we're gonna do actually we need to get the range first and that should accept a row number and a column number so the row number should be this the entry row and the column number is gonna be whatever column it was 1 2 3 4 5 6 and we need to set the value of that row here to whatever it needs to be and that needs to be the link to our PDF file so we'll take our PDF file and we need to figure out what the method is called again looks like geturl is the method just like that so let's try this and see what happens again so I'm gonna save this go back and try my form one more time so we got this doesn't look like we have the link to the PDF file let's go check if we have the PDF file it looks like it was still working on it so it finally finished this file there it is I want to go check if that was actually finally oh there it is so it takes a little bit apparently it's actually in here and if we open it that's a link to that PDF very nice we got the PDF file we got the link let's actually just add the filename here too I'm gonna move this to a variable here and then I'm gonna do this to get the URL and then I also need the name of the file which is gonna be the next column over column number seven and instead of get URL it's probably get name since this is set name this probably get name if it's not we'll go back and figure it out so I'm gonna save this again one more time I'm going to test this all right all sent it's probably gonna take a little bit for all of this to populate so let's give it some time all right not that long but there it is we got the name of the file we got the URL took longer the first time so all of that is done I guess the only thing left to do at this point is to just make sure we email the file over and I've just realized we don't have emails so maybe we should ask them for their email all right let's go do that so now we should have emails too and we have to see where that appears but it should be somewhere in here see there it is a new column because of that we need to move this and this need change to f2 add that column and we need the name of the column probably and we also need to change this column numbers so this is now 7 this is now 8 and you could do matching to figure out which column is which but we're not gonna do that save we got all of that that should be their email address which we are going to use now we're not gonna use that in the actual PDF we'll just use it in sending the email out so for that after we did all of this we need to send the email so let's create a different function here so we're going to use Gmail app and that should have send email as one of the methods which is gonna have where it's gonna be sent which I'm gonna pass it as a variable to this function the subject line the body which will be the text body so this is the message in their email body and in this options we should be able to do attachments so let's see how we do attachments there it is so it's an object with attachment we even have an example here how nice is that copy that go back to this and that was the object right in here so this should be the actual name of who's sending this so you can just say my company and you don't really have to do that at all you can just skip this but we'll keep it and we need the attachment so it has a file as PDF so basically a PDF file which will be passing again as a variable here now as I'm doing this maybe we shouldn't have to do another function for this maybe we could just incorporate it here it's not that many lines but you know it's up to you so I'm gonna go here and do that send email so we need email for that person so that's gonna come out of this een aimed values and the name of the field was cold that and the PDF file is gonna be this PDF file we already have it right here so nothing else it should match okay so now there's only one thing left to do is to test this and check our email and see if we have it so here's our sent folder there's nothing happening here we're gonna have to check if everything was sent once we test this let's go back to our form let's just make sure we open a new one alright so that was sent here let's see what happens on our spreadsheet and our Gmail accounts so let's first of all go check our spreadsheet so our spreadsheet has the ID with the length and all of that and if I open this that's the file very good let's go check our email and nothing was sent so what did I do oh I forgot that it brings those things as an array apparently that's probably what's going on here this should be zero we'll have to test this and see what happens you okay unfortunately I can't see that anything was sent apparently the executions transcript says that it doesn't have permissions because well because I forgot to give those permissions so let's do that run that send email function for a second yeah that's fine so it is going to be invalid but it should give all the permissions now let's try it one more time I guess okay recorded let's go check what happened finally let's check out if we have a PDF attachment we do and let's download it and there we have it to Peter Peters we were able to email this out very good so let's also go check our link yeah there it is that's Peter Peters and responses are going here and that's it and that's how we can take this from a Google form to a spreadsheet then to a Google document then make a PDF out of it and then put that PDF back in a spreadsheet and email it out with Gmail all of that in a single script and that should do it for this video thanks for watching please subscribe and I'll see the next one

Channel: Learn Google Spreadsheets

Views: 83,373

Keywords: Google Forms, PDF, Email, Google Sheets, script

Id: EpZGvKIHmR8

Length: 27min 2sec (1622 seconds)

Published: Tue May 26 2020