長期運営で肥大化したExcelマスターデータの解消に向けた移行事例

by gree_tech

Embed

Start on current slide

Slide 1

Slide 1 text

長期運営で肥大化した Excel マスターデータの解消に向けた移行事例株式会社WFS 麻生航平 / 嶋田翔太 ©WFS

Slide 2

Slide 2 text

自己紹介麻生航平 / Kohei Aso 嶋田翔太 / Shota Shimada スタジオ本部スタジオ本部アナザーエデンチームメインプログラマアナザーエデンチームマネージャー ©WFS

Slide 3

Slide 3 text

プロジェクト概要 ©WFS

Slide 4

Slide 4 text

アナザーエデン時空を超える猫 ©WFS

Slide 5

Slide 5 text

10年分のマスターデータの規模シート数 Excelファイル数 80ファイル約最大の行数(1シート) 50万行最大 600シート約最大のファイルサイズ 50MB 最大

Slide 6

Slide 6 text

アナザーエデン時空を超える猫マスターデータは Excelで管理していました動作が重い… 80ファイルを超える Excel ©WFS

Slide 7

Slide 7 text

アナザーエデン時空を超える猫マスターデータは Excelで管理していました 80ファイルを超える Excel スプレッドシート化 ©WFS

Slide 8

Slide 8 text

セッションで得られること Excel → スプレッドシート運用の移行事例 ● 長期運用におけるマスターデータ移行のプロセス ● スプレッドシート化による生産性向上効果 ● 「事故らない運用」を実現するためのUX設計 ©WFS

Slide 9

Slide 9 text

アジェンダ 1. 従来の運用課題 2. 改善の方針 3. 実装詳細 4. 移行計画とサポート体制 5. 移行後の課題 6. 導入効果 7. 知見と教訓 8. まとめ ©WFS

Slide 10

Slide 10 text

1. 従来の運用課題 ©WFS

Slide 11

Slide 11 text

マスターデータの更新頻度更新頻度（１日あたり）通常時 19.4回繁忙期 25.9回 ©WFS

Slide 12

Slide 12 text

Excelでのマスターデータ管理の課題 ● データ数増大によるExcelの処理落ち ● ファイル単位のロックによる待ち時間 ● Git管理のためデータの活用が困難 ©WFS

Slide 13

Slide 13 text

課題① データ数増大による Excelの処理落ち ● データ数増大による Excelの処理落ち ● ファイル単位のロックによる待ち時間 ● Git管理のためデータの活用が困難開くのに 1分かかる Excel ©WFS

Slide 14

Slide 14 text

課題② ファイル単位のロックによる待ち時間 ● データ数増大によるExcelの処理落ち ● ファイル単位のロックによる待ち時間 ● Git管理のためデータの活用が困難エリアクエストキャラクター定義シートスタイルシートパラメータシートスキルシートアビリティシートキャラクター ©WFS

Slide 15

Slide 15 text

課題② ファイル単位のロックによる待ち時間 ● データ数増大によるExcelの処理落ち ● ファイル単位のロックによる待ち時間 ● Git管理のためデータの活用が困難編集したいエリアクエストキャラクターキャラクター定義シートスタイルシートパラメータシートスキルシートアビリティシート git lfs lock 編集したいシート以外もロックされてしまう…… ©WFS

Slide 16

Slide 16 text

課題③ Git管理のためデータの活用が困難 ● データ数増大によるExcelの処理落ち ● ファイル単位のロックによる待ち時間 ● Git管理のためデータの活用が困難日頃の業務にスプレッドシートを活用している ● 企画書・仕様書 ● 入稿データを生成するシート ● QAのデータの静的チェック ©WFS

Slide 17

Slide 17 text

課題③ Git管理のためデータの活用が困難 ● データ数増大によるExcelの処理落ち ● ファイル単位のロックによる待ち時間 ● Git管理のためデータの活用が困難日頃の業務にスプレッドシートを活用しているダウンロードアップロード GitHubから最新のExcelをダウンロードして更新し続ける必要性 ©WFS

Slide 18

Slide 18 text

スプレッドシートへ移行した場合 ● データ数増大によるExcelの処理落ち ● ロードが速い ● ファイル単位のロックによる待ち時間 ● シートごとのロックが可能 ● Git管理のためデータの活用が困難 ● データの活用が容易 ©WFS

Slide 19

Slide 19 text

スプレッドシートへ移行を決意 ©WFS

Slide 20

