functions/New-ADSWorkBook.ps1

<#
.SYNOPSIS
Creates a New Azure Data Studio WorkBook
 
.DESCRIPTION
This will create a new Azure Data Studio Notebook
 
.PARAMETER Path
The full path where you want the notebook saved - must end with .ipynb
 
.PARAMETER cells
An array of New-AdsWorkBookCells to add to the WorkBook
 
.PARAMETER Type
The type of WorkBook to create - SQL or PowerShell
 
.EXAMPLE
$introCelltext = "# Welcome to my Auto Generated SQL Notebook
 
## Automation
Using this we can automate the creation of notebooks for our use
"
$SecondCelltext = "## Running code
The next cell will have some code in it for running
 
## Server Principals
Below is the code to run against your instance to find the server principals that are enabled"
 
$thirdcelltext = "SELECT Name
FROM sys.server_principals
WHERE is_disabled = 0"
$Intro = New-ADSWorkBookCell -Type Text -Text $introCelltext
$second = New-ADSWorkBookCell -Type Text -Text $SecondCelltext
$third = New-ADSWorkBookCell -Type Code -Text $thirdcelltext
 
$path = 'C:\temp\AutoGenerated.ipynb'
New-ADSWorkBook -Path $path -cells $Intro,$second,$third -Type SQL
 
Creates 3 cells with New-AdsWorkBookCells to add to the workbook,
two text ones and a code one, then creates a SQL Notebook with
those cells and saves it as C:\temp\AutoGenerated.ipynb
 
.EXAMPLE
$introCelltext = "# Welcome to my Auto Generated PowerShell Notebook
 
## dbatools
![image](https://user-images.githubusercontent.com/6729780/68845538-7afcd200-06c3-11ea-952e-e4fe72a68fc8.png)
 
dbatools is an open-source PowerShell Module for administering SQL Servers.
You can read more about dbatools and find the documentation at [dbatools.io](dbatools.io)
"
$SecondCelltext = "### Installation
You can install dbatools from the PowerShell Gallery using `Install-Module dbatools`
"
 
$thirdcelltext = "Install-Module dbatools"
 
$fourthCelltext = "### Getting Help
You should always use `Get-Help` to fins out how to use dbatools (and any PowerShell) commands"
 
$fifthcelltext = "Get-Help Get-DbaDatabase"
$sixthCelltext = "Try a command now. get the name, owner and collation of the user databases on the local instance"
$seventhCellText = "Get-DbaDatabase -SqlInstance localhost -ExcludeSystem | Select Name, Owner, Collation"
 
$Intro = New-ADSWorkBookCell -Type Text -Text $introCelltext
$second = New-ADSWorkBookCell -Type Text -Text $SecondCelltext
$third = New-ADSWorkBookCell -Type Code -Text $thirdcelltext
$fourth = New-ADSWorkBookCell -Type Text -Text $fourthCelltext
$fifth = New-ADSWorkBookCell -Type Code -Text $fifthcelltext
$sixth = New-ADSWorkBookCell -Type Text -Text $sixthCelltext
$seventh = New-ADSWorkBookCell -Type Code -Text $seventhCellText
 
$path = 'C:\temp\dbatools.ipynb'
New-ADSWorkBook -Path $path -cells $Intro,$second,$third,$fourth,$fifth,$sixth,$Seventh -Type PowerShell
 
Creates 7 cells with New-AdsWorkBookCells to add to the workbook,
four text ones and three code ones, then creates a PowerShell Notebook with
those cells and saves it as C:\temp\dbatools.ipynb
 
.NOTES
Rob Sewell 10/10/2019 - Initial
Rob Sewell 14/11/2019 - Added PowerShell Notebooks type
#>


function New-ADSWorkBook {
    [cmdletbinding(SupportsShouldProcess)]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidTrailingWhitespace", "", Justification = "the markdown requires a trailing space to work correctly")]
    Param(
        # The full path to the file
        [Parameter(Mandatory)]
        [ValidateScript({
            if($_ -match '^*.ipynb'){
                $true
            }
            else{
                Throw [System.Management.Automation.ValidationMetadataException] "The path $($_) does not have the correct extension. It needs to be .ipynb"
            }
        })]
        [string]
        $Path,
        # The cells (in order) created by New-ADSWorkBookCell to build the notebook
        [Parameter(Mandatory)]
        [pscustomobject[]]
        $cells,
        # The type of notebook
        [Parameter(Mandatory)]
        [ValidateSet('SQL','PowerShell')]
        [string]
        $Type
    )
    $PSCmdlet.WriteVerbose('Lets create a Notebook of type $type')
    $PSCmdlet.WriteVerbose('Creating the base object')
    switch ($type) {
        'PowerShell' {
            $Base = [PSCustomObject]@{
                metadata       = [PSCustomObject]@{
                    kernelspec = [PSCustomObject]@{
                        name         = 'powershell'
                        display_name = 'PowerShell'
                    }
                }
                language_info  = [PSCustomObject]@{
                    name    = 'powershell'
                    version = ''
                    codemirror_mode = 'shell'
                    mimetype = 'text/x-sh'
                    file_extension = '.ps1'
                }
                nbformat_minor = 2
                nbformat       = 4
                cells          = @()
            }
        }
        'SQL' {
            $Base = [PSCustomObject]@{
                metadata       = [PSCustomObject]@{
                    kernelspec = [PSCustomObject]@{
                        name         = 'SQL'
                        display_name = 'SQL'
                        language     = 'sql'
                    }
                }
                language_info  = [PSCustomObject]@{
                    name    = 'sql'
                    version = ''
                }
                nbformat_minor = 2
                nbformat       = 4
                cells          = @()
            }
        }
    }


    $PSCmdlet.WriteVerbose('Adding the array of cells to the base object')
    foreach ($cell in $cells) {
        $base.cells += $cell
    }
    $PSCmdlet.WriteVerbose('Finished adding the array of cells to the base object')
    $PSCmdlet.WriteVerbose('Creating the json and replacing the back slashes and double quotes')
    try {
        if($IsCoreCLR){
            $base = ($Base | ConvertTo-Json -Depth 4 ).Replace('\\r ', '\r').Replace('\\n ', '\n').Replace('"\', '').Replace('\""','"')
        }
        else{
            # Grr PowwerShell
            $base = ($Base | ConvertTo-Json -Depth 4 ).Replace('\\r ', '\r').Replace('\\n ', '\n').Replace('\"\u003e','\">').Replace('"\"\u003c','"<').Replace('"\"', '"').Replace('\""','"').Replace('\u003c','<').Replace('\u003e','>')
        }
    }
    catch {
        $PSCmdlet.WriteWarning('Failed to create the json for some reason. Run `$error[0] | fl -force to find out why')
        Return
    }
    $PSCmdlet.WriteVerbose('json created')
    if ($PSCmdlet.ShouldProcess("$path", "Creating File")) {
        $Base | Set-Content -Path $path
    }
    $PSCmdlet.WriteVerbose('Created json file at' + $path + ' - Go and open it in Azure Data Studio')
}