Feedback-SharePoint Online and MS Power Automate

We often require feedback from our clients based upon completed projects. This feedback questionary usually consists multiple choice questions or rating. We will be using MS word to share questionary with users.

First step is decided questions and possible answers. We will be using following questions:

  1. Name
  2. Email
  3. Quality of work performed
  4. Overall experience
  5. Work again
  6. FeedbackStatus

For first two questions, user will be entering his name and email address. For next two questions, we will be using rating on a scale 1 to 10 (1 been least and 10 been most). For last question, we will be using three options, Yes, No and Maybe.

Let’s create above questions in SharePoint Online by going to SharePoint Online and “Site Settings”

Click on “Site Columns” under “Web Designer Galleries”

Click on “Create” to create new site column

Enter column name “QualityOfWork”, type “Choise (menu to choose from) ”, select “New Group” and enter name of group “Questions”.

Under “Additional Column Settings”, type 1 to 10 specifying 1 as least and 10 as most. Select “Radio Buttons” from “Display choice using:” and delete any text from “Default Value”.

Create column “OverallExperience” with same settings as “QualityOfWork” make sure to select “Questions” as group instead of creating new group.

Create column “WorkAgain” with same setting as “QualityOfWork” except choice will be “Yes, No and Maybe”.

Create column “FeedbackStatus” with same setting as “QualityOfWork” except choice will be “SentToClient and EmailSent” and set default value to “SentToClient”

Once created, columns will look like as under

We will create template which will be used later for content type. Open MS Word, create labels for columns mentioned at the start of blog and save it on disk.

Next is creating content type. “Site Settings” -> “Site content types” under “Web Designer Galleries”.

Click on “Create” to create new content type

Enter name “Feedback”, select “Document Content Types” from “Select parent content type from:” and select “Document” from Parent Content Type:”. Select “New group” from “Group” and enter “Questions”.

Click “Ok” and content type information page will appear

Click on “Add from existing site columns” and add columns.

First column is “Full Name”. Select “Core Contact and Calendar Columns” from “Select columns from” and add “Full Name” from “Available columns”.

Second column is “Email”. Select “Core Contact and Calendar Columns” from “Select columns from” and add “E-Mail” from “Available columns”.

Select “Questions” from “Select columns from” and add all three columns, OverallExperience, QualityOfWork and WorkAgain, from “Available columns”.

Click “Ok” and all columns are added to content type.

Remember, we created template in previous steps. Now, we attach template with content type. Click on “Advance Settings” and select “Upload a new document template” from “Document Template”. Click on “Browse…” and select template created earlier.

Next is creating document library which will be used to store feedback. “Site Setting”-> “Add an App”. Select “Document Library”, enter name “Feedback” and click “Create”

Go to “Library Settings” -> “Advanced Settings”, select “Yes” for “Allow management of content types”, select “Open in the client application” under “Opening Document in the Browser” and click “Ok”

Click on “Add from existing site content types” under “Content Types”

Select “Questions” from “Select site content types from” and “Add” Feedback” from “Available Site Content Types”

Click on “Feedback” under “Content Types”

Click on “Advanced Settings” and click on “Edit Template”. This will open template created earlier. Go to “Insert”, select “Quick Parts” -> “Document Property” and will see list of columns.

Add “Full Name”, “E-Mail”, “QualityOfWork”, “OverallExperience” and “WorkAgain” columns against respective labels

Save and close template. Click “Ok” and back to “Library Settings”. Select “All Documents” view under “Views”

Add and rearrange columns as shown below and click “Ok”

We are done with setting up feedback questionary.  Let’s create a test document. Go back to “Feedback” document library, click on “New” and select “Feedback”

Document will open in MS Word. Select values and save document.

Answers will save in columns

At this point, we can create document and store answers in columns of document libraries. Next step is to make changes at site collection level so that we can share questionary with clients.

Make sure that you are SharePoint Online Administrator. Go to SharePoint Admin Centre, select site collection which has list and click on “Sharing”

Select “Anyone Users cam share files and folders using links that don’t require sign-in” and click “Save”.

This will take time for changes to implement. Once done, do to “Feedback” document library and create a new questionary. Select questionary and “Share” from top menu in the document library

Select “Anyone with the link” from popup window. Select “SET EXPIRATION” as desired and password. Click on Apply.

Copy link or send by entering email address

Once we shared questionary with clients, we need to set up mechanism that when client replies, we get notifications. One way is set up alerts on document library. Other way is set up MS Flow workflow to send email notification when clients answers questions.

Go to “Power Automate” using M365 App launcher

Click on “My flows”

Click on “”New” and “Automated -from blank”

Enter Flow name and select “SharePoint – When a file is created or modified (properties only)”

Select site URL in “Site Address” and “Library Name”

