Wednesday, September 26, 2018

Show names of attachments from a SharePoint list item in a column


If you want to display the name of the attachment and click on the name to open the document in list view, here is a solution.


  • Enable the "Attachments" field in list view.



















  • Save the following code as a js file(showAttachments.js) and then upload the file into Site Assets document library.

(function () {
    (window.jQuery || document.write('<script src="//code.jquery.com/jquery-3.1.0.min.js"><\/script>'));
    //Create object that have the context information about the field that we want to change it output render 
    var linkFiledContext = {};
    linkFiledContext.Templates = {};
    linkFiledContext.Templates.Fields = {       
        "Attachments": { "View": AttachmentsFiledTemplate }
    };
    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(linkFiledContext);
})();

// This function provides the rendering logic for list view
function AttachmentsFiledTemplate(ctx) {
    var itemId = ctx.CurrentItem.ID;
    var listName = ctx.ListTitle;      
    return getAttachments(listName,itemId);
}

function getAttachments(listName,itemId) {
 
    var url = _spPageContextInfo.webAbsoluteUrl;
    var requestUri = url + "/_api/web/lists/getbytitle('" + listName + "')/items(" + itemId + ")/AttachmentFiles";
    var str = "";
    // execute AJAX request
    $.ajax({
        url: requestUri,
        type: "GET",
        headers: { "ACCEPT": "application/json;odata=verbose" },
        async: false,
        success: function (data) {
            for (var i = 0; i < data.d.results.length; i++) {
                str += "<a href='" + data.d.results[i].ServerRelativeUrl + "'>" + data.d.results[i].FileName + "</a>";
                if (i != data.d.results.length - 1) {
                    str += "<br/>";
                }               
            }         
        },
        error: function (err) {
            //alert(err);
        }
    });
    return str;
}


  • Add the following reference in JSLINK in list view web part

~site/SiteAssets/showAttachments.js

























Tuesday, September 25, 2018

Check server time for all servers in SharePoint farm

Servers hosting the Central Administration site should be configured to be in the same time zone. There are many settings that rely on specifying a time. If servers that run the Central Administration site are in more than one time zone, there can be confusion and conflicting input.

All servers that run the SharePoint 2010/2013 Timer Service should also be configured to be in the same time zone. Timer services run to complete a host of jobs such as deploy solutions, content deployment, alerts, workflow and more. A discrepancy in the time zones between servers can lead to synchronization issues. For example, deployment jobs may start on one server but not run on another server until much later


Solution

A PowerShell script that will check all of the server times for all servers in the SharePoint farm.
The script needs to run on one of the SharePoint servers in the farm using an account that has admin access on all servers in that farm.


Save skript as .ps1 file and start in Windows PowerShell


Add-PSSnapin Microsoft.SharePoint.PowerShell -EA 0
$servers = (Get-SPServer) | foreach {$_.Address} 
 
foreach($server in $servers)
{
 
    $time = Get-WmiObject Win32_LocalTime -computer $server  -EA 0
  
    $hour = $time.Hour
    $minute = $time.Minute
    $second = $time.Second
    Write-Host "$server current time is $hour : $minute : $second" -ForegroundColor Green
    
}
Write-host "If the server times are not in sync please adjust the time settings. Press any key to continue" -ForegroundColor Yellow
$null = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")




Monday, September 24, 2018

Find large lists and large files in SharePoint with PowerShell


Detect large lists in SharePoint

Do you want to find out all the Large lists in your SharePoint site collection? This PowerShell script will help you to get the result.

$rootSiteCollectionUrl = "https://sbportal";
$sa = Start-SPAssignment -Global;
(Get-SPSite $rootSiteCollectionUrl).WebApplication.Sites | Foreach-Object {$_.AllWebs} | Foreach-Object {$_.Lists} | Where-Object {$_.ItemCount -ge 2000} | Format-Table Title,ItemCount,ParentWebUrl
$sa | Stop-SPAssignment;



Detect large files in SharePoint

The following PowerShell script would give you the list of files which are larger than 50MB in a SP web application. Just change the $filesize parameter based on your requirement and you should be good to go.



