Collaborate on Automated Invoice in Google Sheets for Technical Support with Ease Using airSlate SignNow

Automated invoice in google sheets for Technical Support

hello friends welcome back today we will be learning something very interesting that is how to automatically create and send pdf invoices using google sheets so this would be very helpful to people who need to send regular invoices to their clients so using this technique the entire process of creation of the pdf invoices as well as sending them will be entirely automated so without wasting any time let's first see a demo so this is my google spreadsheet for explanation purpose i have few columns like invoice number customer id customer name customer email and from columns e through h like these are the items which uh i am selling and i will i have noted the quantities like this customer had bought 12 items of item 1 0 for item 2 and so on and so forth i also have another tab over here the price lookup so here whatever are the prices of the item i have mentioned it over here so if there is any change in the prices i it is only one place where i have to come over here and update it and then using the script we'll create the final invoice and send to the client and i have few more details over here like this invoices for which month which year and what is the tax percentage so this is a generic spreadsheet which i have created like you may want to create this kind of a spreadsheet as per your requirement but this is a genetic example so using this example i'll show you how to create and send invoices so once this kind of a spreadsheet is ready if i click on this button the invoice should get created and sent automatically so you could see the script is running which means in the background it's creating the invoice with the help of the data which has been provided and once the script execution will get complete the invoices shall be sent so let's wait for a few seconds you could see the message is coming as finished script now if i go into my inbox and within my sent items refresh it you could see it is showing like it has sent the invoice for august invites for august like you could see three images why you could see three emails is if you come back to the data sheet row number two the total was zero so if the total amount is zero i'm not sending the invoices but for rest of the items the invoices have been sent i'll just show you the format so this is one of the invoice if i open it here are the details i'll show you so now this is invoice number one zero zero one so if i come back to my data sheet so invoice one zero zero one this is for customer john smith you could see here we have mentioned bill to party john smith invoice number is one zero zero one today is six september so the date is today's date and now the quantity for item one it was twelve so you could see item one is twelve the cost it has taken as 15 why it has taken us 15 because the cost of item 1 per unit price is 15 so it has taken the data from over there and now it is showing you the total if we come down here it is the subtotal and now it has taken the tax as five percent again we have mentioned the tax as five percent over here so it has picked up the data from here did the multiplication and finally the grand total is 254.1 so this invoice was created and sent automatically just with a click of a button so this was one of the invoices similar to the to these like we have other invoices like which was sent like this is invoice 1003 so you could see the details are again different and this is again picked up from the customer detail sheets and the price detail sheet so this was the demo about how to create and send invoices now i'll actually showing you to how to write the script to achieve this the first step to create such invoices and send it automatically is to have an invoice template so if you want to create an invoice template you have to go to drive.google.com within that you could create a google doc so what i have done over here is i have created one folder called invoice over here you could see this folder is there if you want to create a new folder you have to just click on this thing then you'll have this option folder if you click on it you'll have to provide the name of the folder so i have already created this folder and within this folder the invoice folder i have this invoice template so if you want to create your template again you have to go within the full list just double click the new folder which you have created after which you click on this new button over here and this time select google docs and select blank document and now this is this is like a regular document and you could provide the format of the invoice like i'll show you my format my format is like this invoice template so i have the company name at the top the date bill to party invoice number and some details over here so using this kind of a google doc you could have your own invoice template you could give your logos any website link anything could be provided but this is a prerequisite you should have your invoice template ready i have created a very simple invoice template like this over here so whatever is your invoice template create it and save it i have saved this invoice template my invoice template in this invoice folder over here okay now i'll give you more details so your top is the company name you should provide your company name over here and the next details over here the date date i have just marked it as y i would be replacing this with the actual date on which i am sending the invoice similarly to which party so if you had seen the invoice which i had sent to the clients here i am providing the name it could be a company name or it could be the client name so in my example whatever is the customer name i am using it over here so in the script i would be actually updating uh these values with the actual client name similarly the invoice number invoice number if you would see we had seen one zero zero three one zero zero one so again whatever are the dynamic values over here i have just provided some codes over here which i would be replacing with the help of my script and now comes the items like i have item one two three four here you could have your actual items which you are selling and in the next column the quantity this is again a code this is item on i 1 q i 2 q i 3 q which means the quantity for item 1 quantity for item two and so on and so forth similarly this column is for price per unit it is price for item one price for item two next column is total price t1 t2 t3 t4 so all these things will get actually replaced by the actual values when the script will get executed similarly the subtotal amount like this text will get updated in fact it will get replaced with the actual amount which should be coming through the calculations so all these things are handled through the google script which i'll show you right away so to write the google script you have to go to your spreadsheet go to tools script editor so when you will open it by default there would be one function called my function but the name of the function will be my function which you could replace it with any name of your choice so i have given the name as create and send invoices now uh this is a very simple code i'll explain you so you're the first three lines of code which i am highlighting right away so these are for accessing the data from this spreadsheet so the first one sp sheet object would be used to access all the data which is there within this spreadsheet so it mentions spreadsheet app dot get active spreadsheet so this is my active spreadsheet and within the active spreadsheet i have two tabs the customer details and the price lookup so i have created another object where invoice sheet and you have used the previous object sp sheet dot get sheet by name custom material so if i want to perform any operations on the customer details either read or write i would be using this object invoice sheet the next is the price sheet if you see it again i'm using the sp sheet object dot get sheet by name and the price lookup so this price sheet object would be used to perform any read or write operations on the price lookup tab okay uh next few lines of code are very important this is very simple like this i have used the sdr date because i need to provide the date on which the invoice is generated so i am using this utilities dot format date here you have to provide your time zone whatever is your time zone just provide that time zone the next two lines of code are the most important lines of code in this entire script so the invoice folder so this is the folder where the invoices are getting stored like we just saw that the invoices were attached in the email but before the emails are sent uh with the attachment the invoices are getting created and stored somewhere so you have to provide the folder id where you are storing the invoices in my case within within this drive i am using this invoice folder i have the template over here but at this same location i would be storing the invoices as well so i need to know the in this folder id so folder id is nothing just double click on this folder the top url you'll get just use the last few alpha numeric uh values after folders slash so whatever is present after folder slash that is your folder id copy it and paste it over here so drive app dot get folder id whatever is the alphanumeric string that is the id next is i told you we would be using the template to create the actual invoices so my invoice template is present over here so i need this id as well so just double click on this invoice id again you'll get that url on the top uh be careful like select this alphanumeric string before slash edit copy it and provide it over here so this time you're using where invoice template like these variables whatever is on the right hand side of where these variables could be of any name just provide any logical names so where invoice template is equal to drive app dot get file id this time i'm not using folder because i am referring to the template file not the folder previously we were referring to folder so we have selected get folder id this time i'll use get dot get file id and provide the id which i had just shown next few lines of code are very simple we are just declaring and initializing some variables so if you see where invoice number cost id custom customer so these variables are for these calls so each column has its equivalent variable in the script quantity item one two three four i have initialized to zero so these are so i'll be having the quantities of each items over here so for those columns i have equivalent variables now comes the price price item 1 item to item 3 item 4 so price i have initialized to 0 but actually i will be picking up the price from this column within price lookup the column b has the prices i'll show you in where it is in the code similarly i have variables initialized for total price like each item total price i have initialized to zero so whenever the calculations would be performed the final value would get populated in column i before that uh the before the calculations are done i have initialized to zero tax percentage tax amount subtotal price every all the variables which i have created i have initialized to zero now price item one this variable i had created i am fetching the price item one from the price sheet the price sheet will take the value from the price lookup from the range which we have provided so the range is get range b2 so price item 1 would be 15 similarly price item 2 3 and 4 i am fetching from price sheet b2 b3 b4 and b5 tax percentage is coming from f4 you could see tax percentage five percent is coming from f4 and now total rows i need to generate invoices for all my clients so i should know the total number of rows for whom i want to create and send invoices so i need to send invoices for my clients i am having four clients over here you may have more more than four clients or less than four clients so to send the invoices to all the clients i am calculating the total number of rows from the invoice sheet if you remember the invoice sheet is mapped to the customer details so to get the total number of rows within the customer details sheet this line of code is getting used total rows is equal to invoice sheet dot get last row once i have all these details i am using a simple for loop this for loop will iterate for all the rows for row number equal to 2 i'm starting from row number equal to 2 because my first customer detail is present at row number 2. so for row number equal to 2 row number is less than total rows row number plus plus now i am actually reading the data invoice number is coming from column a the other details from the respective columns you could see quantity one two three four is coming from efgh now the total price for each item so each item is the total number of quantity into the price so i am doing a simple multiplication over here like i get the total price item then this variable subtotal price is nothing but the summation of the prices of all the items next is tax amount so tax amount is a floating value so make use of this kind of uh syntax pass float tax percentage like whatever is the tax percentage variable is storing multiplied by subtotal price so subtotal price is coming from your tax percentage we had seen earlier and then i am doing a two fix so it is fixing it to two decimal points finally the total price is nothing but the total of sub total price and the tax amount invoice month and year they are coming from f2 and g2 respectively so within the price lookup if i come to f2 this is for august and the year is 2 0 2 1 so i am reading those values from here once i get the total price like at line number 66 i am getting the full price i am populating the invoice sheet the first customer details sheet with the total price for each of the rows the total price will get populated so this is this would be a verification point like what is the total value like if you would see the email and check like whether the totals are matching with the values which are present in column i so at this point in the script all the calculations are completed now we have to create the invoice and send it to the clients now the invoice would be created and sent only if the total price is greater than zero we have seen in the example there were three emails and there was no email for row number two because the total price is zero so here i have added a condition only if the price is greater than zero go ahead and create the invoice so now how to create the invoice so to create the invoice what we are doing is like we are going to back to the invoice template like we have the invoice template ready and in the earlier part of the code we had created an object for it invoice template so we would be creating a copy of the invoice template using the syntax which i am showing right now so the syntax goes like this i'm using the inverse template object dot make copy and where i'm making the copy within the invoice folder so this is my invoice folder i have the invoice template so i am creating a copy of it because i would be performing some operations on the copy the template would remain as is because i need to create more invoices from the template but i would be manipulating the copy which would be created so i'll show you i'll execute this in a debugging mode so that it would be clear what is happening in the background so to execute in the debugging mode i just placed a breakpoint over here and now i click on this debug button the execution will stop at the debug point so you could see now it has stopped at this debug point at line number 77 actually i should have placed it at 76 but that is not a problem so what happened in the background at line number 75 invoice template dot make copy at the invoice folder got executed so if i check my invoice folder now now this copy of invoice template has been created so next is we have to manipulate the contents of this copy so if you see the next line we are creating a raw file object which is not doing nothing it is just opening this newly created copy of invoice template file in a programmatic mode so we are not able to see it but internally it is opening it and the next line line number 77 we are trying to read its content so whatever are the contents of this raw file using this line draw file dot get body we are getting its content please make a note of these two lines because these are very important uh line number 76 we are using the document app dot open by id using the previous object raw invoice file dot get id so this is the id of the file and we are opening that file and the next line is we are fetching the content of the file so raw file content will have the whatever other contents like whatever is in the template like the copy is same as this invoice so whatever are these contents like company name invoice this entire content is getting stored in this variable raw file content next step would be to replace the contents of this raw file so whatever we are fetching from the actual google sheet like the customer name uh whatever whatever other quantities the price everything should get replaced in the raw file content so if you would see from line number 79 onwards we are just performing replace operation so we are just using this function called replace text the replace text function takes two parameters the first parameter is what is the text which i want to replace so in our case like these are the text like from the invoice template whatever things which we need to replace like build to party invoice number with the xs so these are the things which we need to replace so those are provided as the first parameter then a comma and the second parameter is the actual value which we replaced with so bill to party remains as is but the cust name right now it was uh this thing but i am replacing it with the actual customer name which is coming from the google sheet so all the values which i am replacing from row number 79 will get like all these values uh the quantities the prices the total price everything will get replaced with the actual value from the google sheet if you would come at line number 97 now sub total amount sub total amount in the document is over here so this thing sub total amount will get replaced with the actual calculated amount which is students variable sub total price so one thing should be one thing should be noted that is when we when we are creating this template make sure like all the things which are need to be replaced are unique if you will replace similar text will get repeated anywhere else in the document then that will also get replaced so make sure whatever things you want to replace in the script are unique okay so once all the replacements are done then comes a very very important step for now i am just stopping the execution and now i will place my break point at line number 108 and i'll remove it from the previous line okay and what i'll do is i'll also delete this copy of invoice template now let's see what happens when my execution reaches line number 108 up till here we understood like we are replacing the text one to make ux understand like these lines of code which i'm highlighting i have placed the breakpoint at line number 108 so now once again i click on this debug button the execution has started it will stop at line number 108. okay so at this point the execution has stopped and now once again if i go to the folder you could see now the copy of invoice template remains as is with the contents replaced but at the same time the pdf file is generated so how did the pdf file got generated so if you see line number 105 and 106 are doing that job so i've created one variable where pdf invoice invoice is equal to raw file like we were using the raw file we had closed saved and closed the raw file at 103. so i'm using this raw file and now telling it that its type should be pdf so this is the general syntax which we use and at line number 106 i am providing its name so i have provided the name as invoice plus plus is a concatenation operator the cust id so which means the this is for customer id which is coming from the google sheet so custid one zero zero one so my name of the invoice would be invoice underscored one zero zero one so if you see invoice underscore one zero zero one has been created similarly other invoices will get created so once this invoice in the pdf format is generated i no longer need this copy of invoice template else it would be an extra file so what i am doing is at line number 108 i am removing it if i execute this line one zero eight see only one line of code got executed and in the background you could see the copy has vanished it has been removed and only i have one zero zero one so at this point our pdf invoice is ready to be sent so if we once again come back to the code the next few lines of code at line number 109 we are defining a variable for the mail subject so i've just kept the subject as invoice for for the month so the whatever is the month provided in the price sheet like year i have the month as august aug and then invoice here which is two zero two one so this would be the mail subject the mail body it is just a one liner invoice for the month of uh august is generated details are attached and finally line number 111 gmail app dot send mail this will actually send the mail the first parameter is the email so it is stored in the cust email variable next is the mail subject so this is the mail subject mail body it is coming from 110 and then this highlighted section is the attachment so it should be exactly as is so we had this variable created at 106. so this is the pdf invoice dot get as mime type dot pdf so make sure like the syntax is as is and the email would be sent with the invoice this entire script is available on the blog i'll show you don't worry about that but before that now i am stopping the execution and now i am also deleting this invoice we will execute the entire script in one go so this is my script this is ready so if you want to trigger this script through with the help of this kind of a button what you have to do is go to insert drawing select it select the shape select this shape when you click you'll get this kind of a object your if you do a right click it will give you an option to edit the text i'm just writing click here and uh save and close once you do that the object will be available you could just drag and drop it anywhere on the screen so i just did that and you could see on the top these three three dots these are very important because we have to associate the button with this function so just copy the function name right click on this object go to assign script and paste the function name that's it once you do that now this button is associated with the script so my entire script is ready i also created the button i am deleting the totals from here i delete that now i click on this button and let's observe what happens so i click this you could see the script is running it will take few seconds you could see the first total has been populated second is zero so the email should not be sent for the second item third one is has got populated now and within few seconds the last one will also get populated then we verify the sent item so the emails have been sent to the respective clients if i go back to my inbox sent items refresh it you could see now the emails like it is 7 2 p.m you could check it is uh 7 like 1903 so just a minute ago the emails have been sent here it is in 12 hour format here it is the timings are in 24 hour format but the emails have been sent if i just open one of the invoice this is one zero zero four just open it and see so date it is sent to the client invoice number the total is 504 if i check back in the sheet the total here is 504 so as you saw the script is doing its job it is creating and sending the invoices the invoices are also available over here like these are the invoices which were created it will stay here until you delete them so this was a tutorial about creating and sending invoices automatically using the procedure which i explained you can entirely eliminate the manual process of creating and sending invoices and it could be taken care automatically the script which i have over here which i had executed and explained is entirely available at the blog under google sheets create and send invoices click on it and the entire script is available over here you could refer this and create your own script to automate the procedure of creating and sending invoices i hope you found this tutorial helpful if you like my tutorials i would request you to please subscribe to my channel so that you will be updated with the latest tutorials i would be uploading guys thanks for joining i'll see you soon goodbye