1

I am facing the following 2 issues:

  1. With following code I get the output in excel but not in the form of a table with headings. I just get an output in one column with a colon. I want the headings as ID, Title, STartDate, DueDate, AssignedTo
  2. I am able to store the values of only the last list items, but not for all the remaining lists.

Code is as follows:

Param([STRING]$Url="http://contosos.com")

#-------------------------------------------------------------
# Write the message $logMsg in the file specified by $logFile.
#-------------------------------------------------------------
function Write-Log($logMsg, $logFile, $type) {
    $date = Get-Date -format "yyyy-MM-dd HH:mm:ss"

    if ($type -eq $null) {
        $type = "info"
    }

    $msg = $date + "`t" + $type.ToUpper().Remove(3) + "`t" + $logMsg

    $color = "white"
    if ($type -eq "error") {
        $color = "red"
    }
    elseif ($type -eq "warning") {
        $color = "yellow"
    }

    Write-Host $msg -ForegroundColor $color

    if ($logFile) {
        try {
            Add-content $logFile -value $msg -ErrorAction "Stop"
        }
        catch {
            Write-Host "Unable to write to log file" -ForegroundColor Red
            Write-Host "Script execution stopped" -ForegroundColor Red
            Write-Host $_.InvocationInfo.PositionMessage -ForegroundColor Red
            write-host $_.Exception.Message -ForegroundColor Red
            exit (1)
        }
    }
}

$date = Get-Date -format yyyyMMdd
[string]$logFileName = [io.path]::GetFileNameWithoutExtension($MyInvocation.MyCommand) + "_" + $date+".log"
$logFile = Join-Path (get-location) $logFileName
if (Test-Path $logFile) {
    Remove-Item $logFile
}

try{
    if(!$Url)
    {
        Write-Host "Please specify an Url"
        exit
    }

    else
    {
        #Parameters
        Add-PSSnapin Microsoft.Sharepoint.PowerShell -ErrorAction "SilentlyContinue"[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
        $site = Get-SPSite -Identity $Url
        $root = $site.RootWeb
        $bu1web = $site.OpenWeb("Bu1")
        $listProject=$bu1web.Lists["Projects"]
        $projectitems = $listProject.items

        if($listProject -ne $null ) 
        {
            foreach($projectitem in $projectitems)
            { 
                if($projectitem["ActivitiesList"] -ne $null)
                {
                    $urlProj = $projectitem["ActivitiesList"].Split(",") 
                    #Write-Log $("Update started for project ID: "+ $projectitem["ID"]) $logFile "info"
                    #Sub Parameters
                    $ProjSiteURL= $urlProj[0]
                    $web = Get-SPWeb $ProjSiteURL
                    $ActvityList = $web.Lists["ActivityTasks"]


                    #Display all the list items in the list
                    $Activityitems = $ActvityList.items          
                    #Go through all items
                    foreach($Activityitem in $Activityitems)
                    {
                        $projectItem["ID"]
                        $Activityitem["Title"]
                        $Activityitem["StartDate"]
                        $Activityitem["DueDate"]
                        $Activityitem["AssignedTo"]
                    }
                    $output | Out-File "C:\Scripts\Powershell.csv"
                }
            }#end of foreach project list loop
        }#end of Project list null check
    }#end of else logic 
}#end of try

catch {
    Write-Log ("An exception occured during the execution. Check below for details") $Logfile "error"
    Write-Log $_.Exception.Message $Logfile "error"
    Write-Log $_.InvocationInfo.PositionMessage $Logfile "error"
}
0

Sample script to export list data with header as CSV for your reference.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the Web
$web = Get-SPWeb -identity "http://sp:12001"

#Get the Target List
$list = $web.Lists["OrderDetails"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

#Get All List items where Status is "In Progress"
$list.Items | foreach 
{
    $ExportItem = New-Object PSObject
    $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "OrderDate" -value $_["OrderDate"]
    $ExportItem | Add-Member -MemberType NoteProperty -name "CreatedBy" -value $_["Author"]
    $ExportItem | Add-Member -MemberType NoteProperty -name "Created" -value $_["Created"]

    #Add the object with property to an Array
    $ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV "C:\Lee\ListData.csv" -NoTypeInformation                       

#Dispose the web Object
$web.Dispose()
4
  • Thankyou Lee . I got the output in the form of table. But I'm not getting the each title in invidiual excel colum , instead I got it in one column with the titles. Can you help me with this Nov 20 '18 at 10:39
  • Thankyou. It worked fine. Can you even help me to loop the workflows and display their Name and status of each $Activityitem as per the code in the same way? I tried using WorkflowAssociations. It's not working. @Lee_MSFT Nov 21 '18 at 5:19
  • Workflow status column is lookup filed per my experience, try to convert the field to SPFieldLookupValue sharepointdiary.com/2013/12/…
    – Lee_MSFT
    Nov 21 '18 at 6:18
  • That does not work here Nov 21 '18 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.