To create a leave form using JavaScript with Google Apps Script, you can follow these steps:
1. Set up a Google Form:
- Go to Google Forms (forms.google.com) and create a new form.
- Add the necessary fields to the form, such as "Name," "Leave Type," "Start Date," "End Date," etc.
- Save the form and obtain the form URL.
2. Set up a Google Sheet:
- Create a new Google Sheet and open the script editor by going to "Tools" > "Script editor."
- In the script editor, delete any existing code and replace it with the following code:
JavaScript:
function onSubmit(e) {
var formResponse = e.response;
var itemResponses = formResponse.getItemResponses();
var name = itemResponses[0].getResponse();
var leaveType = itemResponses[1].getResponse();
var startDate = new Date(itemResponses[2].getResponse());
var endDate = new Date(itemResponses[3].getResponse());
// Calculate leave duration (in days) and exclude weekends
var leaveDuration = Math.ceil((endDate - startDate) / (1000 * 60 * 60 * 24));
var weekends = 0;
for (var i = 0; i <= leaveDuration; i++) {
var currentDate = new Date(startDate.getTime() + (i * 24 * 60 * 60 * 1000));
if (currentDate.getDay() === 0 || currentDate.getDay() === 6) { // Sunday (0) or Saturday (6)
weekends++;
}
}
var actualLeaveDuration = leaveDuration - weekends;
// Update leave balance
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var leaveBalanceCell = sheet.getRange('B2');
var leaveBalance = leaveBalanceCell.getValue();
leaveBalance -= actualLeaveDuration;
leaveBalanceCell.setValue(leaveBalance);
// Send email notification
var recipient = 'your-email@example.com';
var subject = 'Leave Request: ' + name;
var message = 'Leave Type: ' + leaveType + '\n' +
'Start Date: ' + startDate.toDateString() + '\n' +
'End Date: ' + endDate.toDateString() + '\n' +
'Leave Duration: ' + actualLeaveDuration + ' day(s)' + '\n' +
'Leave Balance: ' + leaveBalance + ' day(s)';
MailApp.sendEmail(recipient, subject, message);
}
3. Save the script and close the script editor.
4. Link the Google Form to the Google Sheet:
- Open the Google Form.
- Go to "Responses" > "Google Sheets" icon.
- Select the option to create a new spreadsheet.
5. Adjust the leave balance calculation:
- In the Google Sheet, enter the initial leave balance in cell B2.
- Modify the calculation logic based on your specific leave policy and requirements.
6. Test the form:
- Go back to the Google Form and click on "Preview" to test the form.
- Fill in the required fields and submit the form.
- Verify that the leave balance is updated correctly and an email notification is sent.
Please note that this is a basic implementation to get you started. You may need to customize the code further based on your specific requirements. Remember to read the documentation for Google Apps Script for more information on the available features and functions.
