Microsoft Teamsのプレゼンスから部下がサボってないか監視しよう!~ PythonでMicrosoft Graph APIを使おう ~

【目次】Teamsプレゼンス可視化ツール

0. プロローグ 1. 永続的なアクセストークンの取得 2. プレゼンスの取得 3. エクセルで可視化
4. PowerAutomate Desktopで定期実行 5. Pythonで実装    

 

ダメだダメだダメだぁ!Power Automate Desktopはダメだあ!!

何がダメってまず安定しない。気が付いたら定期実行が止まっている。あと今回はエクセルに追記するフローを作りましたが、エクセルがサチる!気が付いたらエクセルのプロセスが残っていて二重では開けませんみたいなエラー画面が出て止まっている。

まあよく考えたら20分毎にエクセルを起動してデータを書き込んで閉じてをずっと繰り返してるんだからそりゃサチるかなと。

安定稼働はLinux + Pythonが一番

というわけでPower Automate DesktopはやめてPythonを使ってMicrosoft Graph APIから情報取得 + Pythonを使ってExcel Onlineへデータの追記を実装して安定稼働を目指します。

Microsoft Graph APIを使ってExcel Onlineを更新する方法

Microsoft Graph APIを使ってExcel Online = OneDriveに保存されたエクセルファイル を更新することができます。

Microsoft Graph での Excel の操作

Microsoft Graph を使用すると、OneDrive for Business、SharePoint サイト、またはグループ ドライブに保存されている Excel ブックを、Web アプリケーションやモバイル アプリケーションで読み取ったり変更したりすることができます。

データの追記は例によってエクセルのテーブルになっている必要がありますが、手順としては以下の通りです。

  1. 更新対象エクセルファイルのファイルIDを取得
  2. 対象ファイルから更に対象テーブルIDを取得
  3. ファイルIDとテーブルIDを指定してデータをPOSTメソッドで送信

こうすることでテーブルにデータが追記されていきます。

更新対象のファイルIDの取得

ファイルIDの取得はGraphエクスプローラーを使って調べます。ファイルIDを知るためのAPIエンドポイントは以下です。

GET https://graph.microsoft.com/v1.0/me/drive/root:/Path/To/presence.xlsx

id:から始まる文字列がファイルIDです。

One Driveのルートディレクトリからファイルまでをフルパスで指定すればよいだけですが、

/drive/root:

という特殊な文字列をファイルパスの途中に入れる必要があります。

更新対象のテーブルIDの取得

テーブルIDを知るためのAPIエンドポイントは以下です。

GET https://graph.microsoft.com/v1.0/me/drive/items/[file id]/workbook/tables

[file id]はファイルIDを入れます。

name: が更新対象のテーブル名。id: がテーブルIDです。

エクセル(テーブル)の更新

エクセルを更新するAPIエンドポイントは以下です。

POST https://graph.microsoft.com/v1.0/me/drive/items/[file id]/workbook/tables/[table id]/rows
Content-Type: application/json

{
    "values" : [
        [
            DATA1,
            DATA2,
        ]
    ]
}

[file id]は更新対象のファイルID、[table id]はテーブルIDを入れます。テーブルIDは {}も含めて入れます。

追記するデータはPOSTメソッドで本文にJSON形式で並べます。データの数とテーブルのカラムの数が合っていないとエラーとなるので注意が必要です。

「テレワーク対応Teamsのプレゼンスから社員が働いているか把握しちゃおうツール」Python版です。

さて準備ができたのでPythonのコードです。

#!/usr/bin/python
# coding: UTF-8

#実行時間を取得
import datetime
dt_now = datetime.datetime.now()
datenow = "{0:%Y/%m/%d}".format(dt_now)
timenow = "{0:%H:%M:%S}".format(dt_now)
weeknow = "{0:%a}".format(dt_now)

import requests
import json
import os

#各種ファイルを変数に代入
tokenfile = "./token.txt"
idsfile = "./ids.txt"
resultfile = "./result.json"

