Trying to learn Javascript (under the influence of Chat GPT)

Programming JavaScript
1

Hey! I was tasked by our HR team to find a way of automating how we currently complete our Parental Leave Calendar. I started using Apps Script despite only knowing the very basics of Javascript and obviously struggled pretty early on. I queried a few steps on Chat GPT and it implemented these changes well and it seemed to be going really smoothly. I then started to ask it to do a few more complex things and it gave a very convincing answer and then something went wrong. I’ve tried undo’ing various steps and changing various things and it just won’t work. A friend recommended asking this forum so I thought I would see if actual people with Javascript knowledge could help me instead of AI!

So my main objectives are:

  • To create a fully automated (colour coded) parental leave calendar so that one of our employees can simply select dates, and it then generates their full parental leave calendar (they can also play around with these dates and it will refresh every time).

  • The dates that they need to select are: The date that they wish to start their parental leave (start date), their due date, their birthday (as this gives us their additional days off that they’re entitled to), they need to select how many days off they have on their current annual leave (Current years leave left), and the number of days that they would like to use from next years annual leave (Days of next years leave to use).

  • The calendar needs to be split out over multiple tabs (each tab representing a month of the year and reflect which year it belongs to).

  • The calendar itself needs to show the first 13 weeks at 100% pay, the second 13 weeks at 50% pay, the next 13 weeks at SMP, and then 13 weeks unpaid - these are all colour coded. It also needs to display weekend dates as non-working days. The calendar also needs to highlight the following: Due date, Bank Holiday days, non working days, and the return date. It also needs to account for the untaken annual leave, accumulated bank holidays, birthday(s), and any leave to be taken from next years allowance. These also need to be colour coded, and added on between the last day of the 13 weeks unpaid, and before the return date. If a birthday occurs twice during this parental leave, there would need to be 2 days added instead of one. Ideally I would also like completely different pastel colours for everything to make it look nice.

  • I would also like the parental leave calendar to refresh each time and not retain any colour coded dates that were inputted beforehand. It also needs to account for the date timeframe that the employee enters.

  • I would like the tab in sheets to be titled “Parental Leave Calendar”, the next option to be titled “Choose Dates”, and then the pop up to be called “Please Select”. I would also like this pop up to look more visually pleasing than it currently does.

The next couple of things aren’t a necessity, but would be very beneficial, but I would like the following:

  • Have a facility where if the employee doesn’t want to take unpaid/SMP/50% pay then they can easily remove this.

  • Where we have the drop down asking about start date, due date, birthday, current years leave left and days of next years leave to use - I would really like a tick box asking if they have a sabbatical they would like to use. If this box is ticked, then I would like it to ask the employee how many days of their sabbatical they would like to use and reflect this at the end of their time off, before their return date.

This seems to me like an impossible task, but I’m sure someone somewhere might be able to help - or tell me that is an impossible task so that I can stop swearing at Chat GPT telling me the next ‘simple step’ that never works!

I’ll post what I currently have in a sec (I apologise in advance for anything that looks hideous - I am still learning):

2

Javascript:

function populateNeutralCell(date, defaultColor) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheetName = (date.getMonth() + 1) + “-” + date.getFullYear();

var sheet = ss.getSheetByName(sheetName);

if (!sheet) {

sheet = ss.insertSheet(sheetName);

var days = [“Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat”];

sheet.getRange(1, 1, 1, 7).setValues([days]).setBackground(“#542E91”).setFontColor(“white”);

// Reordering the sheet to its correct position based on month and year.

var allSheets = ss.getSheets();

var targetPosition = allSheets.length; // Default to the last position

for (var i = 0; i < allSheets.length; i++) {

var sName = allSheets[i].getName();

var sYear = parseInt(sName.split(“-”)[1], 10);

var sMonth = parseInt(sName.split(“-”)[0], 10);

if (date.getFullYear() < sYear || (date.getFullYear() === sYear && date.getMonth() + 1 < sMonth)) {

targetPosition = i;

break;

}

}

ss.setActiveSheet(sheet);

ss.moveActiveSheet(targetPosition + 1); // Sheets are 1-indexed

}

var firstDay = new Date(date.getFullYear(), date.getMonth(), 1).getDay();

var row = Math.floor((date.getDate() + firstDay - 1) / 7) + 2;

var col = (date.getDate() + firstDay - 1) % 7 + 1;

sheet.getRange(row, col).setValue(date.getDate()).setBackground(defaultColor);

}

function setupFullCalendar() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var currentDate = new Date(); // Today’s date

