【目次】Teamsプレゼンス可視化ツール
ダメだダメだダメだぁ！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 アプリケーションやモバイル アプリケーションで読み取ったり変更したりすることができます。
データの追記は例によってエクセルのテーブルになっている必要がありますが、手順としては以下の通りです。
- 更新対象エクセルファイルのファイルIDを取得
- 対象ファイルから更に対象テーブルIDを取得
- ファイル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で行っていたものと同じで
- Microsoft Grap APIからTeamsプレゼンスをまとめて取得
- 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を使って定期実行すれば完成です。
出来上がったエクセルはこのような感じです。
社長、テレワークだからと言って別にサボってないですよ？
終わり。
