Copy from google sheet to another sheet by app script and "Date" columns value changes!

Google Workspace Developers Q&A
1

I copy a sheet from another spreedsheet and “Date” values are changed!

“01/16/2025” to “01/15/2025 21:00:00”

The source is formatted as “Date” and I also use .SetNumberFormats() to apply the formats from source.

Do you know how to fix this or it’s a bug ?

2

I don’t think this is a bug, but a Time Zone Conflict.

Google Sheets stores dates as timestamps (midnight). If your target spreadsheet is set to a time zone that is 3 hours behind your source sheet, ‘Midnight Jan 16’ becomes ‘9:00 PM Jan 15’ the moment it is pasted.

How to fix it:

  1. Check File Settings: In BOTH spreadsheets, go to File > Settings and make sure the Time zone is identical.

  2. Check Script Settings: In the Apps Script editor, click the Gear icon (Project Settings) and ensure the Time zone there matches the one in your sheets.

  3. Change your Code: The easiest ‘coding’ fix is to use .getDisplayValues() instead of .getValues(). This treats the date as text, so it copies ‘01/16/2025’ literally without doing any timezone math in the background.

If you synchronise the time zones across both files and the script settings, your .setValues() should hopefully stop shifting the time.

1 Like