PowerShell

Working with PowerShell Jobs

Posted on Updated on

PowerShell Jobs is very effective way to achieve faster, recently I used to work with it and provides big improvements, but it shouldn’t be used in all cases, but only when you need your Script to run in parallel threading

for example, if you you have 1000 files, and you want to run powershell script to process it, you can split your script to run for 100 file per job, and this will optimize running time by over 80%

but still PowerShell jobs is a bit tricky in executing and debugging, in this post will summarize what you really want to use PowerShell Jobs efficiently

for($i =0; $i -lt 5; $i++)
{
$job=Start-Job -Name "Job-$i" -ScriptBlock {
    Start-Sleep -s 5
    $x = 10
    $z = $x + 5
    (Get-Random -Minimum 1 -Maximum 1000).ToString()   >> "C:\temp\file.txt"
   }
}

Debug-Job -Job $job;

Debugging process is also tricky, you should be aware of few things

  • The .ps1 file must be saved before debugging
  • Write-host will not work in the job, instead use Out-file to write in a log file
  • Put break point on the Debug-Job line
  • If the job ends quickly then debug will not work, make the function run for longer, by adding “start-sleep -s 5” in the job it self, like in the above example

If you want to bypass a parameter to the Job, you can do this in many ways

  1. Pass parameter directly to the job, using $input method, like the below (Please make sure to use $input word (case sensitive), otherwise it will not work
for($i =0; $i -lt 5; $i++)
{
$job=Start-Job -Name "Job-$i" -InputObject $i -ScriptBlock {
    Start-Sleep -s 10
    $z = $input
    $input   >> "C:\temp\file.txt"
   }
}

Debug-Job -Job $job;

2. The job only takes one single input, you can send array of string as object in this input, and process it in the job

3. you can write your input in a file, and then the job read it

In general I prefer to use file with jobs, it is easier in handling like the below

for($i =0; $i -lt 5; $i++)
{
$job=Start-Job -Name "Job-$i" -InputObject $i -ScriptBlock {
    Start-Sleep -s 5
    C:\Temp\otherscript.ps1 -param1 $input

   }
}

Please leave me a comment if you face any issues with the Jobs

Regards

How to read Excel files without having Excel installed on your machine using .NET ?

Posted on Updated on

Luckily, there is a way to read from Excel files that has (*.xlsx) or (*.xls) extensions, without having Office Excel installed on your machine.

This way can be used with .NET Windows, or Webs, also can be used with PowerShell, however, I will address below how to use it with .NET only in details, and will give some guidance how to use it with PowerShell

1. Open your visual studio, and Create a new .NET Project (web app, or windows app, or other) with .NET v4.0 or more
2. Create a new Class, and lets name it “ExcelAdapter.cs”
3. Paste the blow block in “ExcelAdapter.cs”

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
using System.Globalization;

namespace ExcelReader
{
    public class ExcelAdapter
    {
        public static DataTable ReadExcel(string path, string sheetName)
        {
            var data = new DataTable();
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(path, false))
            {
                // Get the worksheet we are working with
                var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                var worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheets.First().Id);
                var worksheet = worksheetPart.Worksheet;
                var sstPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                var ssTable = sstPart.SharedStringTable;
                // Get the CellFormats for cells without defined data types
                var workbookStylesPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<WorkbookStylesPart>().First();
                var cellFormats = workbookStylesPart.Stylesheet.CellFormats;

                ExtractRowsData(data, worksheet, ssTable, cellFormats);
            }
            return data;
        }
        private static void ExtractRowsData(DataTable data, Worksheet worksheet, SharedStringTable ssTable, CellFormats cellFormats)
        {
            var columnHeaders = worksheet.Descendants<Row>().First().Descendants<Cell>().Select(c => Convert.ToString(ProcessCellValue(c, ssTable, cellFormats))).ToArray();
            var columnHeadersCellReference = worksheet.Descendants<Row>().First().Descendants<Cell>().Select(c => c.CellReference.InnerText.Replace("1", string.Empty)).ToArray();
            var spreadsheetData = from row in worksheet.Descendants<Row>()
                                  where row.RowIndex > 1
                                  select row;
            foreach (string columnHeader in columnHeaders)
            {
                data.Columns.Add(columnHeader);
            }
            foreach (var dataRow in spreadsheetData)
            {
                var newRow = data.NewRow();
                for (int i = 0; i < columnHeaders.Length; i++)
                {
                    // Find and add the correct cell to the row object
                    var cell = dataRow.Descendants<Cell>().Where(c => c.CellReference == columnHeadersCellReference[i] + dataRow.RowIndex).FirstOrDefault();
                    if (cell != null)
                        newRow[columnHeaders[i]] = ProcessCellValue(cell, ssTable, cellFormats);
                }
                if (!newRow.ItemArray.All(field =>
                {
                    string s = null;
                    if (field != null)
                        s = field.ToString();
                    return string.IsNullOrEmpty(s);
                }))
                    data.Rows.Add(newRow);
            }
        }
        // Process the valus of a cell and return a .NET value
        private static Func<Cell, SharedStringTable, CellFormats, Object> ProcessCellValue =
            (c, ssTable, cellFormats) =>
            {
                if (c.CellValue == null) return null;
                // If there is no data type, this must be a string that has been formatted as a number
                if (c.DataType == null)
                {
                    if (c.StyleIndex == null) return c.CellValue.Text.Trim();
                    var cf =
                        cellFormats.Descendants<CellFormat>()
                                   .ElementAt<CellFormat>(Convert.ToInt32(c.StyleIndex.Value));
                    if (cf.NumberFormatId >= 0 && cf.NumberFormatId <= 13) // This is a number
                        return double.Parse(c.CellValue.Text, NumberStyles.Any);
                    if (cf.NumberFormatId >= 14 && cf.NumberFormatId <= 22) // This is a date
                        return DateTime.FromOADate(Convert.ToDouble(c.CellValue.Text));
                    return c.CellValue.Text.Trim();
                }
                switch (c.DataType.Value)
                {
                    case CellValues.SharedString:
                        return ssTable.ChildElements[Convert.ToInt32(c.CellValue.Text)].InnerText.Trim();
                    case CellValues.Boolean:
                        return c.CellValue.Text == "1";
                    case CellValues.Date:
                        return DateTime.FromOADate(Convert.ToDouble(c.CellValue.Text));
                    case CellValues.Number:
                        return double.Parse(c.CellValue.Text, NumberStyles.Any);
                    default:
                        return c.CellValue != null ? c.CellValue.Text : string.Empty;
                }
            };
        public static List<string> GetSheet(string filename)
        {
            DataTable dataTable = new DataTable();
            List<string> list = new List<string>();
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
            {
                IEnumerable<Sheet> enumerable = spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>();
                foreach (Sheet current in enumerable)
                {
                    list.Add(current.Name);
                }
            }
            return list;
        }
    }
}

