Zapier を用いて Gmail の受信記録を Spreadsheet に転記する

この記事を読むとできるようになること

• Gmail でのメール受信をトリガーとした Zapier Zap の作成
• メールヘッダの Date 表記を Spreadsheet 上で日時形式に変換

所要時間： 30 分 ～ 1 時間 （読みながら手を動かした場合）

はじめに

Gmail で受信したメールを集計、グラフ化したい場合があります。

例えば、1 ヶ月ごとのアラートメールの数を集計し、月次報告をしたい。
こんなとき、以下のようなアプローチがあるかと思います。

1. 監視システム側のレポートツールを利用 （用意されていれば）
2. Gmail の画面上で人力で集計作業
3. Gmail API を使って集計プログラムを組む

あるいは、今回のように Zapier を使い無料で集計作業ができると楽かもしれません。

Zapier（ザピエル）とは

複数サービスを連携することで、処理やデータを繋ぐことができるサービスです。
「何をトリガーに」「何をする」を Zap と呼ばれるレシピに記載します。
あとは「トリガー」が発生するたびに Zapier が一通りの処理を行ってくれます。

同様のサービスに IFTTT が有名で、この記事の内容は恐らく IFTTT を使っても実装可能だと思います。

しかしながら現状、IFTTT では Gmail をトリガーにすることができなくなっているそうです。
なので Gmail をトリガーとして自動処理をさせたいという場合に IFTTT を使うのは難しそうです。

参考リンク：
IFTTT、3月31日からGmailのトリガーと下書き作成が利用不可に

Zapier の登録方法

1. 公式サイト に行って
2. Sign Up!

この記事では無料プラン（Free プラン）を利用します。 この場合、特に追加設定は要りません。
チュートリアルを終え Home 画面が表示されるところまで進めておきます。

Zapier 無料プラン（Free プラン）の制約

Zapier には無料で利用可能な Free プラン以外に、月額・年額費の掛かる有料プランがあります。
Free プランならではの制約は例えば次の通り。

• 処理の実施間隔は最長 15 分
  • 対して、最長 5 分となる有料プランがあります
• 3 つ以上の処理（≒サービス）を連結できない
• 利用できない処理（≒サービス）がある
  • Webhook など有料プランのみで利用可能な処理があります

詳しくは 公式サイトの記載 を参照してください。

今回のゴール

例として、今回は次のゴールを目指して解説していきます。

送信元が [e-mail] で件名に [alert] の文字が含まれるメールが週に何件届いたか集計し、過去 12 週分をグラフ化する

監視システムが From:[e-mail] のメールを送信してくるので、週にどれくらいアラート発報があるのか知りたい。 そのためにグラフを自動で作りたい、というわけですね。

これを叶えるため Zapier と Spreadsheet を作成します。

処理の流れ

大まかな処理の流れは次の通り。

1. Gmail がメールを受信
2. Zapier が定期的（最長 15 分）にメールボックスを監視
3. フィルタに引っかかるメールが届いていれば次へ
4. Zapier が Spreadsheet に日時と件名を書き込み
5. Spreadsheet 上でデータ整形、グラフに反映

条件を満たすメールが届くたびに Spreadsheet の表に 1 行分データが追記されるわけです。
あとは Spreadsheet 側でデータを元にグラフが作成されます。

設定

設定の流れは次の通りです。

1. 転記先の Spreadsheet の準備
2. Zapier での設定
3. テスト
4. Zap をオン

では始めましょう！

1. 転記先の Spreadsheet の準備

このステップのポイントは次のとおりです。

• シートを作成
  • Zapier がデータを追記するためのシート
  • データを集計するためのシート
  • グラフを表示するためのシート
• シートに行を作成
  • 日時の行、件名の行
  • 日時を表記変換するための行

まずはシート。 ポイントに記載したとおり、最低 3 つのシートが必要です。

1-a. Zapier がデータを追記するためのシートを作成

ここでは 生データ というシート名にしました。
また、シートには 3 つの列を作成します。

列番号	列の名前（1 行目に入力）
A 列	Date
B 列	Date（変換後）
C 列	Subject

1 行目に列の名前を記載したら、2 行目以降は入力不要です。
列の名前は厳密にこのとおりでなくとも構いませんが、列を入れ替えると後述の関数式が正常に動作しないため、この並びで作成してください。

見やすいよう、B 列は表示形式を日時形式に設定しておく、また、1 行目を固定しておくと良いかもしれません。
さらに、このシートは Zapier が処理し、手動で更新や書式変更をしてしまうと全体が動作しなくなるため、シート保護を設定しておくことをおすすめします。

