Tip: Out-ExcelClipboard in PowerShell

From time to time I want to get some result from a PowerShell command into Excel. Unless I’m missing something, there isn’t a great option to do this in standard PowerShell.

I know that you can use Export-Csv or Export-Clixml and then open the file in Excel. I know you can use Out-GridView and then copy and paste from there into Excel (albeit without the column headings). You can also use .Net types but I just want to pipe my result to a function and then paste into Excel. I want <some output of previous functions> | Out-ToExcel

function Out-ExcelClipboard {
    param(
        [Parameter(ValueFromPipelineByPropertyName, ValueFromPipeline)]
        $InputObject
    )
    begin {
        $Objects = @()
    }
    process {
        $Objects += $InputObject
    }
    end {
        $Objects | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Set-Clipboard
    }
}

Export-ModuleMember -Function Out-ExcelClipboard

I’ve added this function to my module which I load by default in my PowerShell profile. This took more code than I was expecting. For the curious, ValueFromPipeline tells PowerShell to bind the output from the pipeline to this parameter. It then calls the process block for each value in the pipeline.

You can add begin and end blocks to do extra stuff before and after all the values have been processed. In my case, stuff them all into a new collection, convert to tab-delimited text without including any type information and then pass that value to the clipboard. Lovely.

Tip: Share a Git Hooks Directory Across Your Repositories

TL;DR

git config --global core.hookspath '<path to hooks directory>'

Sharing Hooks Across Repos

