Windows のサインインログで自動勤怠取得した CSV を Excel 勤務表に転記する

自動化の検証の一環です。
すでに Windows のサインインログから自動的に勤怠を取得するスクリプトは作成している。詳細は以下を参照されたし。

取得し保存されている CSV は SharePoint 上にあるため、これを取得して総勤務時間を取得し同じく OneDrive 上に保存されている勤怠 Excel に自動的に転記してしまおうという試みである。
ただし、注意事項がたくさんあり、特に Excel であるが Power Automate で Excel を扱うには「テーブル」を構成していないと触ることもできない。しかもテーブルはセルの結合などがだめなので既存の勤怠管理 Excel をそのまま使うにはそれなりの工夫が必要になると思われる。
当方の場合月と日付、業務内容ごとの稼働時間を記載する部分にはセルの結合がなかったため、この部分をテーブルとして指定、その際業務内容が記載されている部分はセルの結合があったため、業務内容と時間記載部分の間に行を挿入しそこをタイトル部分としてテーブルを設定し、タイトル部分の行は 0pt 設定で非表示とした。これで見た目には今までと変わらず日付や稼働時間等必要な情報部分だけをテーブルとして利用することができるようになった。ちなみにテーブルのデザインは何もデザインされてない空のデザインを作って設定することで素の Excel 表のようにしている。

前提条件

Microsoft 365 で Power Automate が使用できて、 SharePoint と OneDrive にもアクセスが可能である
SharePoint 上に csv で記録された「日付」「開始時間」「終了時間」が記録されたデータがある
- 例） 02-16,10:00:00,18:00:00
Excel でテーブルが設定された転記する勤務表がある（テーブル名は月ごとに設定し「メイン_202301」等としている）

上記条件のうち csv の形式と Excel の形式は適宜自身の環境に合わせる必要がある。 Power Automate で読み込む列数を数えて指定するだけなのでそれほど難しいことはしていない。

実装

相変わらず長いフローだが、完成形は以下のようになっている。

クリックで開く

実行条件

いつも通り開発時は「手動」にしておき、最終的に「繰り返し」に置き換えた。
基本的に毎日動作させるが、設定時刻が 13 時になっているのは Windows のサインインログを取得するスクリプトが作業が少ないお昼時間に稼働させることに由来している個別の事情だ。適宜設定されるとよろしいかと思われる。

ファイルコンテンツの取得と CSV の変数読み込み

最初に実行するのは PowerShell で取得し作成していた CSV ファイルを読み込むことだ。
「ファイルコンテンツの取得」フローを利用して保存してある SharePoint から直接 CSV ファイルを読み込んでいる。今回のフローは個人的なファイルへの処理のみで、汎用的な仕組みとはしていない。そのため読み込むファイルも自身のログファイルを直接取得している。

「変数を初期化する」フローで文字列の data 変数を用意し「ファイルコンテンツの取得」フローで取得した「本文」データを直接格納している。別途「変数を初期化する」フローを使って CSV 用の変数を用意しこちらも文字列を格納できるようにした。

次に「変数の設定」を用いて先ほど作成した CSV 変数に data 変数の内容を格納しているが、replaceで取得したデータの先頭に含まれている BOM データを取り除いている。さらにtrimで余計な最終行の空行を取り除いてこの後の処理に不具合がでないように前処理を行っている。

trim( replace( variables('data'), decodeUriComponent('%EF%BB%BF'), '' ) )

変数を用意する

いくつかの変数を用意する必要があるが、まずは行と列用に配列変数を用意した。

「変数を初期化する」フローで種類をアレイとし、行の配列には先ほど整地した CSV 変数をsplit関数を利用し改行コードで分割して配列に格納している。

split( variables('CSV'), decodeUriComponent('%0D%0A') )

他にいくつかの変数を用意しているが、以下のようになっている。

「今月分」の変数はアレイになっているが、 CSV には 1 月 1 日からずっとデータが蓄積されているため、ループでの処理を少なくするため一度当月のデータのみ引き抜くために用意している。
「年」の変数は年を文字列として取得しておくためここで「変数を初期化する」の値に

int(convertFromUtc(utcNow(),'Tokyo Standard Time','yyyy'))

として UTC から東京のタイムゾーンで年の部分だけをyyyyとフォーマットを指定し取得している。int関数で数値として取得しているが変数は文字列としている。
「今月」の変数は現在の月数を保存するための箱として用意している。
「今日」の変数は今日の日付を取得し変数に格納するが、年と同様に

int(convertFromUtc(utcNow(),'Tokyo Standard Time','dd'))

としてddとフォーマットを指定して取得している。int関数を挟んでいるため 01 は 1 として取得することになり一桁の日付は頭の 0 が取り除かれる。また、変数は種類を「整数」として設定しているが、これは後ほど足したり引いたりするための設定である。

