PowerShell Script Collection: Automation and Solutions for Everyday Tasks

Over the years, PowerShell has proven to be an invaluable tool in simplifying complex administrative tasks, automating repetitive operations, and bridging the gap between system management and development. During my journey, I have created some scripts tailored to specific challenges.

This collection represents a snapshot of that experience - scripts that solved real-world problems, enhanced productivity, or simply helped me explore the flexibility and power of PowerShell. While some scripts were designed with a specific purpose in mind, others showcase reusable concepts that can be adapted to various situations.

Append Red Bold Text to the End of a Word Document
This script automates the task of appending bold red text to the end of a specified Word document. Utilizing the Word COM object, the script opens a specified document, moves the cursor to its end, and formats the added text with the desired styling. The script can easily be modified to iterate a directory of Word documents for appending.

The steps for this script include:

• Instantiating the Word Application: A COM object is created for Word, operating without the UI.
• Text Formatting: The script specifies font color (red), size, and bold styling.
• Appending Text: The formatted text, preceded by a new line, is added to the document’s end.
• Saving Changes: The modified document is saved before the application is closed.

This script is ideal for automating document updates, ensuring consistency, and reducing manual effort when dealing with Word files programmatically. It is a straightforward example of PowerShell’s ability to interface with desktop applications.

# Add red bold text to end of Word document

$objWord = New-Object -comobject Word.Application # instantiate word com object
$objWord.Visible = $False # do not show word ui
$text = "Text to add to the end of the Word document"
$nl = [Environment]::NewLine

$objDoc = $objWord.Documents.Open("$(filepath_docname)")
$objDoc.Activate()
$objSelection = $objWord.Selection # By default selection object will appear at the beginning of the document
$objSelection.EndKey Unit:=wdStory # move to end of word document
$objSelection.Font.Color = "255" # red
$objSelection.Font.Size = 11
$objSelection.Font.Bold = $True
$objSelection.TypeText($nl) # add carriage return to end of document
$objSelection.TypeText($text) # add the text to end of document
$objDoc.Save() # save to document
$objWord.quit() # quit word

Retrieve Active Directory Users from an OU Tree
This script connects to Active Directory, traverses a specified Organizational Unit (OU) tree, and retrieves all user objects within its scope. Leveraging the ActiveDirectory module and System.DirectoryServices, the script fetches user attributes, such as their "pre-Windows 2000 logon" name (sAMAccountName) and the parent OU's relative distinguished name (RDN). It's an efficient tool for administrators to inventory or audit user accounts across an OU hierarchy. It requires execution with an account that has the appropriate AD access rights (typically a domain service account).

# Import the ActiveDirectory PS module
# connect to AD and list AD users within a certain AD group
# Execute the PowerShell script in the context of an account that has AD access
# This is typically a domain service account


Import-Module ActiveDirectory

write-host ""
write-host "Active Directory" -foregroundcolor "Green"
write-host ""

Write-Host "Processing" 

$Root = [ADSI]"LDAP://ou=TheADOU,dc=DOMAINNAME,dc=DOMAINNAME"
$Searcher = New-Object System.DirectoryServices.DirectorySearcher
$Searcher.SearchRoot = $Root
$Searcher.PageSize = 200
$Searcher.SearchScope = "subtree"

# Filter on all user objects.
$Searcher.Filter = "(&(objectcategory=person)(objectClass=user))"
$Searcher.PropertiesToLoad.Add("distinguishedName") > $Null

$Results = $Searcher.FindAll() 
ForEach ($Result In $Results) 
{
  $DN = $Result.properties.Item("distinguishedName")

  # Bind to the user object.
  $User = [ADSI]"LDAP://$DN"

  # Retrieve the ADsPath of the parent OU/container.
  $Parent = [ADSI]$User.Parent

  # Display "pre-Windows 2000 logon" name and the RDN of the parent OU.
  "$($User.sAMAccountName): ($($Parent.ou))"
}

Iterate Through SharePoint List Items
This script connects to an on-prem SharePoint site, retrieves a specified list, and iterates through its items to display their IDs and specific field values. Using the SharePoint PowerShell snap-in, the script ensures compatibility with the SharePoint object model, making it a practical solution for administrators or developers who need to audit, report, or manipulate a SharePoint list data programmatically. It requires execution with an account that has the necessary SharePoint access rights (typically a domain service or SharePoint service account).

# Load SharePoint snap-in, reference SharePoint list and iterate list items
# Execute the PowerShell script in the context of an account that has SharePoint access
# This is typically a domain service account


# get sharepoint list items
cls

if(!(Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction:SilentlyContinue)) 
{ 
  Add-PsSnapin Microsoft.SharePoint.PowerShell 
} 

Write-Host "working..."
$web = Get-SPWeb https://sharepoint.server.comm/sites/content

