Power AutomateにてTeams のチームメンバーをExcelデータをもとに整備する方法

Teams にてチームを部や本部など大人数で使用すると、入社・退職・異動などに伴い毎月メンバーの管理を行う必要があり、運用負荷がかかります。

今回は、Excelのメンバーリストを更新するだけでチームメンバーの追加や削除を正確に行う方法を紹介します。

フローの概要
準備
フローの作成
- 対象者抽出フロー
- 整備実行フロー
実行手順
おわりに

フローの概要

今回はフローを2つ作成します。

1つ目のフローでは、Excelのメンバーリストをもとに現在のチームメンバーの差分を確認し、別のExcelに今回実行分の参加・削除対象者を抽出します。

2つ目のフローでは、抽出した参加・削除対象者のリストをもとにチームに参加・削除を実行します。

2つに分けている理由としては、メンバーリストの更新手違いなどにより参加・削除対象者が大量に出てしまうことがないかなど、最終的に目視で確認するためです。

準備

Excelファイルを２つ準備し、任意のSharePoint Onlineのドキュメントライブラリに配置します。

メンバーリスト.xlsx

メンバーリストテーブル

チームに所属すべきメンバーをすべて記載するテーブルです。必須となる情報はUPNです。

対象者リスト.xlsx

参加対象者テーブル

フローによって抽出された参加対象者情報が出力されるテーブルです。必須となる情報はUPNです。

削除対象者テーブル

フローによって抽出された削除対象者情報が出力されるテーブルです。必須となる情報はUPNです。

テーブルリセットスクリプト

対象者のテーブルをフローの実行ごとにファイルを開いて前回の情報を消すのは手間になるので、テーブルの情報をリセットするOfficeスクリプトを作成しておき、フローの中でスクリプトを実行するアクションを入れます。

スクリプトの準備は対象者リスト.xlsxをブラウザで開いて作成します。

参加対象者テーブルのリセットスクリプト

ブラウザ版Excelの「自動化」タブをクリックし、「新しいスクリプト」をクリックすると、コードエディターが開くので一度内容を削除します。
以下のスクリプトを貼り付けます。

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getWorksheet("参加対象者");
let tables = sheet.getTables();

for (var table_index in tables) {
// テーブルの行数を取得
let table_range = tables[table_index].getRowCount();
console.log(tables[table_index].getName() + "の行数");
console.log(table_range + "行");

// テーブルを削除
console.log(tables[table_index].getName() + "の行の削除");
if (table_range > 0) {
tables[table_index].deleteRowsAt(0, table_range);
console.log(tables[table_index].getName() + ":削除実行");
} else {
console.log(tables[table_index].getName() + ":何もしない");
}
}
}
名前を「参加対象者リセット」として「スクリプトを保存」をクリックして保存します。保存されたら「スクリプトの詳細」をクリックします。
「スクリプトに名前を付けて保存」をクリックし、対象者リストなどがある任意のライブラリに保存します。

削除対象者テーブルのリセットスクリプト

上記と手順は同じです。スクリプトは以下の内容を貼り付け、名前は「削除対象者リセット」とします。

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getWorksheet("削除対象者");
let tables = sheet.getTables();

for (var table_index in tables) {
// テーブルの行数を取得
let table_range = tables[table_index].getRowCount();
console.log(tables[table_index].getName() + "の行数");
console.log(table_range + "行");

// テーブルを削除
console.log(tables[table_index].getName() + "の行の削除");
if (table_range > 0) {
tables[table_index].deleteRowsAt(0, table_range);
console.log(tables[table_index].getName() + ":削除実行");
} else {
console.log(tables[table_index].getName() + ":何もしない");
}
}
}

整備実行ログ.xlsx

ログテーブル

追加・削除を実行したログを記載するテーブルです。

内容は任意ですが、いつ・だれに対して・どの作業が行われたか、わかる項目を用意しておきます。

フローの作成

対象者抽出フロー

トリガーを設定します。
都度実行となるため手動のトリガーを設定します。特に入力はいりません。
「変数を初期化」アクションを設定します。
このフローでは変数を４つ使用します。
以下は現在のチームメンバーを全員分取得し格納する配列変数です。

