Create a set of folders based on information and Logic in T-SQL View

IT Services

Create a set of folders based on information and Logic in T-SQL View

This code snippet will enable you to use logic created in a T-SQL database and create folders within a SharePoint Library based on the T-SQL View’s content

##########################################################
#SHAREPOINT CONFIGURATION
##########################################################


    ### Get the user credentials
    #$credential = Get-Credential
    $username = '[email protected]'
    $password = 'YourPassword' #If you use 2FA - set at App Password here
    $securePassword = ConvertTo-SecureString $password -AsPlainText -Force

    ### Input Parameters
    $url = 'https://domain.sharepoint.com/sites/sitename'
    $libname ='Shared Documents'

    #NOTE YOU WILL NEED THE SHAREPOINT POWERSHELL ENGINE INSTALLED
    ### References
    # Specified the paths where the dll's are located.
    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'

##########################################################
#SHAREPOINT FOLDER CREATE FUNCTION
##########################################################

    function CreateFolderWithPermissions()
    {
        # Connect to SharePoint Online and get ClientContext object.
        $clientContext = New-Object 
Microsoft.SharePoint.Client.ClientContext($url)
        $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
        $clientContext.Credentials = $credentials

        # Get the SharePoint web
        $web = $clientContext.Web;
	    $clientContext.Load($web)

        #Create Folder
        $folder = $web.Folders.Add($libname + '\' + '(' + $ID + ') - ' +  $Description)
        $clientContext.Load($folder)
        $clientContext.ExecuteQuery()

        #Create Gas Servicing Folder
        $folder = $web.Folders.Add($libname + '\' + '(' + $ID+ ') - ' +  $Description+ "\" + "Sub Folder")
        $clientContext.Load($folder)
        $clientContext.ExecuteQuery()

        #Create Electircal Servicing Folder
        $folder = $web.Folders.Add($libname + '\' + '(' + $ID+ ') - ' +  $Description+ "\" + "Another Sub Folder")
        $clientContext.Load($folder)
        $clientContext.ExecuteQuery()


    }

##########################################################
#SQL CONFIGURATION
##########################################################
    Import-Module SqlServer 

    #Database Information
    $ServerInstance = "SERVERLANME\INATANCENAME"
    $DatabaseName = "DatabaseName"
    $TotalCountAssetToCreateView = "NumberOfItemsToCreateView"
    $AssetToCreateDataView = "FolderNameDataView"

##########################################################
#DO ACION LOOP USING SQL QUERY AND SHAREPOINT FOLDER
##########################################################

    #Get Count
    $AssetCountTable = Read-SQLViewData -ServerInstance $ServerInstance -DatabaseName $DatabaseName -SchemaName "dbo" -ViewName $TotalCountAssetToCreateView -TopN 1
    $AssetCountToCreate = $AssetCountTable.ASSET_COUNT

    IF ($AssetCountToCreate -gt $ZeroAsset){

    Do
    {
        $AssetDetailTable = Read-SQLViewData -ServerInstance $ServerInstance -DatabaseName $DatabaseName -SchemaName "dbo" -ViewName $AssetToCreateDataView -TopN 1
        $ID= $AssetDetailTable.ID
        $Description= $AssetDetailTable.Description
    
#Execute the function
CreateFolderWithPermissions

#Use -Counter to ensure once all records are processed the loop stops
    } While ($AssetCountToCreate -gt $ZeroAsset)

    }
    Else {

    #Do Nothing

    }

#CODE END

Leave a Reply

Your email address will not be published. Required fields are marked *