4. This is an open source library, To install it you need to open “Package Manage Console” in visual studio and then run this command : Install-Package DocumentFormat.OpenXml -Version 2.9.1

PackageManagerConsole2.png

5. If you cannot see the Package manage console, please show it from Tools > NuGet Package Manager > Package Manager Console

PackageManagerConsole.png

6. If you prefer to do it manually, you can search google on DocumentFormat.OpenXml, download the .dll, and use add to reference

7. Now the Document Format should be installed in your project, check the references to confirm

8. Rebuild the project, should be no errors

9. To start using the functions create a new form, or class, and start using the functions, this function will return list of sheets in the excel file

List<string> sheets = ExcelAdapter.GetSheet(@"C:\temp\file.xlsx");

10. Now we will get the data in one of the sheets, and load it to a datatable, then to DataGrid, to do this write the below

DataTable dt = ExcelAdapter.ReadExcel(@"C:\temp\file.xlsx", "Sheet1");//please be aware that Sheet1 is case sensitive!
 this.dataGridView1.DataSource = dt;
 //Please don't forget to add databind if you are using with asp.net
Grid.png

12. and you will have your Dataload as below

If you want to use it with PowerShell, it will be a little bit more complicated, you will need first to, convert this module to *.dll then reference it to your PowerShell script like in this link
https://activedirectoryfaq.com/2016/01/use-net-code-c-and-dlls-in-powershell/

Get InfoPath Usage in your SharePoint Online site using PowerShell

Posted on Updated on

