ExcelとPower Automateで座席管理システムを作る(手順編)

前回の記事では、フリーアドレスの座席管理システムを自作することのメリットについてお伝えしました。

今回は、ExcelとPower Automateでフリーアドレス席の座席管理システムを作る具体的な方法を紹介します。それほど込み入った作業は無いので、半日足らずで作成できます。

(※当記事の想定読者として、Power Automateについてある程度把握している方を対象としています。Power Automateで簡単なフローを作成したことがあればスムーズに読み進められると思います。)

SharePoint上に座席表Excelを作成

まずは、ユーザが自分の氏名を書き込むための座席表を、Excelで作成しましょう。

Excelブックを新規作成

前提として、ユーザ全員がアクセスできるSharePoint Onlineのサイトが用意されているものとして話を進めます(座席表として使うExcelブックはユーザ全員がアクセスして編集できることが前提条件となります)。

SharePointサイトの任意の場所に、Excelブックを新規作成してください。
ブック名は任意で構いません。ここでは例として「座席表.xlsx」とします。

座席の配置図を作成

作成した「座席表.xlsx」を開き、フロア内の座席の配置図を作成しましょう。
配置図は凝ったものである必要はなく、座席の位置関係がわかればOKなので、簡単な模式図的なイメージで大丈夫です。

Excelの各セルを一つの座席に見立てて座席の配置図を作成しましょう。
(厳密には1つのセルだと小さすぎるので、複数セルを結合して見やすい大きさにしたものを一つの座席に見立てて作成すると良いです)

座席の「日時クリア処理」を作成

続いて、座席表に入力された各人の氏名を日次で削除する処理を作成していきましょう。
(便宜上、当処理を「日次クリア処理」と呼ぶことにします)

この処理がないと、朝入力した自分の名前を、帰る際に手動で消していかなければならず、使い勝手が非常に悪くなります(たぶん、面倒くさすぎて誰も使ってくれないでしょう(笑))。

そのため、この「日次クリア処理」はフリーアドレスの座席管理システムにおいて肝となる機能といえます。

作成の大まかな流れは、
（１）Officeスクリプトでクリア処理を記述する
（２）Power Automateで（１）の処理を定期実行する
となります。

Officeスクリプトでクリア処理を記述する

まずは先ほど作成した「座席表.xlsx」の中で、クリア処理を作成します。ここではExcel Onlineに標準で用意されている「Officeスクリプト」という機能を利用します。

「座席表.xlsx」を開き、上部のリボンから「自動化」タブを選択し、「新しいスクリプト」をクリックすると、コードエディターが開きスクリプトの入力ができます。こちらに以下のスクリプトを記述してください。

