Hey everyone! I’m new to Google Sheets and I’m stuck on a problem. I’ve got a spreadsheet where columns L and M have UNIX epoch timestamps. I want to change these into normal dates and times that are easy to read. I tried writing some code to do this, but it’s not working. Here’s what I’ve got so far:
function convertTimestamp() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange('L:M');
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
if (values[i][0]) {
var date = new Date(values[i][0] * 1000);
values[i][0] = Utilities.formatDate(date, Session.getScriptTimeZone(), 'MM/dd/yyyy HH:mm:ss');
}
if (values[i][1]) {
var date = new Date(values[i][1] * 1000);
values[i][1] = Utilities.formatDate(date, Session.getScriptTimeZone(), 'MM/dd/yyyy HH:mm:ss');
}
}
range.setValues(values);
}
Can anyone help me figure out what I’m doing wrong or suggest a better way to do this? Thanks!
hey there! i’ve dealt with this before. instead of scripts, try this formula: =ARRAYFORMULA(TEXT(DATETIME(1970,1,1) + (L2:M/86400), “mm/dd/yyyy hh:mm:ss”))
put it in N2 and it’ll convert both L and M columns at once. way easier than coding! let me know if u need any help
I encountered a similar problem a while back and found that there are multiple approaches depending on your needs. In my case, I preferred using a direct formula in the sheet over writing custom scripts. For instance, inserting =((A1)/86400)+DATE(1970,1,1) into a cell converts the UNIX timestamp to a date when formatted properly. If you stick with Apps Script, make sure the values are numeric and check that the spreadsheet’s time zone matches your expectations.
Sometimes, small discrepancies in data types or formatting can cause issues, so double-checking those details might help solve the problem.
For converting UNIX timestamps in Google Sheets, you might find it easier to use built-in functions rather than custom scripts. Try this formula in the cell next to your timestamp: =DATETIME(1970,1,1) + (L1/86400). Replace L1 with the cell containing your UNIX timestamp. This converts the timestamp to a readable date and time.
To apply this across multiple rows, you can drag the formula down or use array formulas. If you need specific formatting, wrap the formula in a TEXT function, like this: =TEXT(DATETIME(1970,1,1) + (L1/86400), “yyyy-mm-dd hh:mm:ss”).
This method is more straightforward and less prone to errors than custom scripts, especially for beginners. It also updates automatically when your timestamp values change.
