10 Things That Require ZERO Talent

B+ with Yourself

1. Being on Time
2. Work Ethic
3. Effort
4. Body Language
5. Energy
6. Attitude
7. Passion
8. Being Coach-able
9. Doing Extra
10. Being Prepared

Best way to get FILE NAMES from any Folder in Excel

This is one of the easy way to get File Names from your Windows Folder via "Name Manager".

• At first, create a new Excel worksheet.
• Then, select Cell A1.
• Next, go to “Formulas” tab and click “Name Manager” button.
• In the popup dialog box, click “New” button.

• Subsequently, in the next dialog box, input “Files” in “Name” field.
• And change “=Sheet1!$A$1” to “=FILES(Sheet1!$A$1)” in “Refers to:” field.

• Afterwards, click “OK” and close “Name Manager” dialog box.
• Later, copy the Windows folder path in Cell A1 and add “\*” at the end of the path.
• Next, select the Cell A3 and input the function “=INDEX(Files,ROW()-2)”.

At once, a file in this Windows folder will be listed, like the following image.

• Finally, just copy this function down several rows to list the other files until you see the “#REF!” error. The error means that all files have been listed.

Export High Resolution Images Directly from Microsoft PowerPoint

We know that PowerPoint allow you to save side as JPEG, but default resolution of the image in 96. Here we have a trick where you can increase the resolution.
Follow below steps and will allow you to save JPEG with high resolution.

1. Press "Window + R" and type "regedit"
2. Go to "Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\\PowerPoint\Options". (xx.xx is your Office version folder number)
3. Find / Create new "ExportBitmapResolution" DWORD entry and set Decimal Value as per your required resolution. eg. 196
4. Open your PowerPoint and save slide as JPEG.

You can now see all the slides were saved with high resolution. 

Connect-SPOService : Current site is not a tenant administration site.

Working around with: Connect-SPOService : Current site is not a tenant administration site.

For the “Current site is not a tenant administration site” error message, which may look like following:
Connect-SPOService : Current site is not a tenant administration site.
At line:1 char:1
+ Connect-SPOService -Url https://mysite.sharepoint.com -Credential $credentials
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Connect-SPOService], ServerException
+ FullyQualifiedErrorId : Microsoft.SharePoint.Client.ServerException,Microsoft.Online.SharePoint.PowerShell.Con
nectSPOService

You get this error because you’re not entering the URL for your “Sharepoint Online Administration Center” site… Which is typically your normal sharepoint online URL, but then with a -admin after it, such as:
https://mysite-admin.sharepoint.com (assuming that your sharepoint online site is https://mysite.sharepoint.com). Therefore, then whole Powershell script would be:

Connect-SPOService -Url https://mysite-admin.sharepoint.com -credential username@mysharepointsite.com

NOTE: The -admin site is setup automatically. You don’t have to do anything. Just type in your sitename and then add the -admin.sharepoint.com to the end and you’ll be prompted to log in.

Enable the "Insert Rows" with mouse Right Click in Microsoft Excel

To fix this Issue, just follow below steps:

1. Close all you Microsoft Excel files
2. Goto "%appdata%\Microsoft\Excel" in Windows explorer and rename Excel15.xlb to Excel15.old
3. Start Excel and you will see the 'Insert Row' function is now active.

Now you are able to find "Insert Rows" Enable. 

What is .xlb file ?

XLB is a settings file format created by Microsoft Excel. XLB files contain custom settings for the toolbars (or command bars). These settings can be edited or customized by selecting “toolbar” from the view options. XLB files can contain information such as which toolbars are visible, their position, and their function. XLB files allow for the transfer of customization information between computers and users.

Promoted Links -> Script to show tile in rows and column

Promoted Links in SharePoint always show in a single row, following script help to break it into columns and rows also added style that will modify height and width.

// Copy Paste follow script into "Script Editor" above your Promoted Link web-part.


