Tuesday, January 12, 2016

Power Query to Extract SharePoint List Data using Excel

Microsoft Power Query is a Free add-in for Excel - Part of PowerBI that provides self-service Business Intelligence features. Lets see how to extract SharePoint list data using Power Query for Excel.

Step 1: Download & Install Power Query Add-in
To start with, Download the Power Query Add-in for Excel from: https://www.microsoft.com/en-us/download/details.aspx?id=39379

Once installed, You'll find a new tab "Power Query" tab added to Microsoft Excel.
power query and sharepoint 2013
Step 2: Connect to SharePoint List
To extract SharePoint list data, Click on "From Other Sources" button from the ribbon and choose "From SharePoint List"
power query excel 2013 sharepoint
Provide the URL of your SharePoint site and choose Authentication method accordingly - I Selected Windows. For Office 365, choose organizational credentials.

Once you are successfully logged in,  You'll get the Navigator page to choose your source list and click on "Load". By default, Only Lists appear in the Navigator.  Pick any list and use "Advanced Editor", change List name & "SharePoint.Tables" to "SharePoint.Contents" to get data from any library! power query connect to sharepoint list

Step 3: Remove Unnecessary columns, Perform ETL
This loads all columns from the SharePoint list. However, we may not be interested to fetch all columns. Also removing columns which are not required speeds up our data retrieval. So, Right click on the Query from Right side pane, Choose Edit.
power query sharepoint 2010 list
You'll get Power Query Editor window where you can:
  •  Add remove columns to your query -  in order to focus only on what we need
  • Merge-Append (Join-Union) from other lists
  • Perform ETL operations such as replace values, Split Columns, Group by etc.
power query for SharePoint
To Remove unnecessary columns, click on "Choose Columns" button and select only the columns that you need. Once done, click on "Load & Close" button from the ribbon. All activities inside the query editor are recorded as Steps. We can remove/Re-order them as required.
excel power query SharePoint list
To get actual values of Lookup columns, You'll have to "Expand" them. Once ready, click "Close and Load" from the Query Editor ribbon, and the list data will load to your workbook,
Although "Export to Excel" can retrieve data from SharePoint list, Power Query has much more capabilities. 

So next time, without the need to open SharePoint site in browser, You can just open the Excel file and Hit refresh! Once the data is loaded, We can utilize advanced features such as: Pivot Table, Pivot Chart, etc to output a variety of visual data in Excel worksheet.

I used this Power Query tool with SharePoint 2010 and SharePoint 2013.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

Sunday, November 1, 2015

Open Documents in Client Application Instead of Browser

Requirement: Enable Microsoft Word documents, Excel Spread sheets and PowerPoint Presentations to open in client applications like Microsoft Word, Excel and PowerPoint instead of opening them in browser directly.

In SharePoint 2013, you can enable open document in applications by configuring an option in document library settings.
  • Navigate to the Library >> Click on "Library Settings" from the Ribbon
  • Under General Settings, Click on "Advanced Settings" link
  • Under "Opening Documents in the Browser", choose "Open in the client application"
    sharepoint 2013 open document in application
  • Click "OK" to save your changes. 
Activate "Open Documents in Client Applications by Default" Feature:
While the above setting sets the document opening behavior at the specific document library, we've a built-in SharePoint 2013 feature called "Open Documents in Client Applications by Default" to control this behavior at site collection level. 

To Activate this feature:
  • Go to Site Settings >> Site Collection Features
  • Click on "Activate" button next to "Open Documents in Client Applications by Default" featuresharepoint 2013 open document in client application
Once you enable "Open Documents in Client Applications by Default" feature at site collection level, any new document library will follow the setting of "Open in the Client application". However for all existing document libraries in the site collection, this setting needs to be changed!

Definitely, you don't want to do it manually for each and every document library in the site collection, isn't it? Lets do PowerShell.

PowerShell script to change document open behavior:
Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Site Collection URL
$SiteUrl = "http://intranet.crescent.com"

