Collaborate on Google Invoice Template PDF for Sales with Ease Using airSlate SignNow

Google invoice template pdf for Sales

all right in this video I'm gonna show you how we can create some bulk PDFs out of a spreadsheet and in this case we'll be using Google sheets right so I have this folder in my Google Drive I want to do some setup here in this folder to keep everything organized so the first thing I'm gonna do in this folder I'm gonna create a folder and I'm gonna call this PDFs that's where the PDFs are gonna go when they're actually ready and then I'm gonna create another folder I'm gonna call this one temp files so that will be our temporary files so we need two folders here and we have this folder create PDFs that's pretty much all we're probably gonna need here now what I'm gonna do I'm gonna create two files here one is gonna be Google sheet the other one is gonna be Google Doc so right click Google sheets blank spreadsheet I'm gonna call this one on top of your source data let's lay out some data here really quickly you so this is pretty much my spreadsheet I'm gonna go ahead and set a name for this instead of sheet one people and that's all I'm gonna have any spreadsheet I'm gonna go back to my Google Drive to the same folder that's that source data the spreadsheet now I'm also gonna create a quick document here right click Google Docs blank document I'm gonna call this one template this could be as simple or complicated as you want I'm just gonna do something simple that will use this data hopefully later on to get this document together you I'm also gonna drag a picture here you that's good enough so this is pretty much a template I'm going to use to build my PDFs so what we need to do we need to replace this first last with our data from here and the balance also should be replaced with that balance that's pretty much the idea here so again I'll go back to this this should now show those two files the source data and template I'm gonna do the script in the source data spreadsheet so I'm gonna go here go tools script editor name this project and basically we're gonna start by creating a function that will just create one single PDF that's what I'm gonna call this right now I'm not gonna be using the data from the spreadsheet I'll be just taking this template and creating a PDF out of it let's just set some variables for our data for now instead of using this so I'll just do let first-name and repeat this for the rest of the variables I'm planning to use something like this so what I need to do I need to be able to open this document and use that in the script and I'm gonna open that document by the ID of that document so if I copy this URL of this document and I'm gonna do a comment here in JavaScript and paste it basically the part that we need is the ID so everything after this D slash and up to this slash edit so that's the ID of that document so the first thing we're going to do we're going to make a copy of that document to this temporary files folder now to be able to do that we need to get the ID of our temporary folder as well to know where we're gonna actually copy that file so to get that I'm gonna open that temp files and we have now this so if I copy that the link on top basically here we need everything after this folder slash so that will be the ID of the folder so now let's try to use these variables and basically make a copy in that temporary folder right so this is what I'm gonna do I'm gonna first go to my drive app which will give us access to our Google Drive and in this Google Drive I'm gonna try to get the document by its ID so get file by ID and the ID is gonna be the string in codes for this document so let's just save this in a variable so now this doc file is gonna have a method on it to make a copy hopefully let's see where it is see make copy folder destination and that destination is basically going to be the folder so we need to get access to this folder over here so let's just create a variable for that again we have to use drive app and this time instead of getting file we're gonna do get folder by ID and the same idea works here now here as destination we're gonna give that folder so when we get a copy in that folder we also want to have access to that file so we can get some information out of it so we're gonna save this in a variable as well so this is just gonna create a copy of our template file in this temp files folder that's located in this main folder we have and now once we copied we want to open that and replace all of these curly braket stuff with the data we want so let's do that so to open that file we're gonna use our document app and we're going to use open by ID and to be able to get the ID we're gonna get it from this temporary file so if we do temp file dot get ID that should open that file with that ID after we made a copy of it so we're gonna store a variable for this too so in this document file we basically want to be able to get the content in here and search and replace all this stuff so that means we're gonna go to our script take this and get the body and in that body we should be able to do is it search or replace or replace replace text there it is so it says search pattern and replacement so what I'm gonna be searching is well one of the things I'll be searching is this that says first and I'll be replacing that by this Linda which is our first name variable now I'm gonna have to do this for all the other ones as well so we should be able to change this I think well maybe not we're not gonna be able to change this so what we'll do then we'll just store this part to a variable and then just do replacement in there so let's just do that so I'm just gonna take this and put this in a variable here and then take this body variable and use it in here as a replace so now I'm just gonna copy paste this a couple of times and we also have Leste and which is this and we have balance and we're going to be replacing these with these variables here so once we do all this replacements in that document file we should basically just save and close that file so I'm gonna take that temp document file and do save and close so for now I'm gonna try to run this and make sure that I'm able to actually create that copy of the file in this temp files folder and all the stuff is replaced so I'll go back to this and run do all the permissions that's done let's go check the folder see there is a file copy of template if I open this and I should have for the current mom fear see but that doesn't matter so we were able to replace our values and create a copy document here I'm gonna delete this out of here and let's go and fix this our template didn't change because we made a copy of it and we made a replacement in the copy itself so the template is just gonna stay whatever it is okay so now that we have this and we have the part working that creates the document now we're going to use that temporary document that's created in the folder to create the PDF in PDFs folder which means we also have to know what's the ID for PDFs folder so I'm gonna go to PDFs copy this and again just keep the ID part so let's see how we're going to do that so now once we save all of this we already have access to that temporary document file in here because we basically not that one but this one we made a copy of it and we kept it in a variable temp file so we're gonna take that temp file and we're gonna try to get that file as a PDF so we should be able to do that by doing get s and then we have to do the content type so we should be able to get the content type from our mime types and here let's see there it is PDF so we're gonna get that file as a PDF and we're gonna save it in a variable I'm gonna call it a blob because what this get ass returns is what they call a blob so basically that's a type of thing that you can translate two different things in Drive I don't have a good explanation for it but data transfer type that's what we're gonna do so we're gonna get that information so now we need to save that information in a new file that's a PDF file so we'll do that by taking our Drive app and have it create a file for us and see that file accepts a blob source so because we get the blob here we should be able to pass it in here to create a file now we probably also want to set some sort of name for this new file so we'll do that and we'll say our new PDF so that should create the PDF file and set the name to be our new PDF and I think that's all we have to do in here let's just run this and see what happens so that went through go check what's going on so I don't see the PDF here and I know why because when I made the file I didn't make the file in the folder I just made the file in general see right here so that's not good so this file was still created it was just created in my regular drive if I go here most likely but I want to make sure this is created in the folder not in you know generally and just drive so let's just get that folder so I'm just gonna copy this line and make sure I pass the right ID now we have this PDF folder so now instead of using this drive app to create a file if I do this PDF folder that will also have create file which will now be in the folder instead of just regular Drive so basically we just need to put this code in here to make sure we create it in the folder instead of just someplace in our drive I'm gonna save this around this again so let's go check what happened so now you can see in my PDF folder I have a new PDF file let me just download this so you can see it's actually a PDF file so there it is see my PDF viewer opened it and that's our PDF file that was created in there and if I go to create PDFs well we have the file in here and there is a temp file now in here so every time we run that and I've ran it again remember when I was doing the other thing and we now have two temporary files and every time we run we're gonna create another temporary file now we probably don't want to keep this temporary files here we want to get rid of those so I don't want to do this manually if you would want to do that you could but I want that to happen automatically so I want to basically send them to trash every time I create this PDF automatically so let's do that so I'm gonna go back we'll take our drive app and use remove file and it seems like it's doing the file child so maybe we should be doing this in the folder not in the drive app so let's do this in this temp folder so if we take the temp folder we should be able to also probably do the same thing see you've remove file and then I'm gonna pass that temporary file here so let me try to run this and see if it actually deletes the temporary file but we should still have the PDF right now there should be nothing in both folders let's try to run this and see what happens let's go check so we have our PDF and in our temp files there's nothing now that would probably be in your trash still so you still have to clear the trash but it's not gonna be in this folder anymore so that's fine so we were able to create basically a function that just does what it does so I'm gonna bring move all of these for now and what it does it basically makes the copy creates the PDF just one PDF example now we need to make this reusable so we can then run it through our spreadsheet and basically generate multiple PDFs instead of just one so let's see what arguments this function is gonna need well some arguments is gonna need is the first name the last name and the amount so I'm just gonna go ahead and take those and put them in here it would probably be wiser to pass this as an object instead of doing this but I'm gonna keep it simple so just like that it's fine and then I don't need these anymore because I declare those variables here as a function argument other things I'm gonna have to pass to this function is idea of this the idea of this the idea of this but I don't want to pass the ID I'm gonna pass the excel file and a folder so I'll just take the docx file and put it in here then temp folder and put it in here and then the PDF folder and put it in here too it says these are already declared because I declare this but I don't want to delete these lines yet so I'm gonna just cut them and put them outside of this function we're gonna need these in just a second and finally the other thing I might want to pass this thing is the name of the PDF file so I'm gonna call this PDF name and pass that variable in here someplace so I'll do that in here I guess I think that's all I'm gonna need so what I'm going to do now I'm gonna go on top here and create another function all this create bulk PDFs so there are a few things I need in this function well first thing I'm gonna do I'm gonna move all of these to this function like this and then I also need to get access to this spreadsheet now to get the data out of it so that's our current spreadsheet let's actually save that in a variable and I'll actually just take the current worksheet instead of the spreadsheet itself so I'll link to this people worksheet directly so we can do that by using spread sheet app and we'll do get active spreadsheet so if that wasn't a script that was bound to this remember I made this by doing tools script editor from the spreadsheet itself that's why I can do get active spreadsheet otherwise you would have to do open by ID and pass the spreadsheet ID again on top here it's gonna be the same thing you can replace it with the other one it's gonna work the same way I'm gonna do this and then in that spreadsheet I'm gonna get sheet by the name and the name of my worksheet is called people right here so that will be our current sheet now in that current sheet we're gonna have to get this data starting from a two going all the way through d4 so to do that we're gonna take our current sheet and do get range and the row number we're gonna start from is gonna be to the column we're gonna start from is one first column so that would refer to this a two cell and then after the comma we need to say how many rows we have in our data right now we have three so I'm just gonna type three we're gonna replace it in a second comma and then number of columns in our data is four so I'm going to type four and do get values to get that entire range to an array and save this in our variable I'm gonna call it data now one thing I'm gonna do here I'm gonna replace this three that gives us the number of rows with a way of getting that three dynamically because if we add more people I want this to work too so what I'm gonna do I'm gonna get the last row which is gonna be four and do minus 1 could compensate for this headers on top and that should give us three so to do that instead of this three I'm gonna take my current sheet and do get last row and then do minus one to take the headers off now with this data we're gonna loop through that data by doing for each loop and in this one it's gonna accept a call back each call back is going to be one of the rows in our data I'm gonna call this row and do an arrow function and curly brackets so for each row we're gonna do whatever we're gonna do here in this so this is gonna be looping through this whole thing and then we're gonna pass the row which is going to be the row from here with bro we should be able to access different columns by giving indexes of that so the first one is going to be zero as the first one then 1 then 2 then 3 so if I want the first name that would be row zero so what I'm gonna do here I'm just gonna copy this line that's this function and paste it right in here for a second and here I just have to make sure I pass the right arguments so the first name is gonna be coming from that row from the first column so that's gonna be row 0 the last name is going to be coming from that fro from I believe what was the second column so 1 and then balance is going to be 0 1 2 3 so we're gonna take that and that what we call amount here and that's 3 the doc file is always going to be the same because we're going to be opening the same template file to do this so we already have this document file and then we have the temp folder so we already have that 2 here and PDF folder is gonna be the same so that information is gonna be the same so I'm just gonna grab it from here and pass it in here which is fine now the only thing I have to worry about is the PDF name what are we going to be calling oldest PDFs so this is where we have to figure out what we're gonna call these maybe you want to call this first and last name of the person or usually you would probably use some information from your spreadsheet so I'm just gonna do first and last names for now so I'll take the first name caffeinated with a space and concatenated with the last name something like this and save it now there is a potential that this line could fail and if it fails on one of the lines I don't want to stop probably running the rest of the code I would still want to run the rest but for now let's just test this and see what we get and then we'll address that so I'm gonna go ahead and now run this function create bulk PDFs so I'm gonna change it to that and try to run this and see what we get again permission stuff you there it is let's go check what happened so I'm gonna go back here open PDFs I have three this was probably the old one from the last run so we have Anna brown Joe Smith Linda Doe so let's open in a brown see that's Anna Browns information it has 210 yep that's correct I'm gonna go back and open Joe Smith that's that so far so good I probably want to pass the formatting of this instead of having 100 it should probably use the formatting from the spreadsheet here so to do that I'm just gonna go back to my script and here instead of doing get values we'll do get display values a small replacement but let's just go check and make sure the temp folder is actually empty it is so let's just remove all of this that's good so far so good I want to add a try/catch on this so if it fails I want to basically give it some sort of way to keep continuing this so this will be our try-catch block let mean then this a little more so don't confuse this brackets with this bracket this bracket is closing this for each now what this is gonna do it's gonna try to run this and if it fails instead of stopping the rest of running the rest of the files it's just gonna give us some sort of error now if we have errors we might want to place that someplace in here I guess possibly to say that this file didn't work or this file didn't work for whatever reason so we should be able to do that too and I could just create a variable here and I'll use an empty array and what we'll do if we have an error we'll take that errors array and we'll push to that right whatever error we're gonna basically be getting out of this I'm not gonna get too crazy about it I'm just gonna say failed and over here so that same errors I could do this in a prettier code I guess but this is fine I'll just pass nothing so if there's no error and it goes through with this we'll just push an empty string if there is an error we'll push that it failed that should be good enough for this so what we'll do after this whole thing is done we'll just write that to our spreadsheet now to be able to write that to our spreadsheet we should be pushing this as a row as an array not as just one string so this way we should be able to just write it back right away without having to do any transformation so what I have to do now is just write it back to the spreadsheet any errors we might have so I'll just take this line copy and paste it here and we don't need this to be stored in a variable so I'm just gonna do this so we're gonna get the range so we're we're gonna be writing this is where you're gonna be writing your errors so assuming that's gonna be column e that will be column number one two three four five so if I wanted to do the range it would be from the second row fifth column and the number of rows is still gonna be the same thing as we did on top here to get this number and it might be wise to store it in a variable but I'm gonna leave that up to you and the number of columns in this case because I'm just writing to this range it's just one column so this is gonna be one not four and in this case we don't want to do get values we want to do set values and the values we're gonna use are this errors I don't know how I'm gonna test this to see if there's an error well I can get an error for all of them I guess that should be fine so let's just miss type this variable okay so that kind of ran through it let's go check what happened so see it says failed for all of this because well they all failed it shouldn't have created any PDS for us and there's nothing so now I'm gonna just correct this variable and now we should be able to run this thing and now we should have our hopefully pdfs here there we are Anna Jo Linda all good now we have our pdfs and if we check our pdfs now see the formatting looks better all of that works so we have our PDF so I'm gonna remove these anyways but hopefully you understand how this works now so finally you could assign this function to a button so I'm gonna copy that go to my spreadsheet and add a button here anyways you can go crazy about this to make it pretty just gonna keep it simple click on this little dots a sign script and paste the name of that function hit OK and now you should be able to just click on this and it should just run this and create your PDF right away just do that one last time close this hmm let's make sure I save this okay create PDFs so now if I go back here there we go our pdfs are created based on our template and you can go back and modify your template or modify your spreadsheet with new names and click on it and it should just work and that should do it for this video thanks for watching please subscribe and I'll see you next one