<style type="text/css"> <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> <script type="text/javascript" src="https://code.jquery.com/jquery-1.10.2.min.js"></script> <script type="text/javascript"> $(document).ready(function () { // Update this value to the number of links you want to show per row var numberOfLinksPerRow = 4; // local variables var pre = " <tr><td><div class='ms-promlink-body' id='promlink_row_"; var post = "'> </div> </td></tr> "; var numberOfLinksInCurrentRow = numberOfLinksPerRow; var currentRow = 1 // find the number of promoted links we're displaying var numberOfPromotedLinks = $('.ms-promlink-body > .ms-tileview-tile-root').length; // if we have more links then we want in a row, let's continue if (numberOfPromotedLinks > numberOfLinksPerRow) { // we don't need the header anymore, no cycling through links $('.ms-promlink-root > .ms-promlink-header').empty(); // let's iterate through all the links after the maximum displayed link for (i = numberOfLinksPerRow + 1; i <= numberOfPromotedLinks; i++) { // if we're reached the maximum number of links to show per row, add a new row // this happens the first time, with the values set initially if (numberOfLinksInCurrentRow == numberOfLinksPerRow) { // i just want the 2nd row to currentRow++; // create a new row of links $('.ms-promlink-root > table > tbody:last').append(pre + currentRow + post); // reset the number of links for the current row numberOfLinksInCurrentRow = 0 } // move the Nth (numberOfLinksPerRow + 1) div to the current table row $('#promlink_row_' + currentRow).append($('.ms-promlink-body > .ms-tileview-tile-root:eq(' + (numberOfLinksPerRow) + ')')); // increment the number of links in the current row numberOfLinksInCurrentRow++; } } $(".ms-tileview-tile-detailsListMedium").attr('style', 'text-align: center'); }); </script>

CSOM Example

You have to reference following two dll file in your project.

• Microsoft.SharePoint.Client.dll
• Microsoft.SharePoint.Client.Runtime.dll

You can find this file at "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell"

example.

using System;
using System.Windows.Forms;
using Microsoft.SharePoint.Client;
using System.Security;

namespace CAML_CSOMDemo
{
    public partial class Form1 : System.Windows.Forms.Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void bntExecute_Click(object sender, EventArgs e)
        {
            using (ClientContext ctx = new ClientContext("https://contoso.sharepoint.com/"))
            {
                string uname = "myemail@contoso.com";
                SecureString upwd = new System.Net.NetworkCredential("", "P@ssw0rd").SecurePassword;
                ctx.Credentials = new SharePointOnlineCredentials(uname,upwd);


                Web myWeb = ctx.Web;
                List productsList = myWeb.Lists.GetByTitle("CSOM_TestList");

                ctx.Load(myWeb);
                ctx.ExecuteQuery();
       
                MessageBox.Show(myWeb.Title);

            }

        }

}// end of class

}// end of namespace

SharePoint '/_layouts/' locations

Some times you want to jump right to a specific system list, page or go to the edit mode in a SharePoint site. I wanted to list all the famous urls I used for the past years and ask you to tell me about any other ones you know to add it here in the list. Other have already contributed a bunch 

(Remember for SharePoint 2013, 2016 you might want to add "15", "16" respectively after "/_layouts/" but if you don't SharePoint will take care of that for you) .. Also some of these are turned off on Office 365 SharePoint online.

Site collection level recycle bin:

/_layouts/15/AdminRecycleBin.aspx

Site level recycle bin (Added by Steve Stewart):

/_layouts/RecycleBin.aspx

Recreate default site sp groups (Added by Neal Bongers):

_layouts/15/permsetup.aspx

Load document tab initial (Added by Dominik Gempeler)

?InitialTabId=Ribbon.Document

Delete user from Site collection (on-premises) (Added by SamDavid):

/_layouts/15/people.aspx?MembershipGroupId=0

Display list in grid view. 'True' is case sensitive (Added by Antoine L.):

?ShowInGrid=True

Quick Launch settings page (Added by Ishani M.):

/_layouts/quiklnch.aspx

Navigation Settings page (Added by Abdur Raheem):

/_layouts/15/AreaNavigationSettings.aspx

Sandboxed Solution Gallery:

/_catalogs/solutions/Forms/AllItems.aspx

Workflow history hidden list:

/lists/Workflow History

Filter toolbar for Lists and libraries (Added by Asimaili):

?Filter=1

Site usage page (Added by @Dnyag):

/_layouts/usage.aspx

Site content and structure  page (Added by @Dnyag):

/_layouts/sitemanager.aspx

Site settings page (Added by Aowworld):

/_layouts/settings.aspx

View all site content page (Site content) (Added by Aowworld):

/_layouts/viewlsts.aspx

Manage site collection features - CASE SENSITIVE -  (Added by Vardhaman):

/_layouts/ManageFeatures.aspx?Scope=Site

Manage site features (Added by Vardhaman):

/_layouts/ManageFeatures.aspx

 Get the version of the SharePoint server (Patch level) (Added by: John Liu):

 /_vti_pvt/Service.cnf

Web Part Maintenance Page (Added by: Ricky):

?Contents=1

Show Page in Dialog View (Added by:Ricky):

?isdlg=1

Application page for registering SharePoint apps

/_layouts/15/appregnew.aspx

Save Site as a template

/_layouts/savetmpl.aspx

Sign in as a different user

/_layouts/closeConnection.aspx?loginasanotheruser=true

Enable SharePoint designer

/_layouts/SharePointDesignerSettings.aspx

Welcome Page (Default page settings)

/_layouts/AreaWelcomePage.aspx

Change Site Master Page

/_layouts/ChangeSiteMasterPage.aspx

Page Layouts and Site Templates

/_Layouts/AreaTemplateSettings.aspx

Master Pages library

/_catalogs/masterpage/Forms/AllItems.aspx

Quick Deploy List

Quick%20Deploy%20Items/AllItems.aspx
 

Open Page in Edit Mode

?ToolPaneView=2

Taxonomy Hidden List (MMS)

Lists/TaxonomyHiddenList/AllItems.aspx

User Information List:

 _catalogs/users

_catalogs/users/simple.aspx

Force displaying the user profile in the site
collection:

/_layouts/userdisp.aspx?id={UserID}&Force=True

Site hierarchy page (lists of sub sites) - (Added by community contributions)

/_layouts/vsubwebs.aspx

/_layouts/1033/vsubwebs.aspx

Reference Site

WE CAN’T DO THAT FOR YOU BECAUSE THE FILE IS NO LONGER CHECKED OUT OR HAS BEEN DELETED IN SHAREPOINT DESIGNER

SCENARIO

In SharePoint Designer, When I tried to “Check In” a file inside, I got the below error:

WE CAN’T DO THAT FOR YOU BECAUSE THE FILE IS NO LONGER CHECKED OUT OR HAS BEEN DELETED

I already have tried to

• Close Share Point Designer and restart it again,
• Close SPD then clear cache from the following folder:
  • %USERPROFILE%\AppData\Local\Microsoft\WebsiteCache
  • %USERPROFILE%\AppData\Roaming\Microsoft\Web Server Extensions\Cache

Unfortunately, the “We can’t do that for you because the file is no longer checked out or has been deleted” still persists.

---

CAUSE

This problem might occur in case, the file title contains disallowed characters like “/” as shown below:

---

SOLUTION

• Right-click on the affected file >  Select “Edit file in advanced mode“.

• Right-click again on the affected file > Select “properties“.

• The properties page should be shown.

• Remove URL > Change the title to an appropriate name by avoiding using the disallowed characters.

The file should be now checked in properly.

Reference of this POST

REST API Compare Date Field

add following "toISOString()" that will allow you to check Date is >,<,>=,<=,!=

var today = new Date();
...$filter=StartDate ge  + today.toISOString() + and ...

Calculate Day in SharePoint

LastAction = '7 Apr 2018'
Created = '1 Apr 2018'

=DATEVALUE(TEXT(LastAction,"dd/mm/yyyy"))-DATEVALUE(TEXT(Created,"dd/mm/yyyy"))

OUTPUT
6

Remove duplicate through JQuery

 
    test1
     
 

 
    test2
     
 
 
    test1
     
 
 
    test2
     
 




var seen = {};
$('table tr').each(function() {
  var txt = $(this).text();
  if (seen[txt])
    $(this).remove();
  else
    seen[txt] = true;
});

Display User Profile Picture in SharePoint 2013 Site

Get the URL from the current user's profile via REST, the image tag used to display when the src is updated:
/_api/sp.userprofiles.peoplemanager/getmyproperties endpoint which returns PersonalUrl property, user profile picture could be requested via _layouts/15/userphoto.aspx?size=&accountname= page, where

size - could be set to S/M/L which stands for Small/Medium/Large image size
accountname - user account name

example : [src='/_layouts/15/userphoto.aspx?size=L&accountname=username@contoso.com' ]

Only Numeric with Single Decimal - JavaScript

//Keypress event on a Class
$('.onlyNumeric').on('keypress', function(e) {
return isNumber(event, this);
   });

//Function to validate Number, Decimal and Negative sign
function isNumber(evt, element) {
        var charCode = (evt.which) ? evt.which : event.keyCode

        if (
            (charCode != 46 || $(element).val().indexOf('.') != -1) &&  
            (charCode < 48 || charCode > 57))
            return false;

        return true;
    }    

Send Email with Powershell

Example1
# You must have Site Collection Access to use following process.

function SendEmail($requestor)
            {
                try
                {
                    $emailTo = $requestor
                    $emailSubject = "Test"
                    $emailBody = "Hi,This is a Test Email."

                    $emailUser = "yourEmail@myDomain.com"
                    $emailPwd = "pass@123"
                    
                    Send-PnPMail -To $emailTo -Subject $emailSubject -Body $emailBody -From $emailUser -Password $emailPwd
                    Write-output "Email sent: Successful!"
                }
                catch
                {
                    #known suppression
                    Write-output "Email sent: Failed! Reason:"$_.Exception.Message.ToString()
                }
            }
SendEmail("myEmail@myDomain.com")


Example 2
# Relay emailID example.

$smtp = new-object Net.Mail.SmtpClient("relay-mailbox.com") #Dns name or IP

$smtp.Send("fromEmail@myDomain.com", "toEmail@myDomain.com", "PowerShell Email", "This is a email from powershell")

Delete List Item with Excel Data Using Powershell

function DeleteListItem(){

    $inputfile = Get-FileName "c:\"
    if($inputfile -ne "")
    {
        $objExcel=New-Object -ComObject Excel.Application
        $objExcel.Visible=$false
        $WorkBook=$objExcel.Workbooks.Open($inputfile)
        $worksheet = $WorkBook.sheets.Item(1)
    
        ##########################
        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count
        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
        {
            $ID = $worksheet.cells.item($intRow,1).value2
            Remove-PnPListItem -List $listName -Identity $ID -Force # -Force will delete without confirm
        }  
        $WorkBook.close()
        $objexcel.quit()

        ##########################
    }
}

Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}

DeleteListItem # Call Function

Update List Item with Excel Data Using Powershell

function UpdateListItem(){

    $inputfile = Get-FileName "c:\"

    if($inputfile -ne "")

    {

        $objExcel=New-Object -ComObject Excel.Application

        $objExcel.Visible=$false

        $WorkBook=$objExcel.Workbooks.Open($inputfile)

        $worksheet = $WorkBook.sheets.Item(1)

    

        ##########################

        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count

        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)

        {

            $ID = $worksheet.cells.item($intRow,1).value2

            $Title = $worksheet.cells.item($intRow,2).value2

            $Description = $worksheet.cells.item($intRow,3).value2

            $itemValue = @{"Title"=$Title;"Description"=$Description} #item value

            Set-PnPListItem -List $listName -Identity $ID -Values $itemValue            

        }  

        $WorkBook.close()

        $objexcel.quit()

        ##########################

    }

}

