フォルダ参照権しかないOutlookの予定表を抽出する【Powershell,Excel VBA】
Outlookの予定表を抽出して会議室の使用実績を取りたいのですが、それには予定表の全詳細参照権が必要です。詳細参照権がない場合は予約調整のための空き情報参照の機能が使えます。
目次
Outlookの予定表抽出
自分自身の予定表抽出なら権限があるので、詳細データまで含めて抽出できます。
OutlookではScriptを使った時と人間の目で見る時と得られる情報が違うようで、フォルダ参照権限のある予定表でもscriptでは予定表をBind（掴む）しようとする時点でエラーになってしまいます。（正確にはLimited Details,日本語Outlookだと空き時間情報、件名、場所という権限の場合）しょうがないので色々調べていたら、予定調整の時にユーザーの空き情報を調べるGetUserAvailabilityというWeb APIを使うと開始・終了時間／空き情報とともに、権限のある場合Detailで件名と場所を取ることができるということがわかりました。これはOutlookの機能ではなくサーバー側のExchangeの機能です。
ExchangeサーバーのWeb API
Exchangeサーバーは2007の頃からWeb APIが実装されていて下記のURLに設定されます。
|
1 |
"https://[Exchangeサーバーアドレス]/ews/exchange.asmx" |
office365の場合は人によってアドレスが違うようで、Exchangeサービスに対してautoDiscoverメソッドを使ってURLを引くのが確実です。が、大概下記のアドレスで動きそうな情報がHitします。
|
1 |
"https://outlook.office365.com/EWS/Exchange.asmx" |
このアドレスはSOAPのAPIとして実装されていてhttpプロトコルを使ってxmlで書いたRequestを投げるとxmlのResponseが返ってくるというものです。最近（2019）はxmlを使うSOAPよりJSONを使ったREST APIの方が主流（というか書く方もその方が書きやすい）で、Exchange/Office365もREST APIにシフトしようとしていますがまだまだ技術情報の点ではSOAP APIの方が多いのが現状です。
なので、今回このSOAP API（Exchange Web Service,EWS）を使って抽出を試してみます。
EWSを使ってOutlookの予定表を抽出する
検索したところ、下記のMicrosoftの情報がみつかりました。
Office developer center:Exchange の EWS を使用して空き時間情報を取得する
これを読むと「会議の調整を自動化するためのAPI」ということでManaged APIを使ったC#のサンプルソースとXML Queryのサンプルが載っています。
Managed APIを使ってPowerShellで作ったサンプルソースがこちら。
-UseDefaultCredentialsオプションを使うと何故かautoDiscoverでコケる。
オンプレ環境なら動くかもしれません。
驚いたことにManaged APIのWebCredentialsが平文パスワードしか受け付けないらしいので、苦肉の策で入力されたパスワードを平文に戻してます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
param( #$ExchangeUser = "User01@tenantname.mail.onmicrosoft.com" ,# Office365に接続するログインIDとパスワードです $ExchangePassword , #Password直書きで気にならない方はここに記述 $CSV_File = "UserAvailability_by_powershell.csv", # ExportFileName。 $Mailboxes = @("User01@xxx.onmicrosft.com", "User02@xxx.onmicrosft.com", "User03@xxx.onmicrosft.com"), $EWS_URL = "https://outlook.office365.com/EWS/Exchange.asmx" #$EWS_URL = "https://[OnPremissExchange.server.local]/ews/exchange.asmx" # On premiss EWS URL ) #PowerShellにOffice365のExchange Web Services Managed APIをImport $EWS_DLL = "C:\Program Files\PackageManagement\NuGet\Packages\exchange.webservices.managed.api.2.2.1.2\lib\net35\Microsoft.Exchange.WebServices.dll" Import-Module $EWS_DLL if([String]::IsNullOrEmpty($ExchangePassword)) { $PSCredential = Get-Credential -Message "ExchangeOnlineのユーザ情報を入力してください。" -UserName $ExchangeUser $ExchangeCredential = New-Object Microsoft.Exchange.WebServices.Data.WebCredentials($PSCredential.UserName.ToString(),$PSCredential.GetNetworkCredential().password.ToString()) }else{ $ExchangeCredential = New-Object Microsoft.Exchange.WebServices.Data.WebCredentials($ExchangeUser,$ExchangePassword) } #$ExchangeCredential =New-Object Microsoft.Exchange.WebServices.Data.WebCredentials($user, $pass) $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService $service.Credentials = $ExchangeCredential <# 2007以降のEXchangeは自己署名付き X509 証明書を使用して、EWS からの呼び出しを認証します。 ManagedAPIを使う場合、CallBack Method(Script Block)を指定しないとエラーを起こします。詳細は下記を参照してください。 https://docs.microsoft.com/ja-jp/exchange/client-developer/exchange-web-services/how-to-validate-a-server-certificate-for-the-ews-managed-api #> $service.AutodiscoverUrl($ExchangeUser, {$true}) <#AutoDiscoverしない場合は、バージョンも合わせないとエラーを起こします。 $ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP1 $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion) $service.Url =$EWS_URL #> $Date= (get-date).AddMonths(-1) $StartDate = Get-Date ($Date.tostring("yyyy/MM/") +"21") $EndDate = $StartDate.AddMonths(1) $drDuration = new-object Microsoft.Exchange.WebServices.Data.TimeWindow($StartDate,$EndDate) $AvailabilityOptions = new-object Microsoft.Exchange.WebServices.Data.AvailabilityOptions $AvailabilityOptions.RequestedFreeBusyView = [Microsoft.Exchange.WebServices.Data.FreeBusyViewType]::DetailedMerged $listtype = ("System.Collections.Generic.List"+'`'+"1") -as "Type" $listtype = $listtype.MakeGenericType("Microsoft.Exchange.WebServices.Data.AttendeeInfo" -as "Type") $Attendeesbatch = [Activator]::CreateInstance($listtype) foreach ($Mailbox in $Mailboxes){ $Attendee = new-object Microsoft.Exchange.WebServices.Data.AttendeeInfo($Mailbox) $Attendeesbatch.add($Attendee) } #$Attendee = new-object Microsoft.Exchange.WebServices.Data.AttendeeInfo($Mailboxes[0]) #$Attendeesbatch.add($Attendee) $availresponse =$null #初期化 $CSV =@() $availresponse = $service.GetUserAvailability($Attendeesbatch,$drDuration,[Microsoft.Exchange.WebServices.Data.AvailabilityData]::FreeBusy,$AvailabilityOptions) For ($i=0 ;$i -lt $Attendeesbatch.Count ;$i++){ #foreach($avail in $availresponse.AttendeesAvailability){ $TargetUser = $Attendeesbatch[$i].SmtpAddress $avail = $availresponse.AttendeesAvailability[$i] foreach($cvtEnt in $avail.CalendarEvents){ $Row =""| Select User,StartTime,EndTime,Subject,Location $Row.User = $TargetUser $Row.StartTime = $cvtEnt.StartTime $Row.EndTime = $cvtEnt.EndTime $Row.Subject = $cvtEnt.Details.Subject $Row.Location = $cvtEnt.Details.Location "User : " + $TargetUser "Start : " + $cvtEnt.StartTime "End : " + $cvtEnt.EndTime "Subject : " + $cvtEnt.Details.Subject "Location : " + $cvtEnt.Details.Location "" $CSV += $Row } } $CSV| Export-Csv -Path .\$CSV_File -Encoding UTF8 -NoTypeInformation |
ExcelでOutlookの予定表を抽出する
オマケ:
偉大なるOutlook研究所様に上記の内容をOutlook VBAで実現するマクロが掲載されていました。
Outlookのマクロは昨今セキュリティが厳しいので、これをExcelに移植することにしました。で、試作してみたExcel VBAがこちら。
Excelマクロの説明
先月の21日から今月の20日までの予約を抽出して、CSVとExcelのsheet1に転記する、というものです。
日付は適当に書き換えてみてください。
“フォルダ参照権しかないOutlookの予定表を抽出する【Powershell,Excel VBA】” に対して1件のコメントがあります。