1-b. データを集計するためのシートを作成

ここでは 集計 というシート名にしました。
シートを作成したら、3 つの列を作成します。

列番号	列の名前（1 行目に入力）	列の内容（2~14 行目に入力）
A 列	n 週前	A2~A14 まで順に 0~12 の数字を記入
B 列	週（開始日）	B2~B14 まで関数式を記入（後述）
C 列	期間内総数	C2~C14 まで関数式を記入（後述）

今回のゴールのためには「1 週間のメール数」のデータが「過去 12 週間」必要です。
A 列 A2~A14 のセルに、0 週前（つまり今週です）から 12 週前までの数値を表すため、0~12 の数字を順に入力します。

B 列 B2~B14 のセルに、すべて同じ次の関数式を入力します。

=(TODAY())-WEEKDAY(TODAY(),2)+1-INDIRECT(ADDRESS(ROW(),COLUMN()-1))*7

これにより B 列には、n 週間前（A 列の数字）の週初の日付が表示されます。

今日の日付 =TODAY() から、月曜日を 1 として今日が週の第何日目か =WEEKDAY(TODAY(),2)+1 を引き、さらに n 週間前までの日数、つまり A 列の数字 =INDIRECT(ADDRESS(ROW(),COLUMN()-1)) の 7 倍を引いています。
月曜日でなく日曜日を週頭とする場合は =WEEKDAY(TODAY(),1)+1 と変更します。詳しくは WEEKDAY() について調べてみてください。

次に C 列 C2~C14 のセルに、すべて同じ次の関数式を入力します。

=COUNTIFS('生データ'!$B:$B,">="&INDIRECT(ADDRESS(ROW(),COLUMN()-1)),'生データ'!$B:$B,IF(ROW()>2,"<"&INDIRECT(ADDRESS(ROW()-1,COLUMN()-1)),">0"))

これにより C 列には、n 週前の週間メール数が表示されます。
作成時は 0 となっているはずです。
Zapier がデータを追記するためのシートの名前を別のものにした場合、生データ の箇所をそれぞれその名前に置き換えてください。

同じ行の B 列の日付より未来 '生データ'!$B:$B,">="&INDIRECT(ADDRESS(ROW(),COLUMN()-1)) かつ 1 行上の B 列の日付より過去 '生データ'!$B:$B,IF(ROW()>2,"<"&INDIRECT(ADDRESS(ROW(),COLUMN()-1)),">0") の条件を満たす行数を生データシートの B 列から検索しカウント =COUNTIFS() しています。
IF(ROW()>2,"<"&INDIRECT(ADDRESS(ROW(),COLUMN()-1)),">0") としているのは、2 行目すなわち 0 週前の場合は「～より過去」を比較する日付が存在しないためです。

1-c. グラフを表示するためのシートを作成

ここでは グラフ というシート名にしました。
このシートの内容は単純で、データを集計するためのシートの B1~C14 を選択し、棒グラフを作成するだけです。

データ集計用とグラフ表示用は同じシートにまとめても構いません。

以上で Spreadsheet 側の設定は完了です！

2. Zapier での設定

このステップのポイントは次のとおりです。

• Gmail App をトリガーとして設定
  • フィルタ条件に合致するメールを受信することでトリガーが引かれるよう設定
• Google Sheet App をアクションとして設定
  • 先ほど作成した Spreadsheet を対象に、メールヘッダのデータが Send されるよう設定

2-a. Gmail App をトリガーとして設定

Zapier にログインしたら、画面右上の Make a Zap! というボタンを押します。

Zap とは Zapier の動作レシピ、あるいはタスクのようなものです。

まずは画面の左上に目を移し、作成する Zap に名前をつけましょう。
今回の場合は Weekly Alert Report などでしょうか。

2-a-1. 何をトリガーに処理を始めるか

それでは Zap を作成していきましょう。

Zapier にはサービスごとのテンプレートのようなものが幾つも用意されており、一般的なサービスであれば非常に直感的に設定することができます。
今回は Gmail App を選択します。
一覧にない場合は、虫めがねの入力欄に Gmail と入れて検索してください。

2-a-2. どんなイベントをトリガーにするか

Choos App が Gmail になっていることを確認し、Choose Trigger Event の部分をクリックし New Email Matching Search を選択、CONTINUE ボタンを押します。

2-a-3. どの Gmail メールボックスを監視するか

はじめて Zapier を使う場合、Zapier に設定されている Gmail アカウントはありませんので、次のような画面になっているはずです。