#Get All sites under given site collection
$WebsColl = Get-SPSite $SiteURL -Limit All | Get-SPWeb -Limit All

#Iterate through each site
foreach($web in $WebsColl)
    #Get All document libraries 
    $LibrariesColl =$web.Lists | where {$_.BaseType -eq "DocumentLibrary" -and $_.BaseTemplate -eq "DocumentLibrary"}

    foreach($Library in $LibrariesColl)
        #Set Document Open behavior
        $Library.DefaultItemOpen = "PreferClient"
        Write-Host "Updated Document Library Settings on $($web.URL+"/"+$Library.RootFolder.URL)”
This sets all documents in SharePoint 2013 to open document in client applications.

Related post: How to disable office web apps in SharePoint 2013

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

Saturday, October 31, 2015

SharePoint 2013 Search Crawl Log History Report

Requirement: Generate SharePoint 2013 search crawl report on daily basis.

Solution: Crawl history can be viewed from SharePoint 2013 search service application as in the below screen. It gives the crawl log history report for all content sources.
sharepoint 2013 crawl history
Why don't we automate it with PowerShell, so that we'll get crawl history report in E-mail?

PowerShell to get crawl log history in E-mail:
This PowerShell script extracts crawl history data and sends out Email.
Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Get the search service application
$SSA = Get-SPEnterpriseSearchServiceApplication #-Identity "Search Service Application Name"

#Get all content sources
$ContentSources = Get-SPEnterpriseSearchCrawlContentSource -SearchApplication $SSA #| where {$_.Name -eq $ContentSourceName}

$ReportDate = Get-Date -format "dd-MM-yyyy"

#CSS Styles for the Table
$style = "Crawl History Report: "
$style = $style + "<style>BODY{font-family: Arial; font-size: 10pt;}"
$style = $style + "TABLE{border: 1px solid black; }"
$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px; }"
$style = $style + "TD{border: 1px solid black; padding: 2px; }"
$style = $style + "</style>"

#Frame Email body
$EmailBody = $ContentSources | Select Name, SuccessCount, WarningCount, ErrorCount,CrawlStarted,CrawlCompleted,  @{label="CrawlDuration";expression={$_.CrawlCompleted - $_.CrawlStarted}} | ConvertTo-Html -Head $style 

#Set Email configurations

#Get outgoing Email Server
$EmailServer = (Get-SPWebApplication -IncludeCentralAdministration | Where { $_.IsAdministrationWebApplication } ) | %{$_.outboundmailserviceinstance.server} | Select Address

$From = "CrawlReport@crescent.com"
$To = "SharePointAdmin@crescent.com"
$Subject = "Crawl History Report as on: "+$ReportDate
$Body = "Hi SharePoint Team,<br /><br />Here is a Crawl History report as on $ReportDate <br /><br />" + $EmailBody

#Send Email
Send-MailMessage -smtpserver $EmailServer.Address -from $from -to $to -subject $subject -body $body -BodyAsHtml
Schedule the above script in Windows task scheduler to receive E-mail notifications on schedule basis. Here is the script in action:
sharepoint 2013 search crawl history

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

Tuesday, October 20, 2015

Configure Variations Feature in SharePoint 2013 - Step by Step

What is Variation in SharePoint 2013?
Variations feature provides multi-lingual support for SharePoint 2013 sites. It help us to manage multilingual sites by making the same content available to specific language audiences across different sites by maintaining customizable copies of the content from the source variation in each target variation. A variation consists of a set of labels (sub-sites: Each site for one variation) that is used to create a set of sites in a site collection. For example, if you want four language variations of your site, you must create four labels, one for each language.
Important: Variations feature relies on "SharePoint Server Publishing feature", So it works only on Publishing site template or sites with publishing feature activated!
Configure variation in SharePoint 2013:
Configuring variations in SharePoint 2013 is quite a easy task, just follow below steps:
Info: SharePoint 2013 Variation feature content synchronization is a unidirectional process! It syncs content from a source variation site to one or more target variation sites

