1

I need to record my expenses from around the World on the same Spreadsheet (be able to write a date at local time in East Africa, a date at local time in Dubai, in Thailand...)

So, amongst all other data, I need to record:

  1. The date and time the transaction has taken place (I copy the shop receipt with date, time and amount) and time zone it has taken place at
  2. The date and time in the Time Zone of Rome which is my home time zone and so the bank account time zone
  3. The date and time the transaction is being recorded (timestamp at local time zone, i.e. the time zone of the place I find myself at while travelling)

The location where I perform actions at 1 and 3 may be different, for example, I may be located in Thailand and want to record an expense I made in the US (and translate into the time in Rome, which is the one always present).

  1. I set the column A as a date using "Data validation..." There, I manually input the date of transaction picking from the popup calendar and I manually add the time (as per receipt). To the next column I manually set the time zone where I was when I made the transaction. I want to record in the Date of transaction column the date as it showed locally when I made it. So I would like to get a date made of that column and the next column which contains the time zone. But if I make a transcationDateCell.getValue() I get "November 22, 2016 18:59:51 +0100" which already includes a time zone (either the one where I am when the script runs or the one from the Spreadsheet Settings?) So I cannot use the manually set time zone.

  2. I used:

    var tsTransDateRome = Utilities.formatDate(new Date(tsTransDateValue), "Europe/Rome", dateFormatString);

    where tsTransDateValue contains the date set manually from the Spreadsheet UI as to point 1.

  3. I should be okay with this. It's automatically filled in via GAS. I used:

    var timeZoneScript = Session.getScriptTimeZone(); // Check if when I travel to a different TimeZone it will adjust or if the ScriptTimezone keeps being the one when the SpreadSheet was created (File->Spreadsheet Settings)
var tsTimeZoneRecCell = transSheet.getRange(tsActiveRowIdx, tsTimeZoneRecColIdx);

    And I speculate that if I move to a different timezone my Session.getScriptTimeZone() will adjust to the local timezone (i.e. it's not the time zone of the Spreadsheet set via Spreadsheet Settings...)?

Now I'm in Italy so all locations are the same. When I calculate 2. from 1. it shows the right time only if the expense was made in the same Time Zone (Central Europe Time) because when I pick the date and add the time it gets written in the current time zone.

If I want to write the expense including the time zone manually set in the next column how can I do it?

Have I been able to set out the problem correctly?

Sara
  • 11
  • 1
  • 2
    Your script time zone will not adjust as you move into a different time zone. You might be able to get the time zone of your current location from the browser. See the following link: [get client time zone from browser](http://stackoverflow.com/a/8984850/2946873) I would use a sidebar for input, and have the sidebar run the code needed to adjust the dates, and then enter the value. You might need to set the format on the date columns to something other than date, and save the date values as text. You already know how to use `Utilities.formatDate()` Also: [Moment.js](http://momentjs.com/) – Alan Wells Nov 24 '16 at 13:16
  • @SandyGood: Thanks. I tried to use the library Moment.js thanks to the project-key MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48 fount in the post [link](http://stackoverflow.com/questions/16149760/calculating-year-month-days-between-dates-in-google-apps-script/16928369#16928369) but I cannot add the add-on moment-timezone.js. Any clues? – Sara Nov 27 '16 at 17:15
  • I don't know. Sorry. This might be a good question for the Apps Script community: [Link to Apps Script Community](https://plus.google.com/communities/102471985047225101769) – Alan Wells Nov 27 '16 at 19:08
  • 1
    @SandyGood. Thanks. I did ask in the Apps Script Community you pointed me to and I was pointed me to the library files but I actually only used the project key to add the Library via the add Library of the Script Editor. So, the key: [1RsFFZZvosueH3avP3mQu6CU1TCDKITyrhXlRzfvWIlg698cYLPPJTJer](https://script.google.com/d/1RsFFZZvosueH3avP3mQu6CU1TCDKITyrhXlRzfvWIlg698cYLPPJTJer/edit?usp=drive_web) is version 2.11.2 of Moment.js (at least 2.6 is required for the moment-timezone add-on) and the add-on is included. Thanks! – Sara Nov 30 '16 at 00:30

0 Answers0