Time zone conversion in Google Sheets

Google Sheets does not have a built in way of converting time zone data but by using the power of Moment.js and Google’s script editor we can add time zone functionality to any sheet.

First, we need to add the Moment.js code as a library that can be shared between different documents. This is the javascript library that adds date and time zone manipulation support. A good way to do this is to create it from Google Drive so it can be easily edited and shared with all your sheets.

In Google Drive, go to New > More > Connect more apps and choose Google Apps Script. Now, create a new Google Apps Script document. This will open the Google script editor. Call the project ‘Moment’ and create two files in the project called moment.js and moment-timezone.js using the moment and moment-timezone libraries. Make sure you choose one of the moment-timezone files with time zone data. You should end up with a project something like this:

To easily use this in multiple sheets we can publish it as a library. Go to File > Manage versions and save a new version. We are nearly finished here but before we move on go to File > Project properties and make a note of the project key, you will need this to refer to your library in your sheets.

In our test we are going to use our new library to convert times in a local time zone to UTC.

Create a new Google sheet and enter a date in A1 and a time zone in B1, like this:

 

Go to Tools > Script editor and create a project called Functions. In the script editor, go to Resources > Libraries and using the project key you made a note of before add your Moment library and select version 1. I prefer to use a shorter identifier like ‘m’. Click save and your library is now accessible to your sheet’s script. We can create a function to convert to UTC like this:

Save your project and you can now use this function in your sheets like this:

 

9 Comments +

  1. Thank you for this information! It was a life saver. I was using moment.js in my google sheets project but I was working with a client based in India. The spreadsheet settings were set for the right timezone but moment would still bring up the wrong timezone. So I searched and found your post here and it worked out great! Thanks again.

  2. Hi David

    Thank you sooo much!! You have no idea of the countless hours I’ve been looking for this!!

    Cheers.

  3. Hello David,

    First of all, thank you very much for this script, it’s going to be very useful for me. However, I have an error with my sheet, it tells me “TypeError: Cannot read property “version” from undefined. (line 27).” which corresponds to this part of the script: “var VERSION = “0.5.13”,”
    Is there something I’m missing?

    Thank you so much for your help,

    Elena

    1. It sounds like the problem is this bit:

      Do you have both moment.js and moment-timezone.js (moment-timezone-with-data-2012-2022.js) in your script project (check out the screenshot in the post)?

       

      1. Hi David  – I’m having the same issue as the above commenter. Just confirmed that I’m using the right scripts.

        1. I think I can reproduce this. It looks like the order of scripts in the script editor is important.

          Make sure you have the moment.js script above the moment-timezone.js script:

          If yours is the other way round just remove and re-add moment-timezone.js and it will place it underneath. Publish a new version of your library and update the version used in your sheet.

  4. Doesn’t seem to be the root of the issue for me. Moment.js is at the top: https://screencast.com/t/1L4p1Kgoi

    This thread points out that you need to have data accompanying the scripts…but I’m using moment-timezone (with data) version.

    Thread: https://github.com/moment/moment-timezone/issues/465

Leave a Reply

Your email address will not be published. Required fields are marked *