マスタ修正の困りごと
いくらRailsなどでUIが楽に作れるといっても、エクセルやスプレッドシートでマスタを修正する機会はまだまだ残っています。
よくあるフローとしては、こんな感じ。
- MySQLなどのDBからCSV出力
- CSVをエクセルやGoogleSpreadSheetに取り込み
- Sheet上で編集
- CSVとして保存
- CSVをロード
まぁ面倒。
こんなフローはどうでしょう
- DBeaverやSequelProなどのUIからマスタをコピー
- GoogleSpreadSheet上にペースト
- GoogleSpreadSheet上で編集
- GoogleSpreadSheetの数式でSQL生成&コピー
- DBeaverやSequelPro上でSQL実行
という流れです。ステップとしては変わりませんが。ファイルが一切登場せず、クリップボードのみのやりとりで可能です。
スプレッドシート上でSQLを生成している例
こんなイメージです。左の黄色がDBからコピーしてきたテーブルです。黄色の部分で編集をします。灰色が中間の計算値フィールド、青色がペーストするSQL文です。
https://docs.google.com/spreadsheets/d/1TH1p92-F9X-AmyuEy7QOrLrVyGDI7QJSaMphS7TaaNs/edit#gid=0
各数式の説明
カラム名をカンマで繋ぐ
JOIN関数で可能です。
カラム値をクォートしながらカンマで繋ぐ
ARRAYFORMULA関数は配列を引数に全要素に対して同じ計算をしてくれます。他のプログラミング言語でよく出てくるmap関数に近いです。ARRAYFORMULA関数でクォートで囲み、さらにJOIN関数を使ってカンマでつないでいます。
カンマでつないだ各値をSQLに変換する
更新処理をするにはプライマリーキーが必要なので、今回もid列がプライマリーキーの前提です。プライマリーキーがあれば、「もし同じキーがあればUPDATEでなければinsert」というREPLACE構文が使えます。
replace inot [テーブル名] (カラム名をカンマでつないだ文字列) values
(値をカンマでつないだ文字列),
(値をカンマでつないだ文字列)
を作ります。
手抜きポイント
- フィールド内に出てきたクォートを本当はエスケープすべき
- やろうと思えば、replace関数を全フィールドにかければいい
- ARRAYFOMURAを使えば全フィールドに一回で処理できる
- 最後に余計なカンマがつく
- 最後にカンマは不要だけどそのロジックをつけるのが面倒で最後までつけてる
- SQL実行する前に削除するのは手間ではない
- 削除には対応してない
- 主キーを元に削除するなら、特にスプレッドシートのUIは要らないから
- この手のマスタは論理削除みたいなのが多いし