はじめに

これはSpreadSheetをBigQueryから外部テーブル参照した時に
安直にSpreadSheetを触って、それが起因でどん詰まったお話です。
ほぼSpreadSheetの話なのでご了承ください。

やりたかったこと

SpreadSheetの日付列start_dateを、BigQueryから外部テーブルとして参照しました。

BigQueryのテーブルスキーマはこんな感じ

selectしてちゃんと値とれました。

select * from `{project}.{dataset}.test_table`

/*
2023-01-01
2023-01-13
*/

事件発生

ここから、ちょっとSpreadSheetを触りました。

再びselectをすると。。。

select * from `{project}.{dataset}.test_table`

/*
Error while reading table: {project}.{dataset}.test_table, error message: Could not convert value to date. Error: Invalid date: '2023/01/01'. Row 1; Col 0. File: {spreadSheetのid}
*/

目に見えるものが真実とは限らない的な？
見た目は子供頭脳は大人的な？
一体何が起こったのでしょう。

調査

ちょっとSpreadSheetを触った

何をやったかというと、表示形式を数字の"自動"にチェックをいれたのです。

編集履歴を見ると、なにかがかわった跡がありますね。
よく見ると、触った時にセル表示が右寄りから左寄りに変わってるので何かが変わったのは間違いなさそうです。

型を調べてみます。

2なので、どうやらテキスト型にされてしまったのが原因のようです。

試行錯誤

では、一度列を日付形式に戻してみましょう。

select * from `{project}.{dataset}.test_table`

/*
2023-01-01
2023-01-13
null
null
.
.
.
null
(999件)
*/

selectはできるようになりましたが、大量のnullが発生しました。

どうやらSpreadSsheetのデフォルトの1000行表示分をselectしており、列全体に対して操作したことが起因してる模様です。SpreadSheetの行をふやすとその分レコード数増えました。

どうしたらnullを消せるのでしょうか。もう一度自動を試します。

もはや日付でもなくなってしまいました。

どうやらシリアル値とやらに変換されてしまった模様です。

さて、じゃあシリアルになった時にBigQuery側はといいますと。。

select * from `{project}.{dataset}.test_table`

/*
Error while reading table: {project}.{dataset}.test_table, error message: Could not convert value to date. Row 1; Col 0. File: {spreadSheetのid}
*/

数値ですのでまた変換できなくなりました。詰んだ？

自動やめるお

対象のセルだけ選んでもっかい日付フォーマットにしてみます。

見た目は戻りました。

そしてBigQueryは....

select * from `{project}.{dataset}.test_table`

/*
2023-01-01
2023-01-13
*/

戻りました。
危うく"自動"に刺されるところでした。
なんとも腑に落ちない事件ですが警部、解決です。

え？表示形式いじらずに、そもそもセルの値書き直した方が早かったんじゃないか？

・・・・・

クリスマスなんでケーキでも買ってきますか。
え？あんぱんと牛乳？好きですねえ。

（date編完）

わかったこと

日付フォーマットは、安易に表示形式を自動にすると見た目は変わらなくても実体を勝手に変えられることがある
列単位で表示形式をいじると、列全体にnull含めて外部テーブルとしてはmax行レコードが存在する扱いにされることがある
値がある対象のセルだけ表示形式を操作すると対象のセルだけselectに戻ることがある
日付(DATEVALUE)をさらに安直に表示形式を自動にすると、シリアル(数値）に変換されることがある

まとめ

SpreadSheetをBigQueryから外部テーブル参照する場合、"表示形式"の"自動"はむやみに触るべからず。見た目同じでも勝手に内部で値(型なのかフォーマットなのか）が変えられてBigQueryの指定した型と不整合が発生する可能性があるお。

SpreadSheet x BigQuery 外部テーブル参照型エラー事件(date編)