I was involved recently in a project to get InfoPath usage in SharePoint Online site.
This script is useful to know How many InfoPath file is created in your Site/Tenant, it also differentiate between InfoPath path form, and InfoPath Custom list form.
This also works on SharePoint 2013 & SharePoint 2016 as it is relying on Client Object Model, but you will point to SP2013/SP2016 Client dlls


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
Function Get-SPOContext([string]$Url,[string]$UserName,[string]$Password)
{
$SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
$context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
return $context
}
$Header = "Site Url; Web Url; Base Template; List Title; Item Count; "
$UserName = "sitecollectionadmin@site.onmicrosoft.com"
$Password = Read-Host -Prompt "Password"
$Url = "https://tenant.sharepoint.com/sites/yoursite"
$context = Get-SPOContext -Url $Url -UserName $UserName -Password $Password
$site = $context.Site
$context.Load($site)
$context.ExecuteQuery()
$rootWeb = $site.RootWeb
$context.Load($rootWeb)
$context.ExecuteQuery()
$webs = $rootWeb.Webs
$context.Load($webs)
$context.ExecuteQuery()
$AllWebs= @()
$AllWebs += $rootWeb
$AllWebs += $webs
foreach ($web in $AllWebs)
{
$web.Url
$context.Load($web)
$context.ExecuteQuery()
$lists = $web.Lists;
$context.Load($lists)
$context.ExecuteQuery()
foreach ($list in $lists)
{
$context.Load($list)
$context.ExecuteQuery()
#Write-Host $list.Title $list.BaseTemplate -ForegroundColor Yellow
if($list.BaseTemplate -eq 115 )
{
Write-Host "XML InfoPath Forms " $list.Title $list.BaseTemplate -ForegroundColor Green
}
elseif ($list.BaseTemplate -ne 101)
{
$context.Load($list.ContentTypes);
$context.ExecuteQuery();
if($list.ContentTypes.Count -gt 0)
{
$context.Load($list.ContentTypes[0]);
$context.ExecuteQuery();
if ($list.ContentTypes[0].EditFormUrl.Contains("editifs.aspx"))
{
Write-Host "Custom InfoPath List :" $list.Title $list.BaseTemplate -ForegroundColor Cyan
}
}
}
}
}
$context.Dispose()

You an adjust the script to output the results to csv or excel
Please leave me a comment if you faced any issues with it
Enjoy !

How to Remove Duplicates items in SharePoint Online List and keep one by PowerShell ?

Posted on Updated on

Hello folks,

We have faced recently a critical situation with a customer:
He was running a certain PowerShell script to update a SharePoint Online list with Data, it reaches 7000 items, a certain issue occurred in the script, and he found that he as 20,000 items in the same list, 70% was duplicates.

He wants to solve the situation in a safe way with no risk, by removing duplicate items and keep the Original one.
To solve the situation I did the following, This solution can be applied on all SharePoint versions 2010, 2013, 2016, & SharePoint Online, as the script is written to work as Client Object Model.

      1. I took back-up from the List, if you use SharePoint On-premise version, you can take back-up of the site. If you are using SharePoint Online, you can use Sharegate, however this step is not mandatory.
      2. Export AllItems to Excel using the Excel action in the ribbon, and make sure the ID Column is part of the export.
      3.  Download and setup a free tool named Kutools for Excel from here , the page contains also description how to use it to mark the duplicated column.
      4. Now take of the IDs from the Excel, and paste it in a text file on your harddrive
        Ids
      5. Then run the below PowerShell Script
        Add-Type -Path "C:\Path\SPOnline\Microsoft.SharePoint.Client.dll"
        Add-Type -Path "C:\Path\SPOnline\Microsoft.SharePoint.Client.Runtime.dll"
        $siteurl = "https://tenant.sharepoint.com/sites/yourSite/"
        $UserName = "siteadmin@tenant.onmicrosoft.com"
        $SecurePassword = Read-Host -Prompt "Please enter your password" -AsSecureString
        $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $SecurePassword
        $SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
        $context = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)
        $context.Credentials = $SPOCredentials
        $web = $context.Site.RootWeb
        $context.Load($web)
        $context.Load($web.Lists)
        $context.ExecuteQuery()
        $listTitle = "List Name"
        $list = $Context.Web.Lists.GetByTitle($listTitle)
        $Context.Load($list)
        $Context.ExecuteQuery()
        $TargetSites = Get-Content "C:\temp\List-of-Ids.txt"
        foreach ($targetSite in $TargetSites)
        {
        $id=$targetSite.Split(";")[0]
        $caml = @"<View Scope="RecursiveAll"><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy><Where><Eq><FieldRef Name='ID' /><Value Type='Number'>$id</Value></Eq></Where></Query><RowLimit Paged="TRUE">1</RowLimit></View>"
        $query = new-object Microsoft.SharePoint.Client.CamlQuery
        $query.ViewXml = $caml
        $items = $list.GetItems($query)
        $Context.Load($items)
        $Context.ExecuteQuery()
        if ($items.Count -gt 0)
        {
        for ($i = $items.Count-1; $i -ge 0; $i--)
        {
        Write-Host "Deleted: " $items[$i].Id.ToString() -ForegroundColor Yellow
        }
        $Context.ExecuteQuery()
        }
        }
        #$context.Dispose()

     

    Now, the items will start to be deleted, you can change the PowerShell script to fits your need.
    This method is safe because you see exactly what do you want to delete before executing the deletion script

