Aug 15, 2017

PowerShell Script: List Files and Sub-Folders in a SharePoint Document Library

Just want to share to you guys for today, which I hope it will help you out when you need it. Here is the script to list all files and sub-folders in a SharePoint Document Library.

Syntax:
.\SPDocContent.ps1 [[-Credential] <PSCredential>] [-URL] <String> [-DocumentLibrary] <String> [[-BaseFolderPath] <String>] [[-CSVPath] <String>] [<CommonParameters>]



##############################################################################
#.SYNOPSIS
# Gets a list of files, folders and sub-folders in a SharePoint Online Document Library.
#
#.DESCRIPTION
# Gets a list of files, folders and sub-folders in a SharePoint Online Document Library.
#
#.PARAMETER Credential
# The credential to access with SharePoint Online.
#
#.PARAMETER URL
# The URL of the SharePoint site (can be a sub-site or root site).
#
#.PARAMETER Document Library
# The Document Library name to connect to.
#
#.PARAMETER BaseFolderPath
# Start from particular sub-folder (optional).
#
#.PARAMETER CSVPath
# The path of the CSV file to export the result.
#
#.EXAMPLE
# $Crd = Get-Credential
# .\SPDocContent.ps1 -URL "https://rdz.sharepoint.com" -DocumentLibrary "Documents" -CSVPath "D:\Documents.csv"
##############################################################################

[Cmdletbinding()]
param (
	[Parameter(Mandatory = $false)]
    [System.Management.Automation.PSCredential]$Credential = $null,
    [Parameter(Mandatory=$true)]
    [string]$URL,
    [Parameter(Mandatory=$true)]
    [string]$DocumentLibrary,
    [Parameter(Mandatory=$false)]
    [string]$BaseFolderPath,
	[Parameter(Mandatory = $false)]
    [string]$CSVPath = ""
)

Function ListContent(
    [Parameter(Mandatory=$true)]
    [Microsoft.SharePoint.Client.Folder]$BaseFolder,
    [Parameter(Mandatory=$false)]
    [switch]$Recursive = $false,
    [Parameter(Mandatory=$false)]
    [Microsoft.SharePoint.Client.Folder]$RootFolder,
    [Parameter(Mandatory=$false)]
    [int]$Level = 0
)
{
    $ContentInfo = @()

    $RootLevel = $false;
    $ctx.Load($BaseFolder.Folders)
    $ctx.Load($BaseFolder.Files)
    $ctx.Load($BaseFolder.ParentFolder)
    $ctx.ExecuteQuery()

    if ($RootFolder -eq $null) { $RootLevel = $true; $RootFolder = $BaseFolder; }
    $tabsRepeat = New-Object System.String("`t", ($Level + 1))

    if ($BaseFolder.Folders.Count -gt 0)
    {
        Write-Host "$($tabsRepeat)$($BaseFolder.Folders.Count) sub-folders for $($BaseFolder.ServerRelativeUrl.Replace($RootFolder.ServerRelativeUrl + "/", [System.String]::Empty))" -ForegroundColor Yellow
        for ($i = 0; $i -lt $BaseFolder.Folders.Count; $i++)
        {
            $ctx.Load($BaseFolder.Folders[$i])
            $ctx.ExecuteQuery()
            if ($BaseFolder.Folders[$i])
            {
                if (($RootLevel -and $BaseFolder.Folders[$i].Name -ne "Forms") -or -not $RootLevel)
                {
                    Write-Host "$($tabsRepeat)$($BaseFolder.Folders[$i].ServerRelativeUrl.Replace($RootFolder.ServerRelativeUrl + "/", [System.String]::Empty))"
                    $ContentInfo += [pscustomobject]@{
                        RelativePath = "$($BaseFolder.Folders[$i].ServerRelativeUrl.Replace($RootFolder.ServerRelativeUrl + "/", [System.String]::Empty))";
                        Type = "Folder";
                        Name = "$($BaseFolder.Folders[$i].Name)";
                        Level = $Level;
                    };

                    if ($Recursive -eq $true)
                    {
                        $ContentInfo += (ListContent -BaseFolder $BaseFolder.Folders[$i] -RootFolder $RootFolder -Recursive:$Recursive -Level ($Level + 1))
                    }
                }
            }
        }
    }
    if ($BaseFolder.Files.Count -gt 0)
    {
        Write-Host "$($tabsRepeat)$($BaseFolder.Files.Count) sub-files for $($BaseFolder.ServerRelativeUrl.Replace($RootFolder.ServerRelativeUrl + "/", [System.String]::Empty))" -ForegroundColor Yellow
        for ($i = 0; $i -lt $BaseFolder.Files.Count; $i++)
        {
            $ctx.Load($BaseFolder.Files[$i])
            $ctx.ExecuteQuery()
            if ($BaseFolder.Files[$i])
            {
                $ContentInfo += [pscustomobject]@{
                    RelativePath = "$($BaseFolder.Files[$i].ServerRelativeUrl.Replace($RootFolder.ServerRelativeUrl + "/", [System.String]::Empty))";
                    Type = "File";
                    Name = "$($BaseFolder.Files[$i].Name)";
                    Level = $Level;
                };
                Write-Host "$($tabsRepeat)$($BaseFolder.Files[$i].ServerRelativeUrl.Replace($RootFolder.ServerRelativeUrl + "/", [System.String]::Empty))"
            }
        }
    }
    return $ContentInfo;
}

