Google Sheets time data showing unwanted date info in HTML table

Other Questions Google Sheets
1

I’m having trouble with time data from Google Sheets. When I put it in an HTML table, it’s showing the full date too. Here’s what I’m doing:

console.log('Time:', sheetData[0][col], ':', sheetData[row][col]);
let formattedTime = Utilities.formatDate(sheetData[row][col], 'GMT', 'HH:mm');
console.log('Formatted:', formattedTime);
let tableRow = `<tr><td>${sheetData[0][col]}</td><td>Formatted: ${formattedTime} Original: ${sheetData[row][col]}</td></tr>`;
tableContent += tableRow;

This gives me:

Formatted: 15:30 Original: Thu Aug 07 2014 15:30:00 GMT-0400 (EDT)

I just want to show 15:30. How can I fix this? The date part is messing things up. Any ideas?

2

I’ve run into this issue before with Google Sheets time data. The problem is that Sheets stores times as full datetime objects, even if you’re only displaying the time in your sheet.

Here’s a workaround that worked for me:

Instead of using Utilities.formatDate(), try using the built-in JavaScript Date object methods. You can extract just the hours and minutes like this:

let timeObj = new Date(sheetData[row][col]);
let formattedTime = timeObj.getHours().toString().padStart(2, '0') + ':' + 
                    timeObj.getMinutes().toString().padStart(2, '0');

This should give you just the time in HH:mm format without any date information. The padStart() method ensures you always get two digits for hours and minutes.

Remember to adjust for timezone if needed, as the Date object uses the script’s timezone by default.

3

I’ve encountered similar issues when working with time data from Google Sheets. One effective solution I’ve found is to use the getDisplayValues() method instead of getValues() when fetching data from your sheet. This method returns the values as they’re displayed in the sheet, which is often just the time if that’s how you’ve formatted the cells.

Here’s how you might modify your code:

let sheetDisplayData = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getDisplayValues();
let formattedTime = sheetDisplayData[row][col];

This approach bypasses the need for additional formatting and should give you the time exactly as it appears in your sheet. Just make sure your sheet cells are formatted to show only the time (Format > Number > Time in Google Sheets).

4

hey, have u tried using the .toLocaleTimeString() method? it’s pretty handy for this kinda stuff. something like:

let timeOnly = new Date(sheetData[row][col]).toLocaleTimeString(‘en-US’, { hour: ‘2-digit’, minute: ‘2-digit’ });

this should give u just the time part witout all that extra date nonsense. hope it helps!