JSON向けの文字列を表計算ソフトで扱うとダブルクオートがおかしくなった話

背景

JSONとはデータを記述する形式の名前です。文字コードはutf-8で下記のようにデータを記述できます。

{
  "users": [
    { "name" : "pu" },
    { "name" : "nya" }
  ]
}

PosgreSQLなど一部のデータベースでは、JSON形式で値を保存できる形式でフィールドを定義できます。
PosgreSQLの場合はJSONやJSONBがそれです。（JSONBは格納時に時間がかかりますが、その後の処理がJSON形式より早くなるようです。）

参考:
PostgreSQL Documentation - 8.14. JSON Types
8.14. JSONデータ型

取り組んでいるシステムの作成依頼者からCSVのダウンロードとアップロードの対応を要求されたので、JSONB形式のデータをCSVに出力してアップロードする仕組みを実装しました。
実装後の動作確認で表計算ソフト（LibreOffice Calc）で値をCSVの変更してアップロードすると、一見問題無さそうな文字に対してエラーが発生しました。
対応に時間をとられたので、備忘録を兼ねて内容を共有します。

使ったもの

• nodejs
  この記事で利用したnodejsのバージョンは11.10.1です。
• 表計算ソフト
  この記事で利用したのはLbreOfficeのCalcのバージョン6.0.7.3です。
  なお、Microsoft OfficeのExcelでは、今回問題となる文字列置換が起こらないようなので、問題は発生しないと思います。
  （Excelを持ってないので確認はできていません。発生する場合は、コメントなどで教えていただけると嬉しいです。）

遭遇したエラー

LibreOfficeのCalcで ["test"] と入力したセルを

nodejsのJSONのparse関数で処理しようとしたところ（cellStrには問題となる文字列が入っています。）

JSON.parse(cellStr)

下記のエラーが発生しました。

SyntaxError: Unexpected token “ in JSON at position 1
    at JSON.parse (<anonymous>)

ダブルクオートが期待されていない？
一見問題無さそうですが、このログだけでは対応方法がまだ分からないため、解析してみます。

解析

cellStrを下記のコードで、文字列全体と文字ごとの文字コードをログに出してみました。

console.log(cellStr)
for (const char of cellStr) {
  console.log(char, char.charCodeAt(0))
}
JSON.parse(cellStr)

実行した結果がこちらです。

[“test”]
[ 91
“ 8220
t 116
e 101
s 115
t 116
” 8221
] 93
SyntaxError: Unexpected token “ in JSON at position 1
    at JSON.parse (<anonymous>)

注目すべきはこの2行です。

“ 8220
” 8221

文字コード8220は左バッククオート、文字コード8221は右バッククオートを意味します。
LibreOfficeのCalcで ["test"] と入力したセルをよく見てみると、入力した"（バッククオート）が左右バッククオートとに置き換えられていることが分かります。

表計算ソフトによる上記の処理が今回のエラーの原因と分かりました。

対応案

表計算ソフトの設定を変える

LibreOfficeのCalcは、下記の操作でクオートを左右ダブルクオートに置き換えないように設定を変更できます。
（Excelはクオートの置き換え機能を持っていないため、この問題は発生しないようです。）

ツール -> オートコレクトオプションを選択します。

オートコレクト画面で「言語固有のオプション」タブを表示し、「ダブルクオーテーションマーク」の「置換」をチェックしていない状態にして、OKを選択します。

上記の設定で左右ダブルクオートに置き換えられずに入力できるようになります。

プログラムで文字列の左右ダブルクオートを文字コード34のダブルクオートに置き換える

JSONとして認識したい文字列の左右ダブルクオートを期待するダブルクオートに置き換えます。

cellStr = cellStr
  .replace(String.fromCharCode(8220), '"')
  .replace(String.fromCharCode(8221), '"')
JSON.parse(cellStr)

これにより、ダブルクオートに置換されたJSONも処理できるようになります。

この手法の欠点として、入力者が左右ダブルクオートをあえて入力した場合もプログラムで置き換えてしまう点です。
複雑な文字を許容するデータに対しては、この手法は適用しないことをお勧めします。

まとめ

LibreOffice Calcで発生するダブルクオートが左右ダブルクオートに置き換えられてしまうことによりJSONが解釈できない不具合に遭遇しましたが、Calcの設定変更かプログラムの記述ついかにより、問題を回避できました。
Excelは左右ダブルクオートへの置き換えが発生しないようなので、気にしなくて良さそうです。