$ContentInfo = @()
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($URL)
Try
{
    if ($Credential)
    {
        if ($Credential.GetType().FullName -eq "Microsoft.SharePoint.Client.SharePointOnlineCredentials")
        {
            $ctx.Credentials = $Credential
        } elseif ($Credential.GetType().FullName -eq "System.Management.Automation.PSCredential")
        {
            $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credential.Username,$Credential.Password)
        }
    }
    elseif ($PSCrd)
    {
        Write-Host "No credential set, retrieving from `$PSCrd." -ForegroundColor Yellow
        $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($PSCrd.Username,$PSCrd.Password)
    }
    elseif ($SPCrd)
    {
        Write-Host "No credential set, retrieving from `$SPCrd." -ForegroundColor Yellow
        $ctx.Credentials = $SPCrd
    }
    else
    {
        Write-Host "No credential set, none retrieved." -ForegroundColor Red -BackgroundColor Black
        exit
    }

    $Site = $ctx.Site
    $Web = $ctx.Web
    $ctx.Load($Site)
    $ctx.Load($Web)
    $ctx.Load($Web.Lists)
    $ctx.Load($Web.Webs)
    $ctx.ExecuteQuery()

    $DL = $Web.Lists.GetByTitle($DocumentLibrary)
    $ctx.ExecuteQuery()

    $ctx.Load($DL.RootFolder)
    $ctx.ExecuteQuery()

    if ($BaseFolderPath)
    {
        $RootFolderPath = "$($DL.RootFolder.ServerRelativeUrl)/$($BaseFolderPath)"
        $RootFolder = $DL.RootFolder.Folders.GetByUrl($BaseFolderPath)
        $ctx.Load($RootFolder)
        $ctx.ExecuteQuery()
        $ContentInfo += (ListContent -BaseFolder $RootFolder -Recursive)
    }
    else
    {
        $ContentInfo += (ListContent -BaseFolder $DL.RootFolder -Recursive)
    }

    if ($CSVPath)
    {
        if (Test-Path $CSVPath) { Remove-Item $CSVPath }
        $ContentInfo | Export-Csv $CSVPath -NoTypeInformation
    }
}
Catch
{
    Throw
}
Finally
{
    if ($ctx -ne $null) { $ctx.Dispose(); }
}

Note: this script is only meant for SharePoint Online.

Jun 13, 2017

