Scripted Success Master

Google Form Leave Approval System with Auto Email, Attachments & Manager Action Buttons

This tutorial shows how to automate leave request approvals using Google Forms, Google Sheets, and Apps Script.

• Trigger auto emails on form submit
• CC manager with in-email action buttons
• Update status in Google Sheet

🧾 Google Form Structure

Create a Google Form with the following fields:

1. Full Name
2. Email ID
3. Manager Email ID
4. Leave Type
5. Start Date
6. End Date
7. Duration
8. Reason

📊 Google Sheet Columns

After linking your form to Google Sheets, add 3 columns:

• Status – Manager’s decision
• Approve Link
• Reject Link

⚙️ Apps Script Setup

Open your linked Sheet → Extensions > Apps Script and paste the following code:

🔁 Full Combined Apps Script

const SHEET_NAME = 'Form Responses 1';
const WEB_APP_URL = 'WEB_APP_URL'; // Replace after deployment

function onFormSubmit(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const row = sheet.getLastRow();
  const data = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const name = data[1];
  const empEmail = data[2];
  const mgrEmail = data[3];
  const leaveType = data[4]; // Full Day / Half Day (First Half) / etc
  const startDate = formatDate(data[5]);
  const endDate = formatDate(data[6]);
  const remarks = data[7];

  const rowId = row;
  const approveLink = `${WEB_APP_URL}?action=approve&id=${rowId}`;
  const rejectLink = `${WEB_APP_URL}?action=reject&id=${rowId}`;

  sheet.getRange(row, sheet.getLastColumn() - 1).setValue(approveLink);
  sheet.getRange(row, sheet.getLastColumn()).setValue(rejectLink);

  const subject = `Leave Request - ${name}`;
  const html = `
    
Hi ${mgrEmail},
    
${name} has submitted a leave request. Details below:
    

      
Name
${name}
      
Leave Type
${leaveType}
      
Start Date
${startDate}
      
End Date
${endDate}
      
Remarks
${remarks}
    
    

      ✅ Approve
        
      ❌ Reject
    
    
Thank you,
HR Automation
  `;

  MailApp.sendEmail({
    to: empEmail,
    cc: mgrEmail,
    subject: subject,
    htmlBody: html
  });
}

function doGet(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const rowId = parseInt(e.parameter.id);
  const action = e.parameter.action;

  if (!rowId || !action) {
    return HtmlService.createHtmlOutput("Invalid link.");
  }

  const row = sheet.getRange(rowId, 1, 1, sheet.getLastColumn()).getValues()[0];

  const name = row[1];
  const empEmail = row[2];
  const leaveType = row[4];
  const startDate = formatDate(row[5]);
  const endDate = formatDate(row[6]);
  const remarks = row[7];

  const statusCell = sheet.getRange(rowId, sheet.getLastColumn() - 2);

  const leaveDays = calculateLeaveDays(startDate, endDate, leaveType);

  if (action === 'approve') {
    statusCell.setValue("✅ Approved");
    MailApp.sendEmail({
      to: empEmail,
      subject: `✅ Leave Approved - ${leaveDays} Day(s)`,
      htmlBody: formatReplyEmail(name, leaveType, startDate, endDate, remarks, "✅ Approved", leaveDays)
    });
    return HtmlService.createHtmlOutput("Leave Approved ✅");
  }

  if (action === 'reject') {
    statusCell.setValue("❌ Rejected");
    MailApp.sendEmail({
      to: empEmail,
      subject: `❌ Leave Rejected - ${leaveDays} Day(s)`,
      htmlBody: formatReplyEmail(name, leaveType, startDate, endDate, remarks, "❌ Rejected", leaveDays)
    });
    return HtmlService.createHtmlOutput("Leave Rejected ❌");
  }

  return HtmlService.createHtmlOutput("No action taken.");
}

// Helper: Format date as DD-MMM-YYYY
function formatDate(date) {
  return Utilities.formatDate(new Date(date), Session.getScriptTimeZone(), "dd-MMM-yyyy");
}

// Helper: Calculate number of leave days
function calculateLeaveDays(start, end, type) {
  const startDate = new Date(start);
  const endDate = new Date(end);
  const days = Math.round((endDate - startDate) / (1000 * 60 * 60 * 24)) + 1;
  if (type.toLowerCase().includes("half")) return 0.5;
  return days;
}

// Helper: Reply email formatting
function formatReplyEmail(name, leaveType, startDate, endDate, remarks, status, days) {
  return `
    
Hi ${name},
    
Your leave request has been ${status}. Below are the details:
    

      
Leave Type
${leaveType}
      
Leave Days
${days}
      
Start Date
${startDate}
      
End Date
${endDate}
      
Remarks
${remarks}
    
    
Regards,
HR Automation
  `;

}

📌 Tip: Deploy as a Web App, copy the deployed URL, and paste it in WEB_APP_URL above.

📤 Deploy Web App

1. Click Deploy > Manage deployments
2. Select Web App, access = “Anyone”
3. Click Deploy and copy the Web App URL
4. Paste it into your script

🚀 Final Output

Once a user submits the form:

• Manager gets an email with approval links
• Clicking a link updates the Google Sheet
• Employee is notified of the decision

📖 Read more on our Blogger Site or 📺 watch tutorials on our YouTube Channel