Leave Request Automation System

Question

good evening , i have just started JavaScript learning a week ago and its logic doesn't yet hit 100% .I am trying to build up a leave System request for my staff and i have recently encountered issues :

1. when I run my DoGet in got 'No parameters provided or event object is undefined.' and I don't get why the e.parameter are not passing (web app Url up to date).
2. 'Invalid action parameter' which I believe may come the first issue as well but not sure totally .
3. my 'updateLeaveTakenBasedOnApproval' function does not log into my targetsheet.

if any of you guys had some guidance to provide and help my learning it would be greatly appreciated .

// Then doget
  // Function to handle HTTP GET requests
  function doGet(e) {
    try {
      // Logging to check if the event object is defined
      Logger.log("Event Object: " + JSON.stringify(e));

      // Ensure the event object and parameters are defined to avoid errors
      if (!e || !e.parameters) {
        Logger.log("No parameters provided or event object is undefined.");
        return HtmlService.createHtmlOutput("No parameters provided or event object is undefined.");
      }

      // Extract parameters from the event object
      var action = e.parameter.action;
      var name = e.parameter.name;
      var leaveType = e.parameter.leaveType;
      var leaveStartDate = new Date(e.parameter.leaveStartDate);
      var leaveEndDate = new Date(e.parameter.leaveEndDate);
      var duration = Number(e.parameter.duration);
      var isApproved = e.parameter.isApproved === "true"; // Convert string to boolean

      // Logging to check parameter values
      Logger.log("Action: " + action);
      Logger.log("Name: " + name);
      Logger.log("Leave Type: " + leaveType);
      Logger.log("Leave Start Date: " + leaveStartDate);
      Logger.log("Leave End Date: " + leaveEndDate);
      Logger.log("Duration: " + duration);
      Logger.log("Is Approved: " + isApproved);

      // Validate the action parameter
      if (!action || (action !== 'approve' && action !== 'reject')) {
        Logger.log("Invalid action parameter.");
        return HtmlService.createHtmlOutput("Invalid action parameter.");
      }

      // Process the action based on the "approve" or "reject" input
      if (action === 'approve') {
        if (isApproved) {
          // Perform actions for approval (e.g., update status in the source sheet)
          // Call the function to update leave taken based on approval status
          var isApproved = true; // Assuming leave is approved by default
          var databaseSpreadsheetId = '1yivdugmI2Bpkw0fU179_3G9A6YfvgMFAEVqbFHU_6Sw';
          var targetSpreadsheetId = '1iUpFilWPgIOs_e7ZUPBp5FHhoxXRtYsE14ALkQYkRiA';
          updateLeaveTakenBasedOnApproval(targetSpreadsheetId, databaseSpreadsheetId, name, duration, leaveType, isApproved);
          Logger.log("Request approved successfully. Name: " + name);
          return HtmlService.createHtmlOutput("Request approved successfully. Name: " + name);
        } else {
          // Perform actions for rejection
          Logger.log("Request rejected. No action taken. Name: " + name);
          return HtmlService.createHtmlOutput("Request rejected. No action taken. Name: " + name);
        }
      } else {
        // Perform actions for rejection (e.g., update status in the source sheet)
        Logger.log("Request rejected successfully. Name: " + name);
        return HtmlService.createHtmlOutput("Request rejected successfully. Name: " + name);
      }
    } catch (error) {
      Logger.log("Error in processing request: " + error);
      return HtmlService.createHtmlOutput("Error processing request: " + error);
    }
  }