PowerShell Script: Upload Large File to SharePoint in Chunks

Get back to the business again! Just wanted to share another great things in SharePoint (although to user is not fun at all).

According to MSDN, there are a few options to upload files to SharePoint.

Apr 3, 2017

SharePoint CAML Query through jQuery

I was just digging into my code snippet, and I thought this worth to share. I got the sample code where we can query using CAML in jQuery.

After a few tests, it’s still valid to be used. Here it is!


var DocLibName = "Shared Documents";
var camlQry = 
	"<View Scope='RecursiveAll'>" +
		"<Query>" +
			"<Where>" +
				"<And>" +
					"<Leq>" +
						"<FieldRef Name='Created'/>" +
						"<Value Type='DateTime'><Today /></Value>" +
					"</Leq>" +
					"<Eq>" +
						"<FieldRef Name='FSObjType'/>" +
						"<Value Type='Integer'>0</Value>" +
					"</Eq>" +
				"</And>" + 
			"</Where>" +
		"</Query>" +
	"</View>";
var requestData = { "query" :
	{ "__metadata" :
		{ "type" : "SP.CamlQuery" },
		"ViewXml" : camlQry
	}
};
$.ajax({
	url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('" + DocLibName + "')/GetItems?$expand=FieldValuesAsText",
	method: "POST",
	async: false,
	data: JSON.stringify(requestData),
	headers: {
		"X-RequestDigest": $("#__REQUESTDIGEST").val(),
		"Accept": "application/json; odata=verbose",
		"Content-Type": "application/json; odata=verbose"
	},
	success : function(d) {
		$.each(d.d.results, function (iRow, vRow) {
			//do your own processing here
		});
	}, error : function(a, b, c) {
		console.log(a);
		console.log(b);
		console.log(c);
	}
});

Enjoy!

Mar 29, 2017

PowerShell: Remove Site Collection Administrator Account

This time, I’m going to share you on the script to remove Site Collection Administrator of ALL site collections at one go.

12_13_47-000117


[Cmdletbinding()]
param (
    [string]$UserName = "",
    [string]$Tenant = "",
	[Parameter(Mandatory=$true)]
    [string]$AccountToRemove = "",
    [string]$RelativeSiteUrl = ""
)

Connect-SPOService -Url "https://$($Tenant)-admin.sharepoint.com" -Credential $UserName

if ($RelativeSiteUrl)
{
    $siteUrl = "https://$($Tenant).sharepoint.com$($RelativeSiteUrl)"
    Write-Host "Removing $AccountToRemove from $siteUrl..." -Fore Yellow -NoNewline
    Set-SPOUser -Site $site.Url -LoginName $AccountToRemove -IsSiteCollectionAdmin $false
    Write-Host "REMOVED" -Fore Green
}
else
{
    $SiteColl = Get-SPOSite
    Write-Host "Removing $AccountToRemove from:" -Fore Green
    foreach ($site in $SiteColl)
    {
        Write-Host "`t$($site.Url)..." -Fore Yellow -NoNewline
        Set-SPOUser -Site $site.Url -LoginName $AccountToRemove -IsSiteCollectionAdmin $false
        Write-Host "REMOVED" -Fore Green
    }
}

Disconnect-SPOService

Enjoy!

Mar 22, 2017

PowerShell Script to Iterate All Documents (including sub-folders)

Seems like I want to dump again an old script to you. This PowerShell Script is commonly used to list down all documents in the Document Library regardless of their location, whether it’s in the root or sub-folders.

No need to explain in detail again, here’s the script.

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.WorkflowServices.dll"
$Siteurl = "https://consotoayam.com/sites/EPRG" #you can replace this url with your own
$ListName = "Shared Documents" #replace this with your own Document Library name
$credential = Get-Credential #to prompt for credential