function main(workbook: ExcelScript.Workbook) {
  //処理対象のシートを選択
  let selectedSheet = workbook.getWorksheet("[シート名]");
  //指定した範囲のセルの内容をクリア
  selectedSheet.getRange("[セル範囲]".clear(ExcelScript.ClearApplyTo.contnts);
}

[シート名]の部分はご自身の環境に合わせて読み替えてください。
例えばシート名が「sheet1」の場合は以下のようになります。

let selectedSheet = workbook.getWorksheet("sheet1");

[セル範囲]も同様に、ご自身の環境に合わせて読み替えてください。例えばセル範囲がA1〜M30の場合は以下のようになります。

selectedSheet.getRange("A1:M30").clear(ExceIScript.ClearApplyTo.contents);

スクリプトが記述できたら、動作確認してみましょう。[セル範囲]で指定した範囲内の任意のセルに適当に文字を入力してから、コードエディターの左上辺りの「実行」ボタンをクリックすると、スクリプトが実行されます。先ほど入力した文字が消えればスクリプトは正常に動作しています。

Power Automateでクリア処理を定期実行させる

続いて、作成したクリア処理が決まったタイミングで定期的に実行されるような仕掛けを準備します。この仕掛けはPower Automateで作成します。
さっそくPower Automateのトップ画面(https://make.powerautomate.com/)を開き、「作成」「スケジュール済みクラウドフロー」を選択しましょう。

フロー名を入力し(ここでは例として「クリア処理を日次実行」とします)、フローの実行開始日と実行間隔を指定します。

毎日の夜間帯にクリア処理を実行したいので、開始日の時間は例えば12:00AMや01:00AM等、社内の誰も使っていない時間を指定すればOKです。

繰り返し間隔は1「日」としましょう。

「作成」ボタンをクリックすると、フローの原型が作成されます。フローの画面に自動的に遷移すると「Recurrence」のステップが既に作成されており、そこへ繰り返し間隔やフローが実行される時刻を入力することで、任意の間隔・時刻にフローを実行するよう設定できます。

続いて「新しいステップ」を作成し、「コネクタとアクションを検索する」のボックスに”excel”と入力。表示されたExcelOnlineのアクションの候補の中から「スクリプトの実行」を選択します。

作成された「スクリプトの実行」のステップの中の「場所」「ドキュメントライブラリ」には「座席表.xlsx」の場所を指定し、「ファイル」欄には「座席表.xlsx」自体を指定します。ここまで指定すると、「スクリプト」欄で先ほど作成したOfficeスクリプトが自動的に候補として選択できるはずなので、それを選択します。

以上の2ステップで、指定した時間に毎日クリア処理が実行されるフローの完成です。フローを保存し、動作確認の為に手動でテストもしてみましょう。

「座席表.xlsx」に適当な文字を入力し、フローを実行して文字が消えれば問題無しです!

座席表のバックアップ処理を作成

前項までで座席管理システムの核となる部分は作り終えましたが、追加機能として「座席表.xlsx」を定期的にバックアップする機能も作成しておきましょう。座席表をバックアップしておくことで、過去のある時点に誰がどこに座っていたか、後から追跡することができるようになります。新型コロナウイルスのような感染症が再び流行した場合には、職場内で陽性者が出た時に濃厚接触者を特定するのにも活用できます。

バックアップ処理の作り方ですが、こちらもPowerAutomateを利用して作成します。作成するのは3ステップだけです。

ステップ1：新規で「スケジュール済みクラウドフロー」を作成します。
ステップ2：SharePointの「ファイルコンテンツの取得」を作成します。
ステップ3：SharePointの「ファイルの作成」を作成します。

ステップ1については日次クリア処理と同じ要領なので割愛します。

ステップ2については、「サイトのアドレス」に「座席表.xlsx」が置いてあるSharePointサイトを指定し、「ファイル識別子」に「座席表.xlsx」を指定します。ステップ3については、「サイトのアドレス」にバックアップ先のSharePointサイトを「フォルダーのバス」にバックアップ先のフォルダを指定し、「ファイル名」にアップファイル名を指定し、「ファイルコンテンツ」にステップ2で取得したファイルコンテンツを指定します。

ちなみにバックアップファイル名は重複するとエラーとなってしまうので、末尾に年月日と時刻の情報を付与する等、重複しないファイル名となるよう工夫する必要があります。
(例えば「座席表_20231020_150030.xlsx」のようなファイル名にするとか)

ファイ名の末尾に年月日と時刻の情報を付与するには、関数を組み込んであげます。

formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Tokyo Standard Time'),'yyyyMMdd_HHmmss')

「ファイル名」に「座席表」まで手で打ち込んでから、「式」をクリックして上記の関数をコピペし関数を組み込みます。関数が組み込まれたらその後ろに「.xlsx」と手で打ち込めば、「座席表_20231020_150030.xlsx」のようァイル名にすることができます(この辺り、Power Automateではアナログ入力と自動入力が混在していて最初は戸惑うところです(笑))。

フローが作成出来たら保存し、こちらも動作確認の為に手動でテストしてみましょう。実行後、バックアップ先のSharePointフォルダに「座席表_yyyymmdd_hhmmss.xlsx」の形式で座席表エクセルファイルのコピーが保存されていればOKです。

以上、フリーアドレス席の管理システムを自作する方法について紹介しました。有料のシステムを購入する前に、当記事で紹介した自作システムでも要件を満たせるか検討してみてはいかがでしょうか。