// Main function to process leave requests and notify the manager
function processLeaveRequestAndNotifyManager() {
    try {
      console.log("Processing leave request and notifying manager...");

      // Spreadsheet IDs
      var sourceSpreadsheetId = '1jq55QKkyBW3c1v2COvm-mLxya9XSY9ujYJDLIqmitAc';
      var databaseSpreadsheetId = '1yivdugmI2Bpkw0fU179_3G9A6YfvgMFAEVqbFHU_6Sw';
      var targetSpreadsheetId = '1iUpFilWPgIOs_e7ZUPBp5FHhoxXRtYsE14ALkQYkRiA';

      // Manager email
      var managerEmail = '[email protected]';
      console.log("Manager email: " + managerEmail);

      // Open the source spreadsheet and get the latest request data
      var sourceSheet = SpreadsheetApp.openById(sourceSpreadsheetId).getSheets()[0];
      var lastRow = sourceSheet.getLastRow();
      console.log("Last row in source sheet: " + lastRow);
      var requestData = sourceSheet.getRange(lastRow, 2, 1, 4).getValues()[0];
      console.log("Request data: " + requestData);

      // Extracting request details
      var name = requestData[0];
      var leaveType = requestData[1];
      var leaveStartDate = new Date(requestData[2]);
      var leaveEndDate = new Date(requestData[3]);
      var duration = (leaveEndDate - leaveStartDate) / (1000 * 60 * 60 * 24) + 1;
      console.log("Request details - Name: " + name + ", Leave Type: " + leaveType + ", Start Date: " + leaveStartDate + ", End Date: " + leaveEndDate + ", Duration: " + duration);

      // Open the database spreadsheet and find staff row index
      var databaseSheet = SpreadsheetApp.openById(databaseSpreadsheetId).getSheets()[0];
      var staffRowIndex = findStaffRowIndexByName(databaseSheet, name);
      console.log("Staff row index: " + staffRowIndex);

      if (staffRowIndex === -1) {
        console.error(`Staff member "${name}" not found in the database.`);
        return;
      }

      // Check leave availability
      var columnIndex = getLeaveTypeColumnIndex(leaveType); // Get the column index based on the leave type
      console.log("Column index for leave type '" + leaveType + "': " + columnIndex);
      var remainingBalance = parseFloat(databaseSheet.getRange(staffRowIndex, columnIndex).getValue()); // Convert to number
      console.log("Remaining Balance: " + remainingBalance);

      var leaveAvailable = duration <= remainingBalance;
      console.log("Leave available: " + leaveAvailable);

      if (leaveAvailable) {
        var webAppUrl = 'https://script.google.com/macros/s/AKfycbzsVsT2xWJguDbikTEJq_XQASY1ynB7y8IHb5x5Pohu2nvjvRqVt3b80i5v3lzrZEdohA/exec';

        // Construct URLs for approval and rejection
        var approveUrl = constructApprovalUrl(webAppUrl, name, leaveType, leaveStartDate, leaveEndDate, duration);
        var rejectUrl = constructRejectionUrl(webAppUrl, name, leaveType, leaveStartDate, leaveEndDate, duration);
        console.log("Approval request URL: " + approveUrl);
        console.log("Rejection request URL: " + rejectUrl);

        // Send approval request email with the constructed URLs
        sendApprovalRequestEmail(name, leaveType, leaveStartDate, leaveEndDate, duration, managerEmail, approveUrl, rejectUrl);
        console.log("Approval request email sent.");


      } else {
        sendInsufficientLeaveNotification(name, leaveType, managerEmail);
        console.log("Insufficient leave notification email sent.");
      }

    } catch (error) {
      console.error("Error processing leave request: " + error.toString());
      // Consider sending an error notification email to a system administrator or logging the error to a monitoring system.
    }
  }




  // Function to construct approval URL
  function constructApprovalUrl(webAppUrl, name, leaveType, leaveStartDate, leaveEndDate, duration) {
    var approvalUrl = webAppUrl + '?action=approve' +
        '&name=' + encodeURIComponent(name) +
        '&leaveType=' + encodeURIComponent(leaveType) +
        '&leaveStartDate=' + encodeURIComponent(leaveStartDate) +
        '&leaveEndDate=' + encodeURIComponent(leaveEndDate) +
        '&duration=' + encodeURIComponent(duration) +
        '&isApproved=true'; // Hardcoded to true for approval action

    // Log the constructed approval URL
    console.log("Approval URL: " + approvalUrl);

    return approvalUrl;
  }

  // Function to construct rejection URL
  function constructRejectionUrl(webAppUrl, name, leaveType, leaveStartDate, leaveEndDate, duration) {
    var rejectionUrl = webAppUrl + '?action=reject' +
        '&name=' + encodeURIComponent(name) +
        '&leaveType=' + encodeURIComponent(leaveType) +
        '&leaveStartDate=' + encodeURIComponent(leaveStartDate) +
        '&leaveEndDate=' + encodeURIComponent(leaveEndDate) +
        '&duration=' + encodeURIComponent(duration);

    // Log the constructed rejection URL
    console.log("Rejection URL: " + rejectionUrl);

    return rejectionUrl;
  }


  // Function to find staff row index by name
  function findStaffRowIndexByName(databaseSheet, name) {
    var data = databaseSheet.getDataRange().getValues(); // Fetches all the data from the sheet
    for (var i = 0; i < data.length; i++) {
      if (data[i][0] === name) { // Assuming names are in the first column
        return i + 1; // Spreadsheet rows are 1-indexed
      }
    }
    return -1; // Name not found
  }

  // Function to get the column index based on the leave type
  function getLeaveTypeColumnIndex(leaveType) {
    switch (leaveType) {
      case 'Annual Leave':
        return 4; // Assuming Annual Leave columns start from column 4
      case 'Unpaid Leave':
        return 7; // Assuming Unpaid Leave columns start from column 7
      case 'Sick Leave':
        return 10; // Assuming Sick Leave columns start from column 10
      case 'Special Leave':
        return 13; // Assuming Special Leave columns start from column 13
      default:
        return -1; // Leave type not recognized
    }
  }

  // Function to update leave taken based on approval status
  function updateLeaveTakenBasedOnApproval(targetSpreadsheetId, databaseSpreadsheetId, name, duration, leaveType, isApproved) {
    try {
      if (!isApproved) {
        console.log("Leave request rejected. Database will not be updated.");
        return;
      }

      var targetSheet = SpreadsheetApp.openById(targetSpreadsheetId).getSheets()[0];
      var databaseSheet = SpreadsheetApp.openById(databaseSpreadsheetId).getSheets()[0];

      // Find the row index of the staff member in the database sheet
      var staffRowIndex = findStaffRowIndexByName(databaseSheet, name);

      if (staffRowIndex === -1) {
        console.error(`Staff member "${name}" not found in the database sheet.`);
        return;
      }

      // Define the column indices for leave taken based on leave type
      var leaveTakenColumnIndices = {
        'Annual Leave': 3,    // Column 3
        'Unpaid Leave': 6,    // Column 6
        'Sick Leave': 9,      // Column 9
        'Special Leave': 12   // Column 12
      };

      var leaveTakenColumnIndex = leaveTakenColumnIndices[leaveType];

      if (leaveTakenColumnIndex === undefined) {
        console.error(`Leave type "${leaveType}" not recognized.`);
        return;
      }

      // Get the current leave taken value
      var leaveTaken = parseFloat(databaseSheet.getRange(staffRowIndex, leaveTakenColumnIndex).getValue());

      // Update the leave taken by adding the duration
      leaveTaken += duration;

      // Set the updated leave taken value
      databaseSheet.getRange(staffRowIndex, leaveTakenColumnIndex).setValue(leaveTaken);

      console.log("Leave taken updated successfully.");

    } catch (error) {
      console.error("Error updating leave taken: " + error.toString());
      // Consider sending an error notification email to a system administrator or logging the error to a monitoring system.
    }
  }



  // Function to adjust duration based on leave type and remaining balance
  function adjustDurationBasedOnLeaveType(duration, leaveType, remainingBalance) {
    var leavePrecision = {
      'Annual Leave': 1, // Days
      'Unpaid Leave': 0.5, // Half-Days
      'Sick Leave': 0.25, // Quarter-Days
      'Special Leave': 0.1 // Tenths of a Day
      // Add more leave types if needed
    };

    var precision = leavePrecision[leaveType];
    if (precision === undefined) {
      console.error(`Leave type "${leaveType}" not found in leave precision.`);
      return duration; // Return original duration if leave type not found
    }

    // Adjust duration based on precision and remaining balance
    var adjustedDuration = Math.min(duration, remainingBalance * precision);
    return adjustedDuration;
  }


  // Function to send approval request email
  function sendApprovalRequestEmail(name, leaveType, leaveStartDate, leaveEndDate, duration, managerEmail, approveUrl, rejectUrl) {
    // Log the URLs for debugging
    console.log("Approval URL: " + approveUrl);
    console.log("Rejection URL: " + rejectUrl);

    var subject = "Leave Approval Request";
    var body = "Dear " + managerEmail + ",<br><br>" +
               "Please review and approve the leave request for " + name + ".<br><br>" +
               "Leave Type: " + leaveType + "<br>" +
               "Start Date: " + leaveStartDate + "<br>" +
               "End Date: " + leaveEndDate + "<br>" +
               "Duration: " + duration + " days<br><br>" +
               "To approve, click <a href='" + approveUrl + "'>Approve</a><br><br>" +
               "To reject, click <a href='" + rejectUrl + "'>Reject</a><br><br>" +
               "Thank you,<br>" +
               "Leave Management System";
    MailApp.sendEmail(managerEmail, subject, body, {htmlBody: body});
  }



  // Function to send insufficient leave notification email
  function sendInsufficientLeaveNotification(name, leaveType, managerEmail) {
    // Construct email body for insufficient leave notification
    var subject = "Insufficient Leave Balance";
    var body = "Mr/Mrs " + name + ",<br><br>" +
               "requested for " + leaveType + " however the leave has been rejected due to insufficient leave balance.<br><br>" +
               "Please contact HR for further assistance.<br><br>" +
               "Thank you,<br>" +
               "Leave Management System";
    MailApp.sendEmail(managerEmail, subject, body, {htmlBody: body});
  }

the concept behind was once the employee fill the leave request form (google) and data are copied in SourceSheet then an email would be sent to the manager for approval or rejection (if they have enough leave remaing) . the manager would then approve or reject and depending this action - approve will log data in TargetSheet then update database (if rejected then only log in TargetSheet) .