月が替わった場合の処理

毎日実行するフローになっているが Windows から取得したデータは前日のデータであるため、このフローが実行されるのも翌日になる。毎月 1 日になると一時的に前月の処理として実行しなければ最終日のデータが抜けてしまう。
「条件」フローを利用し先ほど取得した今日の日付が 1 である場合に「今月」の変数には先月の月を指定して処理を実行する。

「今日」の変数が「 1 」の場合に先月のデータとして「今月」の変数に以下の処理を追加し

convertFromUtc(addDays(utcNow(),-1),'Tokyo Standard Time','MM')

現在の日付データからaddDays関数で -1 を実施しMMで先月の月数を設定している。
それ以外の場合は「今月」の変数に以下の処理で当月の月数を設定している。

convertFromUtc(utcNow(),'Tokyo Standard Time','MM')

Excel 処理用の変数を用意

Excel のテーブルからデータを取得したり書き込みしたりする場合の変数を用意している。「テーブル名」の変数には動的に「メイン_」をプレフィックスとして「年」と「今月」変数から処理すべきテーブル名を生成している。

「データ」変数はループで処理する際に仮の値を入れ込んだりするために用意している。

勤務時間を格納する変数を用意

実際に CSV から取得した勤務時間を格納する変数を用意している。「開始時間」変数は文字通りその日の端末起動時間が格納される。「終了時間」変数はその日の端末終了時間が格納される。

「勤務時間」変数は開始時間と終了時間から導き出した勤務時間を格納するため、計算ができるように整数として作成した。同様に「分」の変数も整数で作成してある。

「日」として変数を作っているが「今日」の変数とは違って Excel を処理している際に何日の行を処理しているかを格納するための変数である。もっとわかりやすい名前はない物か…
「プロパティ」変数は、最終的に Excel に書き込むにはオブジェクトを作成し xml のような形式でデータを用意する必要があるため、オブジェクトが格納できる変数を用意した。

CSV から今月分のみのデータを抽出する

「Apply to each」関数を利用し「行」変数に成形された CSV を投げて今月分だけ抜き出す処理を行っている。

「列」の配列に「行」から抜き出された 1 行をさらにsplit関数でカンマで区切ったデータを格納しデータを処理していく。

split(items('Apply_to_each（行）'),',')

今月であることを判定するためsubstring関数で先ほど「列」に格納した最初の変数を「今月」の変数と比較し、今月以外は処理しないことで不要な部分を排除している。

substring(variables('列')[0], 0, 2)

取り込んでいる CSV データには同じ日に再起動やサインアウト等にて一時的に端末から抜けていた場合や、帰宅後のテレワーク残業等の場合にも正確にデータが取得できるように同日でも複数のデータが記録されている場合がある。
これをまとめて当日の稼働時間を集計するため、「データ」変数に日付のデータを格納しながら集計を行う処理を追加している。少しまどろっこしい感じではあるが、 Power Automate のみで処理するには工夫をするしかなさそうだ。
まずは「データ」変数が空であることを確認し、初回のループであると判定している。

次に初回ループとしてそれぞれの変数に最初の値を格納していく処理になる。

まず「データ」変数に以下の記述で初回の日付データを格納している。

variables('列')[0]

同様に「開始時間」変数に以下の記述で初回の列の開始時間を格納している。

variables('列')[1]

「終了時間」変数も以下の記述で現在処理している初回の列の終了時間を格納している。

variables('列')[2]

最後に「勤務時間」変数に勤務時間を格納する。ticks関数を利用し開始時間データと終了時間データから経過ミリ秒数を算出、さらにsub関数で終了時間から開始時間を減算する。その数値をdiv関数にて 600000000 で除算して経過時間を分として取得している。

div(sub(ticks(variables('列')[2]),ticks(variables('列')[1])),600000000)

ループでこちらのルートを通るのは最初の 1 回目のみで、初期データとしてそれぞれの変数に格納している。 2 回目からはこの初期データを利用し、当日分データとして集計を行う。

2 回目以降のループではまず同日のデータがあるかどうかを判定している。「データ」変数に格納されたデータと以下の記述で「列」の最初の項目を比較し同日かどうかの判定を行う。

variables('列')[0]

同日の場合、勤務時間は合算し終了時間は書き換えている。

「変数の値を増やす」フローを利用し「勤務時間」変数に以下の記述で同日の勤務時間を追加している。

div(sub(ticks(variables('列')[2]),ticks(variables('列')[1])),600000000)

基本的には初回データ作成時の記述と同じだ。
「終了時間」変数も同様に上書きしている。

同日データでなかった場合、まずはそこまでに作られたデータを利用し「今月分」の配列に CSV データとして格納している。この時「データ」には現在処理中の日付、「開始時間」には処理中の日の最初の稼働開始時間、「終了時間」には処理中の日の最後の稼働終了時間が格納されている。元の CSV データにはなかった「勤務時間」が追加され、当日複数のデータがあった場合でも集計された時間が格納される。

