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サービスのメソッドが得られます。こんな感じ。
|
1 2 3 |
$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）
|
1 2 3 4 5 6 7 8 9 10 |
$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]でキャストするだけ。楽ちん
|
1 |
$XML =[XML]$XMLStr |
UpdateListItemsの引数はXMLnodeオブジェクトである必要があるらしいので、＄XMLオブジェクトの中の更新部分のnodeを指定します。（SelectNodeメソッドを使うと、Nodelistオブジェクトに格納されてしまうようです。）
|
1 |
$XMLElm = $XML.SelectSingleNode("/Batch") |
データの更新をしたいので、キー項目を使って、GetListItemsメソッドを使ってList Itemを取ってきてList IDを取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$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 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 |
##公開するデータの列番号 $タイトル = 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に注力していくって事なんでしょうね。