その場合、Sign in to Gmail をクリックします。
そうでない場合は Gmail account の部分をクリックし + Add a New Account をクリックします。

Google アカウントの認証を行います。 このあたりは直感的に操作すれば OK だと思いますので割愛します。

元の画面に戻ったら CONTINUE ボタンを押します。

2-a-4. メールのフィルタ条件

どんなメールが届いたら処理を始めるかを設定します。
Search String 欄に記入するための書式は Gmail のメールフィルタと全く同じです。

今回のゴールをもう一度。

送信元が [e-mail] で件名に [alert] の文字が含まれるメールが週に何件届いたか集計し、過去 12 週分をグラフ化する

ですので、今回は from:[e-mail] subject:[alert] と入力します。
Gmail のメールフィルタの書式について、詳しくは 公式ドキュメント を参照してください。
実際に Gmail メール検索画面で入力してみて、フィルタが機能するか確認するのがおすすめです。

入力が終わったら CONTINUE を押します。

2-a-5. トリガーのテスト

ここまでの設定がうまく機能するかのテストが可能ですが、今回は後ほどまとめて動作テストをするため SKIP TEST をクリックします。

すべての設定完了までもう少しです！

2-b. Google Sheet App をアクションとして設定

「何をトリガーにするか」が定義できたので、続いて「その時何をするか」を定義していきます。
Do this ... を押した後、トリガーを定義したときと同じ要領で Google Sheets を選びます。

2-b-1. どんなイベントを起こすか

Spreadsheet に複数の列（日付、件名）を新たに追記したいので、今回は Create Spreadsheet Row(s) を選びます。
仮に単一列を扱う場合でもこれを選んで問題ありません。

2-b-2. Spreadsheet に書き込むためのアカウント

Gmail トリガーを定義したときと同じ流れですね。
書き込み先の Spreadsheet に対する編集権限を持っているアカウントを追加しましょう。

2-b-3. どの Spreadsheet に何を書き込むか

前のステップ 1. で作成した Spreadsheet を書き込み先に設定します。

複数の入力欄では、2-b-2. で設定したアカウントを元に一覧がリストアップされますので、その中から次のように選択していきます。

入力欄	選択するもの
Drive	Spreadsheet の場所。マイドライブ My Google Drive か、あるいは共有ドライブ
Spreadsheet	今回作成した Spreadsheet の名前
Worksheet	書き込み先シートの名前。今回の例では 生データ

そのまま画面の下に目を移し、該当シートの各列に何を書き込むかを設定します。

入力欄（列名）	選択するもの
Date	Date
Date（変換後）	後述の文字列
Subject	Subject

Date と Subject 欄については、文字を入力するのでなく、入力欄右側のアイコンをクリックし、表示された一覧から選択する必要があります。

Date（変換後） 欄には、次の文字列を入力します。

