Export outputs into a csv in PowerShell

Recently I needed to perform some basic cleanup and syncing operation in one of the SharePoint online site. In process I also need to export differnces or changes I have made to keep in a CSV. I have not found any specific documentation about doing so in PowerShell. I have put a sample script here to find such details and generate a CSV. Here I have used connection to a SharePoint list.

#Setup Credentials to connect

Write-Host -ForegroundColor Yellow "Starting utility to sync data on instance lists from template list..." (get-date).ToString('T')

$siteUrl = "https://yourserver/sites/ListABC"

$instanceListFields = "Id","Title", "Description"

$applicationRegsitryList = "List For All List Titles";

$applicationRegsitryListFields = "Id","Title"

Write-Host -ForegroundColor Yellow "Connecting to Sharepoint online services to site... " $siteUrl

Connect-PnPOnline -Url $siteUrl -UsewebLogin

Write-Host -ForegroundColor Yellow "Connected to Sharepoint online services successfully."

New-Object -TypeName System.Collections.ArrayList

$finalMessage = [System.Collections.Arraylist]@()

$clientContext = Get-PnPContext

$targetWeb = Get-PnPWeb

$countOfListsUpdated = 0;

foreach($appListItem in $applicationRegsitryList)

{  

                    try {

                        $instanceListItems = Get-PnPListItem -List $appListItem["Title"] -Fields $instanceListFields -ErrorAction Stop

                        Write-Host "Got list : "$appListItem["Title"]" Item count: "$instanceListItems.Count

                        $actionStatus = "Got list : " + $appListItem["Title"] + " Item count: " + $instanceListItems.Count

                        $message = [System.Collections.Arraylist]@()

                        $message.Add("Found");

                        $message.Add($appListItem["Title"]);

                        $message.Add($actionStatus);

                        $message.Add($appListItem["ID"]);

                        $finalMessage.Add($message);

                        }

                    catch {

                        Write-Host -ForegroundColor Red "Exception Occurred... while fetching list from title"

                        $errormsg = $_.ToString();

                        $exception = $_.Exception;

                        $actionStatus = "Could not fetch list with Title " + $errormsg + " " +  $exception;

                        $message = [System.Collections.Arraylist]@()

                        $message.Add("Not Found");

                        $message.Add($appListItem["Title"]);

                        $message.Add($actionStatus);

                        $message.Add($appListItem["ID"]);

                        $finalMessage.Add($message);

                    }

}

$holder = @()

$pName = @("Status", "ListName", "Message", "ID")

$num = 4

FOREACH($row IN $finalMessage){

    $obj = New-Object PSObject

    for($i=0;$i -lt $num ; $i++){

        $obj | Add-Member -MemberType NoteProperty -name $pName[$i] -Value $row[$i]

    }

    $holder += $obj

    $obj = $null

}

$currTime = Get-Date -UFormat %R; 

$excelFileName =  "DiffChecker_" + (get-date).ToString('d_M_y') + "_" + $currTime.Replace(":", "") + ".csv"
$holder | Export-Csv $excelFileName -NoTypeInformation
Write-Host -ForegroundColor Yellow "Completed utility to find list for each title. Total lists processed: " $countOfListsUpdated " " (get-date).ToString('T')

Logic App to send emails.

 Working on one of my project I got a requirement regarding sending emails whenever a record moved from one status to another status. After going through multiple things we decided to use logic apps and send grid APIs to send emails. We have used a very straight forward approach to get it. Simply created a a new trigger on this table to capture change on status column and then through logic app started processing it on every 5 minutes. Below I will talk about, how to setup a logic app. 

Step 1: Search for logic app in https://portal.azure.com  search bar. Create a logic app:

Step 2: Provide details as requested and click on create. Once created successfully you will find below details:

 

Step 3: Select recurrence as it’s a time based logic app to check for status and sending emails.

 

Step4: Set recurrence interval, in this case keep it 5 mins.

 

 

Step 5: Create a connection with SQL server and select your db. Also need to provide db admin details.

 

 

Step 6:  Select server name, db name and procedure.

 

Step 7: Select another procedure, which process our send email table and return rows we need to send emails.

Step 8: Search send grid and select an action as Send Email.

Step 9: As soon as you would select to ResultsTo from dynamic contents in Send email action to field. A for each loop will be auto inserted as shown in step 8.

 

Step 10: Press save and click on run.

Step 11: Go to overview section and check its executing successfully as expected.