Extracting time from Google Sheets to HTML table without date info

Other Questions Google Sheets
1

Hey everyone,

I’m working on a project where I’m pulling data from Google Sheets into an HTML table. I’ve run into a problem with one of the cells that contains time info. For some reason, I’m getting the full date and time instead of just the time.

Here’s a snippet of my code:

console.log('Time: ' + rawData[0][col] + ': ' + rawData[row][col]);
let formattedTime = Utilities.formatDate(rawData[row][col], 'GMT', 'HH:mm');
console.log('Formatted time: ' + formattedTime);
let htmlRow = '<tr><td>' + rawData[0][col] + '</td><td>Formatted: ' + formattedTime + ' Original: ' + rawData[row][col] + '</td></tr>';
tableHtml += htmlRow;

This code gives me:

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

I just want to show 15:30 without all the extra date stuff. Any ideas on how to fix this? Thanks!

2

hey bob, i’ve run into this before. quick tip: try using the getHours() and getMinutes() methods on ur date object. something like:

let d = new Date(rawData[row][col]);
let time = d.getHours() + ‘:’ + (d.getMinutes()<10?‘0’:‘’) + d.getMinutes();

this shud give u just the time without all that extra stuff. hope it helps!

3

I’ve dealt with this exact issue before in a project. The problem stems from how Google Sheets handles date-time data internally. A quick fix is to use the JavaScript Date object’s methods directly on your data.

Try modifying your code like this:

let dateObj = new Date(rawData[row][col]);
let formattedTime = dateObj.toLocaleTimeString('en-US', { hour: '2-digit', minute: '2-digit', hour12: false });
console.log('Formatted time: ' + formattedTime);
let htmlRow = '<tr><td>' + rawData[0][col] + '</td><td>' + formattedTime + '</td></tr>';
tableHtml += htmlRow;

This approach uses toLocaleTimeString() with specific options to format the time exactly as you need it, without any date information. It’s cleaner and more reliable than manually extracting hours and minutes.

4

I’ve encountered a similar issue when working with Google Sheets and time formatting. The key is to understand that Google Sheets stores dates and times as serial numbers, which can cause confusion when extracting just the time.

Here’s a solution that worked for me:

Instead of using Utilities.formatDate(), try using the built-in getHours() and getMinutes() methods on the Date object. You can create a Date object from your rawData[row][col] value, then extract just the time components.

Here’s how you could modify your code:

let dateObj = new Date(rawData[row][col]);
let hours = dateObj.getHours().toString().padStart(2, '0');
let minutes = dateObj.getMinutes().toString().padStart(2, '0');
let formattedTime = hours + ':' + minutes;

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

Hope this helps solve your problem!