I posted before about using a pre-commit hook to check that I’m not committing anything that I really shouldn’t be (anything I’ve tagged with //DONOTCOMMIT).

Hooks are specified in the .git/hooks directory. That’s great, a git repository is completely contained within its parent folder, you can copy it somewhere else and all of the code, history and config come with it.

It’s not so convenient if you want to create some hooks that apply across multiple repositories though. You can just copy your hook files between all of your repos, or it turns out that there is a smarter way. Git config has a core.hookspath key. You can create a folder somewhere with the hooks that you want to apply to all repos and set this key.

Use git config --global to set the value of a key in the global config file and git config --global --list to list the config keys and their current values.

git config --global core.hookspath '<path to hooks directory>'

Tip: Editing RapidStart Configuration Package Files

TL;DR

Extract the package with 7-Zip
Open the extracted file in a VS Code / Notepad++ / text-editor-of-choice
Edit the xml as required
Use 7-Zip to compress in gzip format

Editing Config Packages

Sometimes you might want to edit a config package file without having to import and export a modified copy from BC. In my case I wanted to remove the Social Listening Setup table from the package. Microsoft have made this table obsolete and BC throws an error if I try to import the package with this table present. (Probably not a bad idea – stopping listening to socials).

Fortunately, a rapidstart file is just a compressed xml file. Extract the rapidstart file with 7-Zip and then open the extracted file in a text editor. The format of the file is pretty straight forward. Each table is represented with an XYZList node where XYZ is the name of the table which the table-level settings followed by one or more XYZ nodes with the data.

Here are two records for the Payment Terms table.

<PaymentTermsList>
  <TableID>3</TableID>
  <PageID>4</PageID>
  <SkipTableTriggers>1</SkipTableTriggers>
  <PaymentTerms>
    <Code PrimaryKey="1" ProcessingOrder="1">10 DAYS</Code>
    <DueDateCalculation ProcessingOrder="2">&lt;10D&gt;</DueDateCalculation>
    <DiscountDateCalculation ProcessingOrder="3">
    </DiscountDateCalculation>
    <Discount ProcessingOrder="4">0</Discount>
    <Description ProcessingOrder="5">Net 10 days</Description>
    <CalcPmtDisconCrMemos ProcessingOrder="6">0</CalcPmtDisconCrMemos>
    <LastModifiedDateTime ProcessingOrder="7">
    </LastModifiedDateTime>
    <Id ProcessingOrder="8">{6BD87497-B233-EB11-8E89-E8FD151D8C93}</Id>
  </PaymentTerms>
  <PaymentTerms>
    <Code>14 DAYS</Code>
    <DueDateCalculation>&lt;14D&gt;</DueDateCalculation>
    <DiscountDateCalculation>
    </DiscountDateCalculation>
    <Discount>0</Discount>
    <Description>Net 14 days</Description>
    <CalcPmtDisconCrMemos>0</CalcPmtDisconCrMemos>
    <LastModifiedDateTime>
    </LastModifiedDateTime>
    <Id>{6DD87497-B233-EB11-8E89-E8FD151D8C93}</Id>
  </PaymentTerms>
</PaymentTermsList>

All I need to do is find the offending Social Listening Setup node in my file and remove it. Here it is:

Once you are finished editing you can use 7-Zip to compress the file again with the gzip method and import.

Tip: Test for Tables Missing from Permission Sets

In PowerShell:

$tablesInPermissionSets = @()

$permissionSets = gci . -Recurse -Filter '*.al' | ? {(gc $_.FullName).Item(0).startsWith('permissionset')}
 $permissionSets | % {
    $content = gc $_.FullName -Raw
    [Regex]::Matches($content, '(?<=tabledata ).*(?= =)') | % {
        $tablesInPermissionSets += $_.Value
    }
 }

$tablesInTables = @()

$tables = gci . -Recurse -Filter '*.al' | Where-Object {(Get-Content $_.FullName).Item(0).StartsWith('table ')}
 $tables | % {
    $content = gc $_.FullName -Raw
    [Regex]::Matches($content, "(?<=table \d+ ).*(?=$([Environment]::NewLine))") | % {
        $tablesInTables += $_.Value
    }
 }

$missingTables = ""

Compare-Object $tablesInTables $tablesInPermissionSets | ? SideIndicator -eq '<=' | % {
    $missingTables += $_.InputObject + [Environment]::NewLine
}

if ('' -ne $missingTables) {
    throw "Missing table permissions: $missingTables"
}

In English:

Find all the files in the current folder, and child folders, with a filename ending in .al and which have a first line starting with “permissionset”
Build a collection of the tabledata objects that are referenced in those permission sets
Find all the files in the current folder, and child folders, with a filename ending in .al and which have a first line starting with “table ” (with a space to avoid matching “tableextension”)
Build a collection of the names of the tables
Use Compare-Object to compare the collections and find names which appear in the list of tables but not in tabledata permissions
Build an error message of missing table permissions
Throw the error

PowerShell Profile:

Like most small PowerShell scripts that I write, I’ve just added it to my PowerShell profile. Run code $profile in a PowerShell prompt to open the profile file in VS Code.

function Test-Permissions() {
  #...all of the above code
}

Maybe there is already a VS Code extension that checks for this? It would make sense, but I’m pretty minimalist with the extensions that I have installed anyway. I run it from the terminal in VS Code.

Tip: Get Current Callstack with a Collectible Error

The Code

codeunit 50104 "Get Callstack"
{
    SingleInstance = true;

    [ErrorBehavior(ErrorBehavior::Collect)]
    procedure GetCallstack() Callstack: Text
    var
        LF: Char;
    begin
        LF := 10;
        Error(ErrorInfo.Create('', true));
        Callstack := GetCollectedErrors(true).Get(1).Callstack;
        exit(Callstack.Substring(Callstack.IndexOf(Format(LF)) + 1));
    end;
}

Yea, but…why?

I dunno, I was just curious whether it was possible. And, it is 🧐 Any sensible applications are probably going to be do with error handling or reporting.

You may be tempted to have your code respond differently depending on the context in which it has been called and read the callstack for that purpose. That’s not a train you want to ride though. I’ve tried, it stops at some pretty weird stations.

One advantage of this approach over using a TryFunction (as below) is that the debugger doesn’t break on collectible errors. It can sometimes be frustrating stepping through errors that are always caught to get to the code that you actually want to debug.

procedure LessGoodGetCallstack(): Text
begin
    ThrowError();
    exit(GetLastErrorCallstack());
end;

[TryFunction]
procedure ThrowError()
begin
    Error('');
end;