Write-Host "getting list item count..."
$list = $web.Lists["TheSPList"]
$listitemsCount = $list.Items.Count
Write-Host "Items in list: " $listitemsCount " -" $list.title

$listitems = $list.Items

foreach($item in @($listitems))  
{
  write-host $item.ID - $item["ListItem: value"]
}

Write-Host "done."

Retrieve SQL Stored Procedure Results
This script executes a SQL Server stored procedure and processes its result set. It establishes a secure connection to a specified SQL Server database, invokes the stored procedure, and retrieves the results into a dataset for iteration and display. This script is particularly useful for administrators and developers automating database interactions. It requires execution with an account that has the necessary SQL access permissions (preferably a domain account and not a SQL account).

cls

Write-Host "working..."

$SQLServer = "sqlservername"
$SQLDBName = "sqldatabase"
$SqlQuery = "stored_procedure"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

foreach ($Row in $DataSet.Tables[0].Rows)
{
  #Write-Host "$($Row)"
  Write-Host "$($Row.DisplayName)"
}

Write-Host "done."

Copy and Resize SharePoint List Images to a Network Location
This script automates the process of copying image files from a SharePoint list to a specified network location and resizing them to 100x100 pixels. It integrates SharePoint snap-ins for accessing and iterating through SharePoint list items and utilizes custom logic to handle image resizing with high-quality interpolation. The script is ideal for administrators needing to process and manage SharePoint-hosted media files. It requires execution with an account that has both SharePoint and network access permissions.

# copy image files from a sharepoint list to a network location
# size the copied image files to 100px x 100px and then save


cls


# load the sharepoint stuff
function LoadSPPreReqAssemblies
{
  param()

  cls

  ## -- load reference assemply/snapin
  $snapin = Get-PSSnapin | where-object { $_.Name -eq "Microsoft.SharePoint.PowerShell" } ## load sharepoint snapin
  if ($snapin -eq $null)
    {
    write-host "Loading SharePoint PowerShell Snapin..." -foregroundcolor "Green"
    add-pssnapin Microsoft.SharePoint.PowerShell
    write-host "SharePoint PowerShell Snapin loaded." -foregroundcolor "Yellow"
    }
    else
    {
    write-host "SharePoint PowerShell Snapin already loaded." -foregroundcolor "Yellow"
    }

  #[void][system.reflection.assembly]::Loadwithpartialname("Microsoft.SharePoint") | out-null ## reference sharepoint assembly
  #[void][system.reflection.assembly]::Loadwithpartialname("Microsoft.Office.Server.Search") | out-null ## reference office server search assembly
  #[void][system.reflection.assembly]::Loadwithpartialname("Microsoft.Office.Server") | out-null ## reference office server assembly

  write-host "Prerequisite SnapIn Assemblies Loaded " -foregroundcolor "Yellow"
  $host.Runspace.ThreadOptions = "ReuseThread"
  write-host "ThreadOptions is set to ReuseThread" -foregroundcolor "Green"
}
# end pre-requisites definition


LoadSPPreReqAssemblies  # load the sharepoint pre-requisites