Add-PSSnapin Microsoft.SharePoint.PowerShell
Start-SPAssignment -Global
#Change the site url below
$Site = Get-SPSite https://sbportal     
$spWeb = $Site.WebApplication
#Enter the target file size in MB
$fileSize = 50
[string]$fileUrl
Write-Host "------Checking the SP web app for large files------"
# Enumerate though all site collections, sites, sub sites and document libraries in a SP web app
if($spWeb -ne $null)
{
foreach ($siteColl in $spWeb.Sites)
{
  foreach($subWeb in $siteColl.AllWebs)
   {
     foreach($List in $subWeb.Lists)
      {
        if($List.BaseType -eq "DocumentLibrary")
        {
          $ItemsColl = $List.Items
             foreach ($item in $ItemsColl)
           {   
             $itemSize = (($item.File.Length)/1024)/1024
              if($itemSize -Ge $fileSize)
             {
               $itemUrl = $item.Web.Url + "/" + $item.Url;
               Write-Host $itemUrl ", File size:: " $('{0:N2}' -f $itemSize) MB -ForegroundColor Green
             }
           }                   
        }
      }    
   }
}
}
Write-Host "---------DONE---------"
Stop-SPAssignment -Global

Friday, September 21, 2018

Resize Promoted Links in SharePoint

A simple way to change size of Promoted Links in SharePoint using CSS.
Add the following CSS inside a Content Editor web part.


<style type="text/css">
/* The height of the Promoted Link Webpart */
.ms-promlink-body {
     height: 100px;
}

/*  Tile and border size. Delta between ms-tileview-tile-root and size of the promoted link tiles is the space between tiles  */
.ms-tileview-tile-root {
     width: 105px! important;
     height: 105px !important;
}

/*  size of the promoted link tiles */
.ms-tileview-tile-content, .ms-tileview-tile-detailsBox {
     width: 100px !important;
     height: 100px !important;
}

/*  size of the image within the promoted link tile */
.ms-tileview-tile-content > a > img {
     width: 100% !important;
     height: 100% !important;
}

/* Changes the Promoted Link Title when you hover over it*/
.ms-tileview-tile-detailsListMedium {
     font-weight: bold;
     height: 100px;
     padding: 4px 7px 7px;
     font-size: 12px;
     line-height: 14px;
}

/* Changes the Promoted Link Description when you hover over it*/
.ms-tileview-tile-descriptionMedium {
     font-weight: normal;
     padding-top: 10px;
     font-size: 11px;
     /*  If you want to change the font size, change above */
}

/*  Remove this if you only what to show the images and not the text */
/*  Adjust the text in the Collapsed Promoted Link */
.ms-tileview-tile-titleTextMediumCollapsed {
     font-weight: normal;
     background-color: rgba(0, 0, 0, 0.6);
     width: 100px;
     height: 40px;
     position: absolute;
     top: -20px;
     left: 0px;
     text-align: center;
     vertical-align: middle;
     font-size: 11px;
     /*  If you want to change the font size, change above */
     line-height: 20px;
     word-wrap: break-word;
}

</style>

Thursday, September 20, 2018

Make attachment required field in SharePoint list


Make attachment mandatory in SharePoint list we can use simple JavaScript.
Add content editor or script editor web part on "NewForm.aspx" near SharePoint list form web part. Then place the below code in it.

</pre>
<script type="text/javascript" language="javascript">
function PreSaveAction() {

var elm = document.getElementById("idAttachmentsTable");
 if (elm == null || elm.rows.length == 0)
{
 document.getElementById("idAttachmentsRow").style.display='none';
alert("Please attach resume");
return false ;
}
else { return true ;}
}
</script>



If attachment not found, it will give an alert with message “Please attach resume”.





Wednesday, September 19, 2018

Adapt SharePoint Calendar Size using CSS


We add SharePoint Calendar Web part on the Home page, calendar size is too big as shown below. It covers the most of the area of the Home page.

Size of the Calendar can be adapted using CSS.











Add Content Editor Web part near Calendar Web part and insert code

<style type="text/css">
.ms-acal-item{height: 10px !important;}
.ms-acal-sdiv,
.ms-acal-mdiv,
.ms-acal-ctrlitem,
.ms-acal-month-weeksel,
.ms-acal-title,
.ms-acal-month-top span{display: none;}
.ms-acal-summary-itemrow TD DIV
{
height: 25px !important;
}
</style>