Slide 20 text

2. 改善の方針とシステム構成 ©WFS

Slide 21

Slide 21 text

ゲーム用データ生成フロー Excelの場合ビルド Excel (Git管理) ゲーム用データ ©WFS

Slide 22

Slide 22 text

ゲーム用データ生成フロースプレッドシートの場合ダウンロードスプレッドシート CSV CSV CSV … … … CSV CSV CSV CSV (Git管理) ビルドゲーム用データ ©WFS

Slide 23

Slide 23 text

マスターデータ入稿フロー Excelの場合シート1 シート2 シート3 Excel (ローカル ) PR Excel (リモート ) ©WFS

Slide 24

Slide 24 text

マスターデータ入稿フロースプレッドシートの場合シート1 CSV シート2 CSV PR シート3 上書きスプレッドシート CSV CSV (ローカル ) … CSV (リモート ) ©WFS

Slide 25

Slide 25 text

スプレッドシート化の要件 1 従来の入稿の利便性を損なわないこと 2 ゲーム開発を止めないこと ©WFS

Slide 26

Slide 26 text

① 従来の入稿の利便性を損なわないこと Excelでは簡単に元に戻せたのに…… Excel用の効率化ツールが使えなくなると困るなぁ → 検証環境を用意 → 代替ツールを提供 ©WFS

Slide 27

Slide 27 text

スプレッドシート化の要件 1 従来の入稿の利便性を損なわないこと 2 ゲーム開発を止めないこと ©WFS

Slide 28

Slide 28 text

② ゲーム開発を止めないこと新しいフローに一斉に切り替えると …… GASのシステムビルドスクリプト ※更新頻度：19.4回/日効率化ツール ©WFS

Slide 29

Slide 29 text

② ゲーム開発を止めないこと ※更新頻度：19.4回/日新しいフローに一斉に切り替えると …… イレギュラーなデータトラブル不具合 GASのシステムビルドスクリプト効率化ツール ©WFS

Slide 30

Slide 30 text

一斉に切り替えた場合に予想されるトラブル ①システムの不具合の修正に対応しきれない GAS・移行に伴うツール・600個のシートのどこかで不具合が起き得る ②入稿についての問い合わせ依頼が殺到してしまう慣れない入稿手順の問い合わせに対応しきれない → 約半年の期間をかけて段階的な移行を計画 ©WFS

Slide 31

Slide 31 text

段階的な移行とは ©WFS

Slide 32

Slide 32 text

段階的な移行とは ©WFS

Slide 33

Slide 33 text

段階的な移行とは ©WFS

Slide 34

Slide 34 text

段階的な移行とは ©WFS

Slide 35

Slide 35 text

段階的な移行とは ©WFS

Slide 36

Slide 36 text

段階的な移行とは ©WFS

Slide 37

Slide 37 text

段階的な移行とは ©WFS

Slide 38

Slide 38 text

スプレッドシート化の要件 1 従来の入稿の利便性を損なわないこと → 検証環境「個人用シート」の作成・効率化ツールの移行 2 ゲーム開発を止めないこと → 約半年間かけて段階的に移行 ©WFS

Slide 39

Slide 39 text

3. 実装内容 ©WFS

Slide 40

Slide 40 text

実装内容 ①Excelの前処理 ②ビルドスクリプトの改修 ③シート単位の排他制御 ④個人用シートの作成 ⑤専用GUIツールの開発 ©WFS

Slide 41

Slide 41 text

実装内容 ①Excelの前処理 ②ビルドスクリプトの改修 ③シート単位の排他制御 ④個人用シートの作成 ⑤専用GUIツールの開発 ©WFS

Slide 42

Slide 42 text

①Excelの前処理 Excelをスプレッドシートにインポートすると一部データがエラーに → Excelの数式・他ファイル参照をスプレッドシートでも使える値に変換 =JIS(A1)&"個" １００個 → スプレッドシートで扱えない数式をExcelに指定できないようバリデーションを作成スプレッドシートのセル数上限（ 1000万）を超えるとエラーに → ファイルを分割して複数シートに分離 ©WFS

Slide 43

Slide 43 text

実装内容 ①Excelの前処理 ②ビルドスクリプトの改修 ③シート単位の排他制御 ④個人用シートの作成 ⑤専用GUIツールの開発 ©WFS

Slide 44

Slide 44 text