#アクセストークンの読み取り
token = open(tokenfile, "r" , encoding="utf-8-sig")
access_token = token.read().strip()
token.close

headers = {'Authorization': 'Bearer {}'.format(access_token)}

######################################
# Microsoft Graph APIからプレゼンス取得  #
######################################

#idリストの読み取り
ids = open(idsfile, "r")
idslist = json.load(ids)
ids.close

#URLをセット
url1 = "https://graph.microsoft.com/v1.0/communications/getPresencesByUserId"

#requests.postを使ってアクセスし、jsondataに格納
result1 = requests.post(url1, headers=headers, json=idslist)
jsondata1 = (result1.json())

#プレゼンスを変数に代入
p0 = (jsondata1['value'][0]['availability'])
p1 = (jsondata1['value'][1]['availability'])
p2 = (jsondata1['value'][2]['availability'])
p3 = (jsondata1['value'][3]['availability'])
p4 = (jsondata1['value'][4]['availability'])
p5 = (jsondata1['value'][5]['availability'])
p6 = (jsondata1['value'][6]['availability'])
p7 = (jsondata1['value'][7]['availability'])
p8 = (jsondata1['value'][8]['availability'])
p9 = (jsondata1['value'][9]['availability'])

#######################################
# Microsoft Graph APIを使ってExcel Onlineを更新 #
#######################################

# POSTするJSONデータをdict型で作成
dict = {
    "values" : [
        [
            f"{datenow}",
            f"{timenow}",
            f"{weeknow}",
            f"{p0}",
            f"{p1}",
            f"{p2}",
            f"{p3}",
            f"{p4}",
            f"{p5}",
            f"{p6}",
            f"{p7}",
            f"{p8}",
            f"{p9}",
            ]
        ]
    }

#URLをセット
url2 = "https://graph.microsoft.com/v1.0/me/drive/items/[file id]/workbook/tables/[table id]/rows"

#requests.postを使ってアクセスし、jsondata2に格納
result2 = requests.post(url2, headers=headers, json=dict)
jsondata2 = (result2.json())

#出力結果をファイルに保存
result = open(resultfile, "w")
json.dump(jsondata2, result)
result.close

解説

プログラム解説です。ロジックはPower Automate Desktopで行っていたものと同じで

  1. Microsoft Grap APIからTeamsプレゼンスをまとめて取得
  2. Microsoft Grap APIを使ってExcel Onlineに追記

です。

#アクセストークンの読み取り
token = open(tokenfile, "r" , encoding="utf-8-sig")
access_token = token.read().strip()
token.close

headers = {'Authorization': 'Bearer {}'.format(access_token)}

アクセストークンはこの手順で取得したものをファイルに保存し、読み取ります。”utf-8-sig”はDOM付きUTF-8の場合指定が必要です。読み取ったトークンをベアラー認証として使用します。

#idリストの読み取り
ids = open(idsfile, "r")
idslist = json.load(ids)
ids.close

Teamsプレゼンス取得対象のリストを読み取ります。リストの作り方はこの手順と同じです。

#URLをセット
url1 = "https://graph.microsoft.com/v1.0/communications/getPresencesByUserId"

#requests.postを使ってアクセスし、jsondataに格納
result1 = requests.post(url1, headers=headers, json=idslist)
jsondata1 = (result1.json())

Teamsプレゼンスを取得するMicrosoft Graph APIエンドポイントは以下です。

https://graph.microsoft.com/v1.0/communications/getPresencesByUserId

requests.postを使ってプレゼンスを取得します。

#プレゼンスを変数に代入
p0 = (jsondata1['value'][0]['availability'])
p1 = (jsondata1['value'][1]['availability'])
p2 = (jsondata1['value'][2]['availability'])
p3 = (jsondata1['value'][3]['availability'])
p4 = (jsondata1['value'][4]['availability'])
p5 = (jsondata1['value'][5]['availability'])
p6 = (jsondata1['value'][6]['availability'])
p7 = (jsondata1['value'][7]['availability'])
p8 = (jsondata1['value'][8]['availability'])
p9 = (jsondata1['value'][9]['availability'])