Click on “New Step” and select “Condition”

Select “FeedbackStatus” and set value to “SentToClient”

Go to “If yes”, select “Add an action”, select “SharePoint” and select “SharePoint – Update file properties”

Select site URL in “Site Address”, select “Library Name”, select “Id” in Id column and set value to “FeedbackStatus Value” to “EmailSent”

Select “Add an action” and select “Office 365 Outlook – Send an email”

In the “To” filed, select “Created By Email”, in subject “Feedback from FullName” and in body add columns “QualityOfWork”, “OveraallExperience” and “WorkAgain”

And we are done with workflow. Set name of workflow and click on “Create flow”. Let’s test the flow by creating a feedback document in “Feedback” document library and updating values in the document. Once Feedback document is saved, will receive following email:

Also, we update “FeedbackStatus” from “SentToClient” to “EmailSent”. Don’t need to add “FeedbackStatus” column to view as this is just for testing purpose.

Leave a Comment

SharePoint Online – Site and sub sites

Number of site collections? Number of subsites in a site collection or site? Last time users made changes to site? Questions like these are raised quite often. We created a solution based on SharePoint Online list and PowerShell Script. Idea is to create SharePoint Online list and populate / synch. information of Site collection / sites using PowerShell Script.  

First step is to create SharePoint Online list. We created SharePoint Online list “Sites List”. Columns of SharePoint Online List “Sites List” is as under:

Next step is to create PowerShell script. First step is make sure that Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll at following location

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll" -ErrorAction Stop
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" -ErrorAction Stop

Next to specify values of following parameters

$userName = “XXXXXX”
$password = ConvertTo-SecureString "XXXXXXX" -AsPlainText -Force
$SPOnlineAdminURL = "XXXXXXXX"
$Global:webinfo = @()

Function below get return $context of SharePoint Online admin site and site collections

function getSiteCollectionContext($siteUrl, $siteCollectionAdmin, $securePassword)
{
$context = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($siteCollectionAdmin, $securePassword)
$context.Credentials = $credentials
return $context
}

Function below get all site collections, update $Global:webinfo and call recursive function to get desired data of all websites below each site collection

function GetSPOSites{
 
    Begin
    {
        $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password) 
    }
 
    Process
    {
        Connect-SPOService -Url $SPOnlineAdminURL -Credential $credentials
        $sites = Get-SPOSite -Limit All -Detailed       
        foreach ($site in $sites)
        {
            # If you want to remove site collection like search
            if(
            $site.Url.Equals("XXXXXX") -or
            $site.Url.Equals("XXXXXX")
            )
            {
                continue;
            }
                       
            try
            {                
                $context = getSiteCollectionContext $site.Url $username $password   
                   $web = $context.Web
                   $context.Load($web)
                   $context.ExecuteQuery()         
                   $context.Load($web.Webs)
                   $context.ExecuteQuery()
   
                $Properties = @{
 
                    Title = $site.Title
                    URL = $site.Url
                    SiteSizeLimitInMB = $site.StorageQuota
                    SiteUsageInMB = $site.StorageUsageCurrent
                    NoOfSubsites = $web.Webs.Count
                    SiteOwner = $site.Owner
                    LastContentModifiedDate = $site.LastContentModifiedDate
                    LastUserModifiedDate = $web.LastItemUserModifiedDate           
                    SiteCollection = 'Yes'
                    LastItemModifiedDate = $web.LastItemModifiedDate
                    }
           
                    $Global:webinfo += New-Object psobject -Property $properties
 
                       if ($web.Webs.Count -gt 0)
                    {           
                              getAllWebSitesRecursively $context $web
                       }       
                }
                catch
                {       
                    Write-Host $_ -ForegroundColor Red           
                }
           
        }
    }
}

We have created a recursive function to go through each site collection / web, find all websites and add desired data to $Global:webinfo  

function getAllWebSitesRecursively($context, $web){
      
       foreach($webSite in $web.Webs)
       {       
              $context.Load($webSite.Webs);
              $context.ExecuteQuery();
       
              $Properties = @{
                     Title = $webSite.Title
                     URL = $webSite.Url
                     NoOfSubsites = $webSite.Webs.Count       
                     LastUserModifiedDate = $webSite.LastItemUserModifiedDate
                     LastItemModifiedDate = $webSite.LastItemModifiedDate
                     SiteCollection = 'No'
                     SiteOwner = $webSite.Author.Title           
              }
               
        $Global:webinfo += New-Object psobject -Property $properties
 
        if ($webSite.Webs.Count -gt 0)
        {
              getAllWebSitesRecursively $context $webSite
        }
}   
}

Now it’s time to add / update to SharePoint Online list. Script below will do add / update site