Note: Height gets reduces by style and width gets adjusted according to the space available.
Width can adapt in "Edit Web part" in Appearance section.


Tuesday, September 18, 2018

Traffic light on calculated column - SharePoint

A traffic light column is a great addition to any SharePoint list, it allows you to quickly recognise high priority tasks.

Here is an elegant way to easily display traffic lights on your SharePoint list, without the need for icons or similar. We can use his calculated column formula in HTMLCC, without having to worry about the JavaScript side of things.


Create column "Priority"























Create column "Traffic lights"

Insert code:

=IF(ISERR(RIGHT(LEFT([Priority],2),1)-1),"","<DIV style='font-weight:bold; font-size:24px; color:"&CHOOSE(RIGHT(LEFT([Priority],2),1),"red","orange","green")&";'>&bull;</DIV>")

Choose Number (1, 1.0, 100)























That's it!


Monday, September 17, 2018

Copy a SharePoint Designer List Workflow to Another Site

I had to migrate a SharePoint Designer 2013 workflow to an environment. The workflow was exported to visio as a .vwi file. Unfortunately when the I tried to import the file using the “Import as visio” option from SharePoint Designer, the message was appearing

“This workflow cannot be imported because it was created in SharePoint Designer for a different site, or the original workflow has been moved or deleted.”

Solution:

  • Open the site you want to copy the workflow from and click Workflows under Site Objects from the left navigation
  • Select the workflow and click Export to Visio from the designer ribbon to save the file
  • Change the extension of the exported workflow from .vwi to .zip
  • Open the .zip file and remove workflow.xoml.wfconfig.xml file



  • Rename .zip file back to .vwi
  • From SharePoint Designer 2013, open the site where the workflow will be migrated
  • Click on Workflows tab
  • Click Import from Visio and browse to select the file and click Next
  • If the workflow is a reusable workflow, pick the reusable workflow option else if the workflow is a list workflow , pick the “List Workflow” option and select the list. 



  • Click Save.
  • Click Publish to make the workflow available to the site


Ref: https://aniketahmed.wordpress.com/2013/08/02/305/

Friday, September 14, 2018

PowerShell Script to export SharePoint List in Excel File

This script exports SharePoint list to csv using PowerShell. The script is suitable if you want to export list from Task Scheduler.