Function Set-ImageSize
{ # begin function

  [CmdletBinding(SupportsShouldProcess=$True, ConfirmImpact="Low")]    

  Param
  (
    [parameter(Mandatory=$true,
      ValueFromPipeline=$true,
      ValueFromPipelineByPropertyName=$true)]
    [Alias("Image")]  
    [String[]]$FullName,
    [String]$Destination = $(Get-Location),
    [Switch]$Overwrite,
    [parameter(Mandatory=$true)]
    [Int]$WidthPx,
    [parameter(Mandatory=$true)]
    [Int]$HeightPx,
    [Switch]$FixedSize,
    [Switch]$RemoveSource
  )

  Begin
  {
    [void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")
  }

  Process
  {

    Foreach($ImageFile in $FullName)
    {
        $OldImage = new-object System.Drawing.Bitmap $ImageFile
        $OldWidth = $OldImage.Width
        $OldHeight = $OldImage.Height


        if($OldWidth -lt $OldHeight)
        {
          $NewWidth = $WidthPx
          [int]$NewHeight = [Math]::Round(($NewWidth*$OldHeight)/$OldWidth)

          if($NewHeight -gt $HeightPx)
          {
            $NewHeight = $HeightPx
            [int]$NewWidth = [Math]::Round(($NewHeight*$OldWidth)/$OldHeight)
          }
        }
        else
        {
          $NewHeight = $HeightPx
          [int]$NewWidth = [Math]::Round(($NewHeight*$OldWidth)/$OldHeight)

          if($NewWidth -gt $WidthPx)
          {
            $NewWidth = $WidthPx
            [int]$NewHeight = [Math]::Round(($NewWidth*$OldHeight)/$OldWidth)
          }            
        }

        $SaveLocation = $theResultingFile

        $NewImage = new-object System.Drawing.Bitmap $NewWidth,$NewHeight

        $Graphics = [System.Drawing.Graphics]::FromImage($NewImage)
        $Graphics.InterpolationMode = [System.Drawing.Drawing2D.InterpolationMode]::HighQualityBicubic
        $Graphics.DrawImage($OldImage, 0, 0, $NewWidth, $NewHeight) 

        $ImageFormat = $OldImage.RawFormat
        $OldImage.Dispose()      
        $NewImage.Save($SaveLocation,$ImageFormat)
        $NewImage.Dispose()
        Write-Verbose "Image '$ImageFile' was resize from $($OldWidth)x$($OldHeight) to $($NewWidth)x$($NewHeight) and save in '$SaveLocation'"
    }

  } #End Process

  End{}
} # end function



# begin program main
write-host ""
write-host "Begin"


# set the target/destination unc path
# get a handle on the main sharepoint website
# reference the source document library

$destination = "\\thenetwork\unc\destination\" 

$web = Get-SPWeb -Identity "https://the_sharepoint_site.com/"
list = $web.GetList("/sp_site/content/the_sp_list_name/")

write-host $list
write-host "there are " + $list.Items.Count + " items in this document library"

# iterate through each document in
# the source document library
# and copy each document to the
# target/destination unc path
# using file binary connection
foreach($item in $list.Items)
{
  # $file = $item.File  ## this will give the full path spec (SiteCollectionImages/Staff/imagefilename.JPG)
  # $file = $item.File.Name  ## this will give just the file name (imagefilename.JPG)

  $file = $item.File # reference the current file in the document library
  $theFileName = $file.Name
  $theResultingFile = $destination+$theFileName


  # copy the file as binary
  $binary = $file.OpenBinary()
  $stream = New-Object System.IO.FileStream($destination + "/" + $file.Name), Create
  $writer = New-Object System.IO.BinaryWriter($stream)
  $writer.write($binary)
  $writer.Close()

##  write-host "copied $destination"
##  write-host $theResultingFile

  ## size the image file to 100x100
  Set-ImageSize -Image $theResultingFile -WidthPx 100 -HeightPx 100 -Verbose

}

write-host ""
write-host "Done"
exit

# end program main



# list files in a sharepoint list
cls

if(!(Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction:SilentlyContinue)) 
{ 
    Add-PsSnapin Microsoft.SharePoint.PowerShell 
} 

Write-Host "working..."
$web = Get-SPWeb https://sharepointsite.com/sites/content/list/

Write-Host "getting list item count..."
$list = $web.Lists["AffiliateCV"]
$listitemsCount = $list.Items.Count
Write-Host "Items in list: " $listitemsCount " -" $list.title

$listitems = $list.Items

foreach($item in @($listitems))  
{
  write-host $item.ID
}

Write-Host "done."

Automate PDF Printing from Directory
This script automates the process of printing all files from a specified directory to PDF using the "Microsoft Print to PDF" virtual printer. It iterates through each file, assigns a destination output folder, and handles the printing logic with custom parameters. It is ideal for printing large volumes of PDF files.

# print pdf files that are in a specific directory
$PrintPageHandler =
{
  param([object]$sender, [System.Drawing.Printing.PrintPageEventArgs]$ev)
}

function ConvertTo-PDF
{
  param($FileToPrint, $OutputFolder)

  Add-Type -AssemblyName System.Drawing
  $doc = New-Object System.Drawing.Printing.PrintDocument
  $doc.DocumentName = $FileToPrint
  $doc.PrinterSettings = new-Object System.Drawing.Printing.PrinterSettings
  $doc.PrinterSettings.PrinterName = 'Microsoft Print to PDF'
  $doc.PrinterSettings.PrintToFile = $true

  $doc.add_PrintPage($PrintPageHandler)

  Write-Host "source = " $FileToPrint -foregroundcolor "Green"
  Write-Host "destination = " $OutputFolder -foregroundcolor "Yellow"

  $doc.PrinterSettings.PrintFileName = $OutputFolder
  $doc.Print()
  Write-Host "Printing: " $OutputFolder
  $doc.Dispose()
}


cls

$TestFileFolder = "C:\DocsToPrint\"
$OutputFolder = "C:\DocsToPrint\Printed\"

$files = Get-ChildItem -Path $TestFileFolder

# Send each test file to the print job
foreach ($testFile in $files)
{
  ConvertTo-PDF $TestFileFolder$testFile $OutputFolder$testFile
}

Conclusion
PowerShell is more than just a scripting language; it is a gateway to easily automate everyday tasks and to address diverse challenges with elegant and efficient solutions.