その後、次のループに向けた現在の列のデータを各変数に再設定し次のループに移る。ここで指定している変数の記述方法も初回データ設定時と同じ記述である。

ループ終了時に「データ」変数に値の設定がなかった場合、今月分のデータがないという事なので処理そのものを終了させている。このまま続けるとデータがないため途中でエラー終了となってしまう。
例えば毎月 2 日に今月最初の処理が走る（ 1 日は前月データの最終処理が走る）ことになるが、仮に 1 日に休みだったりで今月のデータがまったくなかった場合、すべての処理が通過してしまい「データ」変数が空になる。

上記ループでは最後のデータを仮格納後、「今月分」の配列に入れないままループが終了してしまうため、最後のデータを「今月分」の配列に格納して初期データ生成が完了する。

Excel のデータを処理する変数を用意する

まずは Excel から取得した行を格納する「セル」変数をオブジェクトとして用意する。後ほどの処理で実際にどのような内容が取得できるのか説明するが、ここではまず箱を用意している。
次に「条件」変数を用意している。すべてのセルの内容を確認し、すでに数値が設定されているかどうかを判定するために使用する。

取得した行のデータから個々のセルの情報を動的に取得するため「列数」変数と「列名」変数を用意した。これらの内容を組み合わせてテーブルに設定された列名を動的に生成し処理している。

さらに取得した時間を計算するための変数「時間計算」と「計算後の時間」変数を用意している。すでに Excel のセルに時間が入っている場合に全体の勤務時間から差分を算出しなくてはならないため計算用の箱を用意した。

今月分のデータから Excel に挿入するデータを作成する

ここからApply to each関数を使用して実際に挿入するデータを生成するフローに入る。少し煩雑だが順を追って説明する。

まずはそれぞれ作成した変数を処理することから開始する。

「今月分」のデータを渡して 1 行ずつ処理していくが、ループで繰り返すためループ中で使用するいくつかの変数を事前に処理しなくてはならない。
まず「時間計算」変数はループごとに 0 にリセットする必要があるため、最初に設定している。
次にループの現在の値からsplitを用いてカンマ区切りで設定されているデータを「列」の配列に配置しなおしている。

split(items('Apply_to_each（今月）'),',')

配列に配置されたデータから勤務時間の総時間を取り出して「計算後の時間」変数に以下の記述で格納している。

int( variables('列')[3] )

同様に「日」の変数に以下の記述でsubstring関数を利用し日の部分だけ取得、int関数で数値として格納している。

int( substring( variables('列')[0], 3, 2 ) )

次に「行の取得」フローを配置し、 OneDrive から勤務表の Excel を指定、テーブルに指定しているテーブル名は「テーブル名」変数に動的に作成したテーブル名を指定している。
「キー列」には日付が入っている列名を指定しているが、私の使用している Excel ではテーブルを作る際にいろいろと工夫しているので列名の数字が大きいのは気にしなくていい。
「キー値」には対象としているキー列に日だけが入っているためintで数値として取得した「日」の変数を利用している。

取得した Excel 行のデータを一度オブジェクトとして「セル」変数に読み込んでいる。実際に読み込まれたデータは以下のようになっている。

{
  "@odata.context": "https://excelonline-jw.azconn-jw.p.azurewebsites.net/$metadata#drives('')/Files('')/Tables('%E3%83%A1%E3%82%A4%E3%83%B3_202303')/items/$entity",
  "@odata.etag": "",
  "ItemInternalId": "1",
  "列19": "1",
  "列20": "水",
  "列21": "",
  "列1": "6",
  "列2": "",
  "列3": "",
  "列4": "",
  "列5": "",
  "列6": "",
  "列7": "",
  "列8": "",
  "列9": "",
  "列10": "",
  "列11": "",
  "列12": "",
  "列13": "",
  "列14": "",
  "列15": "",
  "列16": "",
  "列17": "",
  "列18": ""
}

さらに次のループのために変数をリセットしている。

まずは上記取得した xml にあるデータのうち利用したいのは「列1」から「列18」までのため「列数」変数を 1 にリセットし、判定に使用する「条件」変数をfalseに設定している。
次に「 Do until 」フローを利用してすべてのセルの内容を確認し時間情報を取得する。今回セルの数は 18 なのでループが 18 回実行されるように設定している。

ループの中では現在の「列数」変数を利用して「列名」を作成、条件を経過後に「変数の値を増やす」フローで「列数」変数を +1 することで順次実行している。

セルの内容は「条件」フローを組み合わせて時間を取得している。まずは以下の記述で現在の列の内容が空であるかどうかempty関数で判定している。セルが空の場合特に何も実行する必要がない。