Start PowerShell as Administrator

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the Web
$web = Get-SPWeb -identity "https://project/test"

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

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

 #Get All List items
 $list.Items | foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "City" -value $_["City"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Job" -value $_["Job"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Department" -value $_["Department"]

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

#Dispose the web Object
$web.Dispose()















When you insert in Excel


















If you want to filter list export just insert line  Where-Object { $_["City"] -eq "New York"}


#Get the Web
$web = Get-SPWeb -identity "https://sbprojekti/test"

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

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

 #Get All List items
 $list.Items |  Where-Object { $_["City"] -eq "New York"} |foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "City" -value $_["City"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Job" -value $_["Job"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Department" -value $_["Department"]

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

#Dispose the web Object
$web.Dispose()




Thursday, September 13, 2018

Alternating row shading in a SharePoint list

The list view display of items in a SharePoint List can be updated to have alternating row colour and border set by using CSS.
Alternating list row colors is quick fix that makes your data easier on your viewers’ eyes and helps with user adoption, especially those coming from programs and platforms that had alternating row colors built-in.













Add a content editor Web Part in the page, add the following css code in it:


<style type="text/css">
.ms-listviewtable > tbody > tr {
background: white;
}

.ms-listviewtable > tbody > tr td{
border-bottom: 1px dotted black !important; }

.ms-listviewtable > tbody > tr.ms-alternating {
background: lightgrey
}
</style>



Tuesday, September 11, 2018

Restore a deleted SharePoint Site Collection without a recent backup

Accidentally deleted a SharePoint site collection? No problem, you can restore it without resorting to database backups.

Solution:

Start SharePoint Management Shell as Administrator and run command Get-SPdeletedsite. You will see your site collection here with Site ID


Now restore the site collection using this site ID Restore-SPDeletedSite –Identity “ID"


Site Collection is back!

Monday, September 10, 2018

Set standard date format to all datatime columns in SharePoint


After migrating content to SharePoint 2016, all date columns were in Friendly display format. Here is a way to update them across the site collection using PowerShell.

Run Microsoft PowerShell as Administrator


Add-PSSnapin "Microsoft.SharePoint.Powershell" -ErrorAction SilentlyContinue
$WebCollection = Get-SPSite "SiteUrl" -Limit All | Get-SPWeb -Limit All

foreach($site in $WebCollection){

$lists = $site.Lists

foreach($list in @($lists)){

Write-Host "Connected to $list" -ForegroundColor Yellow

foreach($field in @($list.Fields)){

$column = $list.Fields[$field.Title]

    if($column.Type -eq "DateTime"){
                    
   Write-Host "Connected to " $column.Title -ForegroundColor Yellow
   $column.FriendlyDisplayFormat = 1
   $column.update()
               
            }
        }
    }
}




You can also limit the list/library you want to do this on by using a where clause in the lists area like the following:

foreach($list in $lists |?{$_.Title -like "ListName"}){}

Find email listed for access requests in SharePoint

This script will walk through your web application and list the site name, url, and email address for all of your sites in SharePoint. Subsites are included.

When a SharePoint site is created, by default the creator’s email address is automatically populated into the “Manage Access Requests”. However, sometimes the creator isn’t the site owner, and doesn’t handle the day-to-day access requests for the site.










Start PowerShell as Administrator

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue

#Starting web app
$site = “https://siteUrl

# Function: FindAccessEmail
# Description: Go through a target web application and list the title, url and access request email.
function FindAccessEmail
{
$WebApps = Get-SPWebApplication($site)
foreach($WebApplication in $WebApps)
{
foreach ($Collection in $WebApplication.Sites)
{
foreach($Web in $Collection.AllWebs)
{
$siteDetails = $Web.title+’#’+$Web.url+’#’+$Web.RequestAccessEmail
write-host $siteDetails
Write-Output $siteDetails
}
}
}
}
#Run Script!
FindAccessEmail | Out-File -filepath C:\Temp\AccessRequestEmails.csv

Friday, September 7, 2018

Copy the value of one column to another column in the same SharePoint List using Powershell script

Powershell Script to Copy the value of one column to another column in the same SharePoint List.


$site = new-object Microsoft.SharePoint.SPSite("http://localhost")
$web =  Get-SPWeb -Identity http://localhost
$list =$web.Lists["List/Library Name"]
$items = $list.items
    foreach ($item in $items)
    {
    $sourcevalue = $item["Column 1"]
    $item["Column 2"] = $sourcevalue
    write-host $sourcevalue
    $item.update()
    }

$list.update()

SharePoint Script Editor and Chrome ERR_BLOCKED_BY_XSS_AUDITOR

In recent Chrome browser, it has become little annoying to work with SharePoint tool due to Chrome throwing ERR_BLOCKED_BY_XSS_AUDITOR error when working with the Script Editor web part. This is due to XSS auditor on Chrome blocking JavaScript included in the Script Editor Web Part. Issue occurs on SharePoint Online and on-premises SharePoint 2016 and 2013 and occurs regardless if site is accessed via HTTP or HTTPS.


Problem:

This page isn’t working
    Chrome detected unusual code on this page and blocked it to protect your personal information (for example, passwords, phone numbers, and credit cards).
        Try visiting the site's homepage.
    ERR_BLOCKED_BY_XSS_AUDITOR

Solution:

You can disable the XSS Auditor by appending the following value to web.config on your on-premises SharePoint 2013 or 2016 web site.

  <system.webServer>
    <httpProtocol>
      <customHeaders>
        <add name="X-XSS-Protection" value="0" />
      </customHeaders>


Ref:https://blog.jussipalo.com/2017/03/sharepoint-workaround-for-script-editor.html

Thursday, September 6, 2018

Disable list throttling just for one SharePoint list with PowerShell

You have a list that is in the process of getting cleaned up, but you’ve gotta leave your throttle up for functionalities sake until it’s resolved. The list throttle is a Web Application Level setting, so any list/library in any site in any Site Collection in your web app is affected and you don’t know what other lists have now creeped over the limit and  will break once you reduce the threshold back down to the recommended limit of 5000.

Here is a powershell script that will disable the throttle for just one list, that way all of the lists can continue to adheed to the throttle limit while this one particular list can continue to function around it.


Disable throttle for SharePoint list:

$mywebsite = Get-SPWeb “http://portal/site”
$mySPList = $mywebsite.Lists[“ListName”]
$mySPList.EnableThrottling = $false
$mySPList.Update()




Verify throttle for SharePoint list:

$mySPList.IsThrottled




Enable throttle for SharePoint list:

$mySPList.EnableThrottling = $true
$mySPList.Update()


Ref:https://www.techrevmarrell.com/throttle-throttle-disable-the-throttle/

Monday, September 3, 2018

SQL script - schema changes history

This SQL script reads events from the default trace, if it is enabled.
It's displays events for when any object changes occured (CREATE, DROP, ALTER).


declare @d1 datetime;
declare @diff int;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;
declare @temp_trace table (
 obj_name nvarchar(256) collate database_default
, database_name nvarchar(256) collate database_default
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256) collate database_default
, login_name nvarchar(256) collate database_default
, application_name nvarchar(256) collate database_default
, ddl_operation nvarchar(40) collate database_default
);

select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX('%\%', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT(@curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

insert into @temp_trace
select ObjectName
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, ApplicationName
, 'temp'
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass in (46,47,164) and EventSubclass = 0 and
DatabaseID <> 2

update @temp_trace set ddl_operation = 'CREATE' where
event_class = 46
update @temp_trace set ddl_operation = 'DROP' where
event_class = 47
update @temp_trace set ddl_operation = 'ALTER' where
event_class = 164

select @d1 = min(start_time) from @temp_trace
set @diff= datediff(hh,@d1,getdate())
set @diff=@diff/24;

select @diff as difference
, @d1 as date
, object_type as obj_type_desc
, *
from @temp_trace where object_type not in (21587)
order by start_time desc




















Here is ObjectType Reference from Default Trace

1 Index
2 Database
3 User Object
4 CHECK Constraint
5 Default or DEFAULT Constraint
6 FOREIGN KEY Constraint
7 PRIMARY KEY Constraint
8 Stored Procedure
9 User-Defined Function (UDF)
10 Rule
11 Replication Filter Stored Procedure
12 System Table
13 Trigger
14 Inline Function
15 Table Valued UDF
16 UNIQUE Constraint
17 User Table
18 View
19 Extended Stored Procedure
20 Ad hoc Query
21 Prepared Query
8259 Check Constraint
8260 Default (constraint or standalone)
8262 Foreign-key Constraint
8272 Stored Procedure
8274 Rule
8275 System Table
8276 Trigger on Server
8277 (User-defined) Table
8278 View
8280 Extended Stored Procedure
16724 CLR Trigger
16964 Database
16975 Object
17222 FullText Catalog
17232 CLR Stored Procedure
17235 Schema
17475 Credential
17491 DDL Event
17741 Management Event
17747 Security Event
17749 User Event
17985 CLR Aggregate Function
17993 Inline Table-valued SQL Function
18000 Partition Function
18002 Replication Filter Procedure
18004 Table-valued SQL Function
18259 Server Role
18263 Microsoft Windows Group
19265 Asymmetric Key
19277 Master Key
19280 Primary Key
19283 ObfusKey
19521 Asymmetric Key Login
19523 Certificate Login
19538 Role
19539 SQL Login
19543 Windows Login
20034 Remote Service Binding
20036 Event Notification on Database
20037 Event Notification
20038 Scalar SQL Function
20047 Event Notification on Object
20051 Synonym
20549 End Point
20801 Adhoc Queries which may be cached
20816 Prepared Queries which may be cached
20819 Service Broker Service Queue
20821 Unique Constraint
21057 Application Role
21059 Certificate
21075 Server
21076 Transact-SQL Trigger
21313 Assembly
21318 CLR Scalar Function
21321 Inline scalar SQL Function
21328 Partition Scheme
21333 User
21571 Service Broker Service Contract
21572 Trigger on Database
21574 CLR Table-valued Function
21577 Internal Table (For example, XML Node Table, Queue Table.)
21581 Service Broker Message Type
21586 Service Broker Route
21587 Statistics
21825 User
21827 User
21831 User
21843 User
21847 User
22099 Service Broker Service
22601 Index
22604 Certificate Login
22611 XMLSchema
22868 Type