=DATEVALUE(INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 3)&"/"&MATCH(INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 2),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)&"/"&INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 1))+TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),"[0-9:]{5,8}"))-VALUE(IF(COUNTIF(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "*-*"), "-", "")&TIMEVALUE(MID(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[+-][0-9]{4}"), 2, 2)&":0:0"))+TIMEVALUE("9:0:0")

長いですが、これは Spreadsheet の関数式です。
メールヘッダ内の Date には 14 Nov 2019 05:24:28 -0500 というような ISO8601 形式に似た形式の文字列が格納されていますが、Spreadsheet はこれを日時と解釈することができません。
Zapier がシートに関数式を書きこみ、Date 文字列を日時形式に変換することで集計可能なデータとして扱うことができるようになります。

この関数式については Appendix として解説を後述 していますので、詳しくはそちらをご覧ください。

以上を入力したら CONTINUE を押します。

2-b-4. アクションのテスト

トリガーと同様、アクションの定義がうまく行ったかのテストが自動で行われます。

Test was successful! と表示されれば「いつでも書き込めまっせ！」と Zapier が言っていることが分かります。
その下には「最近届いたメールを元に、試しにこんな文字列を書き込みまっせ！」という内容が表示されています。
さらにその下の RETEST & CONTINUE を押すと、実際に Spreadsheet に書き込みがなされます。

テストが上手く行かない場合は、Spreadsheet に書き込み権限があるか、などを確認してみてください。

設定は以上で終了です。

3. テスト

2-b-4. のステップで RETEST & CONTINUE を押す、あるいは実際に該当するアラートメールが飛んだあとで、Spreadsheet 側が正常に動作しているか確認してみましょう。

生データ シートに新たな行が書き込まれており、メール日時文字列やその変換後の日時データ、件名が正しく書き込まれていることを確認します。

集計 シートには、生データ シートの内容に基づき集計値が算出されていることを確認します。

最後に、グラフ シートにこれらの内容が反映されていることを確認します。

上手くいきましたでしょうか。

4. Zap をオン

作成した直後の Zap はオフになっており、トリガーが動きません。
画面下か、右上の OFF のスイッチをクリックして ON に切り替えます。

これで Zapier は随時トリガーを引き、Spreadsheet にデータを書き込み続けます。
Zap を停止する場合は、このスイッチを再度 OFF に切り替えれば OK です。

お疲れさまでした！

まとめ

• Zapier を用いると Gmail のメール受信をフィルタ分けし Spreadsheet に情報を記載するという処理が無料で自動化できる
• メールヘッダの Date 文字列はそのままだと日時として扱いづらいが、Spreadsheet の関数式を用いて日時データに変換することができる

Zapier は無料プランだと幾つかの制限がある、特に Webhook でサービス API を叩くことができないのは少し辛いですが、「Python コードを実行する」といったアクションも可能ですので、工夫次第で様々なことが自動化できます。

Spreadsheet と組み合わせることで、データ集計や単純なレポート作成には威力を発揮してくれるのではないでしょうか。

それではまた！

Appendix: メールヘッダの Date を日時形式に変換する関数式

Gmail のメールヘッダに含まれる Date フォーマットを Spreadsheet が解釈できる一般的な日時形式に変換する関数式について解説します。

なおこの関数式は、この記事をレビュー頂いた方にご指摘を頂き、大きく改良することができました。
ありがとうございました…！

メールヘッダの Date とは

メールヘッダ内の Date には、送信元メールサーバがメールを作成、送信処理を開始した日時が記録されます。
しかしこれが曲者で、RFC822 で定義されているフォーマットは次のように様々です。

• [daytime,] d mon yy hh:mm[:ss] zone
• [daytime,] dd mon yy hh:mm[:ss] zone
• [daytime,] d mon yyyy hh:mm[:ss] zone
• [daytime,] dd mon yyyy hh:mm[:ss] zone

[ ] で囲まれた要素は省略可能であることを示します。
次に例を示します。

• Sun, 25 Feb 96 22:20:17 +0900
• Sun, 5 Feb 2001 22:20:17 -0500
• Sun, 25 Feb 1999 22:20 +0000
• 5 Feb 01 22:20:17 +0900
• 25 Feb 2001 22:20:17 -0500
• 25 Feb 2001 22:20 +0000

引用元：
Groupmax Mail - SMTP Version 7　運用ガイド: 付録E　RFCヘッダの必須項目

なんともフォーマット変換泣かせですね…！

さらに、このフォーマットに則っていなくとも Date 部分が空でも、メールサーバはメールを送信できます。 その場合、受信したメールサーバ側が代わりに受信日時を Date として記録する場合が多いようです。

ではどのように変換するか

=DATEVALUE(INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 3)&"/"&MATCH(INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 2),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)&"/"&INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 1))+TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),"[0-9:]{5,8}"))-VALUE(IF(COUNTIF(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "*-*"), "-", "")&TIMEVALUE(MID(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[+-][0-9]{4}"), 2, 2)&":0:0"))+TIMEVALUE("9:0:0")

関数式は大きく 3 ブロックに分かれています。

• 日付（YYYY/MM/DD）部分を形成するブロック
• 時刻（hh/mm/ss）部分を形成するブロック
• タイムゾーンによる時刻補正を行うブロック

それぞれのブロックではシリアル値（起点日からの経過日数と時間を表す数値データ）が出力されるので、それらを足し合わせることで、晴れて Spreadsheet は日時形式で表示することができるようになります。

関数式のその他の部分の処理も含め、Fri, 14 Nov 2019 05:24:28 -0500 という文字列を 2019/11/14 19:24:28 に変換する場合を例として以下に解説します。

日付（YYYY/MM/DD）部分を形成するブロック

DATEVALUE(INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 3)&"/"&MATCH(INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 2),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)&"/"&INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 1))

ここが最もボリュームが大きいですね。
やっていることは、元の文字列を参照しながら、「年」「月」「日」を切り出した上で / で結合、すなわち 2019/11/14 という文字列を形成し、DATEVALUE() 関数でシリアル値に変換しているだけです。

DATEVALUE() 関数は引数として変換元の文字列を取っています。
その中身を見てみましょう。

「年」を形成

INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 3)

ここは「年」を切り出す部分です。
中から外に向かって順に処理を見ていくと、次のようになっています。

