Google Apps Script - Time Formatting Issue in Google Sheets Job Reminder Script

[Otomobiru]

Description
I am working on a Google Apps Script to generate WhatsApp job reminders from a Google Sheets dataset. The script extracts, formats, and pastes the reminders into a specific column (Column J).

However, the extracted time values are incorrect, and I am unable to resolve the issue. The time displayed in the output does not match the actual time in the Google Sheet.

Expected Behavior
The extracted time in the WhatsApp Reminder column (Column J) should match the original time in Column B.

Example:
2025-02-01 | 00:05:00 | 00:05 - DEP - Pasir Ris Dr (Padma .)
2025-02-01 | 04:45:00 | 04:45 - DEP - Hillcrest Rd (Daryl Huang) - VIP

Actual Behavior
Instead, the output time is incorrect and appears shifted.

Example of Incorrect Output:
👤 Padma .
23:23 - DEP - Pasir Ris Dr (Padma .)
21:23 - ARR - Yishun St (Padma .)

Steps Taken to Debug
I have ensured that the time format is correct in Google Sheets (HH:mm:ss).
I used Utilities.formatDate() to try formatting correctly.
I checked if Google Sheets stores time as a fraction of a day and extracted the decimal part using % 1, but the issue persists.
I manually checked serial time values and found discrepancies in the way the script processes them.

Code Used
Here’s the full Google Apps Script I am using:
function generateWhatsAppReminders() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();

var reminders = {}; // Store reminders by driver name
var header = data[0]; // Get column headers

// Find indexes dynamically
var dateIdx = header.indexOf("Date");
var timeIdx = header.indexOf("Time");
var jobTypeIdx = header.indexOf("Job Type");
var flightNumIdx = header.indexOf("Flight Number");
var locationIdx = header.indexOf("Location");
var driverIdx = header.indexOf("Driver Name");
var remarksIdx = header.indexOf("Remarks");
var outputIdx = header.indexOf("WhatsApp Reminder"); // Column J

if (outputIdx === -1) {
sheet.getRange(1, header.length + 1).setValue("WhatsApp Reminder");
outputIdx = header.length;
}

for (var i = 1; i < data.length; i++) {
var time = formatTimeCorrectly(data[i][timeIdx]);
var jobType = data[i][jobTypeIdx] || "";
var flightNum = data[i][flightNumIdx] ? data[i][flightNumIdx] + " - " : ""; // Include flight number if available
var location = data[i][locationIdx] || "";
var driver = data[i][driverIdx] || "";
var remarks = data[i][remarksIdx] ? " - " + data[i][remarksIdx] : "";

if (!driver || !time || !location) continue; // Skip empty rows

var reminderText = `*${time}* - ${jobType} - ${flightNum}${location} (${driver})${remarks}`;

if (!reminders[driver]) reminders[driver] = [];
reminders[driver].push(reminderText);

}

// Write formatted reminders into Column J
var rowCounter = 2; // Start from row 2
for (var driver in reminders) {
var driverReminder = 👤 *${driver}*\n${reminders[driver].join("\n")};
sheet.getRange(rowCounter, outputIdx + 1).setValue(driverReminder);
rowCounter++;
}

Logger.log("WhatsApp reminders generated successfully.");
}

function formatTimeCorrectly(timeValue) {
if (timeValue === null || timeValue === undefined) {
return "??:??";
}

if (typeof timeValue === "string") {
const trimmed = timeValue.trim();
const timeParts = trimmed.split(':');
if (timeParts.length >= 2) {
return ${timeParts[0].padStart(2, '0')}:${timeParts[1].padStart(2, '0')};
}
return trimmed !== "" ? trimmed : "??:??";
} else if (timeValue instanceof Date) {
return Utilities.formatDate(timeValue, Session.getScriptTimeZone(), "HH:mm");
} else if (typeof timeValue === "number") {
const decimalTime = timeValue % 1;
const totalMinutes = Math.round(decimalTime * 1440);
const hours = Math.floor(totalMinutes / 60) % 24;
const minutes = totalMinutes % 60;
const adjustedHours = (hours + 24) % 24;
return ${String(adjustedHours).padStart(2, '0')}:${String(minutes).padStart(2, '0')};
} else {
return "??:??";
}
}

---

Possible Causes & What I Need Help With
The script is still not extracting the correct time value from Google Sheets.
There may be an issue with how Google Sheets stores time in serialized formats.
Need help correcting the time formatting function so that it displays the correct time.
How to Reproduce the Issue

Create a Google Sheet with the following headers:
Date | Time | Job Type | Flight Number | Location | Driver Name | Remarks | WhatsApp Reminder
Enter sample data (ensure time values are properly formatted in Google Sheets).
Run the script generateWhatsAppReminders().
Check Column J → The extracted times do not match Column B.

Expected Fix
Ensure that time extraction works correctly from Google Sheets.
Ensure the script correctly formats and pastes reminders in Column J.
Fix the time discrepancy issue in formatTimeCorrectly().

System Information
Google Sheets Version: Web-based (latest)
Google Apps Script Runtime: V8
Time Zone Settings: Default (Singapore Standard Time, GMT+8)

I have tested multiple formatting methods, but the time extraction issue still persists. Any insights or fixes would be greatly appreciated! 🙏

Thank you for your help! 🚀