Automated Invoice in Google Sheets for Purchasing Made Simple

Automated invoice in google sheets for Purchasing

welcome to code with kurt the channel that brings you the latest google sheets and google apps script videos in today's video i'm going to show you how to create purchase orders using google apps script and basically what this does is you can fill in the information you need for a purchase order and i use the purchase order template that google sheets provides that populate the fields that are needed to create a purchase order so you got the vendor here i got date invoice purchase order number ship date sevilla terms the vendor information the items and the totals but with this also i'm keeping track of the vendors that you're using so you can keep a list of your vendors you can keep a list of your items as well and when you're done creating a purchase order you're keeping a log of each purchase order you create and it's also keeping track of the total and i also got one setting here that keeps track of your purchase order number so it increments every time you create one so i'm going to show you how it works i'm going to clear the form i'm going to select a vendor i'm going to give it an invoice number a ship date i'm just going to select it uh ship via say ups my terms b and my shipping and handling cost which i'll say fifty dollars uh the items i want on the purchase order select item one give it a quantity i'm gonna add that one and i'm gonna select another item 155 and i'm gonna switch the quantity to two add that item and again i'm keeping track of my big list of items so that can keep track of the item description and unit price of all my items that way i can make quicker pos by just selecting which purchase which items i need for the purchase order so here i'm gonna go back to my form here and i'm gonna just get hit create purchase order and now it's going to generate my purchase order from there i can just print this off i filled out all this information the vendor information with this information here this is what you fill out this is static information as well as your ship to this is the stuff that changes the vendor the date the invoice purchase order ship date should be in terms as the items the quantity the unit price it pulls off from the list here and keeps track of the total price by adding that then you get your subtotal your shipping handling your tax rate comes from your vendors it's one of the fields in your vendors your tax rate over here and then your sales tax and then your total so that's pretty much it for how this works in this video i'm going to show you a step-by-step process of how i put this together let's get started here i'm on google drive i'm gonna get started creating a new google sheet so i'm gonna go to my drive i'm gonna go to google sheets i'm gonna click this arrow i'm gonna go from template and here i'm gonna grab the purchase order template which i can find here i'm going to click that let's go ahead and bring it up for me and right now i'm just going to keep the name purchase order up here for my google sheet file name and down here i'm going to change this to print po so if you're following along real close you're going to try to replicate this and edit it later i i always write my code around the names down here so it's important once we get to the google apps script of this that if you're trying to copy this to get the the sheet name as i have it okay the only thing i'm going to do on this sheet is i'm going to come down here i'm going to click this down arrow here and i'm going to extend these all out just so you can see what's going on and how i build this is i delete all these lines out and add the items i need in so i'm just going to expand that out for now so i got this created now i'm going to create all the sheets i need so i'm going to start by creating a main sheet which is pos i'm going to move that into the first spot so i got this is where i'm going to build my form to create with the buttons on here and then next i'm going to add my informational sheets vendors items p log and settings next thing i'm going to start by my settings i'm just going to start here's where i'm going to keep track of my po number so i'm going to start with this number here next time i lay out my po log next time i lay out my items sheet with this items here i'm just going to add some items in here all for my item sheet next i'm going to do my vendor sheet and from there i'm going to add in some vendors here so it takes care of my vendor sheet for listing all my vendors next i'm going to work on my main form here setting all that up so next i'm going to set up a data validation on my ship date we'll select data validation select date hit save there uh next i'm gonna do a i'm gonna change this i don't want this to change this to vendor next i'm gonna do a drop down for my vendor i'm going to go to data validation list from range and i'm going to select this vendor go down here and i'm going to go down to 500. that way we can have 500 vendors hit ok i made it save go back over here i should have a drop down for my list here i'm going to do the same thing for items here i go data data validation i'm going to click here go to my items select here go down make this 500. so we could store up 500 items as well go back over here now i got my list of items next i'm going to create my buttons and then then i'll move over to the google apps script show all the functions and then i'll assign the functions to the buttons but i'll create the buttons first here so we're gonna go insert drawing select our button box here so i did create po i just put it in there and bolded it hit save now you move it up here create another one i'm gonna do one more for my add part okay so i got my three buttons um built and created so next i'm gonna move over to my google apps script so i'm gonna go to tools i'm gonna go script editor i'm gonna name my project purchase order and then i'm gonna copy and decode it and i'm gonna go through everything i'm doing so so there i have it copied i'm going to hit save okay so the first function i'm doing is add item and that goes to this button here so what i'm doing is i'm selecting an item here giving it a quantity and i'm hitting add item what that's going to do is that's going to add a row down here and i'm keeping track of which items i want to add to the po because then when i hit create po those items down here are going to go right here in these rows right here so so in this one i'm doing a decoration of all active sheets here with the ss i'm declaring my po sheet here and i'm declaring my item sheet here because these are the two oh i mean pos here that's my main form here so again i'm just working in this form here so i'm going to grab the last row of the po sheet and i'm going to add one to it so that's going to give us the next open available row on that form which will be right now 17. so there i'm getting the part and quantity from my selection right here these two and then i'm gonna go to my item sheet i'm gonna go try to find the part within the list and if i find that part i'm gonna grab the description and unit cost of that so again i'm doing a for loop using this last row item last row of the item here i think i missed that statement here i'm getting the last row of my po sheet i'm also getting the last row of my item sheet because it's going to go through all that list i'm going through each row in that item list here i'm going through each one of these and trying to find the item i selected then from there that part equals the part i selected equals a row in that sheet then i'm grabbing more information description and unit cost and then once i get that information i'm populating on the next available row on my main form that po sheet so it's going to populate the part description quantity and unit cost and i'm using that set number format give it a dollar so that concludes that add item here and i can go ahead and add that in here which i can right click click there a sign script and there i gotta assign so i'm gonna go back to my google apps script we're gonna go through the next function which is the main function is actually populating the po so basically taking all this information and populating the print po here so the first thing i'm doing here is i'm declaring my active sheets with ss then i'm declaring my sheets my po sheet my vendor sheet which stores all my vendors the setting sheet which stores my po number the print sheet which is actually the po itself that's the sheet we print and the pos is our form the next thing to do is i'm getting the values off our form so i'm getting the name so which is the vendor invoice number ship date cheap bit ship via terms shipping and handling i'm getting all that information so name invoice number ship date ship via terms all this until i come down to my po number i'm grabbing it off the setting sheet i'm adding one to it and i'm setting the number back to that sheet i'm incrementing it by one so i have a new number for the next time i print appeal off the next thing i'm doing i'm getting the last row of the vendor sheet which is here because i'm going to go increment through here and select the name that i selected on this form so if i select john smith i'm going to go back over here as soon as i find john smith i'm going to grab all this information in that row so that's the next statement i'm doing here i'm grabbing the last row of the vendor i'm going through i'm looking for the name if i find the name then i'm getting the company name street address city state zip phone number email and tax rate next thing i'm doing is i'm getting the current date um that's going to be our po date for our purchase order that's going to be the date here so again i'm getting the dates i'm getting the month and year and converting it into a string date this is all javascript code the next thing i'm doing is i'm getting the last row of the print sheet now from here what i'm doing is i'm incrementing through and i'm deleting all the parts off so i'm starting here with this 27 and i'm cutting through here and this subtotal is what i'm using is my stopping point that's where i'm going to stop so i'm going to delete through until i find subtotal then i'm going to stop so that's kind of the next statement here so again i'm starting with 27 cutting through as soon as i find subtotal i'm cutting out i'm keeping the count of how many rows there are and from there i'm doing some math to figure out the last row to print and then i'm running this statement which is starting at 27 and then how many rows which i'm taking my last roto print minus 27 to give me my final number that i need to delete and again i'm keeping the count here i'm doing some math to get this last row to print and i'm making sure it's not equal to zero if it's zero then i don't need to delete anything but if it is then i'm using this delete row statement here to delete those out then after that i'm adding in all my information my name my company name street address all the vendor information all this header information on top of the po my shipping handling and tax rate so here's my shipping account tax rate here's my vendor information and here's my header information up here once i do that then i'm going to go through and add the parts back in that i have selected in my down here the parts i select down here i'm going to add those back into the rows so from here i'm getting the last row of the po sheet which is my main form because i'm going to increment through these starting at 17. so again here's 17 getting the last row my increment each row i added here i'm inserting a row for each row i added on that form i'm inserting a row on the po so if i go to my po sheet i'm adding a row here for each row i have on my form for each row i have here i'm doing that i am getting the values from my form for part description quantity and unit price i'm doing some totals here for total price to keep a c total i'm doing some totals for total price to keep a sub total going because i'm gonna have to add in the subtotal and then once i'm done and here i've added my row to my po sheet now in that row i'm going to add the information the part the description the quantity the unit price and the total price which you'll see on the po sheet item description quantity enterprise total price then i get my subtotal i actually add that here which once i'm done going through everything here then i'm doing my subtotal spot here i'm populating that with the 600 and then i'm keeping this total po declaring that for the next call which is my po log and with the po log i'm declaring my active sheet i'm getting my po log sheet and then all i'm doing here is getting to the next row available so let's go over here our log so here my next row available is two and i'm going to be populating per po number customer which should be vendor here populating vendor issue date ship date and total po so i go back over here i'm doing po number name date ship date and tokyo so i'm adding that row and then the last thing is my clear button function here so what i'm doing is i'm clearing out my whole form so again activesheets i'm grabbing my po sheet which is my form and i'm clearing out all the spots even the and then this down here to clears out my items so i'm back over here oh back over here i'm deleting out all my items clearing out these and this so i could start over so let's go ahead and add those functions in i'm going to do the clear invoice here since i'm right here i'm gonna go ahead and copy that i'm gonna go back over here that sign script copy that hit okay and now i'm gonna go back over and do my create purchase order go back over to the top create po hit copy hit here the sign script hit okay all right the first thing i'm going to do is i'm going to clear my form which it should take me through some security check here i wish to continue yes let's select my account i hit advanced let me go to purchase order we allow it takes me there i'm gonna hit it again so it clears that out i'm gonna go ahead and populate let me hit john smith i'm gonna hit number here my ship date i select that ship via again ups terms fob shipping handling fee i'm going to say 20 uh the item almost like that item one let me give it a quantity and then we'll just add another one two with a quantity of five add that there we go there and next thing we have to do is just create our po so we're gonna hit create is running we can go back over here so we got our totals here or items or bender here i got this a little too far i can actually shorten that up real quick i could just do the state as abbreviated here so i can go ny and this is n i can go ahead and do that again okay credo clear that up there we go we got the new york so our vendor looks good we got the date invoice purchase order membership date everything looks fine our totals our tax rate tax and our final dollar go over our po log here i did two copies of it the first one i fixed the state and it laid another copy in for me as well so and it changed my po number so that concludes this video if you have any questions or comments please leave it below the video until next time [Music] you