Create And Send Salary Slips

  How to Automatically Create And Send Salary Slips With Google Sheets?

  Click Here For Video Explanation

Pre-requisite: Within the Google Drive create a template of the Salary Slip.

Sample script given below. This will have to modified based on your salary structure.

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 otherAllowence=0;

  var proffTax=0;

  var tds =0;

  var totalIncome=0;

  var totalDeduction=0;

  var netSal=0;

  

  

  var spSheet = SpreadsheetApp.getActiveSpreadsheet();

  var salSheet = spSheet.getSheetByName("Salary_Details");

  

  var salaryDetailsFolder = DriveApp.getFolderById("ID of your folder");

  var salaryTemplate = DriveApp.getFileById("ID of Template file");

  

  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();

    otherAllowence= salSheet.getRange("L" + rowNo).getDisplayValue();

    totalIncome=salSheet.getRange("M" + rowNo).getDisplayValue();

    proffTax=salSheet.getRange("N" + rowNo).getDisplayValue();

    tds=salSheet.getRange("O" + rowNo).getDisplayValue();

    totalDeduction=salSheet.getRange("P" + rowNo).getDisplayValue();

    netSal=salSheet.getRange("Q" + 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("BASIC_SAL_XXXX", basicSal);

    rawFileContent.replaceText("HRA_XXXX", hra);

    rawFileContent.replaceText("TA_XXXX", travelAllowence);

    rawFileContent.replaceText("OA_XXXX", otherAllowence);

    rawFileContent.replaceText("PT_XXXX", proffTax);

    rawFileContent.replaceText("TDS_XXXX", tds);

    rawFileContent.replaceText("TI_XXXX", totalIncome);

    rawFileContent.replaceText("TD_XXXX", totalDeduction);

    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)]});

      

   

  }

  

 

  

}