Apply SharePoint Online template using PnP PowerShell

Posted on

In this article we will define a template, and apply it for other SPO sites using PnP library
Please follow these steps :

    1. Set-up PnP module latest version from here https://github.com/SharePoint/PnP-PowerShell/releases
    2. Go to your SPO tenant, and create a SPO Site, you can also define (List structure, Theme, Pages, Content Types) because all of those will be part of the template.
    3. Make sure that the app catalog is activated on the tenant (There is no relation between app catalog and the tenant, but there is a bug found in January PnP package that required App catalog to be activated or a failure occurred)
    4. After having the site ready, run the below PowerShell command:
      $Creds = Get-Credential
      $SiteURL = "https://test.sharepoint.com/sites/demo1/"
      Connect-PnPOnline -Url $SiteURL -Credentials $Creds
      Get-PnPProvisioningTemplate -Out C:\temp\demo1Template.xml
    5. You should see a progress running for a while to export all settings.
    6. Please open the XML file at the provisioned path (C:\temp\demo1Template.xml), and have a look, it is also doable to do medications through the file, by adding items, or fields, but you need to be careful not to crap the schema.
    7. Now it is time to import it to the destination site, Create a destination site on SPO with a default teamsite template
    8. Apply the new template by running the below command.
      $DestUrl = "https://test.sharepoint.com/sites/destination1/"
      Connect-PnPOnline -Url $DestUrl -Credentials $Creds
      $template = Load-PnPProvisioningTemplate -Path "C:\temp\demo1Template.xml"
      Apply-PnPProvisioningTemplate -InputInstance $template

After this, refresh the destination site, and you should see the template applied.
I suggest to start first with a simple template, then apply more complexity to it. this approach should run smoothly also with SP2016, but I haven’t tried to import a template from SPO and import it to 2016 or vise versa

How to get warnings and alerts for your SharePoint Online Site storage limits ?

Posted on Updated on

SharePoint Online in Office 365 is allocated a quantity of storage that’s based on your number of users.
If you want to get a warning email when your site exceeds its limit, please run the below script using PowerShell, you need to make sure that the dlls are correctly referenced.


#Connect to tenant admin center using GA credentials
$username = ""
$password = ConvertTo-SecureString "" -AsPlainText -Force
$cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
Connect-SPOService -Url -Credential $cred
#Local variable to create and store output file
$filename = Get-Date -Format o | foreach {$_ -replace ":", ""}
$result = ""+$filename+".txt"
#SMTP and Inbox details
$smtp = ""
$from = ""
$to = ""
$subject = "Alert : PFA Site Collection Quota Usage details"
$body = "PFA quota usage details"
#Enumerating all site collections and calculating storage usage
$sites = Get-SPOSite -detailed
foreach ($site in $sites)
{
$percent = $site.StorageUsageCurrent / $site.StorageQuota * 100
$percentage = [math]::Round($percent,2)
Write-Output "$percentage % $($site.StorageUsageCurrent)kb of $($site.StorageQuota)kb $($site.url)" | Out-File $result -Append
}
#Sending email with output file as attachment
sleep 5
Send-MailMessage -SmtpServer $smtp -to $to -from $from -subject $subject -Attachments $result -body $body -Priority high

Upload file to SharePoint Online using PowerShell

Posted on

This script help you to upload your file or document to SharePoint library using PowerShell

  1. Download SharePoint Online client library from here , and install it. (If you already have it, please ignore this step)
  2. Create a folder named Temp on your C: drive, and put the Excel/CSV file in it
  3. Modify the parameters below and run the below script.


#Specify tenant admin and site URL
$User = "site.admin@tenantname.onmicrosoft.com"
$Password = "YourPassword"
$SiteURL = "https://tenantname.sharepoint.com/sites/site"
$Folder = "C:\Temp"
$DocLibName = "DocumentLibraryName"
#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files (x86)\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files (x86)\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
#Bind to site collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,(ConvertTo-SecureString $Password -AsPlainText -Force));
$Context.Credentials = $Creds
#Retrieve list
$List = $Context.Web.Lists.GetByTitle($DocLibName)
$Context.Load($List)
$Context.ExecuteQuery()
#Upload file
Foreach ($File in (dir $Folder -File))
{
$FileStream = New-Object IO.FileStream($File.FullName,[System.IO.FileMode]::Open)
$FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
$FileCreationInfo.Overwrite = $true
$FileCreationInfo.ContentStream = $FileStream
$FileCreationInfo.URL = $File
$Upload = $List.RootFolder.Files.Add($FileCreationInfo)
$Context.Load($Upload)
$Context.ExecuteQuery()
}
#Upload completed