取得したプレゼンスを取り出します。プレゼンスはjsondata1のavailabilityに入っていますが、複数人のプレゼンスを一度に取得しているのでインデックスを指定して対象の人のプレゼンスを個別に取り出す必要があります。取り出す方法は以下のように指定します。

jsondata1[‘value’][0][‘availability’]

JSONのインデックスは0から始まるので一人目は0を指定します。

# POSTするJSONデータをdict型で作成
dict = {
    "values" : [
        [
            f"{datenow}",
            f"{timenow}",
            f"{weeknow}",
            f"{p0}",
            f"{p1}",
            f"{p2}",
            f"{p3}",
            f"{p4}",
            f"{p5}",
            f"{p6}",
            f"{p7}",
            f"{p8}",
            f"{p9}",
            ]
        ]
    }

続いてExcel Onlineへの書き込みです。初めに書き込むデータのdict型を作成します。この時、書き込むエクセルファイルのテーブルのカラムとデータの数が合っていないと書き込みできないので注意が必要です。

#URLをセット
url2 = "https://graph.microsoft.com/v1.0/me/drive/items/[file id]/workbook/tables/[table id]/rows"

#requests.postを使ってアクセスし、jsondata2に格納
result2 = requests.post(url2, headers=headers, json=dict)
jsondata2 = (result2.json())

Excel Onlineを更新するMicrosoft Graph APIエンドポイントは以下です。

https://graph.microsoft.com/v1.0/me/drive/items/[file id]/workbook/tables/[table id]/rows

[file id]、[tablel id]は実際のIDを入力します。

最後にrequests.postを使ってデータをPOSTすれば書き込み完了です。

あとはこのプログラムをCRONを使って定期実行すれば完成です。

出来上がったエクセルはこのような感じです。

社長、テレワークだからと言って別にサボってないですよ?

終わり。

 

Microsoft Teamsのプレゼンスから部下がサボってないか監視しよう!~ PowerAutomateで定期実行 ~

【目次】Teamsプレゼンス可視化ツール

0. プロローグ 1. 永続的なアクセストークンの取得 2. プレゼンスの取得 3. エクセルで可視化
4. PowerAutomate Desktopで定期実行      

 

Power Autotame Desktopを使ってTeamsのプレゼンスを取得してエクセルに追記するところまで出来ましたので、あとはフローを定期実行して自動取得させれば完成です。

Power Automate Desktop(無料版)ではフローの定期実行は出来ない

ここで二つ目の壁にぶち当たります。

Power Automate Desktop無償版と有償版のちがい

Power Automate Desktop単体でフローを実行する場合は、手動での実行しか対応していません。実行時は常にPCにサインインを行い、Power Automate Desktop上で対象のフローを選んで実行ボタンをクリック、という作業が必要となります。

有人および無人のデスクトップ フローの実行

Power Automate を通してデスクトップ フローをトリガーするには、コンピュータ または コンピュータ を使用する必要があります。 コンピュータは、デスクトップ プロセスを自動化するために使用される物理デバイスまたは仮想デバイスです。 

( ゚Д゚)ハ?

マイクロソフトの説明はそもそも日本語がよく分かりませんが、要は無料版のPower Automate Desktopではフローの定期実行、自動実行ができません。Power Automate DesktopはパソコンにログインしてPower Automate Desktopを起動してフローの実行ボタンを押さないといけないわけです。

いやいやマイクロソフトさん、Power Automate DesktopはRPA製品ですよね。単純な繰り返し作業を自動化するためのツールですのよね。にも拘らず定期的な実行ができないって、そりゃないですよ。

因みに定期実行するためには有料版のPowerAutomate Desktopに加入し、クラウド版PowerAutomateと連携することで可能となります。

Power Automate Desktop(無料版)で定期的なフローの実行する方法

しかしここで諦めてはいけません。無料版でも定期実行する方法はあります。

単純な何分(何時間)間隔の定期実行

単純な定期実行はループ条件を使って無限ループを作り、定期実行したい間隔の時間をWaitを使って待つというやり方です。