Function Get-FileName($initialDirectory)

{

    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null

    

    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog

    $OpenFileDialog.initialDirectory = $initialDirectory

    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"

    $OpenFileDialog.ShowDialog() | Out-Null

    $OpenFileDialog.filename

}

UpdateListItem # Call Function

Create List Item with Excel Data Using Powershell

function AddListItem(){

    $inputfile = Get-FileName "c:\"
    if($inputfile -ne "")
    {
        $objExcel=New-Object -ComObject Excel.Application
        $objExcel.Visible=$false
        $WorkBook=$objExcel.Workbooks.Open($inputfile)
        $worksheet = $WorkBook.sheets.Item(1)
    
        ##########################
        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count
        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
        {
            $Title = $worksheet.cells.item($intRow,1).value2
            $Description = $worksheet.cells.item($intRow,2).value2

            $itemValue = @{"Title"=$Title;"Description"=$Description} #item value
            Add-PnPListItem -List $listName -Values $itemValue
        }  
        $WorkBook.close()
        $objexcel.quit()

        ##########################
    }
}
# Below function for Dialog Box to select file.
Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}

AddListItem # Call Function

capitalize , lowercase, uppercase to HTML input Text

I have found number of JavaScript to convert text to upper while keypress. But the way we can use CSS is the best... capitalize , lowercase, uppercase