Set variations settings:
To setup variation in SharePoint 2013, start with specifying settings for Variations.

Go to Site Settings >> Site Collection Administration >> Click on "Variations Settings "to set variations
settings. These settings are self-explanatory:
  • Create Everywhere -  Selected by default, this means any content created in source label will propagate to all target variations.  Sync will happen automatically
  • Create Selectively -  Variation should be triggered manually 
  • Recreate Deleted Target Page - Page should be re-created on a target variation site if the page was deleted 
  • Update Target Page Web Parts - Source web part changes updated to the target sites 
  • Notification - Email to label contact person
variation settings sharepoint 2013
These settings can be changed if needed, otherwise leave all settings to default and click OK.

Create variation label SharePoint 2013:
After configuring variation settings, proceed with creating variation labels. The very first variation label you are creating will be the "Source Variation" site. In our case, its English, Lets create it.
  • Language: This setting defines SharePoint User interface elements of the variation site. Choices appear on the site template language drop-down depends on the language packs installed.
  • Locale: Locale determines the language site based on your browser settings. It automatically redirects the browser from Variation home to target variation site. Locale helps in creating content in different languages. 
  • Variation Home: is the location site where sub sites for all labels will be created
  • Specify the Label Name for source variation and choose Publishing site template as "Publishing site". 
  • Click "OK" button to create source variation.
create variation labels

Now the source variation has been created. Proceed with creating target variation(s).

Create variation Target Labels:
Lets create target variation for "Arabic" site. Go to Site settings >> Click on "Variation Label" >> Click on "New Label". This time, You'll get a wizard for creating target variations.
  • On "Configure Your Target Label" page, Select the Site Template Language. This depends on language packs you deployed to the SharePoint server. E.g Install Arabic language pack if you want to configure variation for Arabic & to choose Arabic as as a site template language! Select locale for target variation, and then click on "Continue" button.
    SharePoint 2013 create variation
  • Run through the wizard to create a new target variation.  Choose Translation options.  I have  selected "Allow Human and Machine translation" and selected target language as "Arabic".
    create variation in sharepoint 2013
  • Choose the Target label behavior. I've selected the option to push content updates automatically from source label to target label
     language variation in sharepoint 2013
  • Name your Target label.
    Setup Variation Settings
  • Review and Click on Finish to complete target variation label creation.
    variation set up sharepoint 2013

Create Variations Hierarchy:
Once you are done with creating labels, each label should create a variation sub-site! Click on "Create Hierarchies" link from Variation labels page in site settings, that will invoke the timer job "Variations Create Hierarchies Job Definition". This timer job will be run based on its schedule.
sharepoint 2013 variation root

If the hierarchies created successfully, you can see it in the variation labels listing as below.
variation labels sharepoint 2013
Once variation Hierarchy is created, "Hierarchy is Created" status on Variation Label page will change to "Yes" and sites are created successfully, ready to browse!

SharePoint 2013 variation hierarchy not created?
Well, the timer job runs once per hour, and you got to wait! If you don't want to wait, just trigger "Variations Create Hierarchies Job Definition" on-demand. Here is how:
  • Navigate to SharePoint 2013 Central Administration
  • Click on: Monitoring >> Review job definitions under Timer Jobs
  • Pick your web application and click on "Variations Create Hierarchies Job Definition" timer job
  • Click on "Run Now" to run this timer job.
    variation timer jobs sharepoint 2013
If required, you can change the schedule to run shorter time period too.

SharePoint 2013 Variation Timer Jobs:
Here is the list of timer jobs involved in variations feature in SharePoint:
  1. Variations Create Hierarchies Job Definition: Creates all variation sites, lists and pages from the source variation site. It runs hourly.
  2. Variations Propagate List Items Job Definition: Creates and updates list items on target variation Default, every 15 minutes.
  3. Variations Propagate Page Job Definition: Creates and updates pages on target variation after approved. Runs every 15 minutes.
  4. Variations Propagate Sites and Lists Job Definition: Creates variation sites and lists when the "Create Everywhere" option is enabled.  Runs on every 30 minutes.