上記の例は1分毎にメッセージが表示されるフローですが、「メッセージを表示」の部分に実行させたいフローを入れれば定期実行ができるようになります。このやり方の注意点はフロー実行中の時間はカウントされないので、長いフローの場合、どんどん実行時間がずれていくことです。

毎時何時何分に実行させたい場合

正確に何時何分毎に実行させたい場合は、上記の無限ループとクラウド版Power Automateのスケジュール実行と組み合わせます。

Wait時間は59秒とします。そしてループ条件の中に更に「ファイルが存在する場合」アクションを追加し、OneDrive上のファイルを指定します。

クラウド版Power Automateのスケジュール機能を使い、定期実行させたい間隔でファイルを作成するフローを登録します。この例では毎時0分、20分、40分にフローが定期実行されます。厳密にはWaitが59秒入っているので最大59秒ずれますが、これ以上Waitを短くすると負荷が上がるのでおすすめしません。

プレゼンス取得とエクセル追記の定期実行

さて話は戻ってTeamsプレゼンス取得とエクセル追記フローの定期実行です。

これまで作った各サブフローをループ条件の中に入れてやれば完成です。取得の間隔は20分としました。

出来上がったエクセルはこのような感じです。

社長、テレワークだからと言って別にサボってないですよ?

終わり。(つづく・・・

 

Microsoft Teamsのプレゼンスから部下がサボってないか監視しよう!~ エクセルで可視化編 ~

【目次】Teamsプレゼンス可視化ツール

0. プロローグ 1. 永続的なアクセストークンの取得 2. プレゼンスの取得 3. エクセルで可視化

さて、全員分のTeamsプレゼンスが取得できましたので、エクセルに追記して可視化できるようにします。

土台となるエクセルファイルの準備

初めにプレゼンスを追記していくエクセルファイルを準備します。このようなファイルを作成します。

1行目はヘッダーです。A列は日付、B列からプレゼンスを追記していくメンバーを並べます。並べる順番は前回作ったメンバーリストと同じ順番にします。

またシート全体にこのような形で条件付き書式ルールを設定し、追記したプレゼンスに応じてセルの色を変更します。

シート名は「Dashboard」とします。

PowerAutomate Desktopでエクセルの書き込み

土台ができたのでPowerAutomate Desktopを使って各メンバーのプレゼンスの値をエクセルに追記していきます。

「Excelの起動」を選択します。

①「次のドキュメントを開く」を選択

②土台のエクセルを指定します。

「アクティブなExcelワークシートの設定」を選択します。

ワークシート名を「Dashboard」にします。

「Excelワークシートから最初の空の列や行を取得」を選択します。これは選択したシートの最初の空行を取得するフローです。これを入れることでどんどん下にプレゼンスの値を追記できるようになるわけです。最初の空行は「FirstFreeLow」という変数に格納されます。

「Excelワークシートに書き込み」を選択します。

A列には時刻を追記します。書き込む値は変数名「CurrentDateTime」、行はひとつ前のアクションで取得した変数名「FirstFreeRow」を指定します。

B列からプレゼンスを追記していきます。プレゼンスは変数名「JsonAsCustomObject」のavailabilityに入っていますが、複数人のプレゼンスを一度に取得しているのでインデックスを指定して対象の人のプレゼンスを個別に取り出す必要があります。取り出す方法は以下のように指定します。

%JsonAsCustomObject[‘value’][0][‘availability’]%

JSONのインデックスは0から始まるので一人目は0を指定します。

行は先ほどと同様、変数名「FirstFreeRow」を指定します。

二人目はこのような形。インデックスを “1” とします。あとはこのアクションを人数分繰り返すだけです。

最後は「エクセルを閉じる」を選んで

保存して終了です。

出来上がったアクションを「input_excel」という名前のサブフローとして登録します。

これでメンバー全員のTeamsプレゼンスを取得してエクセルに保存して一覧表示できるようになりました。

今回はここまで。次回は定期実行フローの作成です。