Create Your Own Google Sheets Receipt Template for Personnel with Ease

Google sheets receipt template for Personnel

hello friends welcome back today we will be learning something very interesting that is how to automatically create and send salary slips using google sheets so without wasting any time let's first begin with the demo so this is my google sheet and if you see it has all the details like what is the employee id employee name the salary details whatever things are needed to create the salary slip those details are provided over here so when i click this button what will happen is in the background the salary slips will get created based on the data which is provided over here and at the same time the salary slips would be sent over an email to the email ids which are mentioned in column e over here so as i said the first step would be the salary slips will get created so the salary slips will get created and here in the google drive i have created a folder called salary slips the slips will get stored over here in pdf format and also the same salary slips would be sent to these individuals on this email id so without wasting any time let's click on this button you could see the script is running i'll take my focus on this tab here is the folder salary slips where the salary slips are getting generated you could see one by one this is saturday slips are getting generated in pdf format so in the google sheet here we have data for five employees so the file salary slips will get generated and i'll also show you uh in sent emails these salaries slips have already been sent you could see these five salary slips have been generated over here and now if i come over here into the sent email i just refresh it you could see the salary slip has already been sent as an attachment i am just opening this one of the attachment so this salary slip is for employee with id 365 lennox rod so if you go back to the sheet uh this is the employee at row number six uh you could see the person had worked for 20 days the basic salary was 6000 if you just want to cross verify see the number of days worked is 20 basic salary is 6 000 so these all these things are coming from the data sheet over here and using this data sheet the salary slip is getting auto generated so i'm sure my friends in hr who are spending a lot of time creating these salary slips at the end of the month after using this utility they would be saving a lot of time so before moving on to the script i would like to tell you about the prerequisites like there are a couple of prerequisites which i'll explain you in detail the first one is the salary slip template should be available in google doc format now what is this template i'll show you now the salary slips which got generated for these employees all they all have a specific format and this format i gave it up to you like you will have to create a google doc with all the requirements like you should have company name company logo and whatever are the headers like this is a very simple template which i have created but depending on the different headers which you are following in your organization should be there in the template so how to create the template uh i have created this simple google doc template in this folder sorry this lets the template what you have to do is click on this new select google docs and select a blank document once this document will get loaded here it is a very simple document you must have used and you have to just create your template you could insert tables whatever other things images logos there are a lot of options over here and you should create one template so if you want to see my template this was the template which i created so when the salary slip is getting generated through the script it is actually referring to this template and then creating the salary slip now very important things in the template uh you should have your company name logo everything and you could see like for example employee id this is the header and here what is there on towards its right like this is the actual employee id so when the script would be running this employee id underscore will get replaced with the actual employee id which would be coming from column a of our data sheet similar to employee id we have other fields like employee name tax identification number which is the salary month all these things have been specified in the template and if you see on the left hand side we have the static values like these won't change employee name label would not change but the actual employee name would get picked up from the google sheet and would be placed over here when the script would be executed so make sure whatever fields you have in your template we have one column each in the google sheet so this google sheet has these column headers which i have created based on my knowledge your google sheet and the salary slip template may have more or less feeds depending on the parameters you all use to create the salary slip so this was the first prerequisite where we need a salary slip template in google doc format the next prerequisite is the data sheet should have the required formulas now this is very important and very easy for example uh there would be some formulas to calculate the taxations and what would be the net payable after the deductions for example if i come to column p over there you could see it is showing it is a summation of n and o so n is a professional tax and uh o is tax deduction at source so the total tax would be the summation of these two so all these kinds of formula should be put in the google sheet we could put these formulas in the script as well but that will make the script a bit complex we we are we are trying to avoid all the complications and trying to make the script very simple so this is just my suggestion so whatever are the formulas which you are using put it into your google sheet another advantage would be if tomorrow some formula changes you will not have to change your code you will have to just come over here and update the formula in the sheet and everything will work as is so these were the prerequisites and now let's begin with the script to write the script you have to go to extension earlier the script part was available within the tools menu now a change has been made now the app script option is available in the extensions menu so go to extension with an extension app script and click on this app script when you do that another tab will open up like this and this is the place where you would be actually writing your code to have the salary slip creation automated so i have already written the code and i'll explain you all the lines of the code so when you click on the app script option when this tab will open up by default the name of the function will be my function which you could replace with any name of your choice i have given the name as create and send sal slips so this function is ready and after this curly brace the actual code begins so here you could see all the variables have been defined the variables are nothing those are very easy like we should have one variable for each of the column headers so you could see employee id employee name we will have these variables for each of the column headers here you could see this variable is for employee id this is for employee name so on and so forth so you should have all the variables this is the syntax where emp ids all these variables you could have any name of your choice keep the names logical so that it is easy for readability and i have written equal to with double quotes which means i am initializing the variable with blank for it now all the string variables have been assigned blank but uh the numeric variables for example basic salary number of days worked i have initialized them with zero okay once you have created all the variables now comes the next part now this line of code at line number 25 it is very important it is telling me that i am actually working on the active spreadsheet so at this point this is my spreadsheet this is my active spreadsheet within which i am writing the code so the syntax for it is like where name of the variable you could have any variable name of your choice i have given sp sheet is equal to spreadsheet app dot get active spreadsheet which means you now have a handle of this entire spreadsheet the next line of code at line number 26 tells now i want to read the data like i i am interested the data which is present in the salary details sheet now this spreadsheet has only one sheet but there could be other tabs as well like here you have options to add more tabs so whichever tab from which you want to read the data you should mention it in the script in us example we want to read the data from salary details so what we have done at line number 26 is we have created another variable called sal sheet and using the previous variable sp sheet dot get sheet by name and whatever is the name of the sheet so what this variable storing is like it this variable has a handle to whatever is available in this salary details sheet so you will need these two variables the first variable would be the entire spreadsheet it would be for the entire spreadsheet in our case it is the sp sheet variable and next would be within this entire spreadsheet which is the tab i'm interested in i am interested in salary details tab so i'm using the sale sheet is equal to sp sheet which is my entire spreadsheet dot get sheet by name and then i'm providing the name of the tab the name is salary details as you can moving on to the next line of code if you see the code at line number 28 here we have created a variable named salary details folder now as the name suggests we are specifying some folder this folder is the location where the salary slips are actually getting saved in my case this is the folder salary slips is the folder where i am storing all the salary slips so in the script we will have to mention at which folder you want to store your salary slips which are getting generated through the script so this is the syntax where any variable name of the choice now this in my case the name of the folder the salary details folder is equal to now i'm using drive app dot get folder by id and now the this is this id which is some random alpha numeric string now what is this alphanumeric string that is very important so if i go back to my salary slips folder this is the salary slips folder and if you'll see on the top this url this you are the last part of the url after this slash we have some alphanumeric string this is nothing but the folder id so you have to copy this just the last part of the url copy it and paste it over here within the brackets within double inverted codes so line number 28 where salary details folder is equal to drive app dot get folder id and the id of the folder so this variable is actually storing the location where the salary slips are getting stored so when you create a new folder in your google drive make sure you have the correct id just you have to copy it the copy the last part of the url and paste it within double inverted quotes here okay so we have created the variable for the folder where the salary slips we are getting stored next is we have created the template this is the salary slip template the google doc template which i have created so all the salary slips which are getting generated at the runtime are using this template so i should provide a reference of this template in the script as well so how do we provide that if we see line number 29 again where salary templates already template is just a variable name is equal to drive app dot get file by id this time i am not using folder i am using get file by id because this salary slip template is not a folder it is just a simple file so here we have to provide the id of the file so within this template if i come over here the last part of the url but here you should not use slash edit just the alphanumeric part this part which i am highlighting is the id of the file you should copy it and place it within double inverted codes here so after line number 28 and 29 you have the variable which is storing the details of the folder which would be storing the salary slips and you have also the reference of the template which you are using to create those salary slips next line of the code is very simple we are just calculating the total number of rows in the sheet you could see uh we have created a variable called total rows is equal to sal sheet now if you remember sal sheet is coming from line number 26 this was the variable which we created to refer to the salary details tab so i'm using salesheet variable dot get last row why i did that because i need to create the salary slips for all the employees so this will give me the total number of rows in the sheet in our case we have six rows our employee details are beginning from row number two because row number one we have the headers so we know that in our case we have total six rows and that is getting stored in this total rows variable now i'll use this total rows variables in the for loop since i want to create the salary slip one by one for each of the employee i am using this for loop in each iteration one salary slip will get created for example in the first iteration that the salary slip for employee leonard walk will get created in the next iteration it will get created for stewart jackson so this is the for loop which is iterating from row number two to the total number of rows row number one we are excluding because it has the column headers it does not have the details the details are bending from row number two so this is the for loop at line number 35 it is a regular syntax like it is beginning from row number two to the total number of rows which is six and we are incrementing the row number at each iteration and the next lines of code from line number 37 up to line number 53 uh those are very simple lines of code we are just fetching the data for example employee id is coming from the salary details sheet column a so salary details sheet which is represented by salsheet dot get range now we are providing the column within double inverted codes it is column a and row number the row number is getting incremented after each iteration at the first iteration its value is 2 so it is picking up the value which is present in a2 which is e xc 489 so that is getting stored in employee id and each of these variables are getting populated in similar manner as the for loop will proceed it will pick up values from each of the rows two three four five and six okay so once the values are populated in all these variables now comes the very important part where we would be actually creating the salary slip for the employee now for that we create a variable called raw salary files first we will create the raw salary file from the salary slip template so this is the template using which we would create the raw salary file so i'm using the salary template dot make copy now salary template if you remember was the variable representing the actual salary template so this salary template is being represented with this variable salary template over here and we are using this variable salary template at line number 55 so using this we are telling to make a copy and put it in the salary details folder so once this line of code line number 55 will get executed a copy of the file will get created in the destination folder which is salary slips it may be a bit complex to understand so what i'll do i'll just delete all these files from here and execute this code in debug mode so that you will be able to understand what is happening exactly at this line of code so i'm placing my breakpoint over here so just hover over here and click on that ellipse so this is a breakpoint and now click on the debug button once i do that the execution will pause at the place where i have placed the breakpoint which is line number 56 so since it is at line number 56 line number 55 which is actually creating a copy of the template has been executed so if we go to the salary details folder which is here you could see there is one file called copy of salary slip template if i open it it is nothing but an exact copy of the salary slip template so this was the salary slip template and this is its copy so at this point the copy is ready and whatever changes whatever customization which we need to do we will do in this copy so that the template will remain as is nothing would happen on the template whatever changes we want to do would be done on this copy of the template so in the next line of code what we are doing is we are creating another variable called raw file and this time we're using document app dot open by id and if you go back to line number 55 what was the variable raw sal file so raw salphi dot get in other words in layman's language raw file is referring to this file which is nothing but the copy of the template file do not worry about the syntax of this script this entire script is available on the blog and i'll share the link of the blog towards the end of the tutorial so make sure like these lines of code exactly as is else it will start creating problems so the variable raw file at line number 56 is referring to this copy which is created okay now once we have the raw file ready the next part is we have to read the content of the raw file and update the content of the raw file with the details which are present in the data sheet so how to do that do not worry it is not difficult at all so as i told you we need to read the content so in the next line we have another variable called raw file content and we are fetching the raw file content from this variable raw file which is at line number 56 dot get body so what will happen is the raw file content variable will store this entire stuff which is present in the copy of the template file so in the memory it will have all these details employee id employee name everything is present in the memory of the raw file content variable so at this point the raw file content is ready and now comes the very interesting part from line number 59 to line number 77 where we are using the raw file content and updating the raw file content with the content of the data sheet so these values the employee id is the employee name these will get replaced in the raw file content like this so the syntax is we are using the raw file content variable dot replace text now what i want to replace so if you see this copy of the template i want the actual employee id not employee id underscore id underscore i do not want this i want the actual employee id and if you would go back to the script the actual employee id is stored in this variable called employee id so now at line number 15 9 we are using raw file content dot replace text we are using this exact text employee id and employee underscore id underscore with the actual employee id so when this line of code will get executed in this copy this part will get replaced with the actual employee id so when all the lines of code from line number 59 to 77 will get executed all the actual values will get replaced and we'll have the salary slip in the raw format ready so once that is done the next thing would be to save it so if you would come over here at line number 79 here we are saving the file so for saving the file you have to use the raw file so the raw file the variable you created at line number 56 we are using that variable dot save and close so this raw file will have the replaced content so everything from the data sheet will have been transferred into this raw file now since we want to create salary slips it should not be in a raw google format it should be in the pdf format so at line number 80 if you see we are converting it to a pdf file so this is a standard syntax to convert the file into the pdf so we use raw file dot get as mime type dot pdf so you have to use this and it will get converted into the pdf format so before proceeding further what i'll do is to explain you what we have done up till now so i'll place a break point at line number 80 so that we could see like once the raw file is clear how does it look and then we'll take it forward and show you like how it converts into a pdf file so what i'll do at this point is i will close this copy and from the salary slips uh this template which got created in the previous debug i am deleting it and now i'm once again click clicking on this debug button you have to wait for a few seconds the execution will come at line number 80 and pause so you can see now the execution has paused and once again if we go to the salary slips folder this template has been generated and now if i double click and open it you could see whatever details were there for the row number two employee name employee id the number of days everything that has been picked up and saved into this file now still it is not in to have it converted into a pdf file i will continue the execution for that first i'll close this file i will come to my script and here on the debug window that you have this option step over step in so i'll click on step in once so the line number of 80 will get executed if i do another step now line number 81 is also executed at this point line number 80 and 81 are executed and now if you move back to the folder you could see over here this pdf file is generated if you would see the name of this file it is saturday underscored the id why this id has come because here i'm using this set name i'm using salary underscore and then i'm appending the employee id so because of which the employee id is getting appended and this is the name of the file so here if i open the pdf it is the same file which we had seen earlier but the format is pdf pdf is more professional format for salary slips so now this salary slip for this employee is completely ready so with these two lines of code line number 80 and 81 we have converted the google doc file into a pdf file and also we have renamed it as per our convenience i have renamed it with salary underscored employee it is up to you uh whatever you want to open if you want to append the month uh also the employee name or you could just do it as per your choice so now uh if you see uh in this salary slips folder if i close this uh there are like this this is the pdf which is ready but we still have the copy of salary slip template now i no longer need this since i already have the details in the pdf file i no longer need the uh copy of the template so what i'll do is in the next line of code which is line number 83 which i'm highlighting i am removing the raw file so from where from where where i have to remain remove i have to remove from the salary details folder so i'm using this salary folder dot remove file and what was the file which i want to remove so it was the raw led file which i want to remove so i have passed here the raw salary file so when this line of code will get executed this file copy of sally template will no longer be over here and we only have the required pdf file to demonstrate it uh what i'll do once again i will remove these files from here back to the code this time i'll place the breakpoint at line number 83 click on this d and now it has stopped at line number 83 if we if i come over here to a refresh you will see two files uh the copy of the template and the pdf file but now when this line number 83 will get executed if i do a step in now line number 83 has been executed once again if i see i see two files i just do a refresh the template has been deleted and now only i have the pdf file the salary slip in the pdf file format and next step is we have to email the pdf salary slip to the recipient so recipient email id we have a variable in which we are storing so this is the emp email this is the variable which is storing the email id and we have to use this gmail app dot send mail so now gmail.com takes the following parameters so this is the employee email so this is the email address you could see in the debug window so this is the email address uh next is the mail subject so mail subject here i've created a variable and i have given it a salary slip so this would be the subject of the email it is up to you whatever you want to write like salary slip for the month of xyz like whatever you want to write you could put it in the subject it would be the subject line of the email next is the mail body so here i have just written please find the salary slip off now this is the variable so salary month is coming from the data sheet uh here we have it is as december 21 so in the email body it would be please find the salary slip of december 21 attached and the next parameters like here we have to provide like this this syntax you should be careful by mentioning this syntax it should be as is we are writing attachments then colon then we have a square bracket which file so it was the sal pdf file if you remember line number 81 we had converted into a pdf file and renamed it so sal pdf dot get as mime type dot pdf because it is in pdf format so when this line of code will get executed the email would be sent to the recipient and the salary slip would be in the attachment you want to see so once again if i do a step in now i am at line number 85 now i am at land number 86 and now 10 so now if i go into the email box into the sent items i do a refresh see this one you could see the time stamp this is this email has been sent had 2344 zero minutes ago and this is 23 44. this is for salary e executive 489 and if you come over here here also it is the same file so this pdf file has been sent as an email to the so this is how we can create and send salary slips in an automated manner using the script which i just shown you i showed you the execution of just one condition but uh if we have this entire script and i'll show you where the script is on the blog so that you could prefer it you could uh directly use it maybe like you may have to customize based on the fields which you have but the entire process of creating and sending salaries tips can be automated so let me first show you where on the blog this is available so here is the blog so this is the blog and here within google sheets you have to come over here create and send salary slip just click over here the entire code is available over here you could refer it and create your own automation one small thing which is pending uh that is how to create this kind of button for that you have to come to insert go to drawing you will come up on this window select this option shape you will have multiple shapes select a shape which looks like a button click over here so this will come over here if and using right click edit text you have to write anything like for example send salary slips and click on save and close so this button will somewhere appear on the sheet and then you just move it and now you want like when you click on this button this particular script should get executed so whatever is the name of the function you have to copy it come back over here right click click on these three dots when you do that you get this option assign a script and here you have to provide the name of the function and you click on ok and now this is good to go so when this button will be clicked this entire script will get executed i showed you the execution in the debug mode for just one row but here the for loop is absolutely in place so all the rows of data will get executed and the salary slips will get generated and sent to the recipient so if you want to see that i'm just removing this file which we had earlier generated and once again i am over here click on this it will take a few seconds you could see the script is running and in the background in this folder if you just do a refresh the files would be generating you could see the first saturday slip has been generated now the next one so on and so forth all the salary slips will get generated and also they would be being sent to the recipients so right now two slips are generated if i come to the mailbox see the sent items you can see the saturday slips are being sent let me refresh it see these emails are being sent just now and i think so the execution is complete if i refresh this okay so we have the five files in the pdf format the emails are also sent so this was about how we can automate the process of creating and sending salary slips in an automated manner hope you found this tutorial helpful if you like my videos i would request you to please subscribe to my channel so that you would be updated with the latest tutorials i would be uploading guys thanks for joining