Skip the Add-on: Build a Google Forms Approval Workflow in 30 Minutes

// CONFIGURATION
const CONFIG = {
  // Replace with your manager's email
  MANAGER_EMAIL: 'manager@yourcompany.com', 
  
  // Replace with the URL from "Deploy > Test Deployments" initially, 
  // then "Manage Deployments" after publishing
  WEB_APP_URL: 'YOUR_WEB_APP_URL_HERE', 
  
  STATUS_COLUMN_INDEX: 7, // Column H (1-based index)
  SHEET_NAME: 'Form Responses 1'
};
 
function getSheet() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
}

/**
 * Triggered automatically on Form Submit.
 * Sets status to 'PENDING' and emails the manager.
 */
function onFormSubmit(e) {
  const sheet = getSheet();
  const row = e.range.getRow();
  
  // 1. Set Initial Status
  sheet.getRange(row, CONFIG.STATUS_COLUMN_INDEX).setValue('PENDING');
  
  // 2. Get Form Data
  const responses = e.namedValues;
  const employee = responses['Employee Name'] ? responses['Employee Name'][0] : 'Employee';
  const type = responses['Request Type'] ? responses['Request Type'][0] : 'Request';
  const reason = responses['Reason'] ? responses['Reason'][0] : 'No reason provided';
  
  // 3. Construct Approval Links
  // We pass the Row ID and Action as parameters to our Web App
  const approveLink = `${CONFIG.WEB_APP_URL}?action=APPROVE&row=${row}`;
  const rejectLink = `${CONFIG.WEB_APP_URL}?action=REJECT&row=${row}`;
  
  // 4. Send Email
  const htmlBody = `
    <h3>Approval Request: ${type}</h3>
    <p><strong>Employee:</strong> ${employee}</p>
    <p><strong>Reason:</strong> ${reason}</p>
    <br/>
    <p>
      <a href="${approveLink}" style="background-color:green;color:white;padding:10px 20px;text-decoration:none;border-radius:4px;">✅ APPROVE</a>
      &nbsp;&nbsp;
      <a href="${rejectLink}" style="background-color:red;color:white;padding:10px 20px;text-decoration:none;border-radius:4px;">❌ REJECT</a>
    </p>
  `;
  
  GmailApp.sendEmail(CONFIG.MANAGER_EMAIL, `Approval Needed: ${employee}`, '', {
    htmlBody: htmlBody,
    name: 'Approval Bot'
  });
}

/**
 * Handle HTTP GET requests (Link Clicks)
 */
function doGet(e) {
  const params = e.parameter;
  const action = params.action; // 'APPROVE' or 'REJECT'
  const row = parseInt(params.row);
  
  if (!action || !row) return HtmlService.createHtmlOutput('Invalid Request');
  
  const sheet = getSheet();
  // Check current status to prevent double-approvals
  const statusRange = sheet.getRange(row, CONFIG.STATUS_COLUMN_INDEX);
  const currentStatus = statusRange.getValue();
  
  if (currentStatus !== 'PENDING') {
    return HtmlService.createHtmlOutput(`<h2>Request details have already been processed (${currentStatus}).</h2>`);
  }
  
  // Update Status
  const newStatus = action === 'APPROVE' ? 'APPROVED' : 'REJECTED';
  statusRange.setValue(newStatus);
  
  // Optional: Notify Employee of Decision
  // const employeeEmail = sheet.getRange(row, 2).getValue(); // Assuming email in Col B
  // GmailApp.sendEmail(employeeEmail, `Request ${newStatus}`, ...);
  
  return HtmlService.createHtmlOutput(`
    <div style="font-family:sans-serif; text-align:center; padding:50px;">
      <h1 style="color:${action === 'APPROVE' ? 'green' : 'red'}">Request ${newStatus}</h1>
      <p>The spreadsheet has been updated. You can close this window.</p>
    </div>
  `);
}