②ビルドスクリプトの改修 ● スプレッドシート → CSVをダウンロードする機能 ● CSVからビルドできる機能 ● 分割されたシートに対応 ● ExcelとCSVをファイル単位で切り替え可能に Excel ダウンロードスプレッドシート CSV CSV CSV … … … CSV CSV CSV CSV ビルドゲーム用データ ©WFS

Slide 45

Slide 45 text

実装内容 ①Excelの前処理 ②ビルドスクリプトの改修 ③シート単位の排他制御 ④個人用シートの作成 ⑤専用GUIツールの開発 ©WFS

Slide 46

Slide 46 text

③シート単位の排他制御スプレッドシート標準のロック機能「シートを保護」 ● ロックしたユーザーのみがシートを編集可能 ● 他のユーザーは閲覧のみ可能 ©WFS

Slide 47

Slide 47 text

③シート単位の排他制御スプレッドシート標準のロック機能の課題 ● ロックされていないシートは全ユーザーが編集ができてしまう ○ 閲覧したい場合でも誤って操作されてしまう ● 作業者がロック解除を忘れたら他のメンバーが編集できない ○ 強制的にロックを解除できる仕組みがあると便利 → 編集するときだけユーザーを「編集者」に制御する運用 ©WFS

Slide 48

Slide 48 text

基本は「閲覧者」編集時のみ「編集者」にする方法 1. 専用の管理アカウントを作成、権限はオーナー、全シートをロック 2. ユーザーがシートをロック/解除するAPIを実装ロック API ● ● ● 管理アカウントがユーザーを「編集者」権限に変更ユーザーと管理アカウントが指定シートをロック管理シートにアカウント名を記載 ©WFS

Slide 49

Slide 49 text

基本は「閲覧者」編集時のみ「編集者」にする方法 1. 専用の管理アカウントを作成、権限はオーナー、全シートをロック 2. ユーザーがシートをロック/解除するAPIを実装ロック API ● ● ● 管理アカウントがユーザーを「編集者」権限に変更ユーザーと管理アカウントが指定シートをロック管理シートにアカウント名を記載ロック解除 API ● ● ● ユーザーと管理アカウントが指定シートをロック解除管理アカウントがユーザーを「閲覧者」権限に変更管理シートからアカウント名を削除 ©WFS

Slide 50

Slide 50 text

シートのロック・解除を APIとして提供した理由スプレッドシート以外からもシートのロック・解除が可能 ● 開発ツールからロック・解除できる ● CSVのプルリクをマージしたときに自動で解除できる PRマージロック解除 API ©WFS

Slide 51

Slide 51 text

実装内容 ①Excelの前処理 ②ビルドスクリプトの改修 ③シート単位の排他制御 ④個人用シートの作成 ⑤専用GUIツールの開発 ©WFS

Slide 52

Slide 52 text

④個人用シートの作成個人用シートの誕生背景手元で自由に検証したい本番環境複製ダウンロード個人用シート ©WFS

Slide 53

Slide 53 text

④個人用シートの作成検証したデータを本番環境に反映させたい本番環境手作業で反映 …？個人用シート ©WFS

Slide 54

Slide 54 text

④個人用シートの作成個人用シート →本番環境にマージする機能も用意検証したデータを本番環境に反映させたい本番環境マージ個人用シート ©WFS

Slide 55

Slide 55 text

個人用シートから本番環境へのマージ機能 ● 行単位の差分（追加・削除・変更）を抽出 ● 数式や背景色といった見た目の情報も反映変更追加削除 ©WFS

Slide 56

Slide 56 text

個人用シートから本番環境へのマージ機能処理に時間がかかるため GASからPython製のWebアプリに移行 GAS Python (Streamlit + pandas) ©WFS

Slide 57

Slide 57 text

実装内容 ①Excelの前処理 ②ビルドスクリプトの改修 ③シート単位の排他制御 ④個人用シートの作成 ⑤専用GUIツールの開発 ©WFS

Slide 58

Slide 58 text

⑤専用GUIツールの開発スプレッドシートのシート数が膨大で目当てのシートを探すのが大変でした ©WFS

Slide 59

Slide 59 text

⑤専用GUIツールの開発スプレッドシートのシート数が膨大で目当てのシートを探すのが大変でした ©WFS

Slide 60

Slide 60 text

⑤専用GUIツールの開発目的のシートに即座にアクセスできる GUIツールを開発 ● スプレッドシートを開く ● シート名検索 ● シートのロック・解除 ● シートのロック状態の確認 ● 個人用シートの作成・確認 ©WFS

