GAS-023 Create Invoice from Google Sheet

hello guys today's video I'm going to show you how to create a invoice generator in glow sheet so I'm going to appear in voice lenses so this is a final product here and these tests lines so here tell me do you need another client so here we have order and with three items for client a and we have an invoice number and I'm going to create an invoice for these three items so in this menu invoice menu we have a create button so you just put your invoice number so we are creating the invoice okay now the invoice hub has been created and the invoice so we have to put in this folder and also you will have a link to the invoice alright so this is the invoice the data all the data is from spreadsheet and so this one is very similar to this one so this is just a template configured in this website and this one we are going to use the template and update our information enjoy voice okay so let me show you how to do this so in this file which I were going to share with you you go to file and make a copy of this file and rename it renaming new invoice imagery me okay and I'm going to close this one also get this far so let's go - I still eat this you're ours and go to the script editor leave the code you need code so here we're going to have two files one is for the app script and a one for HTML so you need to leave something for this HTML to kill the body then comes a head so let's build this app spec first first we need to retrieve the data from this spreadsheet for this invoice number okay so the client information is exactly the same for the invoice the only difference is the item this client order is dear friend they have three different items ordered in this invoice you're going to get a function here to carry info so sinful cluster very poor invoice so for test purpose I come to use the invoice zero zero one so here the name is theta so if you go in your example you may need to change this name if you change this name you also need to update the code here okay you need to use a ship name here well equal to 0 and less than gross class class so we are going to loop through the data a database row by row here to check the invoice is phone unlocks phone number this number is the invoice number don't be the data row and the column in text will be in verse number 8 which is 7 columns so some columns index will be 6 since this is array array start from there you can if number equals to invoice we're going to have some variables here for you we're going to write object here and the items go to put a Titans in you are a so if the invoice summary found criteria for item is a certain muscle object so for this items item so for this item going to carry the the item for the description details prized colleague at amount five items I turn table description will be theta Rho and index will be 11 11 minus 1 is 10 okay remember 2 minus 1 and here you are gonna have that details price quantity and amount and we need to add another one for the row index so the role our plus one since there are start from zero okay and we we need to push this item to this array so I just push item you here we can turn lock pattern let's try to test in the first step so I suggest to to test step by step because sometimes you may be made some mistakes in your code and once you funds that you'll be very difficult to do the debug okay this is just my experience listen no need to run again let's check it so okay so for since we have three items which have the same invoice number and here as you can see here this is the first one because description correct amount correct price cut kaneki correct okay so since it looks good and we can remove this in this lock so we choose the loop it's done and we need to check if such items less so if we found the invoice number in the database the items will be the items dance will be bigger than zero okay so because the items less please observe that means we found found something in the database so if we found the invoice number in therapist we need to retrieve the client information which is from column to column J so here we needed to to us to grow equal to items zero so this is a client information and you exactly the same so we only need to use the first one so this items zero means the first client a first item they remember we have roll over will attribute here you can use it and we nitu as a info information let's copy this so you vote you yuppie cattle ranch ranch well we don't need to kill range me to use nuclear the data think we need - one for this because we are using the data this data is array and start from zero I think so too email address country turns okay so ten items where exactly ten items and here welcome to - to log here - no the new phone has tried again so okay we care the date go to address phone number care of terms right do looks card postcode looks fine to climb a city country even voice number and email address so everything looks good and we also need to add the items choose items so these items will be collect from here from this sleep okay and another information which we need to add the company information ok the sender course enter information so this is sender information is another asset called company and here I already named is a range here as you can see here but address phone number city country okay so when you need another way so sender email name as copies keyboard here so the name will be scratch it care branch name we open the original name is name campaign called the past name email address and one more for peaceful you also need to update the wrench name out of copy and paste now we can remove this and we also can turn Locklear know the sender and remove the info that try it again so you need to make sure of the information you you need is correct okay here for the sender information we have this phone address from name looks correct postcodes city country and the email address looks good and finally you add this sender information to the info object so sender well the sender remove this line and finally you just need to return this return info and you put this invoice as a variable in this function that you can come this long commute and more so this is a major party in this ask script so once we have this we can try something so we can another function to create invoice so far invoice has also tried give this test my info so info will be we are going to use this cutting for you function as info so he means to checking the invoice empty or not so if object keys keys in length so if the lens is bigger than zero that means we have a list of one item for this invoice so if we found we can create our invoice message all right and here we need to do something here we need to create a template HTML template so HTML service create a template from file so far will be this HTM file invoice make sure the name met this one render the template to info will be in flow so the same for a curve from this function okay and once we have this HTML template we can create a PDF file from this HTML file so okay template age care as so this content type will be application yeah okay and then we go to invoice yeah create it with the drive app API so create file with this plot and the satyr named name account so name will be boys and class last so we added the date and time behind is the invoice number so locally touch here and extension I serve it so since this HTML now is empty and we also can try this function so the PF will maybe create either like empty PDF file so let's try this function since we use it a lot and a new API is drive API we need to keep in the access for the new API Oh No is not here since they should be your root tribe okay so this is a 1 so it is empty and now we can update our HTML file with the code in this template so you just inspect the copies is this invoice continuity copies element and put it into your HTML file also you need to copy the starship cannister she should be in the header in this one if it if you don't know which points the correct one you can tell searches with this class you just search so this is a starship copy the parliament put it in in this whole section ok we have the style we have the HTML content we can try it again try it again code so create okay so this is a new one so now you have this one so so this one exactly the same with this one and our final step we need to replace the values in this content with variables we found from the database okay so since we assign object to this template so this template has an object named inflow we can use this info in the HTML file okay so here the first one will be from so from either your company name so uterus search so this one I have in this template I have a chair as for the company name you can do a search in your HTML GIS so not here you can see you have you have a header from and you only need to replace this GA as with the inform so this is this chassis from the center information so from center to remove it remove the sender so if you go to the script the info and the info will have a sender object the sender will come to have a name email address like this so it's not true is to eat a name it's not to name and this is the email address you need to replace the mattress with the sculptor's replace the mattress just like this email okay the next one will be your address address and your postcode you'll be here almost next one is your phone number make sure your type is correct a business phone number remember replace this business phone number with these and next one will be the client information I think so here is your client information this one so for the county formation you don't need to get to the sender you just need the info to copy there's an email with the info email and address we are waiting for address city and country city country and a phone number is here info flow from next trip is invoice number I think the boys number so here is the invoice number aggressive with the invoice and have the date and terms terms do so the climate information is done and this for in this table will be the item information okay so the head table row your head so here you can see for the description price kaneki amount so if you don't you can change this so you need to use another name so this is a header and here we have the table the table table body have a row take that so so as you can see here we have the first item in this table road okay so we here we have the description and here we have the details and here we have the price and the quantity and the amount so this is another one in this item road since we told it to the we are going to need a script to create an automatic where the table rows we can delete the second one and okay delete from the TR to the TR just deleted table party will be here table will be closed so the last tip will be your comment not comment C key for the subtotal information so let's just this part first so here we're going to live here I use a script here items close this function set each items in these items so for each item in these items we need to collect the information okay it's own information love me this is a description let's replace the description with items I don't this question and we can copy this code replace this with details replace this price and replace this with quality and replace this with amount and last sing I sing it for the subtotal in total information here in this tea we have a subtlety didn't have the text tags and now we have a total so in this example I'm not going to add some or any text and if you need to edit you can update here and you hear you were gonna have a comment okay here I think we need to we missed one thing so we missed the total and subtitle information in our function our carry information function welcome to add it later so here we'll continue the information total okay so since there is no text involved in this case so the total subtotal will be equal to the total also the talents in the balance - so they are the same so if we in your case is not is different you need to define three different variables okay and the comments you also you can change it so forth in this example I don't need to add any comments here and we also need to add this total information in the code in the total synchrony to the variable here plus item amount and it pushes we are as information is total information save it then we think we can try to write if everything was imported correctly I think we should get something so okay this one this my latest one okay so this one is the latest one tell me a little bit so okay something was as you can see we have descriptions undefined and for others I need to check the company looks correct email looks correct and his the city the city and country is not correct so it is also the address phone number looks carrot I need to fix the description issue in a subtotal looks fine this is the description sure description this scripture description these great misspelled description this creeps stead r10 this corrupt and it's a not just one to country for the sender in your name address this correct what's wrong that's hard to be back the sender let me see so here we have the name email address okay okay so I miss address here look here is a country second country so so trisodium country postcode the description is empty fine I do something wrong let's try it again okay let me see little place one try to run again voids waste your undefined and the address is corrected test excretion is undefined what's wrong with this one let me check this question items his description looks good what's wrong with that so let's use that he tells to me place this and description to see this works so this red looks good and why we cannot use the description okay finally so the description is correct and we almost have everything ready so we need to test something else we need to try to add another client so let's main clunky number kaneki - - we can try to create another invoice ok we need to modify the code here so so you voice we're going to Adam - cattle calculate invoice from end user browser input I can't invoice so now we got out to here and we have four items so looks pretty good that's almost everything so it's just to finish this last for the URL so here you can cut out the white arrow okay so once you create a PDF file and copy this file you're working in voice he also cares ID and you need to move this file to a folder so here the default where the file the credit in your in your good Drive and if you want to remove it of any folder in your core driving on - Curtis folder ID grating voice so here I have a folder you just call his ID you can do a part folder like that get photo ID put his ID here and you can do folder add file family open this PDF and you also need to remove the fat from your worldwide remove file so we also give this video file and you have you need to pass on information to any user so as in the last step you need to feel the URL here so I crunch well there's a column will be the last column okay 16 and you come to set a value set of values already we're busy you are and then we can add this function to your add a menu for this function open expression you I meet you voice and I can create function and wealthy creating voice anyone save it and run this around washing that's so this collar is empty has created these two invoice so minus one so I want fries credit and you should have it in this folder okay so you one strong one so oh one looks good and you can create out why so to also create it and you also should have it in this folder so this is for client B okay that's it and this is a very simple idea for you guys and if you need to do some modification over the code you can do it very you can do a patch creation for the employees okay you just need to do some very simple modification of this code and even if you are very good at HTML you can build this invoice in this HTML file but I copy the code from this website save a lot of time for me I'm not very good at HTML and CSS so that's it for today's video this video is I think very very long videos the longest video I update so I hope you guys can learn something from this video so I'll see you next time bye bye

Channel: Ashton Fei

Views: 4,710

Keywords: gas, google apps script, google sheet, invoice, pdf, html, invoicesimple, javascript, automation

Id: YqaUFpQaHsE

Length: 55min 53sec (3353 seconds)

Published: Fri Mar 01 2019