スプレッドシートで作る、簡単報告資料自動化♪

はじめまして、アプリケーション部でテスト業務を担当している江口です。
テスト業務では主に、システム品質確認実施と結果分析・報告作業を担当させていただいております。
趣味は物書き(一応2冊ほど出版…しましたが…orz)、あとサッカーになります。

　スプレッドシート優秀！

開始：日次、週次、月次…ええい、もう！毎回集計するのめんどい！

複数メンバーに作業をしてもらい、その作業結果の集計をするのが面倒くさい…。

 

その理由は…

　・各メンバーによって書き方が『微妙に』違う（開始時の共通フォーマットを決めていなかった…orz）

　・どの時点の数値として集計すべきか悩む

　・そもそも各ファイル開いてみるのが面倒くさい

↓当時の心境

こういう時「ＸＸ分くらいなら手動で…」と思いがちです。

ですがこういう小さな無駄時間の積み重ねが、あなたの可能を狭めていっているのです！（啓発本のような言い切り）

 

本日の格言（嘘）

 

上図のような心の声が聞こえた…気がしたので、時間をひねり出してひと踏ん張りしてみました。
(土日家で試行錯誤し、そのノウハウを応用していたのは秘密)

その結果、スプレッドシートが優秀だと思い知られました…。

１．「Query＋Importrange」。「ぐーぐるのQueryは化け物か！？（ｂｙ赤い人）」

　「シートからシートにQuery発行して～」と言われても。。。

　「どうせめんどくさいんでしょ？」という意識しかありませんでした。

 

つまり、使ってみよう！

とか言いつつも『かな～り眉唾』な状態だったのです。

ですが…、使ってみると↓こうなりました

 

 

やったこと（実際とは若干異なります）

　・Queryを使った

　・Importrangeを使ってそれぞれのファイルを手動で開く必要がないようにした

　・同じクエリだが、対象列(カウント対象や絞込対象）や対象シート名を任意で変えられるようにして使いやすくした

(使用例)　　　　　

　

総数の部分がリアルタイムで更新されるので数値です。

複数ファイルにこういった『後ほど集計して定期的に報告する』数値が山ほどありました。

ですので、状況把握や報告数値作成時に参照するのは1ファイルでできるように少しだけ工夫をしています。

 

　（工夫の事例上記表の「総数」をリアルタイム更新数値にする）

　=query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/" & D67  ,"集計!A:D"),

　　　　　"Select "&F67&" where "&E67&"='"&G66&"'",false)

 

工夫した所

　・ファイルIDさえわかれば抽出できるようにする

　・フォーマットが違う場合にもシート名や抽出列、絞り込み条件抽出列を一覧上に出し随時変更して対応できるようにした

　　「検索行」「取得列」をファイルごとに変えて対応

　・GASで作業フォルダ管理下のスプレッドシートを抽出し上記関数が設定されている一覧に追加/更新（手作業回避）

　・GASで提出ファイルを自動excel化（ちょっと整えてダウンロードするだけで完成）

 

 

　次々増えて行く作業結果ファイル（最後には100超えて…）もこれで解決しました。。。

 

　・・・

　・・

　・

 

　やりきった。

　そう思ったのですが、敵はもう1種類いました。

　そう…。

 

２．BackLog…あれはデータベースだ（ヲイ）

BackLogの基本機能で十分っしょ。

昔は私もその様に思っていました。

100…200…300と未処理のチケットが増えていくまでは。

 

担当しているのが『テスト』ということもあり、品質管理上ので必要な項目が足りず、その結果…。

 

　・カスタム項目がどんどん増えていく～

　・「項目の組み合わせだ～」「このバグは申し送りだ～」と判定も変わっていく～

　・相手方がいつ更新するかわからない項目を待ってこちらのタスクが始まる作業～

　　そんな項目を日々確認・集計して報告なんてできません。

 

　…数値化…どうせ変化するんだし、なんとなくのタイミングで見た感じの数でよくね？

　…更新の見逃し…、人数少ないしお互い声がけでよくね？通知もあるし？

 

本日の格言２（嘘）

 

はい。そういうことで…

 

　GASでバックログチケットを抜いて、

　スプレッドシートに蓄積し

　その中で完了していないデータを更新し続ける

　そんな仕組みを作ってみました。

 

 

　　　　※チケットDBは1時間に1度新規の確認とデータの更新を行っています。

 

こうしてしまえば、

　・バックログにアクセスしなおして～、ではなく常に確認している資料上からステータスが分かる

　・GASのデータ取得ジョブは手動でも動かせるので、チェックしなければならないタイミングで判定基準

　　を表示する資料上からの各委任ができる

 

勿論、資料に表示するうえでチケットの情報をすべて取得されているわけではないので

詳細を知りたくなったら報告資料上にリンクを表示し、いつでも遷移できるようにておくと…

全てのチケット状態をBackLogに切り替えて確認しなくても済むようになります。

　

 

注意：

「あれ連携したい」「こういった項目を書いてほしい」と要望に次々応えていくとカスタム項目はどんどん変わっていきます。ですので、下図のようなツールを作成し、バックログAPIの機能でプロジェクトに設定されているカスタム項目を随時取り出し上図のチケットDBのGASを更新していかなればなりません。

 

 

最後に、グラフを別で作るのが面倒なあなたへ

アプリケーション部内ではあまり評判が良くないのですが

簡易グラフをセル内で表現できる「SPARKLINE」関数が便利です。(これはエクセルにもありますね)

 

進捗率だけ出すのも味気ないけどグラフを作ると表示位置の微調整が面倒…、セルの中におけない？

そんな気持ちに応えてくれます。

 

・数字だけだと味気ない時

 

・データと一緒に出したいとき

　…結構便利だと思います。

 

 

色々使ってみてもっと書くことはあったのですがずいぶん長くなったのでこの辺りで終わらせていただきます。

この記載が何かの参考となれれば幸いです。