Here is the snapshot from SharePoint 2013 Central Administration Timer Job definitions page:
SharePoint 2013 Variation Timer Jobs

Variation Logs in SharePoint 2013:
During the variation process, there are several timer jobs run. All activities related to this process are logged  and collected under Variation logs to provide details in troubleshooting any variation related issue such as Hierarchy creation, Page propagation, etc. 

Variation logs are located under root site collection's settings page.
  • Site Settings >> Site Collection Administration >> Variation logs 
SharePoint 2013 variation feature logs
How to test variation feature:
Once you hit variation home, It goes to the variation site based on your browser language settings. E.g. When you hit http://portal.crescent.com, It goes to http://portal.crescent.com/english if your browser language is set to English.

How does variations feature works in SharePoint 2013?
So, How does it works? Well, When you create new pages or update existing pages on source variation site, Target variation(s) automatically updated in draft mode. They'll go live in target variations when published!

Syncing Custom list and Libraries in Variation:
By default custom lists are libraries are not synced from source to destination. It needs to be manually triggered. Here is how:
  • Go to any custom list or library in source variation site
  • Click on Variations Tab >> Click on Settings
    sharepoint 2013 create variation labels
  • Select Variation labels (sites) to sync and click "Continue". You'll get "Your new variations are on the way" message.
    configure variation sharepoint 2013
  • Once done, The particular list will be propagated on selected targets.

Technet Reference Variations overview in SharePoint Server 2013

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

Wednesday, October 14, 2015

Copy Alerts from One SharePoint List or Library to Another - Using PowerShell

Requirement: Copy Alerts from one SharePoint list to another list.

Solution: Use this PowerShell script to copy alerts between SharePoint lists or libraries.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

function Copy-SPAlerts($SourceWebUrl,$SourceList, $TargetWebUrl, $TargetList)
    #Get the source Web & List objects
    $SourceWeb = Get-SPWeb $SourceWebUrl
    $SourceList= $SourceWeb.lists.TryGetList($SourceList)   

    #Get the Target Web & List objects
    $TargetWeb = Get-SPWeb $TargetWebUrl
    $TargetList= $TargetWeb.lists.TryGetList($TargetList)   
    #Get All Alerts of the Source list
    $SourceAlertsColl = $SourceWeb.Alerts | Where-Object { ($_.List.Title -eq $SourceList.Title)}
    write-host Found $SourceAlertsColl.count alerts in the source List!

    if($SourceAlertsColl.Count -gt 0)
        foreach ($SourceAlert in $SourceAlertsColl)
            #Copy alerts from source to destination
            $alert = $TargetWeb.Alerts.Add()
            $alert.Title = $SourceAlert.Title
            $alert.AlertType = [Microsoft.SharePoint.SPAlertType]::List
            $alert.User = $SourceAlert.user 
            $alert.List = $TargetList
            $alert.DeliveryChannels = $SourceAlert.DeliveryChannels
            $alert.EventType =  $SourceAlert.EventType
            $alert.AlertFrequency = $SourceAlert.AlertFrequency

            Write-Host "Copied Alert: $($SourceAlert.Title)" -foregroundcolor Green


$SourceListName="Project Documents"
$TargetListName="Project Documents"

#Call the function to Copy Alerts
Copy-SPAlerts $SourceWebURL $SourceListName $TargetWebURL $TargetListName

Remember, when you copy alerts from one list to another, it triggers "New Alert Created" E-mail as:
Copy Alerts from One List to Another List in SharePoint

You can disable alerts for time being before copying: How to Disable Alerts in SharePoint List or Library

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

You might also like:

Related Posts Plugin for WordPress, Blogger...