One of my favorite uses of SharePoint is to keep track of information that spans multiple departments, affecting each of us in different ways. I haven’t found a better tool that creates a central place where everyone can stay on the same page.
When we get a new sales order, most of that information needs to be uploaded to various SharePoint lists that keep track of our project execution, billing status, etc. At first, I entered each new sales order by hand into multiple SharePoint lists. It was a huge waste of manpower, and it ate up an enormous amount of my time. Naturally I thought there must be a way to automate this, so after many months of digging through documentation, I have found a relatively easy way to set up a script that will do these tasks for me.
Here is a breakdown of how the script works:
1. Import the Correct Module
Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking
2. Connect to SharePoint Online
(you must be an administrator for your site collection to do this)
$Username = “username@domain.com”
$Password = Read-Host -Prompt “Please enter your password” -AsSecureString
$Site = “https://domain.sharepoint.com/sitename”
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($Site)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username,$Password)
$Context.Credentials = $Creds
$List = $Context.Web.Lists.GetByTitle(‘List Name’)
$Context.Load($List)
$Context.ExecuteQuery()
3. Translate Your Data
In our case, I’m uploading data from an excel file, so I need to open that file and get the data out of that file. The first piece of data I need is the client name, which is located on the first worksheet, in cell (1, F). This can be a little confusing because PowerShell needs two integers for the cell location. So what you have to do is replace the letters with numbers, starting with A = 1. This is not a simple translation, because if your sheet is large enough eventually you’ll get to the AA, AB, AC, sections and then what do you do? Just keep increasing the number. If Z = 26 then AA = 27, AB = 28, and so on. In the example below (1, F) is translated to (1, 6)
$filePath = Read-Host -Prompt “File path? ”
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($filePath)
$objExcel.Visible = $false
$sheet = $workbook.Sheets.Item(1)
$ClientName = $sheet.Cells.Item(1,6).text
$Title = $sheet.Cells.Item(2,6).text
4. Upload
After you get the information from excel put into variables, you can then upload that information to SharePoint
$listItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$newItem = $List.AddItem($listItemInfo)
$newItem[“Client_x0020_Name”] = $ClientName
$newItem[“Title”] = $Title
$newItem.Update()
$Context.ExecuteQuery()
5. It’s as easy as that.
You now have a new list item that contains the Client Name and Title from the excel spreadsheet
There are more advanced objects and other manipulations that you can do with SharePoint Online and PowerShell that will be coming in future blogs. I hope this helps you save a bit of time in your day.
Allison Sousa, PEI
