By

文章目录

一言难尽啊。
公司上线了O365，于是很多应用就搬到了Sharepoint上面，一些新的开发和需求也出现了，最普遍的就是如何更新List。
用Excel来创建一个新的list不是什么难事，但是要维护一个已有的list就有点讨厌了，公司还禁用了Sharepoint Online的外部数据，也就是说只能在原生的list里面找办法了。
网上也不是没有教程啊什么的，但是都是基于Sharepoint 2013/2016的，都是在Sharepoint服务器上跑的。我们这里是Sharepoint Online装好SDK和PowerShell 工具之后连Get_SPWeb 都没有，我还以为是我装Shell出问题了，重装过也试了好多次，后来去看了看官方文档，Sharepoint Online Shell里面压根就没有Get-SPWeb的，害我找了老半天。
当时呢这个需求也不是很迫切，反正手动删一下，然后list开datasheet view然后复制粘贴一下就行了。
不过数据量一大，问题就出来了，主要是两个方面：一来是操作慢，从Excel 拷贝到List里面调用的好像是IE的Active插件，所以不是IE不行，而且超级慢，不过数据本来也就多，全国一千多将近两千的用户信息，总得等着IE死上一会才行。第二是如果list里面设置了格式的话，每一条还要做格式检查，如果不对还要人手动来修改或者删除才会去做下一条。
所以老是让人事手动也不现实啦，还是得考虑用什么脚本什么的解决问题。

所以还是用PowerShell 吧，起码比搞C#方便一点。
重新开始，用Get_SPWeb这条路肯定是不行了，用对象的方式吧：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll" 
  
##Variables for Processing
$SiteUrl = "https://yoursite.sharepoint.com/"
$ListName="Your_List"
#$ImportFile ="C:\filepath.csv"
$UserName="youraccount"
$Password ="yourpassword"
#Get the Data from CSV and Add to SharePoint List
#Specify the path of the excel file
$FilePath = "C:\yourExcelpath.xls"
#Specify the Sheet name
$SheetName = "sheet1"

前面三个是添加Powershell Sharepoint sdk的支持，如果是装的2016版的SDK文件夹就是16，2013的SDK文件夹就是15。

后面就是一些参数了，带入的文件用Excel或者文本都可以的。

然后先来读取一下 SharePoint的lsit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
 
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $credentials
  
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)
#Delete list items
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()) 
$Context.Load($ListItems)
$Context.ExecuteQuery()       
write-host "Total Number of List Items found:"$ListItems.Count

这样就连接到list了， 这里用的是CSOM (Client Object Model) 的API是从2010开始的新的特性。基本原理是把请求打包成XML然后发给服务器，服务器把结果打包成JSON发回来，不需要保持连接。

然后可以直接删除List的Item了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
if ($ListItems.Count -gt 0)
{
    $j = 1
    $l = 0
    #Loop through each item and delete
    For ($i = $ListItems.Count-1; $i -ge 0; $i--)
    {
        $ListItems[$i].DeleteObject()
        $l ++
        $j ++
        if ($j -eq 30)
        {$Context.ExecuteQuery()
        $j =1}
    } 
    $Context.ExecuteQuery()
     
    Write-Host "All " + $l + "Items deleted Successfully!"
}

删除很简单，接下来要创建了。

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
# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false
# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
# Load the WorkSheet 'BuildSpecs'
$WorkSheet = $WorkBook.sheets.item($SheetName)
$wsRange = $WorkSheet.usedrange
$wsRows = $wsRange.rows.Count
$r = 0
$t = 0
For ($x = 2; $x -le $wsRows; $x ++) {
    try{
    #add item to List
    $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
    $Item = $List.AddItem($ListItemInfo)
    $Item["Company"] = $WorkSheet.cells.item($x,3).text
    $Item["Valmet_x0020_ID"] = $WorkSheet.cells.item($x,4).text
    $Item["Cost_x0020_Center_x0020_NO_x002e"] = $WorkSheet.cells.item($x,5).text
    $Item["Cost_x0020_Center_x0020_Name"] = $WorkSheet.cells.item($x,6).text
    $Item["Country"] = $WorkSheet.cells.item($x,2).text
    $Item["City"] = $WorkSheet.cells.item($x,7).text
    $Item["Email_x0020_address"] = $WorkSheet.cells.item($x,8).text
    if ($WorkSheet.cells.Item($x,8).text -ne "") {
    Write-Host $WorkSheet.cells.item($x,1).text "Uploaded"
    $Email = $Context.Web.SiteUsers.GetByEmail($WorkSheet.cells.item($x,8).text)
    $Item["Name"] = $Email}
    if ($WorkSheet.cells.Item($x,11).text  -ne "") {
    $Manager = $Context.Web.SiteUsers.GetByEmail($WorkSheet.cells.item($x,11).text)
    $Item["Manager"] = $Manager}
    $Item.Update()
    $Context.Load($List)
    $Context.ExecuteQuery()
    $t ++
    }
    catch {Write-Host $WorkSheet.cells.item($x,1).text + "Error"
           Write-Host "$($_.Exception.Message)" -foregroundcolor red
           $r ++}
}
$WorkBook.close()
$objExcel.Quit()

前面是打开一下Excel, 这个也不难。打算真正到创建Item的时候就有点坑了。
首先是有几列创建的时候报错说没有这个Item，我喵了个咪的，明明是有的啊，白纸黑字的Company Name写在上面的啊。
然后查了一下，创建的时候那个 $Item[“Company Name”]是不对的，应该是看浏览器里面这个Column的地址，因为SharePoint里面创建一个Column的时候可能会改名字，而浏览器里面的地址是不改的，我里面Company Name原来是Company，所以就报错了…

好吧这个坑是过去了，结果还有坑，为了方便，list里面有几个是用的Person and Group的字段。然后直接用名字填进去就报错啦，报的是格式不对的错。
这个亏得自己咬着牙咽进去，查呗，得先去抓一下用户的Object,然后才行，在Sharepoint_Server_SDK支持下挺简单的，用一下Ensureuser就行，但是现在不是没有嘛。
网上是找不出什么办法了，只能自己去查SDK了，Sharepoint Online里面这种最基础的功能不可能没有的啊。

后来还是找到了，用$Context.Web.SiteUsers.GetByEmail 这样用用户的邮件地址就能抓到Sharepoint的object的信息了。

到此就简单啦，整个过程用Try Catch包装一下，即使是过程中出问题也没关系，直接跳到下一个就行。

测试了一下挺好的，原来删除复制粘贴，IE 光卡死就得半小时，等把整个list验证完又是一个多钟头过去了，现在整个流程只要十几分钟而且完全不用人在边上看着的，找个共享文件夹让HR定期把文件扔进去，然后自动跑脚本就行了。

其实呢，研究这个还是因为有个其他的想法，现在也在玩PowerShell, 这样所有的数据，只要是能导出来的，就可以自动导入Sharepoint list，然后连接到PowerShell来进行二次分析，虽然最好是直接从源头抓数据，但是有时候的确是有一些零散的东西的，现在这样也不失为一个统一分析的办法。