Auto PDF Slip Generation & Mailing from Google Sheets

Introduction :
Picture a seamless and effortless automation process that empowers you to generate professional slips for invoices, salary statements, bills, and more. Our innovative solution simplifies the entire process using just an Excel sheet, source code (as per your needs), and a slip template resembling bill columns. This Excel sheet acts as a centralized hub, guaranteeing precise and consistent slip generation every time. Say goodbye to complicated manual procedures and welcome a hassle-free experience that streamlines your slip creation.

Pre-requisites :

 Default Slip Template:

•   *) Specific Template: A designated template is essential for streamlining the slip generation process.
  *)Inclusion of Necessary Fields: The template should include all the required fields that exist in the Excel sheet, such as employee details, designation, tax identification number, email, month & year, allowances, and total income.

*) Flexibility for Customization: You have the freedom to customize the template according to your unique requirements.

*)Additional Columns or Fields: If you have additional columns or fields you want to incorporate into the slips, we can easily add them to the template.

*)Perfect Alignment: Customizing the template ensures that the generated slips perfectly align with your desired format.

*)Relevant Information: By customizing the template, you can ensure that all the relevant information you need is included in the generated slips.

 With this tailored template, you can effortlessly generate professional slips that meet your specific needs, saving time and ensuring accuracy throughout the process.

Excel Sheet as a Central Repository:

*)  Information-Filled Sheet: To generate accurate slips, you need an Excel sheet where all the required details are filled in advance.
*)  Corresponding Details:  The details entered in this sheet will be automatically populated into the designated template sheet.
*)  Comprehensive Data:  The Excel sheet should include comprehensive information, such as employee IDs, names, designations, tax identification numbers, email addresses, month and year, allowances, and  total income.
*)  Customizable Columns:  The sheet can be customized to accommodate any additional columns or fields that you may require.
*)  Accuracy and Consistency:  By utilizing the information-filled sheet, you ensure accuracy and consistency in the generated slips.
*)  Streamlined Workflow:  With the information readily available in the Excel sheet, the slip generation process becomes seamless and efficient.

By having an organized and populated Excel sheet, you can effortlessly generate slips that reflect the desired information, making the entire process hassle-free.
*****************************************************************************************************************************************************************************
Other info –
Slip Storage Folder: You have the flexibility to choose a name for the folder where all the generated slips in PDF format will be stored. For example, you can name it “Salary_Slips” or any other name of your choice.
Excel Sheet – Salary Details: The Excel sheet acts as a repository where you store all the necessary details and related information of your employees. It serves as a central source for generating the slips accurately.
Organized Slip Storage: The designated folder, such as “Salary_Slips,” ensures that all the generated slips are conveniently stored in one place, making it easy to access and manage them.

********************************************************************************************************************************************************************************************************
Working
To begin, open your Google Sheet (Excel) where all your details are stored. Next, locate the Extensions menu and click on it. Within the dropdown menu, you will find the option for “Apps Script”. Simply select this option to proceed with the action.

To proceed, please copy and paste the provided code into the Apps Script editor. This will allow you to implement the necessary functionality for automating the slip generation process.