if ($SiteUrl -ne $null)
{
    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
    if ($ctx -ne $null)
    {
        $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($credential.UserName, $credential.Password)

        $st = $ctx.Site
        $wb = $ctx.Web
        $DocsLib = $wb.Lists.GetByTitle($ListName)

        $q = New-Object Microsoft.SharePoint.Client.CamlQuery
        $q.ViewXml = '0'

        $items = $DocsLib.GetItems($q)
        $ctx.Load($st)
        $ctx.Load($wb)
        $ctx.Load($DocsLib)
        $ctx.Load($items)
        $ctx.ExecuteQuery()
        foreach($item in $items)
        {
	    #if necessary, you can load the item
            $ctx.Load($item)
            $ctx.ExecuteQuery()
        }
    }
}

Enjoy!


Aug 19, 2016

Simple Workaround for Content Editor Webpart in Another Site Collection

Guess what? You have created a HTML script stored somewhere in SharePoint, and you want to use it somewhere in another Site Collection. But you are stuck with this error, “Cannot retrieve the URL specified in the Content Link property. For more assistance, contact your site administrator”.

image

There are a few workarounds you can try.

Using Page Viewer or IFRAME

To use the page viewer, the solution is quite straight-forward and can be described in a few sentences.

Firstly, you know that you have created the HTML script, now create a page in the same Site Collection, then add Content Editor Webpart that links to the HTML script.

Then you can go to the other Site Collection, create a page with Page Viewer, or direct HTML with IFRAME which links to the page created in previous step. Remember, the SharePoint top links bar and left navigation will still be displayed. To overcome that, you can add query string “IsDlg=1” at the end of the URL.

If your URL is like: http://contoso.com/Pages/cewp.aspx, then it should be like http://contoso.com/Pages/cewp.aspx?IsDlg=1.

Using jQuery

If your masterpage is attached with jQuery, you can re-use the following code.

<div class="pp-container">
</div>
<script type="text/javascript">
 $.ajax({
  url: "/SiteAssets/common/thescripts.txt",
  method: "GET",
  success: function (data) {
   $('div.pp-container').append(data);
  },
  error: function (data) {
   console.log("Error:");
   console.log(data);
  }
 });
</script>

Where "/SiteAssets/common/thescripts.txt" is the URL of your HTML script.

Aug 8, 2016

Tips: Open Office Document Online (Web Version) in SharePoint

In SharePoint, there are 2 ways of setting a link to an Office Document. The most common way we’ve always seen is by direct link, which will download the Office document to your local storage.

Now, there’s another way to link to an Office document and force it to open in Online viewer such as Excel Online, PowerPoint Online, or Word Online. The most possible way is to copy the URL by clicking on the ellipsis next to the filename.

image

Now, you’ll see something similar to this, which will force-open the file in Excel Online:
https://www.contoso.com/Shared%20Documents/Essential%20Regulatory%20Documents%20Inventory%20List%20Template.xlsx?d=we035ad79215c4e82ba32d989b66bacfd

Or at any point of time if you know the direct URL to the file but unsure what is the number or code next to d=, you can just put the link as below.

https://www.contoso.com/Shared%20Documents/Essential%20Regulatory%20Documents%20Inventory%20List%20Template.xlsx?web=1

This way, it will forcefully open the excel (or essentially any Office document) in Online Viewer.

 

WAIT, there’s more! I know that you’re going to complain!

Jul 27, 2016

Using jQuery to Send Email from SharePoint

I’ve been outside of this blog so far and never post anything in this blog. But let me share a small code snippet that can be used. Below is the code to send email from SharePoint.


var EmailInfo = {
  To : { Address : 'radityo.ardi@gmx.com' },
  Subject : 'This is the subject',
  Body : 'This is the <b>Body</b>'
};

$.ajax({
 url: _spPageContextInfo.webServerRelativeUrl +
   '/_api/SP.Utilities.Utility.SendEmail',
 contentType: 'application/json',
 type: "POST",
 headers: {
  "Accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose",
  "X-RequestDigest": $("#__REQUESTDIGEST").val()
 },
 data: JSON.stringify({
  'properties': {
   '__metadata': { 'type': 'SP.Utilities.EmailProperties' },
   'From': 'Radityo Ardi',
   'To': { 'results': [EmailInfo.To.Address] },
   'CC': [],
   'Subject': EmailInfo.Subject,
   'Body': EmailInfo.Body
  }
 }),
 success: function(dataSendEmail) {
  alert('Email successfully sent.');
 },
 error: function(err) {
  console.log(err);
 }
});

Oct 9, 2015

Snippet: Strip HTML tags using jQuery

After quite a long time I never posted anything on my blog, now I’m ready to back. To begin the adventure again, I’ll post 1 interesting code snippet in jQuery.

Basically it is very hard for us to strip HTML tag in JavaScript, but we also know that jQuery is a powerful framework itself. I didn’t recognize this functionality after today I came into one thing in mind, how to strip HTML tag in JavaScript?

DSC_0017[1]

Now, let us see on the snippet below and enjoy…!

function stripHtml(htmlContent) {
    return $("<p>").html(htmlContent).text();
}

Oct 25, 2013

DateDiff and DateAdd Excluding Weekends in SQL Script

Have you ever wonder how to count date differences and add date, but excluding weekends (Saturday and Sunday)? I stumbled on these 2 functions, which I prefer best to do the operation.


CREATE FUNCTION [VARS].[FN_DateAddBusinessDay]
(
    @InputDate DATETIME = NULL,
    @InputDays INT
)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    DECLARE @OutputDateTime DATETIME
    DECLARE @date DATETIME
    DECLARE @days INT
    DECLARE @weeks INT
    DECLARE @i INT = 0
    -- Add the T-SQL statements to compute the return value here
    SET @date = @InputDate
    SET @days = @InputDays
    IF @days >= 1
    BEGIN
        SET @days = @days - 1
    END
    
    IF DATENAME(dw, @date) = 'Saturday'
    BEGIN SET @i = 2 END
    ELSE IF DATENAME(dw, @date) = 'Sunday'
    BEGIN SET @i = 1 END
    SET @date = DATEADD(d, @i, @date)
 
    IF (@days < 0)
    BEGIN
        IF DATEPART(dw, @date) = 1
            SET @date = DATEADD(d, -1, @date)
        SET @weeks = (datepart (dw, @date) + @days - 6)/5
    END
    ELSE
    BEGIN
        IF DATEPART(dw, @date) = 7
            SET @date = DATEADD(d, 1, @date)
        SET @weeks = (DATEPART(dw, @date) + @days - 2)/5
    END    -- Return the result of the function
    SET @OutputDateTime = DATEADD(d, @days + (@weeks * 2), @date);
    RETURN @OutputDateTime;
 
END
 
GO


CREATE FUNCTION [VARS].[FN_DateDiffBusinessDay]
(
    @InputStartDate DATETIME = NULL,
    @InputEndDate DATETIME = NULL
)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @OutputDays INT
    DECLARE @date DATETIME
    DECLARE @days INT
    DECLARE @weeks INT
    -- Add the T-SQL statements to compute the return value here
    IF DATEDIFF(DD, @InputStartDate, @InputEndDate) >= 0
    BEGIN
        SET @OutputDays = (SELECT
           (DATEDIFF(dd, @InputStartDate, @InputEndDate) + 1)
          -(DATEDIFF(wk, @InputStartDate, @InputEndDate) * 2)
          -(CASE WHEN DATENAME(dw, @InputStartDate) = 'Sunday' THEN 1 ELSE 0 END)
          -(CASE WHEN DATENAME(dw, @InputEndDate) = 'Saturday' THEN 1 ELSE 0 END))
    END
    ELSE
    BEGIN
        SET @OutputDays = 0
    END
    RETURN @OutputDays;
 
END

Enjoy, and hope it helps! Winking smile