Collaborate on Hourly Invoice Template Google Docs for Engineering with Ease Using airSlate SignNow

Hourly invoice template google docs for Engineering

hi i'm jeff everhart and in this short tutorial we're going to talk about how to autofill a google document template from google spreadsheet data to get started we'll need a few things the first of which being your spreadsheet if you look at my sheet you'll see it has some typical data about employees like first name last name their position hire date and so on in this last column labeled document link i've left that blank so that we can use that to store the url to the google document that gets created by our script in addition to our spreadsheet you'll also need a folder to store the documents that we generate and a google doc template with the google doc template you can go ahead and design that document however you would typically but wherever we want to include data from our google sheet we'll need to include what are called replacement tags now i've written my replacement tags by using two curly braces a descriptive name for the data that i'm merging from my spreadsheet followed by two closing curly braces and now these can really be whatever you want them to be based on the data in your sheet but there are two conditions that we'll need to make sure we meet the first is that each replacement tag is unique to the document meaning that it only appears once and secondly that whatever we include as a replacement tag gets matches our script exactly so now let's hop back over to our spreadsheet and get started coding writing our script i'm going to go to the tools menu and then open up the script editor which will pop up in another window and i'll just go ahead and dismiss this little bubble and so the first thing that we're going to do is we're going to write a function called on open and this is a simple trigger in google apps script and what this is going to do is this is going to allow us to add a menu item to our spreadsheet so the first thing i'll do is i'll declare a constant variable called ui set that equal to spreadsheet app dot get ui what this is going to do is return an instance of the spreadsheet ui that we can then use to add a menu so we'll do that next we'll create another variable called menu then we'll say ui.createmenu then we're going to give it a caption we'll call this autofill docs and then to this menu we're going to add a menu item we'll need another caption for this and so this this will be create new docs and right now we don't have another function specified in our script file um so we'll have to write that next but i'll we'll assume that we're going to call that function create new google docs and then lastly now that we have this menu created and have added an item we're going to add that to the ui using menu dot add to ui so we'll go ahead add a semicolon there and just save our script file and then if we pop back open into our spreadsheet and refresh after this working sign goes away we should have a menu item right here there we have our autofill docs menu and if we click that we have our menu item create new docs now if i click on that we see we get this error but that's to be expected because we still need to write our create new google docs function so i'm going to open back up the script editor and right below our on open function i'm going to open up another function and we have already named this create new google docs so we'll use that name and then we'll just give ourselves some room to work okay so now that we have this menu item created that calls this function what is this function going to do this function is going to loop through our google spreadsheet rows and if there is not a document link already created it's going to go ahead and generate us a new google document so we'll need to get a couple variables that we're going to use throughout this so the first one is going to be our google doc template so the first thing that we'll do is create a variable for the google doc template so do a const google talk template equals drive app dot get file by id and then we're going to pass this a string that is the file id i'm going to hop over here to my google document template and if i look at the url for this we can see that right to the left of edit there is a long alphanumerical string and that is our document id so i'm just going to copy that into these quotation marks and there's our first variable that we need so let's see where are we going from there from there we're actually going to get uh the destination folder as well as a variable so we're going to use driveapp.getfolderbyid very similar method and then we'll hop back out here to our destination folder which is called documents copy the id from that and then we'll go ahead and pop that in our script editor add a semicolon now that i have my google doc template stored as a variable and my destination folder stored as a variable let's work on getting the data out of the sheet uh so the next thing we'll do is we'll create a sheet variable that's going to store our spread spreadsheet so there we'll do sheet equals spread [Music] sheet dot get active spreadsheet meaning the spreadsheet that this script is attached to then we'll do get sheet by name and then what we'll hop out here and do is just double check the name of the sheet which is called data so we'll pass that in there don't forget our semicolon and then now that we've got a reference to the sheet we're going to want to get all of the rows from the sheet so we'll do that and we'll create a rows variable that is going to be sheet so we're operating on this google spreadsheet that we've returned and then we're going to do get data range which is going to get us the max extent of data so in this case it's going to get us five columns in four rows including our header row and it's going to return that in a 2d array so do getdatarange.getvalues and we can see there that that's going to return this two-dimensional array and then let's go ahead just so we can see what we're looking at and we're going to log our rows to the logger so go ahead and save that and then if i come up here and switch my function to create new google docs click this run button and then as soon as that's done ah we need to go ahead and authorize this okay so we'll review these permissions click advanced to get through this scary google nonsense and click allow and then our script should go ahead and run and when it does we come up here to view logs and we can see what that looks like right so we get back an array and then each row inside the spreadsheet is its own array and so we can see there's our header row there's our first row with our first person second row with second person so on and so forth so that's just kind of what that looks like so we'll go ahead and remove that because we don't need that anymore and now that we have our rows what we're going to do is we're going to use something called the for each loop to iterate over our rows and so inside of our for each loop we're going to pass a function and then that function is going to get the row the current row as it iterates through the array and then the index of that particular array item and we'll talk about both of those things how they're different and why they're important in just a second okay so now that we've got our for each loop set up and the bones of it stubbed out um we can go ahead and start writing some of the logic that goes inside of it now the first thing that we're going to do is we're actually going to check what index we're on because we have this header row that's included in our our rows variable right here and we don't necessarily want that so basically we want to skip that each time we loop through this array so a really easy way to do that is we can do this if statement and then inside of this if we're going to say if the index triple equals zero um then we're just going to return which means that we're going to return the function call and we're not going to execute any of the logic below that it's very important that we're when we're doing these kinds of comparisons to remember that one equal sign equals variable assignment like we have up here so if we want to set something equal to something we use one equal sign this is not strict equality and this is strict equality so just know that in this instance we want to do if index triple equals 0 we're just going to return in addition what we also want to do is if there's a value in this document link column we also want to just return because we don't want to double create documents for our employees so what we'll do down here is i'm going to do another if statement and then over here we're going to access the current row so row in the context of this for each loop loop represents the current array item so it represents the current row we already know that if if it's the first row or the zeroth row in the array we're going to skip it so that second one we would come down to is here and so what we want to do is we want to access the fifth index of that array now with javascript arrays everything it starts with zero so we've got essentially row index zero one two three four five so we wanna get that fifth index and then so basically what we want to do is say if there's something there then just go ahead and return two because we don't want to double create a document and i'll show you how that's going to work in practice here in just a second um okay have those kinds of conditions out of the way um let's go ahead and just do another little bit of logging so we can see where we're at so we'll do logger.log and then we'll just have that log out the row um so what we can see here is we'll we'll just see what this gets us when we run our for each loop so we'll go ahead and click run that'll run then we can click view logs here we can see we have a logging statement for each one of our rows now let's see what happens if we add in a value here so we'll change our spreadsheet and just throw any kind of value in that particular that particular column we'll click ok and then let's come back in here and run our run our script one more time so do view logs you can see that our conditional check worked right because there's a value in this in this column for this row so our for each loop skips that and returns and doesn't doesn't execute any of that logic so it's a very handy way that we can we can control basically when documents get created by what appears in a particular column so lots of other applications that you could use in other types of scripts okay so now that we've got all of that stuff out of the way we actually want to if once we get to this point anything that gets here we actually want to create a document so let's go about making that happen so the first thing that we're going to want to do is for each row in our spreadsheet that doesn't already have a document we're going to want to create a copy of that of that google document template so we'll go ahead and declare another constant variable and we'll call this copy we'll set that equal to this google doc template which is our our variable up here and then we're going to use the make copy uh method with this option for a string name and then a destination and so in this destination we're going to pass in our destination folder and i might just copy that just to make sure that yeah that takes over okay so what we're going to want to do here is pass a name okay and so i'm what i'm going to do is i'm going to automatically generate a unique name for this document based on the the values that are in our spreadsheet row and to do that i'm going to use something called a template literal in javascript and we create that by using two backticks which should be at the top left of most keyboards so what i'm going to do is i'm going to open up a template literal with those characters and then what a template literal lets me do is basically interpolate variables so what i can do here is use a dollar sign and then curly brace curl open curly brace closing curly brace and then i can pass in our row and i can say that i want to put this first name which is the row the first index of the row variable then we'll do outside of that which is still a string we'll do a comma and then we'll do row 2 and then outside of that employee details okay and so now that that looks good so we've got our copy then next we'll actually open that copy as a google document so declare another variable call that doc set that equal to our copy up nope sorry we're going to do document app dot open by id and then what we're going to do is pass in our copy and then we're going to call get id on copy and so that's going to return the id of this copy we just made and that that's going to open that as a google document so that we can manipulate it using the document app so from here we're going to do a little bit of repetitive stuff based on um uh sonar sorry sorry skipped a step so let's now we have our document we're going to want to get the body of that document because the body is where all of the uh all of the meat lives so document.getbody that's going to return us the body and then i'm going to do one more thing just to help me format my data a little bit because sometimes when you have dates and spreadsheets the values that they return to google apps script can be kind of wonky so i'm just going to use a javascript method to to even that out a little bit so what we're going to do is call this friendly date and then it's going to be equal to a new we're going to make a new date from our row data then we're going to on that new date we're going to call a method called two locale date string and what that's going to do is that's going to use whatever your your your current locale is to format that date in a user-friendly way so now that we have that what we're going to do is we're going to call a couple methods on our document body to replace those replace tags and i actually have some of this stuff saved off in another script so i'm going to hop back there and we'll just kind of copy and paste it since all of it's pretty much the same and then i can i can walk you through how this works so the method that we're going to use is called body replace text and remember we're calling this on the body variable that we just declared here and now this replace text method takes two arguments the first one is a string that references our replacement tag remember we talked about these in the google google doc right and so an easy way to do this is just to come in here copy whatever this is hop back in here and then copy that in our string so that's step one is getting we want to pass the replacement text as a string to this replace text function the second thing that we're going to pass in is the data that we want to replace this text with so in that case each one of these is referencing our row in our for each loop up here and then the index of that particular row or in this case this this variable that we have down here friendly date so we can do that as many times as we want if you have more data that you're pulling into the google doc template obviously you may have additional additional calls to this replace text method that i don't have so that's really going to be specific to your document so after we've done that we've replaced the text then we're going to want to come down here and we're going to again on our document call a method called save and close and so this is going to essentially persist all of the changes that you made using this replace text function and so it's important to remember that those changes won't be permanent until you call that doc's save and close method and so now that we have that we only really have one more thing that we want to do and that's going to be come coming back into our sheet and writing the link to that newly created document back there so we'll declare another variable to store the url for the document and to do that we'll just call url equals doc dot get you guessed it folks get url so that's going to give us the document url and then what we're going to want to do from there is we still have our sheets stored in a variable so that what we're going to do is call set range here we're going to have to do a little math inside of get range okay so remember we have this index that gets passed in each time we iterate through this for each loop okay so as we come through and we loop through each one of these things we get we kind of know where we're at in the in the data model of the spreadsheet so what we're going to want to do is we're going to want to take that array index and basically map that back to a spreadsheet cell and to do that what we're going to do is so we're going to pass an index but if you remember when we when we talk about the indexing of javascript arrays everything starts at zero where a google sheet row or column starts at one so this is the first column not the not the second or the zeroth where in javascript this is the zeroth index and i know that can be a little bit confusing but so what suffice it to say google sheets looks at it this as the first thing javascript looks at it as the zeroth thing so what we're going to need to do is we know if our index is essentially one less than what google sheets would consider that we'll just add one to that so index plus one is going to get us our row and then our column is always going to be static right here with document links so that is one two three four five six so that is our sixth column so we'll do that and then we'll just call a method on that range that is set value and then what we're going to do is pass url into that method okay so we'll go ahead and save that out and now what we can do is we can actually hop back into the spreadsheet now that we have that function written we should be able to click autofill docs click create new docs it's going to ask us to do another round of authorization since we've changed the scope of what we're asking for again we'll click through advanced go ahead and click allow and then that should churn for just a second we might need to fire it off one more time but we'll give it a second to just turn we'll go ahead and sometimes when when we authorize via this add-on menu it doesn't always execute the script at the same time so we'll go ahead and create that click that one more time to run our script and we don't have any errors yet okay sheet dot set range is not a function okay yes let's hop back in here and look and okay great that's just a simple typo so instead of sheet dot set range we actually want to get range um and then set the value so we're getting the range and then setting the value on that range okay so we'll go ahead and dismiss that give this one more try and that's great because that meant we were really close to getting this through so this is going to run then eventually it'll finish and we should get links to our documents and so if we pop back out to our documents folder and we refresh that we can see that um we have a new google docs template that is hopefully merged with all of the spreadsheet data that we have okay great and then we also get the url to that document written back to the spreadsheet now what's neat about this strategy one running it from a spreadsheet menu item and then two also kind of including this is that we could go ahead and say we needed to regenerate this document somebody gets married they change positions or their pay rate goes up we want to regenerate this document we just come back in here and i'll go ahead and we'll remove these two documents for this one employee and then if we come back in here and click this button again we'll just make sure that we've clicked out of there so that the spreadsheet registers there's nothing there we run this again we will get a new document generated just for that one employee where that one value is missing so a very flexible pattern lots of cool stuff going on here hopefully you all enjoyed it if you have any questions about how to get this script running be sure to post them down in the comments on the youtube video or on my blog thanks for watching