Slide 61

Slide 61 text

4. 段階的な移行とサポート体制 ©WFS

Slide 62

Slide 62 text

段階的な移行のスケジュール 6段階で期間を空けて移行を実施大型コンテンツ開発期間のため回避全体説明動画マニュアル視聴期間更新がほぼないもの 9/22〜更新が低頻度のもの 10/6〜フィールド関連 10/20〜バトル関連 1/12〜演出関連 1/26〜運用関連 2/9〜 9月 10月 11月 12月 1月 2月 ©WFS

Slide 63

Slide 63 text

サポート体制動画マニュアル実際に入稿する様子を動画でキャプチャして、ナレーション付きで解説したものをチームメンバーに展開入稿手順書画像付きで見やすいページを用意ビデオ通話での入稿サポート初回の入稿が不安な方向けに、ビデオ通話でサポート → 不便な点・わからない点を開発側で把握し改善 ©WFS

Slide 64

Slide 64 text

5. 移行後の課題 ©WFS

Slide 65

Slide 65 text

移行後の課題① ロックしていないシートが数式参照により変更されてしまうことがあるキャラクター定義編集スタイルパラメータスキル数式で参照 ← 勝手に変更されてしまうアビリティ ● 変更されたシートを編集する際に意図しない差分として出力されてしまう ● 数式参照している値は確認用途であり、ゲーム用データに使われないため許容 ©WFS

Slide 66

Slide 66 text

移行後の課題② 本番環境のスプレッドシートで操作を戻したいという要望が発生本番環境の操作を戻したい本番環境 ● 個人用シートの内容をマージした後に取り消したい場合がある ● CSVをマージするごとにスプレッドシート側でもバックアップを作成 ©WFS

Slide 67

Slide 67 text

6. 導入効果 ©WFS

Slide 68

Slide 68 text

３つの課題は、どう変わったか Excelで運用していた頃に抱えていた課題 1 処理落ち 2 待ち時間 3 データの活用 ©WFS

Slide 69

Slide 69 text

① 処理落ち Excelで運用していた頃に抱えていた課題 1 処理落ち 2 待ち時間 3 データの活用 ©WFS

Slide 70

Slide 70 text

① 処理落ち： Excelとスプレッドシートの比較動画 ©WFS

Slide 71

Slide 71 text

① 処理落ち：スプレッドシート化とシート分割で解消 ← データはクラウドにある ← 開くまでの時間は PCの性能とネットワーク環境に左右

Slide 72

Slide 72 text

① 処理落ち：スプレッドシート化とシート分割で解消 ※ 計測条件（M2 Max搭載のMacBook Pro、全セル同一値、ネットワーク環境に依存する簡易計測）

Slide 73

Slide 73 text

② 待ち時間 Excelで運用していた頃に抱えていた課題 1 処理落ち 2 待ち時間 3 データの活用 ©WFS

Slide 74

Slide 74 text

② 待ち時間会話から「ロック」という言葉が消えた ©WFS

Slide 75

Slide 75 text

② 待ち時間：ロック待ちがほぼ消えたいつまでExcelファイルを使用しますか？先にExcelファイルを編集させていただけないでしょうか他の人が作業できなくなってしまうので解除をお願いします ◯◯さんがロック中なので登録するのがもう少し後になります ©WFS

Slide 76

Slide 76 text

② 待ち時間： 4人同時に作業していた日ある日、キャラクター関係シートを 4人が別々に編集した記録 4人が同時に手を動かした時間 Aさん Bさん Cさん Dさん 0900 1200 1500 1800 ※実ログをもとにした概念図。帯が重なる時間＝同時に作業していた時間 ©WF S ©WFS

Slide 77

Slide 77 text

② 待ち時間シートA シートB シートC シートD シートE ©WFS

Slide 78

Slide 78 text

Slide 79

Slide 79 text

② 待ち時間： Excelなら直列で約 8時間半の待ち Git LFSのロックはファイル単位 Excelで同じことをすると1人ずつ順番にしか進められない実際（並⾏作業） 10時間45分もし直列で作業したら 19時間10分約8時間半（ただ待っていただけの時間） 09:00 12:00 15:00 18:00 21:00 00:00 03:00 ©WFS

Slide 80

Slide 80 text

Slide 81

Slide 81 text

Slide 82

Slide 82 text

Slide 83

Slide 83 text

Slide 84

Slide 84 text

Slide 85

Slide 85 text

