BigQueryにGSSの日時型カラムをDATETIME型で読み込む

BigQueryではGoogleスプレッドシート（GSS）を読み込んでテーブルを作成することができます。
ですが、GSSの日時型カラムをBigQueryでDATETIME型としてそのまま読み込むとエラーが発生します。
この解決に時間がかかったので備忘として残します。

結論

GSS側で日時型カラムを文字列型YYYY-MM-DD hh:mm:ssに変換してからBigQueryに読み込めばOK

エラー状況

例えば、次のようなGSSをBigQueryに読み込みたいとします。

GSSをBigQueryに読み込むには、「テーブルを作成」において次のように指定します。
ポイントとしては、日付型カラムの読み込み先をDATETIME型で指定している点です。

このまま「テーブルを作成」ボタンを押した段階ではエラーは出ません。しかし、テーブルをSQL内で呼び出したときにエラーが吐き出されます。

Error while reading table: my_project.sample_dataset.sample_table, error message:
Could not convert value to datetime. Row 1; Col 0. 
File: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

このような挙動となるのは、「テーブルを作成」を押した段階ではGSSを参照するという設定をしただけであり、SELECT句を走らせたときに初めてGSS上のデータが実際に読み込まれるからです。

解決策

このエラーを回避するには、GSS側で日時型カラムを文字列型YYYY-MM-DD hh:mm:ssに変換する必要があります。

新規シートを作り、文字列型カラムを変換のように変換します。

ポイントは次の通りです。

• ARRAYFORMULA関数を使えば1番上のセルに関数を書くだけで済み、レコード追加にも対応できる。
• 日時型を文字列に変換するにはTEXT関数を使う。第２引数はyyyy-mm-dd hh:mm:ss;;とする。
  末尾に;;を付けないと、空欄を0として認識して変換してしまいます。

【失敗例】

日付型以外のカラムは、ARRAYFORMULA関数だけを適用すればOKです。

以上のようにGSS側で変換を行えば、無事に日付型カラムをDATETIME型で読み込むことができました。