Css :
input.upper { text-transform: uppercase; } input.lower { text-transform: lowercase; } input.capital { text-transform: capitalize; }

Input Field Example.

UPPER CASE :
lower case :
Capital Case :

Download URL Link using VBA

'Add module in VBA, Copy and Paste following code into it.

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim strPath As String

    '~~> Name of the sheet which has the list
    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LastRow '<~~ 2 because row 1 has headers
        Ret = URLDownloadToFile(0, ws.Range("A" & i).Value, ws.Range("B" & i).Value, 0, 0)
'URLDownloadToFile(0, ,
        If Ret = 0 Then
            ws.Range("D" & i).Value = "File successfully downloaded"
        Else
            ws.Range("D" & i).Value = "Unable to download the file"
        End If
    Next i
End Sub

SharePoint REST Filters Operators

Filter operators

Following are the various filter operators that can be used with REST interface

eq Equal to
ne Not equal to
gt Greater than
ge Greater than or equal to
lt Less than
le Less than or equal to
and Logical and
or Logical or
not Logical negation
add Addition
sub Subtraction
mul Multiplication
div Division
mod Modulo
( ) Precedence grouping

SP.ClientContext (siteURL) error this.set_formDigestHandlingEnabled

When you are using Site URL to get ClientContext use 'new' keyword while define else you will see error...
this.set_formDigestHandlingEnabled


example.

$(document).ready(function () {

    //get context 
    context = new SP.ClientContext(siteURL);
    web = context.get_web();

});