Slide 86

Slide 86 text

Slide 87

Slide 87 text

Slide 88

Slide 88 text

Slide 89

Slide 89 text

Slide 90

Slide 90 text

Slide 91

Slide 91 text

Slide 92

Slide 92 text

Slide 93

Slide 93 text

①「事故らない運用」をシステムで強制するあえて「できないこと」を増やす設計機能を拡張するのではなく、マスターデータ本体は管理者アカウントとロックをかけたユーザ以外は編集不可に設定決められたフローの厳守個人用シートの作成やマージ処理、ロック・ロック解除などの操作は、専用に用意した仕組みの中だけでしか実行できないように制御 ©WFS

Slide 94

Slide 94 text

② GASのシステム構築は初期設計が重要 GASの一度の実行時間は最大 6分まで大規模なデータを扱うと、普通に書いたつもりでも簡単に6分の制限にぶつかる遅さの正体は、スクリプト ⇔ スプレッドシート間の「通信の往復」 1セルずつ読み書きすると往復がセルの数だけ積み上がる一括にまとめるだけで、速度は劇的に変わる ©WFS

Slide 95

Slide 95 text

② 計測してみた（ 5回平均）テストデータで処理速度を計測 ● 読み込み：getValue / getValues / Sheets API ● 書き込み：setValue / setValues ● シートのコピー：copyTo / batchUpdate / makeCopy ©WFS

Slide 96

Slide 96 text

② 読み込み： getValue は1.5万セルで 6分の制限 getValue（1セルずつ）タイムアウト getValues（一括）／ Sheets API

Slide 97

Slide 97 text

Slide 98

Slide 98 text

Slide 99

Slide 99 text

② GASのシステム構築は初期設計が重要分割処理を前提とした設計後から分割するのは困難最初から「500件ずつ処理し、続きは次回の実行トリガーへ回す」設計思想を持つ一時シートをキャッシュとして利用計算の途中結果を一時シートに書き出し、APIの往復呼び出し回数そのものを削減する GASに固執しない GASの限界を超える巨大データ処理は、Pythonなど、別の手段で取得・加工する ©WFS

Slide 100

Slide 100 text

Slide 101

Slide 101 text

② コードの組み方：機能ごとに分け、土台を 1箇所にどの機能も共通して使う処理は、土台になる 1箇所だけに置くシートロック・ロック解除個人用シート作成マスターデータへのマージ … ベースの関数定数群シートの高速な読み書き、 APIリクエスト制御、例外ハンドリングなど全ての個別モジュールが共通で依存する処理をカプセル化したプロジェクトスプレッドシート ID、管理者用アカウント情報などのグローバルな情報を一元管理するプロジェクト ©WFS

Slide 102

Slide 102 text

② コードの組み方：機能ごとに分け、土台を 1箇所にどの機能も共通して使う処理は、土台になる 1箇所だけに置くシートロック・ロック解除個人用シート作成マスターデータへのマージ … 読み込み処理の改善ベースの関数定数群シートの高速な読み書き、 APIリクエスト制御、例外ハンドリングなど全ての個別モジュールが共通で依存する処理をカプセル化したプロジェクトスプレッドシート ID、管理者用アカウント情報などのグローバルな情報を一元管理するプロジェクト ©WFS

Slide 103

Slide 103 text

② コードの組み方：機能ごとに分け、土台を 1箇所にどの機能も共通して使う処理は、土台になる 1箇所だけに置くシートロック・ロック解除個人用シート作成マスターデータへのマージ … 定数の変更ベースの関数定数群シートの高速な読み書き、 APIリクエスト制御、例外ハンドリングなど全ての個別モジュールが共通で依存する処理をカプセル化したプロジェクトスプレッドシート ID、管理者用アカウント情報などのグローバルな情報を一元管理するプロジェクト ©WFS

Slide 104

Slide 104 text

Slide 105

Slide 105 text

Slide 106

Slide 106 text

Slide 107

Slide 107 text

Slide 108

Slide 108 text

Slide 109

Slide 109 text

8. まとめ ©WFS

Slide 110

Slide 110 text

まとめ Excel時代の課題スプレッドシート移行後の解決策・結果データ増大による処理落ちクラウド処理・シート分割により瞬時に開けるファイル単位ロックの待ち時間シート単位のロックにより同時編集が可能に Git管理（バイナリ）のデータ活用難 CSV管理化で差分可視化・最新データの参照が容易に ©WFS