function SPOSiteListSynch
{
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true,
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true)]
        [String]
        $siteURL,
        [Parameter(Mandatory=$true,
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true)]
        [String]
        $ListName
    )
 
    Begin
    {
        $dtTable = New-Object System.Data.DataTable
        $col1 = New-Object System.Data.DataColumn Title,([string])
        $col2 = New-Object System.Data.DataColumn ItemID,([string])
        $col3 = New-Object System.Data.DataColumn URL,([string])
 
        $dtTable.Columns.Add($col1);
        $dtTable.Columns.Add($col2);
        $dtTable.Columns.Add($col3);
    }
 
    Process
    {
        GetSPOSites    
       
        $context = getSiteCollectionContext $siteURL $userName $password
 
        $list = $Context.Web.Lists.GetByTitle($ListName)
        $Context.Load($list)
        $Context.ExecuteQuery()
 
        $qry = New-Object Microsoft.SharePoint.Client.CamlQuery
        $qry.ViewXml = "<View><Query><Where></Where><OrderBy></OrderBy></Query>" +
                            "<ViewFields>" +
                            "<FieldRef Name='Title' /> " +                               
                            "<FieldRef Name='URL' /> " +
                            "</ViewFields>" +
                        "</View>"
                        
        $items = $list.GetItems($qry)
        $Context.Load($items)
        $Context.ExecuteQuery()
 
        foreach($item in $items)
        {
            $row = $dtTable.NewRow()
            $row.Title = $item["Title"]
            $row.URL = $item["URL"].Url
            $row.ItemID = $item["ID"]               
            $dtTable.Rows.Add($row)
        }
 
        $TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById("AUS Eastern Standard Time")
 
        try
        {
        foreach($property in $Global:webinfo)
        {                          
            $dtrow = $dtTable.Select("URL='" + $property.URL + "'")
 
            if($dtrow.Count -eq 1)
            {   
                $spoListItem = $list.GetItemById($dtrow.ItemID)
                $spoListItem["Title"]=$property.Title
                $spoListItem["LastItemModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastItemModifiedDate, $TZ)
                $spoListItem["LastUserModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastUserModifiedDate, $TZ)
                if($property.LastContentModifiedDate){$spoListItem["LastContentModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastContentModifiedDate, $TZ)}               
                $spoListItem["NoOfSubsites"] = $property.NoOfSubsites
                $spoListItem["SiteOwner"] = $property.SiteOwner
                $spoListItem["SiteUsageInMB"] = $property.SiteUsageInMB
                $spoListItem["SiteCollection"] = if($property.SiteCollection -eq "Yes"){$true}else{$false}
                $spoListItem["URL"]= $property.URL + ", " + $property.Title                       
                $spoListItem.Update()
                $Context.ExecuteQuery()
            }
            else
            {
                $spoListItemCreationInformation = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
                $spoListItem=$list.AddItem($spoListItemCreationInformation)
                $spoListItem["Title"]=$property.Title
                $spoListItem["LastItemModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastItemModifiedDate, $TZ)
                $spoListItem["LastUserModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastUserModifiedDate, $TZ)
                if($property.LastContentModifiedDate){$spoListItem["LastContentModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastContentModifiedDate, $TZ)}
                $spoListItem["NoOfSubsites"] = $property.NoOfSubsites
                $spoListItem["SiteOwner"] = $property.SiteOwner
                $spoListItem["SiteUsageInMB"] = $property.SiteUsageInMB
                $spoListItem["SiteCollection"] = if($property.SiteCollection -eq "Yes"){$true}else{$false}
                $spoListItem["URL"]= $property.URL + ", " + $property.Title                       
                $spoListItem.Update()
                $Context.ExecuteQuery()           
            }
        }
 
        }
        catch [Exception]
        {
            Write-Host $_.Exception
        }
 
    }
}
 
SPOSiteListSynch -siteURL XXXXX -ListName 'Sites List'

Full script is as under

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll" -ErrorAction Stop
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" -ErrorAction Stop
 
$userName = “XXXXXX”
$password = ConvertTo-SecureString "XXXXXXX" -AsPlainText -Force
$SPOnlineAdminURL = "XXXXXXXX"
$Global:webinfo = @()
 
 
function getSiteCollectionContext($siteUrl, $siteCollectionAdmin, $securePassword)
{
       $context = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
    $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($siteCollectionAdmin, $securePassword) 
    $context.Credentials = $credentials
       return $context
}
 
function getAllWebSitesRecursively($context, $web){
      
       foreach($webSite in $web.Webs)
       {       
        $context.Load($webSite.Webs);
              $context.ExecuteQuery();
       
        $Properties = @{
            Title = $webSite.Title
            URL = $webSite.Url
            NoOfSubsites = $webSite.Webs.Count       
            LastUserModifiedDate = $webSite.LastItemUserModifiedDate
            LastItemModifiedDate = $webSite.LastItemModifiedDate
            SiteCollection = 'No'
            SiteOwner = $webSite.Author.Title           
        }
               
        $Global:webinfo += New-Object psobject -Property $properties
 
              if ($webSite.Webs.Count -gt 0)
        {
                     getAllWebSitesRecursively $context $webSite
              }
       }   
}
 
function GetSPOSites{
 
    Begin
    {
        $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password) 
    }
 
    Process
    {
        Connect-SPOService -Url $SPOnlineAdminURL -Credential $credentials
        $sites = Get-SPOSite -Limit All -Detailed       
        foreach ($site in $sites)
        {
            # If you want to remove site collection like search
            if(
            $site.Url.Equals("XXXXXX") -or
            $site.Url.Equals("XXXXXX")
            )
            {
                continue;
            }
                       
            try
            {                
                $context = getSiteCollectionContext $site.Url $username $password   
                   $web = $context.Web
                   $context.Load($web)
                   $context.ExecuteQuery()         
                   $context.Load($web.Webs)
                   $context.ExecuteQuery()
   
                $Properties = @{
 
                    Title = $site.Title
                    URL = $site.Url
                    SiteSizeLimitInMB = $site.StorageQuota
                    SiteUsageInMB = $site.StorageUsageCurrent
                    NoOfSubsites = $web.Webs.Count
                    SiteOwner = $site.Owner
                    LastContentModifiedDate = $site.LastContentModifiedDate
                    LastUserModifiedDate = $web.LastItemUserModifiedDate           
                    SiteCollection = 'Yes'
                    LastItemModifiedDate = $web.LastItemModifiedDate
                    }
           
                    $Global:webinfo += New-Object psobject -Property $properties
 
                       if ($web.Webs.Count -gt 0)
                    {           
                              getAllWebSitesRecursively $context $web
                       }       
                }
                catch
                {       
                    Write-Host $_ -ForegroundColor Red           
                }
           
        }
    }
}
 
function SPOSiteListSynch
{
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true,
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true)]
        [String]
        $siteURL,
        [Parameter(Mandatory=$true,
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true)]
        [String]
        $ListName
    )
 
    Begin
    {
        $dtTable = New-Object System.Data.DataTable
        $col1 = New-Object System.Data.DataColumn Title,([string])
        $col2 = New-Object System.Data.DataColumn ItemID,([string])
        $col3 = New-Object System.Data.DataColumn URL,([string])
 
        $dtTable.Columns.Add($col1);
        $dtTable.Columns.Add($col2);
        $dtTable.Columns.Add($col3);
    }
 
    Process
    {
        GetSPOSites    
       
        $context = getSiteCollectionContext $siteURL $userName $password
 
        $list = $Context.Web.Lists.GetByTitle($ListName)
        $Context.Load($list)
        $Context.ExecuteQuery()
 
        $qry = New-Object Microsoft.SharePoint.Client.CamlQuery
        $qry.ViewXml = "<View><Query><Where></Where><OrderBy></OrderBy></Query>" +
                            "<ViewFields>" +
                            "<FieldRef Name='Title' /> " +                               
                            "<FieldRef Name='URL' /> " +
                            "</ViewFields>" +
                        "</View>"
                        
        $items = $list.GetItems($qry)
        $Context.Load($items)
        $Context.ExecuteQuery()
 
        foreach($item in $items)
        {
            $row = $dtTable.NewRow()
            $row.Title = $item["Title"]
            $row.URL = $item["URL"].Url
            $row.ItemID = $item["ID"]               
            $dtTable.Rows.Add($row)
        }
 
        $TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById("AUS Eastern Standard Time")
 
        try
        {
        foreach($property in $Global:webinfo)
        {                          
            $dtrow = $dtTable.Select("URL='" + $property.URL + "'")
 
            if($dtrow.Count -eq 1)
            {   
                $spoListItem = $list.GetItemById($dtrow.ItemID)
                $spoListItem["Title"]=$property.Title
                $spoListItem["LastItemModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastItemModifiedDate, $TZ)
                $spoListItem["LastUserModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastUserModifiedDate, $TZ)
                if($property.LastContentModifiedDate){$spoListItem["LastContentModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastContentModifiedDate, $TZ)}               
                $spoListItem["NoOfSubsites"] = $property.NoOfSubsites
                $spoListItem["SiteOwner"] = $property.SiteOwner
                $spoListItem["SiteUsageInMB"] = $property.SiteUsageInMB
                $spoListItem["SiteCollection"] = if($property.SiteCollection -eq "Yes"){$true}else{$false}
                $spoListItem["URL"]= $property.URL + ", " + $property.Title                       
                $spoListItem.Update()
                $Context.ExecuteQuery()
            }
            else
            {
                $spoListItemCreationInformation = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
                $spoListItem=$list.AddItem($spoListItemCreationInformation)
                $spoListItem["Title"]=$property.Title
                $spoListItem["LastItemModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastItemModifiedDate, $TZ)
                $spoListItem["LastUserModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastUserModifiedDate, $TZ)
                if($property.LastContentModifiedDate){$spoListItem["LastContentModifiedDate"] = [System.TimeZoneInfo]::ConvertTimeFromUtc($property.LastContentModifiedDate, $TZ)}
                $spoListItem["NoOfSubsites"] = $property.NoOfSubsites
                $spoListItem["SiteOwner"] = $property.SiteOwner
                $spoListItem["SiteUsageInMB"] = $property.SiteUsageInMB
                $spoListItem["SiteCollection"] = if($property.SiteCollection -eq "Yes"){$true}else{$false}
                $spoListItem["URL"]= $property.URL + ", " + $property.Title                       
                $spoListItem.Update()
                $Context.ExecuteQuery()           
            }
        }
 
        }
        catch [Exception]
        {
            Write-Host $_.Exception
        }
 
    }
}
 
SPOSiteListSynch -siteURL XXXXX -ListName 'Sites List'

Leave a Comment

Export data from SharePoint Online List

Export data from SharePoint Online List

Exporting SharePoint list to Excel or CVS is one requirement that have been raised again and again. We approach to solution usually by opening Excel and importing data from SharePoint Online. We will go through details later but first; this approach has one huge problem and huge means huge.

Let us share a screenshot.

We have started to get data from SharePoint Online list and after about 105 minutes, 7815 rows loaded out of 32932. Do math and you will have idea how much time it will take. MS documentations states that we can have 30 million items in a list and 32932 rows are 0.1 percent of limit.

To export list with “large data”, we return to our friend PowerShell and CSOM. When list data is “huge”, we will be using PowerShell and CSOM and when list data is not “huge”, we prefer importing data to Excel.

Let’s start with importing SharePoint Online list data to Excel. Start Excel and go to select “Data” tab. Click on “Get Data” -> “From Online Services” -> “From SharePoint Online List”.

Pop up will appear with heading “SharePoint lists”. Don’t get fool with this heading and enter URL of SharePoint Online site, not URL of SharePoint Online list.

Pop up will appear connecting

List of all lists will appear

Select desired list and data will appear in preview window

 Click on “Load” and pop up will appear

Eventually, you will be able to see data from SharePoint Online list. Don’t be afraid looking at all jargon that appear in Excel. Go to “Query” tab under “Query Tools” and click on “Edit”.

“Query Editor” window will appear. Scroll horizontally and find column “FieldValuesAsText”.

Make sure “FieldValuesAsText” is selected and click on “Remove Columns” -> “Remove Other Columnes”. This will remove all columns from “Query Editor”.

Once all columns are removed, click on right corner on “FieldValuesAsText” and pop will appear.

Select columns that are required, Excel will take time to load and you will have all columns from SharePoint List along with data. Rename columns and click “Save & Load”. This will take time as Excel will update itself based on changes made and we have imported data from SharePoint Online list to Excel.

One last detail is about “Query Settings” window. This window, display all the changes performed in sequential order and can undo any change.

This approach works fine when we are working with small data size but takes a lot time with each step added in Query Settings. One way is to don’t add any step in Query Settings and try to with work with data populated in Excel from SharePoint Online list in first place. Other way is to write PowerShell script and export data from SharePoint Online list to CSV or Excel. We will be using CSV format for simplification.

First step is make sure that Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll at following location

Please specify values of following parameters and we will get context of SharePoint Online site

We will be using following CAML query which will have OOTB columns. We can add as many columns as desired

Accessing SharePoint Online site and list in script below

$secpasswd = ConvertTo-SecureString $userPassword -AsPlainText -Force
$context = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $secpasswd) 

$list = $context.Web.Lists.GetByTitle($listtitle)
$context.Load($list)
$context.ExecuteQuery()

Below is important part of script. First is $position variable which will store the current position and $itemsinfo is array of PSObject

$position = $null
$itemsinfo = @()

We will be using Do Until loop and will be terminating when $position variable is null and adding data in $itemsinfo

Do
{
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ListItemCollectionPosition = $position
    $currentCollection = $list.GetItems($qCommand)
    $context.Load($currentCollection)
    $context.ExecuteQuery()
     
    $position = $currentCollection.ListItemCollectionPosition
    
    foreach($listitem in $currentCollection)
    {
        try
        {        
        $fieldvalue = @{            
            Created = [System.TimeZoneInfo]::ConvertTimeFromUtc($listitem["Created"], $TZ)
            Modified = [System.TimeZoneInfo]::ConvertTimeFromUtc($listitem["Modified"], $TZ)
            }
        }catch
        {
            Write-Host $_ 
        }

        $itemsinfo += New-Object psobject -Property $fieldvalue
    }
}
Until($position -eq $null) 

Last step is to create CSV file

$itemsinfo | Select-Object Created, Modified | export-csv "C:\DataFromSharePointOnlineList.csv" -NoTypeInformation

Full script is as under

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Exten-sions\15\ISAPI\Microsoft.SharePoint.Client.dll" -ErrorAction Stop
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Exten-sions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" -ErrorAction Stop

$strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
$TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById($strCurrentTimeZone)

$username = "XXXXXX"  
$userPassword = "XXXXXXXXX"
$siteURL = "XXXXXXX" 
$listtitle = "XXXXXX"

$qCommand = @"
<View Scope="RecursiveAll">
    <Query>                
        <OrderBy Override='True'><FieldRef Name='Modified' /></OrderBy>
    </Query>
    <ViewFields>
        <FieldRef Name='Modified' /><FieldRef Name='Created' />
    </ViewFields>
    <RowLimit Paged="TRUE">5000</RowLimit>
</View>
"@

$secpasswd = ConvertTo-SecureString $userPassword -AsPlainText -Force
$context = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
$context.Credentials = New-Object Mi-crosoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $secpasswd)

$list = $context.Web.Lists.GetByTitle($listtitle)
$context.Load($list)
$context.ExecuteQuery()

$position = $null
$itemsinfo = @()
Do
{
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ListItemCollectionPosition = $position
    $currentCollection = $list.GetItems($qCommand)
    $context.Load($currentCollection)
    $context.ExecuteQuery()
     
    $position = $currentCollection.ListItemCollectionPosition
    
    foreach($listitem in $currentCollection)
    {
        try
        {        
        $fieldvalue = @{            
            Created = [System.TimeZoneInfo]::ConvertTimeFromUtc($listitem["Created"], $TZ)
            Modified = [Sys-tem.TimeZoneInfo]::ConvertTimeFromUtc($listitem["Modified"], $TZ)
            }
        }catch
        {
            Write-Host $_ 
        }

        $itemsinfo += New-Object psobject -Property $fieldvalue
    }
}
Until($position -eq $null)

$itemsinfo | Select-Object Created, Modified | export-csv "C:\DataFromSharePointOnlineList.csv" -NoTypeInformation


One last workaround if want to create Excel, following code will generate Excel from CSV

$excel = New-Object -ComObject excel.application
$excel.visible=$false
$excel.DisplayAlerts = $false

$reportOut = $excel.Workbooks.Add()
$wb = $excel.WorkBooks.Open("C:\DataFromSharePointOnlineList.csv")
$wb.Worksheets.Item(1).Name = "DataFromSharePointOnlineList"
$wb.Worksheets.Copy($reportOut.WorkSheets.Item(1))
$wb.Close(0)

$reportOut.worksheets.item("Sheet1").Delete()
$strdate = get-date
$filename = "C:\DataFromSharePointOnlineList.xlsx"
$reportOut.SaveAs($filename,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
$reportOut.Close(0)
$excel.Quit()


Comments (1)

Microsoft Ignite 2017 – SharePoint

Quite excited. Below are some of announcements that were made at Microsoft Ignite 2017

SharePoint 2019: Yes. Yes and Yes. Preview version will be available by mid of 2018.

Hub site: Group together related sites to share navigation and look and feel. Bring together announcements, news and activities. Making search easy and can associate team sites / communication sites.

DIP: Document Information Panel is back for Word 2016.

Threshold Limit: Predicative indexing will automatically index columns once increased 5000 items. Yes, we are getting rid of paging and contents will load as scroll.

Columns Formatters: Script to format columns.

Custom Forms with Power Apps: Not sure how much powerful but we can built custom forms using power apps as previously accomplished by InfoPath or code.

Flow Launch Panel: This is existing. A form to get input when starting workflow using Flow.

Attention View: View that list items with missing required columns.

New Admin Center: As usual, a new admin center to learn and find desired info.

Migration Tool: This is unexpected. Free of cost migration tool to migrate data from File Server, SharePoint On-Premises and CSV file to SharePoint Online. Migration tool can be downloaded from @ https://hrcppestorageprod.blob.core.windows.net/migrationtool/default.htm

Session Timeout Policies: Enables to set up policies to sign out a session after a certain time of inactivity.

Device Access Policies: Enables to set up policies on site collection level to limit access of devices.

Geo-Capabilities: Good news for government sector to meet data residency requirements. A single O365 tenant can span to multiple regions based upon user or site.

Shorter Url: Shorter Url for file sharing.

Sharing Files: Share file with people outside organizations using simple email based verification.

O365 Groups and Existing sites: Existing sites can be connected with O365 groups.

MS Teams: Can open pages of SharePoint sites in MS Teams.

New Web Part: Yammer web parts, file viewer web parts, Planner and connector web part.

 

 

Leave a Comment

SharePoint Online – List of Sites & Webs

Scenario:

Need to create a PowerShell script that iterates through all SharePoint Online site collections and webs and generate info. regarding site collection size, last time site/web update and other.

Resolution:

Tweaked script by Jose Quinto @ https://blog.josequinto.com/2016/03/17/using-powershell-to-retrieve-all-sites-web-object-recursively-from-sharepoint-online/.

Output is two CSV files in temp folder on C drive root. One with the details and other with site collection on which account gets 401 error.

$Global:webinfo = @()

function AuthenticateUserProfile($siteUrl, $tenantAdmin, $secureAdminPassword)
{
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($tenantAdmin, $secureAdminPassword)
$ctx.Credentials = $credentials
return $ctx
}

function RecursiveWebs($ctx, $web){

foreach($w in $web.Webs)
{
$ctx.Load($w.Webs);
$ctx.ExecuteQuery();

$Properties = @{
Title = $w.Title
URL = $w.Url
NoOfSubsites = $w.Webs.Count
LastUserModifiedDate = $w.LastItemUserModifiedDate
LastItemModifiedDate = $w.LastItemModifiedDate
SiteCollection = ‘No’
SiteOwner = $w.Author.Title
}

$Global:webinfo += New-Object psobject -Property $properties

if ($w.Webs.Count -gt 0)
{
RecursiveWebs $ctx $w
}
}
}

$username = “adm@test.com”
$password = ConvertTo-SecureString “PASSWORD” -AsPlainText -Force
$O365Credential = New-Object System.Management.Automation.PsCredential($username, $password)

#SharePoint Online Admin site URL
Connect-SPOService -Url https://test-admin.sharepoint.com -Credential $O365Credential
$sites = Get-SPOSite -Limit All -Detailed

foreach ($site in $sites)
{
if(
$site.Url.Equals(“https://test.sharepoint.com/sites/new&#8221;) -or
$site.Url.Equals(“https://test.sharepoint.com/search&#8221;)
)
{
continue;
}

Write-Host $site.Url
try
{
$ctx = AuthenticateUserProfile $site.Url $username $password;
$web = $ctx.Web
$ctx.Load($web)
$ctx.ExecuteQuery()
$ctx.Load($web.Webs)
$ctx.ExecuteQuery()

$Properties = @{

Title = $site.Title
URL = $site.Url
SiteSizeLimitInMB = $site.StorageQuota
SiteUsageInMB = $site.StorageUsageCurrent
NoOfSubsites = $web.Webs.Count
SiteOwner = $site.Owner
LastContentModifiedDate = $site.LastContentModifiedDate
LastUserModifiedDate = $web.LastItemUserModifiedDate
SiteCollection = ‘Yes’
LastItemModifiedDate = $web.LastItemModifiedDate
}

$Global:webinfo += New-Object psobject -Property $properties

if ($web.Webs.Count -gt 0)
{
RecursiveWebs $ctx $web
}
}
catch
{
Write-Host $_ -ForegroundColor Red
$Properties1 = @{

Title = $site.Title
URL = $site.Url

}

$site401 += New-Object psobject -Property $properties1

}

}

$webinfo | Select-Object Title, URL, SiteSizeLimitInMB, SiteUsageInMB, LastItemModifiedDate,
LastUserModifiedDate, LastContentModifiedDate, NoOfSubsites,
SiteOwner, SiteCollection | Export-Csv -notypeinformation -Path ‘C:\temp\SPOinfo.csv’

$site401 | Select-Object Title, URL | Export-Csv -notypeinformation -Path ‘C:\temp\SPOinfo401.csv’

 

Comments (2)

SharePoint Online – Synch is not working

Scenario:

Installed Office 2016 on computers and unable to Synch document libraries

Resolution:

First check the version of OneDrive. Please make sure when press synch, exe should be running OneDrive.exe not groove.exe. If OneDrive.exe is running, please make sure OneDrive sync client must be version 17.3.6674.1021 or higher. If not, please follow steps:

  1. Download and install the preview build of the new OneDrive sync client.
  2. Download and open TeamSiteSyncPreview.reg to enable SharePoint document library sync.
  3. Restart computer
  4. Use Chrome to Synch document library.

Source:

https://support.office.com/en-us/article/Transition-from-the-previous-OneDrive-for-Business-sync-client-for-SharePoint-sites-887b9846-2192-4511-9311-de562ef64076

https://support.office.com/en-us/article/Get-started-syncing-SharePoint-sites-with-the-new-OneDrive-sync-client-Preview-6de9ede8-5b6e-4503-80b2-6190f3354a88?ui=en-US&rs=en-US&ad=US

Comments (1)

SharePoint: You cannot deploy this solution on this type of server computer as it is configured. This solution must be installed on a server of type: front-end Web server

Scenario:

We tried to deploy wsp via Visual Studio 2013 on our development server, deployment stopped with following error:

You cannot deploy this solution on this type of server computer as it is configured. This solution must be installed on a server of type: front-end Web server

We tried using PowerShell, but it hangs at “Deploying”. Tried solution like adding “loopback check” but it didn’t work in case of wsp deployment.

Resolution:

Finally, disconnecting and connecting configuration database did the trick but at the cost of creating new CA.

Steps are:

  1. Run cmdlet to disconnect configuration database
  • Disconnect-SPConfigurationDatabase –Confirm:$false
  1. Close PowerShell window and open new PowerShell window
  2. Run cmdlet to connect configuration
  • $passphrase = ConvertTo-SecureString -String “sharepoint@2013” -asPlainText -Force
  • Connect-SPConfigurationDatabase -DatabaseServer SQLServerInstanceName -DatabaseName SharePoint_Config -Passphrase $passphrase
  • Start-Service SPTimerv4
  1. Run SharePoint Configuration wizard and created new central administration.

We were able to deploy solutions via Visual Studio but newly created Central Administration need to deploy every wsp.


Leave a Comment

SharePoint 2013 Migration: User not able to access after migration from SharePoint 2010 to SharePoint 2013

Scenario:

We have migrated our sites from SharePoint 2010 to SharePoint 2013. After migration, users are not able to access newly migrated site. If we delete user and provide access again, user able to access site. (Fortunately, this was happening on test migration)

Resolution:

The missing link was warning that we overlooked. When we ran PowerShell Test-SPContentDatabase cmdlet, we missed following warning:

Category : Configuration

Error : False

UpgradeBlocking : False

Message : The [SharePoint2013] web application is configured with claims authentication mode however the content database you are trying to attach is intended to be used against a windows classic authentication mode.

Remedy : There is an inconsistency between the authentication mode of target web application and the source web application. Ensure that the authentication mode setting in upgraded web application is the same as what you had in previous SharePoint 2010 web application. Refer to the link http://go.microsoft.com/fwlink/?LinkId=236865 for more information.

Locations :

In other words, SharePoint 2013 discourage classic mode authentication. If web application is created via Central Administration, claim based is the default and preferred method of authentication. If want to create web application with classic mode authentication, you need to use PowerShell cmdlets.

It goes like this:

  1. Create classic mode web application using PowerShell cmdlets
  2. Attach all content database with web application
  3. Convert classis mode web application to claim base authentication
  4. Configure Object cache

Create classic mode web application using PowerShell cmdlets

$ap = New-SPAuthenticationProvider -UseWindowsIntegratedAuthentication –DisableKerberos

New-SPWebApplication -Name “SharePoint – 2013” -ApplicationPool “SharePoint 2013 Web Apps” -ApplicationPoolAccount (Get-SPManagedAccount “domain\login”) -Port 80 -Url http://sharepoint2013 -AuthenticationMethod NTLM -AuthenticationProvider $ap -DatabaseName “WSS_Content_01”

Attach all content database with web application

Mount-SPContentDatabase “WSS_Content_2” -DatabaseServer “SQLDB2013” -WebApplication http://sharepoint2013

Convert classis mode web application to claim base authentication

Convert-SPWebApplication –Identity http://sharepoint2013 –To Claims -RetainPermissions –Force

Configure Object cache

$wa = Get-SPWebApplication -Identity http://sharepoint2013

$wa.Properties[“portalsuperuseraccount”] = “domain\login”

$wa.Properties[“portalsuperreaderaccount”] = “domain\login”

$wa.Update()

Make sure you enter user login using SharePoint 2013 claims encoding. It must be in this format “i:0#.w|contoso\chris” [For details, please visit] and don’t forget to restart IIS.


Leave a Comment

Nintex Workflow: Remove / Delete Custom Actions

Scenario:

We have created a lot of custom actions and wanted to remove some custom actions that are created for POC purpose only.

Resolution:

For Nintex Workflow 2010,

  • Using windows powershell go to, “C:\Program Files\Nintex\Nintex Workflow 2010” or “C:\Program Files (x86)\Nintex\Nintex Workflow 2010”
  • Run NWAdmin.exe with switches -o RemoveAction -adapterType <namespace.class of adapter> | -id <id of action>
  • One way of finding ID of Custom Action is accessing Nintex database and looking into table “Activities”. In this table you will be able to find all details related to custom activities.

For Nintex Workflow 2013,

  • Path of NWAdmin.exe changes to “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN”

Leave a Comment

Older Posts »