Just another developer blog

David Keen

David Keen

Just another developer blog

Recent Posts

Loading test data with Play Framework Evolutions

Loading test data with Play Framework Evolutions

In a previous article I described how to load test data that your ScalaTest Play Framework functional tests might need using Play Framework’s Evolutions. This made use of the SimpleEvolutionsReader class and defining evolutions in the test setup code. Recently I wanted to also load […]

Java enums can implement interfaces

Java enums can implement interfaces

Making an enum implement an interface can be a useful way to split large enums into smaller classes.

Jumbled Headers

Jumbled Headers

Have you ever noticed misspelled HTTP response headers?

That ‘Cteonnt-Length’ sure looks weird!

According to this StackOverflow answer, the jumbled header contains the uncompressed size of the response and, sure enough, it does seem to be the case. But why?

It seems like this is a trick employed by hardware appliances (eg Citrix NetScaler) to ‘remove’ a header without affecting the check-sum value.

Back up your server to Backblaze B2 with Duplicity

Back up your server to Backblaze B2 with Duplicity

Amazon S3 has been around for more than ten years now and I have been happily using it for offsite backups of my servers for a long time. Backblaze’s cloud backup service has been around for about the same length of time and I have […]

Oops! I committed to the wrong branch

Oops! I committed to the wrong branch

It is common when working with git to use lots of branches. Occasionally you might accidentally commit to the wrong branch but thankfully git makes it easy to put these commits in the right place. It’s worth noting that the fixes described here are only […]

Time zone conversion in Google Sheets

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:

 


My Diary

Post ID:

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:
[crayon-5b4e71c0857b9111196967/]
Save your project and you can now use this function in your sheets like this:
[crayon-5b4e71c0857c0055981188/]