empty( variables('セル')?[variables('列名')] )

あらかじめ説明をしておくと、私の場合 1 日の総稼働時間のうちシステムに関する基本的な業務を実施している時間がほとんどのため、他の部署のサポートを行った時間以外は基本業務時間として記録している。そのため今回の Excel の場合最初の 1 列目が基本業務時間の「時」、 2 列目が「分」となっており、他のサポート時間と合わせて 1 日の業務時間（ 7 時間とか）となる。

そのため 1 、 2 列目が空でなかった場合には条件をtrueに変更している。基本業務時間がすでに入っている場合と空の場合で処理が変わることを「条件」変数で分岐させる。

3 列名以降には個別の業務に対する稼働時間が入っている。例えば 3 列目には A 業務の「時間」、 4 列目には A 業務の「分」が入っているという感じだ。そのためこれを「時間」と「分」で処理を分岐させるため「条件」フローを利用し偶数と奇数で処理を分けて行っている。

偶数と奇数の判定は以下の記述で行った。

equals( mod( int( variables('列数') ), 2 ), 0 )

「列数」変数をint関数で数値化しmod関数で 2 で除算した余りを求めている、これをequalsで 0 であるかの判定を行い条件としている。
偶数の場合「分」のセルであることが分かるため「時間計算」変数に「変数の値を増やす」フローでそのまま合算している。

int( variables('セル')?[variables('列名')] )

奇数の場合「時間」のセルであるため「時間計算」変数に分数に変換して「変数の値を増やす」フローで合算している。

mul( int( variables('セル')?[variables('列名')] ), 60 )

これで基本業務時間以外のセルにすでに入っている時間を分数として取得することができた。

次に勤務時間が 6 時間を超えた場合 60 分の休憩時間を取得しなくてはならないため、「計算後の時間」変数が 360 分以上の場合「変数の値を減らす」フローで 60 分減算している。

最後に勤務した総時間から休憩時間を差し引いた「計算後の時間」変数から、すでに Excel に記入済みの個別の業務ごとの稼働時間を集計した「時間計算」を「変数の値を減らす」フローで差し引いて Excel に記載するべき基本業務時間を割り出している。

これを「勤務時間」変数に時間として以下の記述で格納している。

int( div( variables('計算後の時間'), 60 ) )

残りの分数を「分」変数に以下の記述で格納している。

mod( variables('計算後の時間'), 60 )

ここから Excel に登録するためのプロパティを作成するために「条件」フローで分岐を作成している。

まずは稼働時間が 1 時間以内だった場合の処理となる。 Excel に登録するプロパティにもセルごとに設定する必要があるため稼働時間が 1 時間以内だった場合「時間」のセルに 0 が入ってしまうことになる。時間のセルは空欄のままにしておきたいため、分岐で処理を分けている。

次に勤務時間が 1 時間以内で 15 分以内であった場合、まるめ処理を行って 15 分固定として処理している。この場合「プロパティ」変数には「列 2 」の処理のみ記載して終わる。
16 分以上 59 分以内の場合も 15 分ごとにまるめ処理を行っている。

単純に 30 分を起点に分岐し「プロパティ」変数に固定で「列 2 」の処理のみ記載して終わる。

ひとつ戻って 1 時間以上の勤務があった場合にも同様のまるめ処理を行っている。
まずは「分数」があるかないかで処理を分岐している。「分」が 0 の場合も同様にセルを空欄としておきたいため「プロパティ」変数には「列 1 」に「勤務時間」変数を指定して処理している。

最後に「時間」も「分」もある場合の分のまるめ処理を行っている。
単純に「分」が 15 分以下の場合、まるめ処理で 15 分以下を切り捨て「プロパティ」変数には「列 1 」に「勤務時間」変数のみを指定して処理している。

次に「分」が 30 分未満かどうかを判定し、 15 分以上 30 分未満の場合にはまるめ処理を行って「プロパティ」変数の「列 1 」には「勤務時間」変数を指定、「列 2 」にはまるめた 15 分を指定して処理している。

残りの 30 分以上、 45 分以上も分岐で処理している。
45 分未満の場合「プロパティ」変数の「列 2 」にはまるめた 30 分を指定、 45 分以上の場合には「列 2 」にはまるめた 45 分を指定している。

これですべての条件と Excel に記載すべきプロパティが揃った。
すでに基本業務時間が入ってしまっている場合には上書きされないように「条件」変数を確認しセルが空でなければ Excel への記載は行わない。

「行の更新」フローを利用し「テーブル」には作成しておいた「テーブル名」変数を、「キー列」には固定で「列 19 」を指定している。「キー値」には「日」の変数を、「項目のプロパティを指定する」に「プロパティ」変数を指定すれば Excel の該当箇所が更新される。
これを今月分繰り返し必要な空のセルに数値を格納していけば Excel の更新は完了だ。