2

It's hours I'm struggling to know why I don't get correct timeZone using this script ! I've used moment.js and moment-timezone and followed instruction explained here: Timezone conversion in a Google spreadsheet google-spreadsheet/40324587

I am trying to convert timezones usign following script:

 var DT_FORMAT = 'YYYY-MM-DD HH:mm:ss';

    function fromUtc(dateTime, timeZone) {
      var from = m.moment.utc(dateTime, 
    DT_FORMAT);//https://momentjs.com/timezone/docs/#/using- 
   timezones/parsing-in-zone/
      return from.tz(timeZone).format(DT_FORMAT);
    }
    function toUtc(dateTime, timeZone) {  
      var from = m.moment.tz(dateTime, DT_FORMAT, 
    timeZone);//https://momentjs.com/timezone/docs/#/using-timezones/parsing- 
    in-zone/
       return from.utc().format(DT_FORMAT);
     }

     function myFunction(datetimeString,timeZone,format) {
       var moment = new Date(datetimeString);
       return Utilities.formatDate(moment, timeZone, format)
     }

In my spreadsheet, the first column is date and time in standard GMT and I'm going to change date and time to different TimeZones which I wrote in next columns. Even-though I'm getting the date and time, but it is incorrect and I don't know how to fix it. Here is the link to Google sheet: https://docs.google.com/spreadsheets/d/1Tc0G2daX9FYIL354iyOffd06BtNPx5v-40KE5RtwDB4/edit?usp=sharing and here is the link to my script app: https://script.google.com/d/1u54McW1HBm-A2alno1DWWTFwk-vdok8ljwKfI_5htAHK-XrMt554YGLn/edit?usp=sharing

Update (replying to comment) : I use the function like this, I write in in a cell in spreadsheet: =fromUtc(A2, D1)

Majid
  • 421
  • 6
  • 19
  • 1
    Hi. The code you showed here defines three functions The first two depend on moment and moment-timezone. The third uses the `Utilities.formatDate` function built in to Google Apps Script. Other than that, I cannot tell from your question what the problem is. Please edit to show *in the question itself* how you use these functions. Please read [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). Thanks. – Matt Johnson-Pint Jun 26 '19 at 17:23
  • thanks for your reply. Please look at the share spreadsheet. I've used the functions there to convert timezone. In face, in a cell that I want to show the time, I call the functions like this: =fromUtc(A2, D1), where. A2 is the time in string format and D1 is the TimeZone that I want the time. – Majid Jun 26 '19 at 18:36
  • 1
    You can link to supporting details, but as documented in the help link I gave previously, all information needed to reproduce the problem needs to be included **in the question itself**. From your edit, I can now see you call the `fromUtc` function, but it is not clear what values are passed. (Please provide examples of `A2` and `D1`.) Also, it is not clear why you have the `toUtc` and `myFunction` definitions in your question. If they are not related to your question, then please remove them. Thanks. – Matt Johnson-Pint Jun 26 '19 at 18:41
  • 1
    Keep in mind that the primary purpose of Stack Overflow is not just to help you with this one question at this time, but to provide high quality questions and answers that can help a large number of people over a long period of time. Linking to off-site resources always comes with the possibility that these resources could be removed in the future. That is the reason for the policy of including all vital details in the question itself. – Matt Johnson-Pint Jun 26 '19 at 18:44
  • 2
    Utilities.formatDate is the best way to do what you want, simply change the timezone argument and the resulting string will be returned as you want. – Serge insas Jun 26 '19 at 18:50
  • 1
    If your going to use Utilities.formatDate() the try this format `yyyy-MM-dd HH:mm:ss` – Cooper Jun 26 '19 at 19:08
  • @Sergeinsas Thanks for yoiur suggestion. I switched to what you've suggested. function myFunctions(datetimeString,timeZone,format) { var moment = new Date(datetimeString); return Utilities.formatDate(moment, timeZone,"dd-MM-yyyy HH:mm:ss") } This also give me wrong time. For instant, 6/30/2019 6:32:00 should return June 30, 2019 11:02 AM in (GMT+4:30), However, it returns wrong time (June 30, 2019 02:02). Any idea how where the problem is ? Thanks. – Majid Jun 30 '19 at 09:38
  • @Cooper Thanks for your reply, I used it, however, it gives me wrong time ! For instant, 6/30/2019 6:32:00 should return June 30, 2019 11:02 AM in (GMT+4:30), However, it returns wrong time (June 30, 2019 02:02) – Majid Jun 30 '19 at 09:39
  • GMT +4:30 seems to be America/Caracas... try using "America/Caracas" as argument, this is referenced in [Joda-Time web site](http://joda-time.sourceforge.net/timezones.html), the reference that Google script uses. I'm not sure how to handle non integer GMT+X values ;) – Serge insas Jun 30 '19 at 14:07
  • btw, using GMT+X is always source of many issues when regions use daylight savings... Using name reference does take that into account – Serge insas Jun 30 '19 at 14:25

0 Answers0