PleasanterとExcelを接続してみた－活用編①

# はじめに
以前投稿した[[こちら]](https://qiita.com/m-isik/private/0abfd51a22587bed2e47)の記事で汎用的なエクセルツールとして、Pleasanterのテーブルを取得・登録できるものを作ってみました。今回はさらに活用編として、より実践的なツールを作ってみたいと思います。

# 作るもの
進捗スケジュール管理のためのエクセルを用意して、そこにPleasanterからの情報を読み込ませてみたいと思います。

イメージするのはこんなもの
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/da0968da-8425-33ee-ed35-2b755dd8e008.png)
だれもが一度は目にしたことがあるような進捗表エクセル。予実・進捗の遅れ・作業の完了未完了がみえるもの。そして、イナズマ線をひきたい。

# Pleasanterテーブルの用意
データを蓄積するPleasanterを用意します。今回は、テンプレートから”進捗管理”をそのまま使います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/83d4e8af-be2a-b6be-18eb-5e69b0bfa3cb.png)
適当にそれっぽいデータを仕込んでおきます。

一応、Pleasanterにもガントチャート機能がありますが、今回はあくまで古き良き馴染みのExcelで実装を進めます。（イナズマ線ひきたい）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/38348134-5c9b-bcfa-f98e-a8638907d6ed.png)



# 実装方針
上記の手作りエクセルシートもいいのですが、イナズマ線を自分で実装するのはしんどい（厳しい）、ということでというところで、Web探してみたらいい感じのアドインがあったので、利用させていただきます。
[[こちら]](https://forest.watch.impress.co.jp/library/software/inazumalin/)

実行すると以下のような感じで、イナズマ描画用の機能が追加されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/f77280b7-03ff-0e51-d8c0-2f80b35b693b.png)
こちらを利用させていただきます。（ちなみにこのアドイン、すごい高性能）

DL時に添付されていたサンプルExcelがそのまま使えそうなので、これにPleasanterから取得した情報を転記してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/ce9c80d5-8607-a9a6-9f7c-2cf097afc9a8.png)
開始終了日入力でセルに色をつけてくれます。（この画面はDLしたまんまの状態）

# テーブル取得用のシートとマクロを設定
DLしてきたサンプルのExcelブックから不要なシートを削除、テーブル取得用のシートを追加します。参照設定も忘れずに。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/ae462157-b9e3-9e86-4706-390f58936df1.png)
[[テーブル取得用]](https://github.com/m-ishikawa1980/ExcelToPleasanter/tree/main)と[[VBA-JSON]](https://github.com/VBA-tools/VBA-JSON)のモジュールをインポート
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/d6619f9c-1b99-f1db-2a72-2077b212cb48.png)
これでPleasanter接続の準備はOK（のはず）
# 連携項目の整理
Excelに連携する項目を整理、以下の項目をAPIで取得します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/29b1a401-61c3-3029-046c-ce06c9e657c0.png)
テーブル取得用シートに項目を設定、情報が取得できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/01f5809a-9279-965c-68c2-f4c9af340f4d.png)
データの件数が少ないので抽出条件（６，７行目）は無し。
# 中間編集
一部、Pleasanterの項目そのままでは使えないものがあるので、「編集シート」を用意して項目を加工します。（①～④）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/3f97b8fb-fe7d-f4cf-b6ea-d9db9eec52d8.png)

####  加工した内容

#####  ①ソート  
   テーブル取得シートから、Sort関数でソートしながら編集シートに転記  

```vb
'A1セル
=SORT(INDIRECT("テーブル取得!A24:F"&COUNTA(テーブル取得!A:A)+15),{2,1},{1,1})
```
開始日（2列目）とタイトル(1列目)でソートします。
INDIRECT関数でタスクが増えても対応できるような関数としています。（[[こちら]](https://qiita.com/m-isik/items/8fdedc4f2dacc90c8bf4)を参考）
>本当はAPIで取得時にソートすればいいんでしょうけど、ツールに実装してないため。。

#####  ②担当者(Owner)  
   Pleasanterから取得するとユーザIDとなるため、ユーザ名リストを別シートに用意、Lookupで変換します。

```vb
'G1セル
=XLOOKUP(INDIRECT("E1:E"&COUNTA(A:A)),ユーザ名!A:A,ユーザ名!B:B,"")
```

名前変換用シート
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/797d25e3-33e3-66b5-d56e-8e182c535b7d.png)
>本当はこれもAPIでPleasanterから取得・・したかったけど時間の都合で割愛

#####  ③開始日(StartTime)・完了日(CompletionTime)  
   Pleasanterから取得した形式だとExcelで扱えないので、日付形式に変換

```vb
'H1セル
=DATEVALUE(MID(SUBSTITUTE(INDIRECT("B1:B"&COUNTA(A:A)),"-","/"),1,10))

'I1セル
=DATEVALUE(MID(SUBSTITUTE(INDIRECT("C1:C"&COUNTA(A:A)),"-","/"),1,10))
```
'-'を'/'に置き換え(SUBSTITUTE関数)、年月日まで抜き出し(MID関数)、日付形式に変換(DATEVALUE関数)します。


#####  ④進捗率(ProgressRate)   
   1/100します。

```vb
'J1セル
=INDIRECT("D1:D"&COUNTA(A:A))/100
```
# 仕上げ
#### 編集シートから描画シートへ転記
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/ec72ddad-548b-777b-756e-878bc579c19a.png)
Pleasanter項目からの連動は以下の通り
Title→大分類
Owner→担当
StartTime→予定/開始
CompletionTime→予定/終了
ProgressRate→進捗率

→ この時点で、StartTime・CompletionTimeの値から、セルに色が付きました。


#### アドイン機能で稲妻描画
稲妻線描画ボタンクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/922382/e1f6dc15-fed8-d022-fb79-97fedf097431.png)
ProgoressRateの値から、イナズマ線が描画されました。
# まとめ
データはPleasanterで共有しつつ、見慣れたExcelで進捗を管理、ということができそうです。
次回はこのExcelをさらに改造して、工数管理を考えてみたいと思います。