Google Spreadsheet で日付時刻を別のタイムゾーンに変換する関数を作る

9268 ワード
timezone GoogleAppsScript GoogleSpreadSheet GoogleAppsScript テキストリンク

ローカルタイムを別のタイムゾーンの日時に変換するのは案外面倒

Google Spreasheet に記入した情報の日時を、別のタイムゾーンの日時に変換したくなったのですが、調べたかぎりでは、そういう変換が行えるワークシート関数が見当たりませんでした。

Google Spreadsheet 自体はタイムゾーンの設定があり、この設定をスプレッドシートの操作履歴で使用したり、またその他の時刻関連操作でも設定されたタイムゾーンの時刻が用いられるらしいです（ショートカットキーでの現在時刻入力など）

でも時差変換ができるワークシート関数は見当たらないので、そのような操作が必要な場合は時差の計算式を自分で書く必要があります。以下の例は日本時間の日時をホノルルの時間に変換する例です。

ホノルルくらいなら夏時間、冬時間の切替がないからこういう計算式でやっても良いかもしれませんが、ハードコーディングされた数値で時差計算するのはあまり楽しくない作業なので、タイムゾーン指定で時差計算したいです。

Google Apps Script の formatDate() なら指定したタイムゾーンの時刻に変換できる

Google Apps Script の Utilitiesクラスには、formatDate() があり、これは Java SE の SimpleDateFormat クラスと同様に使えるそうです。

詳細はこちらのリンクをご確認ください。
https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format

どのように動作するかを確かめてみる

まずはプロトタイピングということで、次のような実装を Google Spreadsheet のスクリプトエディタに書いてみます。

function testrun() {
  date = new Date;
  formatDate(date, 'JST');
  formatDate(date, 'CET');
  formatDate(date, 'HST');

  formatDate(date, 'PST');
  formatDate(date, 'PDT');

  formatDate(date, 'UTC');

  formatDate(date, 'GMT-5');
  formatDate(date, 'UTC-5');
}

function formatDate(date,timeZone,format) {
  if (date === '' ) {
    return ''
  } else {
    var format = 'yyyy/MM/dd HH:mm';
    var retval = Utilities.formatDate(date, timeZone, format);
    Logger.log(date + '->' + retval + '(' + timeZone +')');
    return retval;
  }
}

これを実行すると、次の内容がログに出力されます

# このSpreadsheetのタイムゾーンはJSTなので、JSTに変換しても結果は同じ
[19-11-16 17:47:49:985 JST] Sat Nov 16 2019 17:47:49 GMT+0900 (JST)->11/16 17:47(JST)

# ヨーロッパ時間、ハワイ時間、アメリカ西海岸時間は正しく変換できている
[19-11-16 17:47:49:986 JST] Sat Nov 16 2019 17:47:49 GMT+0900 (JST)->11/16 09:47(CET)
[19-11-16 17:47:49:987 JST] Sat Nov 16 2019 17:47:49 GMT+0900 (JST)->11/15 22:47(HST)
[19-11-16 17:47:49:988 JST] Sat Nov 16 2019 17:47:49 GMT+0900 (JST)->11/16 00:47(PST)

# サマータイムのタイムゾーン名はおそらく未定義のタイムゾーンとして扱われるようで、その場合はUTCに変換される
[19-11-16 17:47:49:989 JST] Sat Nov 16 2019 17:47:49 GMT+0900 (JST)->11/16 08:47(PDT)
[19-11-16 17:47:49:990 JST] Sat Nov 16 2019 17:47:49 GMT+0900 (JST)->11/16 08:47(UTC)

# GMT-5 のような時差指定は可能
[19-11-16 17:47:49:991 JST] Sat Nov 16 2019 17:47:49 GMT+0900 (JST)->11/16 03:47(GMT-5)

# UTC-5 のような指定はできず、UTCとして扱われる
[19-11-16 17:47:49:992 JST] Sat Nov 16 2019 17:47:49 GMT+0900 (JST)->11/16 08:47(UTC-5)

とりあえずちょっとわかった。

  • サマータイムが運用される地域向けの変換は冬時間を指定しておけばよい
  • タイムゾーン名がわからなくても GMT-5 のような方法で計算できる。

Google Spreadsheet からワークシート関数として使えるようにする

プロトタイプ実装を次のように変更します。プロトタイプ実装は時差計算した結果を文字列として返していましたが、Google Spreadsheet で扱うなら Date オブジェクトで返したほうが利便性が高いと思われるからです。

function formatDate(date,timeZone) {
  if (date === '' ) {
    return ''
  } else {
    var format = 'yyyy/MM/dd HH:mm:ss';
    var convertedDateString = Utilities.formatDate(date, timeZone, format);
    var convertedDate = new Date (convertedDateString);
//    Logger.log(date + '->' + convertedDate + '(' + timeZone +')');

    return convertedDate;
  }
}

さて、この実装は Google Spreadsheet のスクリプトとして作成しましたが、これはそのままワークシート関数として利用できます。

A1セルに日時が入っているときに A2 セルに =formatdate(A1,"PST") と記述すれば、次のように指定されたタイムゾーンの日時が取得できます。値はDateオブジェクトなのでセルの書式設定で表示形式を自由に調整できます。

そして、夏時間と冬時間の対応も自動的に行われていることもわかります。2019/11/03まではサマータイムですが、11/04以降はサマータイムではないので出力結果が１時間違っていることが確認できます。