PowerShellでWebサービスを使う
手元でExcel管理している情報の一部を、SharePointのリストにして公開することにしました。Excelからリストにするのは一回であればいいですが、定期的に手動で更新なんてやってられない。先輩に相談したらSOAPを使って通信すればできるよ、と教えてもらい、PowerShellを使って自動更新することにしました。
New-WebServiceProxyコマンドレッドを使うと、Webサービスと通信するオブジェクトを作ることができます。
SharePointの場合だとhttp://[サイトのURL]/_vti_bin/Lists.asmx?WSDLとしてやることで、SharePointリストをマネージするWebサービスの仕様がXML形式で返ってきます。
このURIを指定してオブジェクトを作ると、Get-MemberでWebサービスのメソッドが得られます。こんな感じ。
$URI = "http://[サイトのURL]/_vti_bin/Lists.asmx?WSDL" $WebServiceProxy = New-WebServiceProxy -Uri $URI -UseDefaultCredential $WebServiceProxy|Get-Member > WebServiceMethod.txt
メソッド一覧をみると、UpdateListItemsメソッドを使えばできそうです。更新対象のList IDがわかっているのであれば、Update用のXMLを作って引数にすればいいそうな。
Listの5項目をID指定して更新するXMLのサンプル($Title,$sno,$name,$dev,$useの5項目をUpdate)
$XMLStr = "<?xml version=""1.0""?>" $XMLStr += "<Batch OnError=""Continue"" ListVersion=""1"">" $XMLStr += "<Method ID=""" + $id + """ Cmd=""Update"">" $XMLStr += "<Field Name=""ID"">" + $id + "</Field>" $XMLStr += "<Field Name=""Title"">" + $title + "</Field>" $XMLStr += "<Field Name=""SNO"">" + $sno + "</Field>" $XMLStr += "<Field Name=""Name""><![CDATA[" + $name + "]]></Field>" $XMLStr += "<Field Name=""Dev""><![CDATA[" + $dev + "]]></Field>" $XMLStr += "<Field Name=""use""><![CDATA[" + $use + "]]></Field>" $XMLStr += "</Method></Batch>"
出来上がったXMLの文字列をPowerShell上のXMLオブジェクトにするのは、[XML]でキャストするだけ。楽ちん
$XML =[XML]$XMLStr
UpdateListItemsの引数はXMLnodeオブジェクトである必要があるらしいので、$XMLオブジェクトの中の更新部分のnodeを指定します。(SelectNodeメソッドを使うと、Nodelistオブジェクトに格納されてしまうようです。)
$XMLElm = $XML.SelectSingleNode("/Batch")
データの更新をしたいので、キー項目を使って、GetListItemsメソッドを使ってList Itemを取ってきてList IDを取得します。
$XMLStr = "<?xml version=""1.0""?><xmlstr>" $XMLStr += "<Query><Where><And><Eq>" $XMLStr += "<FieldRef Name=""Title"" />" $XMLStr += "<Value Type=""Text"">" + $title + "</Value>" $XMLStr += "</Eq><Eq>" $XMLStr += "<FieldRef Name=""SNO"" />" $XMLStr += "<Value Type=""Number"">" + $sno + "</Value>" $XMLStr += "</Eq></And></Where></Query>" $XMLStr += "<ViewFields><FieldRef Name=""Title"" /></ViewFields>" $XMLStr += "<QueryOptions />" $XMLStr += "</xmlstr>" $XMLElm = $XML.SelectSingleNode("/xmlstr/Query") $viewFields = $XML.SelectSingleNode("/xmlstr/ViewFields") $queryOptions = $XML.SelectSingleNode("/xmlstr/QueryOptions") $ret = $WebServiceProxy.GetListItems($LitName,$ViewName, $XMLElm, $viewFields, "1", $queryOptions, "") $id = $ret.getElementsByTagName("z:row").Item(0).getAttribute("ows_ID")
今回もとのデータはExcelなので、Excelからデータを読み出す方法は過去に記事を書いたのでこちらを参考→
PowerShellからExcelを使う
Excelのシートを$Sheetオブジェクトに入れて、Do文で一行ずつ処理するようにしたのがこちら。
##公開するデータの列番号
$タイトル = 1
$番号 =2
$名前 = 5
$所属 = 6
$利用目的 = 12
$URI = "http://[サイトのURL]/_vti_bin/Lists.asmx?WSDL"
$WebServiceProxy = New-WebServiceProxy -Uri $URI -UseDefaultCredential
$ListName ="{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}"
$ViewName ="{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}"
$Row=2
DO
{
#Excelの$Sheetオブジェクトから、更新用のデータを取得
$title = $Sheet.Cells.Item($Row,$タイトル).Text
$sno = $Sheet.Cells.Item($Row,$番号).Text #番号
$name = $Sheet.Cells.Item($Row,$名前).Text #利用者氏名
$dev = $Sheet.Cells.Item($Row,$所属).Text #利用者所属
$use = $Sheet.Cells.Item($Row,$利用目的).Text #利用目的
#キー項目を使って、一致するアイテムのIDを調べる
#QueryのXML作成
$XMLStr = "<?xml version=""1.0""?><xmlstr>"
$XMLStr += "<Query><Where><And><Eq>"
$XMLStr += "<FieldRef Name=""Title"" />"
$XMLStr += "<Value Type=""Text"">" + $title + "</Value>"
$XMLStr += "</Eq><Eq>"
$XMLStr += "<FieldRef Name=""SNO"" />"
$XMLStr += "<Value Type=""Number"">" + $sno + "</Value>"
$XMLStr += "</Eq></And></Where></Query>"
$XMLStr += "<ViewFields><FieldRef Name=""Title"" /></ViewFields>"
$XMLStr += "<QueryOptions />"
$XMLStr += "</xmlstr>"
$XMLElm = $XML.SelectSingleNode("/xmlstr/Query")
$viewFields = $XML.SelectSingleNode("/xmlstr/ViewFields")
$queryOptions = $XML.SelectSingleNode("/xmlstr/QueryOptions")
$ret = $WebServiceProxy.GetListItems($LitName,$ViewName, $XMLElm, $viewFields, "1", $queryOptions, "")
$id = $ret.getElementsByTagName("z:row").Item(0).getAttribute("ows_ID")
#リストのidが取得できたら、データを更新する
If ($id -ne 0 ){
#更新用のXML作成
$XMLStr = "<?xml version=""1.0""?>"
$XMLStr += "<Batch OnError=""Continue"" ListVersion=""1"">"
$XMLStr += "<Method ID=""" + $id + """ Cmd=""Update"">"
$XMLStr += "<Field Name=""ID"">" + $id + "</Field>"
$XMLStr += "<Field Name=""Title"">" + $title + "</Field>"
$XMLStr += "<Field Name=""SNO"">" + $sno + "</Field>"
$XMLStr += "<Field Name=""Name""><![CDATA[" + $name + "]]></Field>"
$XMLStr += "<Field Name=""Dev""><![CDATA[" + $dev + "]]></Field>"
$XMLStr += "<Field Name=""use""><![CDATA[" + $use + "]]></Field>"
$XMLStr += "</Method></Batch>"
$XML =[XML]$XMLStr
$XMLElm = $XML.SelectSingleNode("/Batch")
$ret = $WebServiceProxy.UpdateListItems($ListName, $XMLElm)
}
$Row++ } while (Row -le ($Sheet.UsedRange.rows.count))
$WebServiceProxy.dispose()
SOAPは昔はExcel VBAでも直接通信できたけど、ツールの更新自体をMicrosoftがやめてしまったそうな。
.netに注力していくって事なんでしょうね。