var endDate = new Date(2025, 11, 31); // End of 2025

// Iterate through all dates and set them up in sheets

while (currentDate <= endDate) {

populateNeutralCell(currentDate, “#E0E0E0”); // Neutral color for dates not included

currentDate.setDate(currentDate.getDate() + 1);

}

}

function resetCalendarColors() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var allSheets = ss.getSheets();

for (var i = 0; i < allSheets.length; i++) {

var sheetName = allSheets[i].getName();

// Check if the sheet name matches the pattern for the calendar sheets

if (/^\d{1,2}-\d{4}$/.test(sheetName)) {

var dataRange = allSheets[i].getDataRange();

dataRange.setBackground(“#E0E0E0”); // Neutral color

}

}

}

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(‘Parental Leave Calendar’)

.addItem(‘Choose Dates’, ‘showDialog’)

.addToUi();

}

function showDialog() {

var htmlOutput = HtmlService.createHtmlOutputFromFile(‘Page’)

.setWidth(250)

.setHeight(300);

SpreadsheetApp.getUi().showModalDialog(htmlOutput, ‘Set Dates’);

}

function processForm(startDate, dueDate, birthday,currentYearLeaveLeft, nextYearLeaveToUse) {

// Convert string dates to Date objects

startDate = new Date(startDate);

dueDate = new Date(dueDate);

birthday = new Date(birthday);

currentYearLeaveLeft = new String(currentYearLeaveLeft);

nextYearLeaveToUse = new String(nextYearLeaveToUse);

// Call your original calendar function (you might want to modify the original function to accept these as parameters)

createVisualMaternityCalendar(startDate, dueDate, birthday, parseInt(currentYearLeaveLeft), parseInt(nextYearLeaveToUse));

}

function createVisualMaternityCalendar(startDate, dueDate, birthday, currentYearLeaveLeft, nextYearLeaveToUse) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

resetCalendarColors();