function createAndSendSalSlips() {
  var empID = "";
  var empName="";
  var empEmail = "";
  var empDesg="";
  var empTIN="";
  var noOfDaysWorked=0;
  var salMonth="";
  var basicSal=0;
  var hra=0;
  var travelAllowence=0;
  var grossIncome=0;
  var netSal=0;
  var otherallowance=0;
  var spSheet = SpreadsheetApp.getActiveSpreadsheet();
  var salSheet = spSheet.getSheetByName("<Name of google sheet>");
  var salaryDetailsFolder =       DriveApp.getFolderById("<id of folder in which pdf generated>");
 var salaryTemplate =  DriveApp.getFileById("<id of template docs>");
 var totalRows = salSheet.getLastRow();
 for(var rowNo=2;rowNo <=totalRows; rowNo++)
  {
 empID= salSheet.getRange("A" + rowNo).getDisplayValue();
 empName = salSheet.getRange("B" + rowNo).getDisplayValue();
 empDesg= salSheet.getRange("C" + rowNo).getDisplayValue();
 empTIN= salSheet.getRange("D" + rowNo).getDisplayValue();
 empEmail= salSheet.getRange("E" + rowNo).getDisplayValue();
 salMonth = salSheet.getRange("F" + rowNo).getDisplayValue();
 noOfDaysWorked = salSheet.getRange("H" + rowNo).getDisplayValue();
 basicSal = salSheet.getRange("I" + rowNo).getDisplayValue();
 hra = salSheet.getRange("J" + rowNo).getDisplayValue();
 travelAllowence = salSheet.getRange("K" + rowNo).getDisplayValue();
 grossIncome=salSheet.getRange("L" + rowNo).getDisplayValue();
 otherallowance=salSheet.getRange("O" + rowNo).getDisplayValue();
 netSal=salSheet.getRange("M" + rowNo).getDisplayValue();
 var rawSalFile = salaryTemplate.makeCopy(salaryDetailsFolder);
 var rawFile = DocumentApp.openById(rawSalFile.getId());
 var rawFileContent = rawFile.getBody();
 rawFileContent.replaceText("EMP_ID_XXXX",empID);
 rawFileContent.replaceText("EMP_NAME_XXXX",empName);
 rawFileContent.replaceText("DESG_XXXX", empDesg);
 rawFileContent.replaceText("MONTH_XXXX", salMonth);
 rawFileContent.replaceText("DAYS_XXXX", noOfDaysWorked);
 rawFileContent.replaceText("TIN_XXXX", empTIN);
 rawFileContent.replaceText("OA_XXXX", otherallowance);
 rawFileContent.replaceText("BASIC_SAL_XXXX", basicSal);
 rawFileContent.replaceText("HRA_XXXX", hra);
 rawFileContent.replaceText("TA_XXXX", travelAllowence);
 rawFileContent.replaceText("TL_XXXX", grossIncome);
 rawFileContent.replaceText("NSP_XXXX", netSal);
 rawFile.saveAndClose(); 
 var salSlip = rawFile.getAs(MimeType.PDF)
 salPDF = salaryDetailsFolder.createFile(salSlip).setName("Salary_" + empID);
salaryDetailsFolder.removeFile(rawSalFile);
var mailSubject = "Salary Slip";
var mailBody = "Please find the salary slip for the month of " + salMonth + "attached.";
GmailApp.sendEmail(empEmail, mailSubject, mailBody, {attachments:[salPDF.getAs(MimeType.PDF)]});
  }
}

In this line of code, you will need to enter the specific folder ID where you want to store all the generated PDF slips.

var salaryDetailsFolder = DriveApp.getFolderById("<id of folder in which pdf generated>");

For example :

To find the exact location of your desired folder in your Google Drive, you can simply copy the folder ID from the URL and paste it into the provided code. This ensures that the generated PDF slips are saved in the correct folder, making it easier for you to locate and manage them.
Do same with Slip_template file

var salaryTemplate = DriveApp.getFileById("<id of template docs>");

After this back to Google sheet

To add a button to your Google Sheet, follow these steps:

1. Click on the “Insert” tab at the top of the sheet.
2. Select “Drawing” from the drop-down menu.
3. Click on the icon representing the shape you want to use for your button.
4. Draw the button in the desired location on the sheet.
5. Add the text “Download PDF” or any other label you prefer to the button.
6. Click on the “Save and Close” button to finalize the button creation.

Once the button is added, you can simply click on it and assign a script that we write on Apps Script

*) When assigning a script to the button you created, you will be prompted like this “”What script do you want to assign?””” to give a name to the button.

*) It is recommended to use the same name as the first function in the script. This ensures that the button is associated with the correct function and will execute.

Now, simply click on the button you created, and the result will be the generation of PDF slips. These slips will be automatically saved in the “Salary_slip” folder, which is the designated location for storing the generated PDFs.And also mail them with attachment of their slip .

Conclusion 
By following these steps and utilizing the automated script, you can effortlessly create PDF slips. This streamlined process eliminates the need for manual creation and saves you valuable time and effort.

(_Support from experts: ShekharSushantPal , Sukhdeep Singh , and Pooja Sandhu  provide ongoing support._)