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:
function toUtc(dateTime, timeZone) {
var from = m.moment.tz(dateTime, timeZone);
return from.tz("Etc/UTC").format('YYYY-MM-DD HH:mm:ss');
}
Save your project and you can now use this function in your sheets like this:
=toUtc(TEXT(A1, "YYYY-MM-DD HH:mm:ss"), B1)
Ok, I got it to work, So now how do we update this to be dynamic?
I would like this to display live time by using Now() or similar. I’ve tried various tricks like converting Now() to its text equivalent, or Concatenate(), Year()…Second(), Date() etc.
Dynamic [on the fly] per minute updating of Time(s):* A solution for “cannot use NOW(), RAND(), or RANDBETWEEN()”…
I was going insane trying to figure out what to do as dynamic [on the fly] updates was essential for my needs. I thought that using Text() would force it to a text(), but it still did not work… So I created crude workaround.
* The Quick version:1) Create a Cell with =Now() as per your Spreadsheet Location.2) Create a Function to copy the above value and then CopyPasteType.PASTE_VALUES it into another cell.3) Use the above Pasted Cell as your Text/Value version of =Now().4) Create an OnChange() Function to update ever Minute.Done!
* Breakdown Version:Locations in Images are numbered as per each bullet-point below.
Image for reference: https://prnt.sc/vm7lub
Steps based on toZone(), but should work for the other methods too:
Required-A) A Cell for each Calculated Location – (B2:B),B) The Cells with the ‘toZone’ formula (C2:C),C) An =Now() Cell to get the current date and time (D2),D) A Cell for the Text(Value) of =Now() above (D3),
1) From your spreadsheet, Click… Tools > Script Editor,2) From script Editor, Click… File > New > Script File (Name it whatever you want),3) Paste and Modify the following,
function OnChange() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('D3').activate(); // Select cell with Now() Value spreadsheet.getRange('D2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); // Paste above Value here as 'Plain Text' / 'Paste Value' forcing it to be text only };4) Save (but don’t close yet),5) Click on File > Open (This will take you to all your Scripts),6) Select ‘My Triggers’ and Look for ‘OnChange’ under the Function Column. Match the current date and time under ‘Last run’ column,7) Select ‘Edit Trigger’ [Far Right],8) Change your settings to the following or modify as per your requirements.
That’s it!
Thank you so much for your response David .
I have been using this library and since my sheet as huge amount of data ,I see that it is a little slow and occasionally not able to convert.
Is there a way we could use other libraries like Luxon js or Day.js in a similar way in the appscript? Day.js seems to be lighter weight and I am assuming it could run much faster .
Look forward to your response. Thank you .
Regards
Nagendra
Hey David.
Thank you.
I followed all your steps and the script is working.. sort of.
It returns the INPUT TIME both on the toUtc and toZone function
Like if there WAS NO TIME DIFFERENCE.. Hmm
What could be wrong?
BR
Simon
Hello I am getting an error when trying to run the code. Cannot read property ‘tz’ of undefined (line 2, file ‘code’)I have connected the functions project to moment.js library