functions/New-ADSWorkBook.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
<#
.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
 
.EXAMPLE
$introCelltext = "# Welcome to my Auto Generated 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
 
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
 
.NOTES
Rob Sewell 10/10/2019 - Initial
#>


function New-ADSWorkBook {
    [cmdletbinding(SupportsShouldProcess)]
    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
    )
    $PSCmdlet.WriteVerbose('Lets create a Notebook')
    $PSCmdlet.WriteVerbose('Creating the base object')
    $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')
}