var stages = [

{name: “100% Pay”, weeks: 13, color: “#AEDFF7”}, // Pastel Blue

{name: “50% Pay”, weeks: 13, color: “#A8E6CF”}, // Pastel Green

{name: “SMP”, weeks: 13, color: “#FFF9A1”}, // Pastel Yellow

{name: “Unpaid”, weeks: 13, color: “#FFB5B5”} // Pastel Red

];

var weekendColor = “#D3D3D3”;

var bankHolidayColor = “#FFD1BA”; // Pastel Orange

var dueDateColor = “#D0A2DA”; // Pastel Purple

var birthdayOffColor = “#FFED9A”; // Pastel Golden

var firstDayBackColor = “#A2DEE3”; // Pastel Cyan

var annualLeaveColor = “#FFDACD”; // Pastel Peach

var bankHolidayAccumulatedColor = “#BAA7E1”; // Pastel Violet

var nextYearLeaveColor = “#FFC0CB”; // Pastel Pink

var bankHolidays = [

new Date(2023, 0, 2),

new Date(2023, 3, 14),

new Date(2023, 3, 17),

new Date(2023, 4, 1),

new Date(2023, 4, 29),

new Date(2023, 7, 28),

new Date(2023, 11, 25),

new Date(2023, 11, 26)

];

function setDaysBackground() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheets = ss.getSheets();

for (var i = 0; i < sheets.length; i++) {

var sheet = sheets[i];

sheet.getRange(1, 1, 1, 7).setBackground(“#542E91”).setFontColor(“white”).setFontFamily(“Nunito”);

}

}

function populateCalendarCell(date, defaultColor) {

var sheetName = (date.getMonth() + 1) + “-” + date.getFullYear();

var sheet = ss.getSheetByName(sheetName);

if (!sheet) {

sheet = ss.insertSheet(sheetName);

var days = [“Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat”];

sheet.getRange(1, 1, 1, 7).setValues([days]).setBackground(“#542E91”).setFontColor(“white”).setFontFamily(“Nunito”); // Lighter gray for header background

}

var firstDay = new Date(date.getFullYear(), date.getMonth(), 1).getDay();

var row = Math.floor((date.getDate() + firstDay - 1) / 7) + 2;

var col = (date.getDate() + firstDay - 1) % 7 + 1;

var cellColor = defaultColor;

if (date.getTime() === dueDate.getTime()) {

cellColor = dueDateColor;

} else if (date.getTime() === birthday.getTime()) {

cellColor = birthdayOffColor;

if (birthdayOccursTwice(startDate, currentDate, birthday)) {

var secondBirthday = new Date(birthday);

secondBirthday.setFullYear(birthday.getFullYear() + 1);

if (date.getTime() === secondBirthday.getTime()) {

cellColor = birthdayOffColor;

}

}

} else if (bankHolidays.some(bh => bh.getTime() === date.getTime())) {

cellColor = bankHolidayColor;

} else if (date.getDay() === 6 || date.getDay() === 0) {

cellColor = weekendColor;

}

sheet.getRange(row, col).setValue(date.getDate()).setBackground(cellColor).setFontFamily(“Nunito”);

}

var currentDate = new Date(startDate.getTime());

for (var stage of stages) {

for (var week = 0; week < stage.weeks; week++) {

for (var day = 0; day < 7; day++) {

populateCalendarCell(currentDate, stage.color);

currentDate.setDate(currentDate.getDate() + 1);

}

}

}

var bankHolidaysDuringLeave = bankHolidays.filter(bh => bh >= startDate && bh < currentDate).length;

3

(javascript continued)

daysAdded = 0;

while (daysAdded < bankHolidaysDuringLeave) {

if (currentDate.getDay() !== 6 && currentDate.getDay() !== 0) {

populateCalendarCell(currentDate, bankHolidayAccumulatedColor);

daysAdded++;

} else {

populateCalendarCell(currentDate, weekendColor);

}

currentDate.setDate(currentDate.getDate() + 1);

}

// After existing code…

var daysAdded = 0;

// Using current year’s leave left

while (daysAdded < currentYearLeaveLeft) {

if (currentDate.getDay() !== 6 && currentDate.getDay() !== 0) {

populateCalendarCell(currentDate, annualLeaveColor);

daysAdded++;

} else {

populateCalendarCell(currentDate, weekendColor);

}

currentDate.setDate(currentDate.getDate() + 1);

}

// Reset the daysAdded for the next year

daysAdded = 0;

// Using next year’s leave

while (daysAdded < nextYearLeaveToUse) {

if (currentDate.getDay() !== 6 && currentDate.getDay() !== 0) {

populateCalendarCell(currentDate, nextYearLeaveColor);

daysAdded++;

} else {

populateCalendarCell(currentDate, weekendColor);

}

currentDate.setDate(currentDate.getDate() + 1);

}

// Existing code to process bank holidays and birthday…

// Add birthday off (excluding weekends)

while (currentDate.getDay() === 6 || currentDate.getDay() === 0) {

populateCalendarCell(currentDate, weekendColor);

currentDate.setDate(currentDate.getDate() + 1);

}

populateCalendarCell(currentDate, birthdayOffColor);

currentDate.setDate(currentDate.getDate() + 1);

// Indicate first day back to work (excluding weekends)

while (currentDate.getDay() === 6 || currentDate.getDay() === 0) {

populateCalendarCell(currentDate, weekendColor);

currentDate.setDate(currentDate.getDate() + 1);

}

populateCalendarCell(currentDate, firstDayBackColor);

}

function birthdayOccursTwice(startDate, endDate, birthday) {

var firstOccurrence = new Date(startDate.getFullYear(), birthday.getMonth(), birthday.getDate());

var secondOccurrence = new Date(startDate.getFullYear() + 1, birthday.getMonth(), birthday.getDate());

return firstOccurrence >= startDate && secondOccurrence <= endDate;

}

4

And this is the HTML:

body { font-family: 'Nunito', sans-serif; padding: 20px; } 
    label, select, input {
        margin-bottom: 15px;
        font-family: 'Nunito', sans-serif;
    }

    select {
    border: 1px solid #ccc;
    border-radius: 4px;
    padding: 5px 10px;
    font-size: 16px;
    outline: none;
    appearance: none; /* Remove default arrow in some browsers */
    -webkit-appearance: none; /* Remove default arrow in Webkit browsers */
    background: url('data:image/svg+xml;utf8,<svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" fill="currentColor" class="bi bi-chevron-down" viewBox="0 0 16 16"><path fill-rule="evenodd" d="M1.646 4.646a.5.5 0 0 1 .708 0L8 10.293l5.646-5.647a.5.5 0 0 1 .708.708l-6 6a.5.5 0 0 1-.708 0l-6-6a.5.5 0 0 1 0-.708z"/></svg>') 96% / 15% no-repeat #eee;
}

    button {
        padding: 10px 15px;
        background-color: #4CAF50;
        color: white;
        border: none;
        cursor: pointer;
        border-radius: 4px;
        font-family: 'Nunito', sans-serif;

    }

    button:hover {
        background-color: #45a049;
    }
</style>
Start Date: 
<label>
    Due Date:
    <input type="date" id="dueDate">
</label>
<br>

<label>
    Birthday:
    <input type="date" id="birthday">
</label>
<br>

<label>
    Current Year's Leave Left:
    <select id="currentYearLeave">
        <!-- Max is 33 days -->
        <option value="0">0</option>
        <option value="1">1</option>
        <option value="2">2</option>
        <option value="3">3</option>
        <option value="4">4</option>
        <option value="5">5</option>
        <option value="6">6</option>
        <option value="7">7</option>
        <option value="8">8</option>
        <option value="9">9</option>
        <option value="10">10</option>
        <option value="11">11</option>
        <option value="12">12</option>
        <option value="13">13</option>
        <option value="14">14</option>
        <option value="15">15</option>
        <option value="16">16</option>
        <option value="17">17</option>
        <option value="18">18</option>
        <option value="19">19</option>
        <option value="20">20</option>
        <option value="21">21</option>
        <option value="22">22</option>
        <option value="23">23</option>
        <option value="24">24</option>
        <option value="25">25</option>
        <option value="26">26</option>
        <option value="27">27</option>
        <option value="28">28</option>
        <option value="29">29</option>
        <option value="30">30</option>
        <option value="31">31</option>
        <option value="32">32</option>
        <option value="33">33</option>
    </select>
</label>
<br>

<label>
    Days of Next Year's Leave to Use:
    <select id="nextYearLeave">
        <!-- Max is 33 days -->
        <option value="0">0</option>
        <option value="1">1</option>
        <option value="2">2</option>
        <option value="3">3</option>
        <option value="4">4</option>
        <option value="5">5</option>
        <option value="6">6</option>
        <option value="7">7</option>
        <option value="8">8</option>
        <option value="9">9</option>
        <option value="10">10</option>
        <option value="11">11</option>
        <option value="12">12</option>
        <option value="13">13</option>
        <option value="14">14</option>
        <option value="15">15</option>
        <option value="16">16</option>
        <option value="17">17</option>
        <option value="18">18</option>
        <option value="19">19</option>
        <option value="20">20</option>
        <option value="21">21</option>
        <option value="22">22</option>
        <option value="23">23</option>
        <option value="24">24</option>
        <option value="25">25</option>
        <option value="26">26</option>
        <option value="27">27</option>
        <option value="28">28</option>
        <option value="29">29</option>
        <option value="30">30</option>
        <option value="31">31</option>
        <option value="32">32</option>
        <option value="33">33</option>
    </select>
</label>
<br>

<button onclick="submitDates()">Submit</button>

<script>
    function submitDates() {
        var startDate = document.getElementById('startDate').value;
        var dueDate = document.getElementById('dueDate').value;
        var birthday = document.getElementById('birthday').value;
        var currentYearLeave = document.getElementById('currentYearLeave').value;
        var nextYearLeave = document.getElementById('nextYearLeave').value;

        google.script.run.processForm(startDate, dueDate, birthday, currentYearLeave, nextYearLeave);
        google.script.host.close();
    }
</script>

I’m now going to continue learning the proper way, rather than thinking I’m clever taking a shortcut using Chat GPT - TIA!

5

Hello :slight_smile:
It strikes me that you’re trying to write a relatively complex program in three different languages (HTML, CSS, JS) without really knowing what you’re doing :slight_smile: That’s brave, admirable, exactly the sort of thing I’ve done lots of time in my career, but its probably going to fall down at some stage and cause you pain!

While someone might want to come along and look at all this code and try to help, it’s probably a multi-hour investment for someone to read all this and make sense of it. While we’re probably still better at coding than ChatGPT, it’s definitely faster than us at reading :slight_smile:

I’d highly suggest trying to put your code into some sort of GitHub (or equivelent) repository, rather than sharing pages of it here. You could probably use a GitHub Gist for this. There’s probably all sorts of other services too. But somewhere you can upload multiple files of code and we can read it easily. It’s pretty difficult to read like this as nothing is formated, and I looked at trying to put into into code braces to make it render here, but that won’t help in this situation either.

My main concern, though, would be you taking responsibility for putting this code online and it breaking or going wrong when your company is using it, and you having no idea how to fix it. That feels a little like a recipe for disaster for you. Of course, your company might be kind and forgiving and then it might not matter, but maybe just consider the consequences of things breaking, or someone being able to choose book parental leave for a different employee due to a bug, etc.

Those are my initial thoughts :slight_smile: