ReferenceManagerPackage fails to install, VS 2017 Community edition (unable to add reference)

CD C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\PublicAssemblies

Step 1: Launch Developer Command Prompt for VS 2017
Step 2: CD C:\Program Files\Microsoft Visual Studio\2017\Community\Common7\IDE\PublicAssemblies
Step 3: Run below command
gacutil -i Microsoft.VisualStudio.Shell.Interop.11.0.dll

You should now see
Assembly successfully added to the cache

Restart Visual Studio and hopefully all will be well and you can add references.

Split Comma Separated Values Into Columns With Text To Columns Function

Step 1: Select the range of cells you want to split values into columns, and then click Data > Text to Columns. See screenshot:

Step 2: In the first Convert Text to Columns Wizard dialog box, select the Delimited option, and then click the Next button.

Step 3: In the second Convert Text to Columns Wizard dialog box, only check the Comma box in the Delimiters section, and click the Next button.

Step 4: In the last Convert Text to Columns Wizard dialog box, select a cell for locating the splitting values in the Destination box, and finally click the Finish button. See screenshot:

Now all the values in selected cells which were separated by commas are split to different columns as bellow screenshot shown.

Get App Expiry Dates using Powershell

Step 1:
Connect-MsolService

Step 2:
$applist = Get-MsolServicePrincipal -all  |Where-Object -FilterScript { ($_.DisplayName -notlike "*Microsoft*") -and ($_.DisplayName -notlike "autohost*") -and  ($_.ServicePrincipalNames -notlike "*localhost*") }

Step 3:
foreach ($appentry in $applist) {
    $principalId = $appentry.AppPrincipalId
    $principalName = $appentry.DisplayName

    Get-MsolServicePrincipalCredential -AppPrincipalId $principalId -ReturnKeyValues $false | ? { $_.Type -eq "Password" } | % { "$principalName;$principalId;" + $_.KeyId.ToString() +";" + $_.StartDate.ToString() + ";" + $_.EndDate.ToString() } | out-file -FilePath d:\appsec.txt -append
}

List Item Count in SharePoint Online

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



function GetListItemCount($siteUrl)
{
    #*** you can also move below line outside the function to get rid of login again if you need to call the function multiple time. ***
    $Cred= Get-Credential
 
    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
    $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
    $ctx.Credentials = $credentials

    $web = $ctx.Web

    $lists = $web.Lists
    $ctx.Load($lists)
    $ctx.ExecuteQuery()
    Write-Host -ForegroundColor Yellow "The site URL is" $siteUrl

    #output the list item count
    $tableListNames = foreach ($list in $lists)
    {
        $objList = @{
        "List Name" = $list.Title
        "No. of Items" = $list.ItemCount
        }
        New-Object psobject -Property $objList
    }

    Write-Host -ForegroundColor Green "List item count completed successfully"
    return $tableListNames;
}

GetListItemCount "https://SharepointSite"| Out-GridView

#GetListItemCount "https://SharepointSite"| ExportCsv -Path "C:\itemcount.csv"

Embedding PowerApps As A Web Part On SharePoint Page

It is now possible to embed PowerApps into SharePoint Online pages as web parts using iframes. The PowerApps and the SharePoint Site (where the app is to be embedded) need to be part of the same Office 365 Tenancy.

Although an app with mobile layout can be embedded on the SharePoint page, for better usability and look-feel, it is recommended to create an app with tab layout.

I have used the ‘Asset Checkout’ tab layout default template from PowerApps to create the app.

• To embed the app in a SharePoint Page, we will need to generate the embeddable URI for the app. For this, get the App id. Go to the app details page on web.powerapps.com and the app id will be listed as a GUID on the app's details page.

• The iframe code is as follows - 
  
  <iframe width="98%" height="98%" src="https://web.powerapps.com/webplayer/iframeapp?hideNavBar=true&source=iframe&screenColor=rgba(104,101,171,1)&appId=/providers/Microsoft.PowerApps/apps/[yourAppID]" />

• The width and height propertiesare set to 98%, to ensure that there is no grey background area around your app.
• hideNavBar - It is a Boolean which controls whether the PowerApps header is visible or not.
• screenColor- controls the loading screen color while the app loads
  
• Create a page on your SharePoint Site and embed the above code using the "Edit" mode.
  
• You will be able to access PowerApps from your SharePoint Page.
  
  
  
  

This is not the final version of the capability and it is expected that the Microsoft team will bring in a better way to do this in future.

Remove all users from particular Sharepoint Group

#Load SharePoint Online Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Function to remove all users from a group
Function Remove-AllUserFromGroup()
{
  param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $GroupName
    )
   Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
       
        #Get the Group
        $Group=$Ctx.web.SiteGroups.GetByName($GroupName)
        $Ctx.Load($Group)
        $Ctx.ExecuteQuery()

        #Get users of the group
        $GroupUsers = $Group.Users
        $Ctx.Load($GroupUsers)
        $Ctx.ExecuteQuery()

        #Remove all users from the group
        ForEach($User in $GroupUsers)
        {
            $Group.Users.RemoveByLoginName($User.LoginName)
        }
        $Ctx.ExecuteQuery()
        Write-host "All Users are Removed from the Group!" -ForegroundColor Green     
    }
    Catch {
        write-host -f Red "Error Removing All Users from Group!" $_.Exception.Message
    }
}

#Set parameter values
$SiteURL = "Site Collection URL"
$GroupName="Group Name from which you want to delete"

#Call the function to remove all users from group
Remove-AllUserFromGroup -SiteURL $SiteURL -GroupName $GroupName

Get all the users per SharePoint Group in a SharePoint Online Site

$host.Runspace.ThreadOptions = "ReuseThread"

#Definition of the function that gets all the site collections information in a SharePoint Online tenant
function Get-SPOSharePointUsersPerGroup
{
    param ($sSPOAdminCenterUrl,$sSiteUrl,$sUserName,$sPassword)
    try
    {   
        Write-Host "--------------------------------------------------------------------------------"  -foregroundcolor Green
        Write-Host "Getting all Users per Group in a SharePoint Online Site" -foregroundcolor Green
        Write-Host "--------------------------------------------------------------------------------"  -foregroundcolor Green   
        $msolcred = Get-Credential -UserName $sUserName -Message $sMessage
        Connect-SPOService -Url $sSPOAdminCenterUrl -Credential $msolcred
        $spoGroups=Get-SPOSiteGroup -Site $sSiteUrl

        foreach($spoGroup in $spoGroups){       
           Write-Host "Users in " $spoGroup.Title ":"
           $spoUsers=Get-SPOUser -Site $sSiteUrl -Group $spoGroup.Title
           Write-Host " -> " $spoUsers.LoginName
           Write-Host "---------------------------------------------------" -ForegroundColor Green
        }
    }
    catch [System.Exception]
    {
        write-host -f red $_.Exception.ToString() 
    }   
}

#Required Parameters
$sSiteUrl = "https://<SharePointOnline_SiteUrl"
$sUserName = "<SharePointOnlineUser>"
$sMessage="<Custom_Prompt_Message>"
$sSPOAdminCenterUrl="https://<SPODomain>-admin.sharepoint.com/"

Get-SPOSharePointUsersPerGroup -sSPOAdminCenterUrl $sSPOAdminCenterUrl -sSiteUrl $sSiteUrl -sUserName $sUsername -sPassword $sPassword

Get All Users and Groups using Powershell in Sharepoint Online Site

#Load SharePoint CSOM Assemblies
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"
 
#Set Variables for Site URL
$SiteURL= "https://SharepointSiteCollectionURL/"

#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

Try {
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Cred

    #Get all Groups
    $Groups=$Ctx.Web.SiteGroups
    $Ctx.Load($Groups)
    $Ctx.ExecuteQuery()

    #Get Each member from the Group
    Foreach($Group in $Groups)
    {
        Write-Host "--- $($Group.Title) --- "

        #Getting the members
        $SiteUsers=$Group.Users
        $Ctx.Load($SiteUsers)
        $Ctx.ExecuteQuery()
        Foreach($User in $SiteUsers)
        {
            Write-Host "$($User.Title), $($User.Email), $($User.LoginName)"
        }
    }
}
Catch {
    write-host -f Red "Error getting groups and users!" $_.Exception.Message
}

Create site collection using custom site template SharePoint Online

1. simply proceed as you would normally to create a new Site Collection, however when it comes time to select a template to base the new site collection select the Custom tab (on the extreme right), which should only have one option – < Select template later … >.
2. Select this option and continue with the new site collection creation process.
3. Now navigate to the new site collection URL. You will see the Solution Gallery heading.
4. Click on the Solution Gallery, it will empty( for first time).
5. Now you need to upload your Custom Site template.
6. After Uploading, please make sure it is Activated if not click on the Activate button.
7. Once activated close the dialog and Now You will this template under Custom Tab
8. Select the Custom Site Template and Click Ok

Duplicate Site Collection

Steps to Duplicate Site Collection in Backup in Same Web Application
==================================================

Step1:
Create Empty Site Collection

Step2:
Create Empty Database

Step3:
Add Database as Content DB

Step4:
Mount Database to Created Site Collection

Add Content Database
stsadm -o addcontentdb -url http://server:port/ -databasename WSS_Content_New -assignnewdatabaseid  -databaseserver  yourdatabaseserver

Step5:
Restore the Backup File to newly created Site Collection
Backup-SPSite -Identity http://server:port/ -path D:\Portal_05May2013.bak
Restore-SPSite -Identity http://server:port/ -path D:\Portal_10Jun2014.bak -force


Mount-SPContentDatabase "<ContentDb>" -DatabaseServer "<DbServer>" -WebApplication http://SiteName

Generate Document info Report

[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Portal, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Publishing, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")

function Get-DocInventory([string]$siteUrl) {
$site = New-Object Microsoft.SharePoint.SPSite $siteUrl
foreach ($web in $site.AllWebs) {
foreach ($list in $web.Lists) {
if ($list.BaseType -ne “DocumentLibrary”) {
continue
}

foreach ($item in $list.Items) {
$data = @{
"Site" = $site.Url
"Web" = $web.Url
"list" = $list.Title
"Item ID" = $item.ID
"Item URL" = $item.Url
"Item Title" = $item.Title
"Item Created" = $item["Created"]
"Item Modified" = $item["Modified"]
"Created By" = $item["Author"]
"Modified By" = $item["Editor"]
"File Size" = $item.File.Length/1KB
"File Size (MB)" = $item.File.Length/1MB
}
New-Object PSObject -Property $data
}
}
$web.Dispose();
}
$site.Dispose()
}

Get-DocInventory "http://sp2013" | Out-GridView
Get-DocInventory "http://sp2013" | Export-Csv -NoTypeInformation -Path "c:\Document_Detail_Report.csv"

Get Attached Event Receivers for List in On Premises

Add-PSSnapin Microsoft.Sharepoint.Powershell

$site = Get-SPSite -Identity "http://SiteURL"
$web = $site.RootWeb
$list = $web.Lists["test"]
$list.EventReceivers | Select assembly, name, type

Dashboard in Sharepoint

# Add SharePoint cmdlets reference
Add-PSSnapin "Microsoft.SharePoint.Powershell" -ErrorAction SilentlyContinue

#Enable Dashboard
$contentSvc = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
$devDahsboardSettings = $contentSvc.DeveloperDashboardSettings
$devDahsboardSettings.DisplayLevel = "On"
$devDahsboardSettings.Update()

#Disable Dashboard
$contentSvc = ([Microsoft.SharePoint.Administration.SPWebService]::ContentService)
$devDahsboardSettings =$contentSvc.DeveloperDashboardSettings
$devDahsboardSettings.DisplayLevel = [Microsoft.SharePoint.Administration.SPDeveloperDashboardLevel]::Off
$devDahsboardSettings.Update()

Update App Credentials using Powershell

 import-module MSOnline
 $msolcred = get-credential
 connect-msolservice -credential $msolcred

 $clientId = "ac3f9ccc-edee-4ceb-943a-4870758c4264"
 $keys = Get-MsolServicePrincipalCredential -AppPrincipalId $clientId
 Remove-MsolServicePrincipalCredential -KeyIds @("7a5f64f2-93eb-42c6-8ab8-dd25940838dc","0abb7c26-b95e-4be9-9b4e-82b99a8cbd18","165bd912-093e-4aec-a286-221290cb31e7","5e93eb02-29fa-4e74-b5ac-f6d60aedbf03","5e93eb02-29fa-4e74-b5ac-f6d60aedbf03","5e93eb02-29fa-4e74-b5ac-f6d60aedbf03") -AppPrincipalId $clientId

 $bytes = New-Object Byte[] 32
 $rand = [System.Security.Cryptography.RandomNumberGenerator]::Create()
 $rand.GetBytes($bytes)
 $rand.Dispose()
 $newClientSecret = [System.Convert]::ToBase64String($bytes)
 New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Symmetric -Usage Sign -Value $newClientSecret -StartDate (Get-Date) -EndDate (Get-Date).AddYears(1)
 New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Symmetric -Usage Verify -Value $newClientSecret -StartDate (Get-Date) -EndDate (Get-Date).AddYears(1)
 New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Password -Usage Verify -Value $newClientSecret -StartDate (Get-Date) -EndDate (Get-Date).AddYears(1)
 $newClientSecret

Custom Callout

_spBodyOnLoadFunctionNames.push('_executeCallout');

function _executeCallout()
{ 
ExecuteOrDelayUntilScriptLoaded(executeCallout, 'callout.js');
}

function executeCallout()
{
        SP.SOD.executeFunc("callout.js", "Callout", function () {
            var itemCtx = {};
            itemCtx.Templates = {};
            itemCtx.BaseViewID = 'Callout';
            itemCtx.ListTemplateType = 101;
            itemCtx.Templates.Footer = function (itemCtx) {
                return CalloutRenderFooterTemplate(itemCtx, AddCustomAction, true);
            };
            SPClientTemplates.TemplateManager.RegisterTemplateOverrides(itemCtx);
        });
}

        function AddCustomAction(renderCtx, calloutActionMenu) {
            CalloutOnPostRenderTemplate(renderCtx, calloutActionMenu);
           
            calloutActionMenu.addAction(new CalloutAction({
                text: Strings.STS.L_CalloutFollowAction,
                tooltip: Strings.STS.L_CalloutFollowAction_Tooltip,
                onClickCallback: function (calloutActionClickEvent, calloutAction) {
                    var callout = GetCalloutFromRenderCtx(renderCtx);
                    if (!(typeof (callout) === 'undefined' || callout === null))
                        callout.close();
                    SP.SOD.executeFunc('followingcommon.js', 'FollowSelectedDocument', function () { FollowSelectedDocument(renderCtx); });
                }
            }));
  if (renderCtx.CurrentItem.File_x0020_Type.toLowerCase() != "") {
            calloutActionMenu.addAction(new CalloutAction({
                text: "Ramesh Callout",
                tooltip: 'Ramesh Callout',
                onClickCallback: function () {
                    CallBackFunc(renderCtx, 'Lookup', '/sites/sitecollectionname/SitePages/SendDocument.aspx');
                }
            }));
}
        }

        function CallBackFunc(renderCtx, DispTitle, PageUrl) {
          //debugger;
         
              var docUrl = renderCtx.CurrentItem.FileRef;
            var FileName = renderCtx.CurrentItem.FileLeafRef;
            var listName = renderCtx.ListTitle;
            currentCtx = renderCtx;

            var currentItem = renderCtx.CurrentItem;
            var currentItemID = renderCtx.CurrentItem.ID;
            var PgURL = '/sites/sitecollectionname/SitePages/SendDocument.aspx?FileName=' + FileName + '&FilePath=' + docUrl + '&ListName='+ listName;
           //alert(pgURL);
         
            var viewportWidth = $(window).width();
            var viewportHeight = $(window).height();

            var options = {
                url: PgURL,
                title: '' + DispTitle,
width: viewportWidth * 0.8,
                height: viewportHeight
            }

            SP.SOD.execute('sp.ui.dialog.js', 'SP.UI.ModalDialog.showModalDialog', options);
            $('#lblFileName').val(FileName);
            $('#lblFileID').val(currentItemID);
        }

        function CloseSPUIPopoup() {
            SP.UI.ModalDialog.commonModalDialogClose(SP.UI.DialogResult.Cancel);
        }