I’m working on a project where I need to transfer data from Google Sheets into an HTML table format. I have a specific column that contains only time values, but when I process this data, it keeps showing unwanted date information along with the time.
Here’s what I’m trying to do:
console.log("Processing: " + headers[0][index] + ": " + sheetData[row][index]);
var formattedTime = Utilities.formatDate(sheetData[row][index], "GMT", "HH:mm");
console.log("formatted result: " + formattedTime);
htmlContent = previousContent;
currentRow = "<tr><td>" + headers[0][index] + "</td><td>Formatted: " + formattedTime + " Original: " + sheetData[row][index] + "</td></tr>";
htmlContent = previousContent + currentRow;
The output I’m getting looks like this:
Formatted: 07:30 Original: Thu Aug 07 2014 15:30:00 GMT-0400 (EDT)
What I actually want is just the time part like 15:30 without any date stuff. The cell in my spreadsheet only contains time data, so I’m not sure why the full date and timezone info keeps appearing. Has anyone dealt with this before? Any suggestions on how to get just the clean time format?
yeah, this happens all the time. use toLocaleTimeString() instead - just do sheetData[row][index].toLocaleTimeString(‘en-US’, {hour12: false}). way easier than dealing with formatDate and you get clean 24hr format without the date stuff.
Had the exact same issue last month building a dashboard from timesheet data. Google Sheets stores time values as full datetime objects (even when you only enter time), so when you pull data through the API, you get the whole datetime mess. What fixed it for me: skip Utilities.formatDate entirely. Instead, grab the time directly using getHours() and getMinutes(), then format the string yourself. This dodges all the timezone conversion headaches that screw up your output. Alternatively, you can set your timezone explicitly in the formatDate call to match your sheet’s timezone instead of using GMT. Bottom line: Google Sheets always treats time as part of a full datetime, so you’ve got to extract just the time piece you actually need.
The issue is that Google Sheets stores time as a decimal fraction of a day, but Apps Script converts it to a full Date object and tacks on today’s date. I hit this same problem building a scheduling app and found the best fix is parsing just the time parts. Try var timeOnly = new Date(sheetData[row][index]).toTimeString().slice(0,5); - this grabs the HH:MM portion straight from the time string. It skips the formatting utilities completely and gives you consistent results no matter what timezone you’re in. The slice method chops off everything after the minutes, so you get clean time values like 15:30.