以下は現在のチームメンバーのUPNを１人分格納する文字列変数です。

以下はExcelのメンバーリストを全員分取得し格納する配列変数です。

以下はExcelのメンバーリストのUPNを１人分格納する文字列変数です。
「SharePoint ライブラリからスクリプトを実行する」アクションを設定します。
準備で作成したスクリプトを実行するアクションを２つ設定します。
「表内に存在する行を一覧表示」アクションを設定します。
メンバーリスト.xlsxでメンバー一覧を取得します。
「グループメンバーの一覧表示」アクションを設定します。
現在のチームメンバーの一覧を取得します。
※Teams アクションにはメンバー一覧取得アクションがないのでOffice365Groupアクションを使用します。
「配列変数に追加」アクションを設定します。
「グループメンバーの一覧表示」アクションの出力である動的アイテムの「ユーザープリンシパル名」を挿入します。挿入すると自動でApply to eachが生成されます。
「変数の設定」アクションを設定します。
「表内に存在する行を一覧表示」アクションの出力である動的アイテムの「UPN」を挿入します。挿入すると自動でApply to eachが生成されます。
「条件」アクションを設定します。
項番７で生成されたApply to each内に「条件」アクションを追加し条件式に以下の式を挿入します。ここで現在のチームメンバーにExcelのメンバーが含まれているか確認し参加対象者を抽出します。

contains(variables('Teams_Member_List'), variables('Excel_UPN'))
「表に行を追加」アクションを設定します。
項番８の条件の「はいの場合」内に「表に行を追加」アクションを追加し、対象者リストの参加対象者テーブルに「表内に存在する行を一覧表示」アクションの出力である動的アイテムの「DisplayName」「UPN」を挿入します。
「配列変数に追加」アクションを設定します。
「表内に存在する行を一覧表示」アクションの出力である動的アイテムの「UPN」を挿入します。挿入すると自動でApply to eachが生成されます。
「変数の設定」アクションを設定します。
「グループメンバーの一覧表示」アクションの出力である動的アイテムの「ユーザープリンシパル名」を挿入します。挿入すると自動でApply to eachが生成されます。
「条件」アクションを設定します。
項番11で生成されたApply to each内に「条件」アクションを追加し条件式に以下の式を挿入します。ここでExcelのメンバーにチームメンバーが含まれているか確認し削除対象者を抽出します。

contains(variables('Teams_Member_List'), variables('Excel_UPN'))
「表に行を追加」アクションを設定します。
項番８の条件の「はいの場合」内に「表に行を追加」アクションを追加し、対象者リストの参加対象者テーブルに「グループメンバーの一覧表示」アクションの出力である動的アイテムの「表示名」「ユーザープリンシパル名」を挿入します。

対象者抽出フローの作成は以上です。

整備実行フロー

トリガーを設定します。
都度実行となるため手動のトリガーを設定します。特に入力はいりません。
「表内に存在する行を一覧表示」アクションを設定します。
対象者リスト.xlsxで参加対象者一覧を取得します。
「チームにメンバーを追加する」アクションを設定します。
対象のチームを選択し、ユーザープリンシパル名には「表内に存在する行を一覧表示：参加対象者テーブル」アクションの出力である動的アイテムの「UPN」を挿入します。挿入すると自動でApply to eachが生成されます。
「表に行を追加」アクションを設定します。
項番3で生成されたApply to each内に「表に行を追加」アクションを追加し各項目を設定します。例として、日付は「yyyy-MM-dd」形式で入力されるように以下の式を挿入しています。
formatDateTime(addHours(utcNow(),9),'yyyy-MM-dd')
「表内に存在する行を一覧表示」アクションを設定します。
対象者リスト.xlsxで削除対象者一覧を取得します。
「グループからメンバーを削除する」アクションを設定します。
対象のチームを選択し、ユーザープリンシパル名には「表内に存在する行を一覧表示：削除対象者テーブル」アクションの出力である動的アイテムの「UPN」を挿入します。挿入すると自動でApply to eachが生成されます。
「表に行を追加」アクションを設定します。
項番6で生成されたApply to each内に「表に行を追加」アクションを追加し、項番4と同様に各項目を設定します。