When creating a new table, if you put the time value "9:00" in the cell, it will become time and will look like this

Sun Dec 31 1899 09:00:00 GMT+1000 (AEST)

But there is a Sheet, where in the cell is 9:00, and is stored as

 Sun Dec 31 1899 03:00:00 GMT+1000 (AEST)

The problem surfaced when trying to copy a value from gs from one cell to another

 .getValue().getHours()

comes 3 instead of 9.

  • The question is how to get exactly what the user sees in the cell through gs? * How to understand programmatically what is displayed there?
  • The secondary question is how did it happen and how to get rid of it, for example, bring everything to one belt?

I am in the GMT + 10 belt.

  • one
    As it turned out, you can get what the user sees with getDisplayValue (). On the remaining issues is still not clear. - Slava32768

1 answer 1

To get the displayed data, you must call the Range class's getDisplayValue () method.

Copy date

 function moveTime() { var spreadsheetFrom = SpreadsheetApp.openById("..."); var rangeFrom = spreadsheetFrom.getSheets()[0].getRange("A1"); var valueFrom = rangeFrom.getValue(); var spreadsheetTo = SpreadsheetApp.openById("..."); var rangeTo = spreadsheetTo.getSheets()[0].getRange("A1"); rangeTo.setValue(valueFrom); Logger.log(spreadsheetFrom.getSpreadsheetTimeZone()); Logger.log(spreadsheetTo.getSpreadsheetTimeZone()); }

Conclusion

 [18-03-01 17:37:16:795 MSK] Atlantic/Bermuda [18-03-01 17:37:16:796 MSK] Europe/Minsk

In the tables

  • First date 03/01/2018 18:10:02
  • in the second 3/2/2018 1:10:02

Time change Attention!!! Data will be changed

 function stornoTime() { var spreadsheetFrom = SpreadsheetApp.openById("..."); var rangeFrom = spreadsheetFrom.getSheets()[0].getRange("A1"); var valueFrom = rangeFrom.getValue(); var spreadsheetTo = SpreadsheetApp.openById("..."); var rangeTo = spreadsheetTo.getSheets()[0].getRange("A1"); rangeTo.setValue(valueFrom.addHours(-7)); Logger.log(spreadsheetFrom.getSpreadsheetTimeZone()); Logger.log(spreadsheetTo.getSpreadsheetTimeZone()); } Date.prototype.addHours = function(h) { this.setTime(this.getTime() + h * 60 * 60 * 1000); return this; }

In the tables

  • First date 03/01/2018 18:10:02
  • in the second 3/2/2018 18:10:02

How to "normally" determine the amount of reversal is not clear.

The script returns the date in the time in which it is configured. Script time settings are in Menu => File => Project properties => Time zone

Example

 function getLocalTime() { var spreadsheet = SpreadsheetApp.openById("..."); var range = spreadsheet.getSheets()[0].getRange("A1"); var value = range.getValue(); var displayValue = range.getDisplayValue(); Logger.log(Session.getScriptTimeZone()); Logger.log(spreadsheet.getSpreadsheetTimeZone()); Logger.log(value); Logger.log(displayValue); }

The data in the cell " 03/01/2018 18:10:02 "

  • For the script " Africa / Sao_Tome " GMT + 1
  • For Table " Europe / Luxembourg " GMT + 1

will get

 [18-03-01 17:13:17:998 MSK] Africa/Sao_Tome [18-03-01 17:13:17:998 MSK] Europe/Luxembourg [18-03-01 17:13:17:999 MSK] **Thu Mar 01 18:10:01 GMT+01:00 2018** [18-03-01 17:13:17:999 MSK] 01.03.2018 18:10:02
  • For the script " Asia / Kamchatka " GMT + 12
  • For Table " Europe / Moscow " GMT + 3

will get

 [18-03-01 17:18:17:243 MSK] Asia/Kamchatka [18-03-01 17:18:17:244 MSK] Europe/Moscow [18-03-01 17:18:17:245 MSK] Fri Mar 02 03:10:01 GMT+12:00 2018 [18-03-01 17:18:17:245 MSK] 01.03.2018 18:10:02

Conclusion

You do not need to do anything, time is indicated correctly. The reason for displaying different times is due to the fact that users look at different Tables with different time zones.