部分	処理の内容	処理中の文字列
INDIRECT(ADDRESS(ROW(),COLUMN()-1))	元の文字列が入ったセルを参照	Fri, 14 Nov 2019 05:24:28 -0500
REGEXEXTRACT(上の部分, "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}")	正規表現で年月日部分を切り出し	14 Nov 2019
SPLIT(上の部分, " ")	スペースで文字列を分割	14, Nov, 2019
INDEX(上の部分, 0, 3)	分割したうちの 3 番目を参照	2019

ポイントは次のとおりです。

• INDIRECT(ADDRESS()) による相対的なセル参照
• INDEX(SPLIT()) による分割要素へのアクセス

INDIRECT(ADDRESS(ROW(),COLUMN()-1)) は単純に「左隣のセルを参照」します。 この部分は、例えば 2 行目のセルであればこの箇所を $B2 と置き換え、他の行にコピペしても OK なわけです。
ただし、今回の用途では Zapier がどんな行にも同じ文字列を書き込む必要があるため、こういった関数式で表現する必要があります。

SPLIT() 関数自体は、文字列をセパレータで分割して、結果を数セルに渡って表示する、というものですが、INDEX() 関数でこれを受けることでリストの要素にインデックス番号でアクセスするという、一般的なプログラミング言語の基本的な操作と同様のことを行うことができます。

これらを含め、それぞれの関数の詳細は公式ドキュメントを参照してください。

「月」を形成

MATCH(INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 2),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)

部分	処理の内容	処理中の文字列
INDIRECT(ADDRESS(ROW(),COLUMN()-1))	元の文字列が入ったセルを参照	Fri, 14 Nov 2019 05:24:28 -0500
REGEXEXTRACT(上の部分, "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}")	正規表現で年月日部分を切り出し	14 Nov 2019
SPLIT(上の部分, " ")	スペースで文字列を分割	14, Nov, 2019
INDEX(上の部分, 0, 2)	分割したうちの 2 番目を参照	Nov
MATCH(上の部分, {リスト}, 0)	月の短縮英語表示を数字に変換	11

基本的には「年」を形成するのと同様の処理をしており、今度はスペースで区切った 2 番目の要素を切り出していますが、その他のポイントは次のとおりです。

• MATCH() による月の短縮英語表記から数字への変換

MATCH() 関数は、配列の中で該当する要素があった場合、その相対位置を返すものですが、この配列部分が {"Jan";～"Dec"} つまり、月の短縮英語表記のリストになっています。
"Nov" であればリストの 11 番目にあるため、"11" という数字が出力され、月の数字表記に変換できるわけです。
ちなみに、配列内を探索する際、アルファベットの大文字小文字の違いは考慮されません。

月の表記が November といった「短縮されていない」英語表記であれば、もう少し少ない関数で処理できるのですが、Spreadsheet は Nov を 11 月とは認識してくれないため、このようなひと手間をかける必要があります。
なお、Excel には月の短縮英語表記を簡単に数字に変換できる関数が用意されています。

「日」を形成

INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 1))

「年」「月」と同様の処理なので、解説は省略します。
「年」「月」「日」が揃ったので、これらを / 文字で結合、DATEVALUE() 関数に渡すことで 2019/11/14 を表すシリアル値が出力されます。

時刻（hh/mm/ss）部分を形成するブロック

+TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),"[0-9:]{5,8}"))

このブロックは、単純に 05:24:28 という時刻部分を切り出しシリアル値に変換しているだけです。
メールサーバ側の実装によっては Date の内容に「秒」が含まれないため、正規表現で考慮しています。

タイムゾーンによる時刻補正を行うブロック

-VALUE(IF(COUNTIF(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "*-*"), "-", "")&TIMEVALUE(MID(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[+-][0-9]{4}"), 2, 2)&":0:0"))+TIMEVALUE("9:0:0")

タイムゾーン識別子部分は +0900 や -0500 といった UTC を基準とした時差を表しています。
このブロックでは以下のように処理がされます。

部分	処理の内容
IF(COUNTIF(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "-"), "-", "")	識別子がマイナスであれば抽出
TIMEVALUE(MID(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), "[+-][0-9]{4}"), 2, 2)&":0:0")	識別子の数字の上 2 桁を切り出し「n 時 0 分」を表すシリアル値に変換
-VALUE(上の部分同士を & で結合)	タイムゾーンが UTC マイナスであれば-(-n 時 0 分) として時間を足し、プラスであれば引く
+TIMEVALUE("9:0:0")	日本時間（+0900）に合わせるため 9 時間を足す

EOF