How to write cloud PowerShell scripts

Sometimes the data you want sits behind an API no plugin covers, or needs a few lines of logic before it's worth a tile. The cloud PowerShell plugin handles both: write a script in the Run script data stream, and whatever it outputs becomes the tile's data.

Scripts run in PowerShell 7 on Linux, in SquaredUp's cloud. Windows-only cmdlets and modules aren't available there, and neither is your own network. For either of those, use the PowerShell On-Prem plugin instead.

Data source variables

When configuring the PowerShell data source, add Variables for values your scripts need, such as API keys. Click the padlock icon to encrypt a value, so secrets never appear in the script itself.

Scripts read these values from $variable, listed under Built-in variables below. Every value arrives as a string, so convert numbers before doing any math with them.

Built-in variables

The Script field of the Run script data stream can use these variables:

• $variable: A dictionary of the variables from the data source configuration. To read a variable named foo, use $variable['foo'] or $variable.foo.
• $paging: Lets a script output its data over several runs. See Paging below.
• $timeframe: The dashboard timeframe, as an object like the one below. start and end arrive as DateTime values, so you can compare dates against them directly.

{
  "unixStart": 1697375649,
  "start": "2023-10-15T13:14:09Z",
  "unixEnd": 1697462049,
  "end": "2023-10-16T13:14:09Z",
  "enum": "last24hours",
  "interval": "PT15M"
}

Script output

Whatever the script outputs becomes the tile's data. Each object becomes a row, and each of its properties a column.

Objects work with the Table visualization, and with any other visualization once they have the columns it needs. A single value, such as a number, becomes one row with one column, which suits the Scalar visualization.

Nested objects are flattened, so their properties become columns too. JSON depth sets how many levels of nesting are kept. Anything deeper arrives as its type name, such as System.Collections.Hashtable, instead of its data, so raise it if you see that in a column.

Anything written to the error stream, for example with Write-Error, fails the whole tile, even if the script also output data. Write-Warning and Write-Host don't affect the result.

Paging

If a script would take too long or return too much data in one run, it can output its data in pages. SquaredUp runs the script once for each page and combines the pages into one result for the tile.

• $paging.context: The value that identifies the page being read. On the first page it's $null.
• $paging.next: Set this to request another page. SquaredUp passes the value back as $paging.context on the next run. To end paging, leave it unset on the last page.

This script outputs 25 rows over three pages:

$offset = if ($paging.context) { $paging.context.offset } else { 0 }
$all = 1..25 | ForEach-Object { @{ id = $_ } }
$all | Select-Object -Skip $offset -First 10
if ($offset + 10 -lt $all.Count) { $paging.next = @{ offset = $offset + 10 } }

Each page runs as a separate script, so paging is slower than outputting everything at once. SquaredUp stops requesting pages after about 12 seconds, and the tile shows "The request timed out. The tile may not display all available data."

Examples

These examples come from the PowerShell Example Data dashboard. SquaredUp offers to install it when you add the data source, and again whenever you add the data source to another workspace.

Last 10 orders

Calls the SquaredUp sample data API, keeps the orders inside the dashboard timeframe, and outputs the 10 most recent with their customer details.

#Make request to the API endpoint and convert the response from JSON to a PowerShell Object
$request = Invoke-WebRequest -URI 'https://download.squaredup.com/api/plugins/powershell/sample-data'
$request = $request.content | ConvertFrom-Json

#Separating response data into 2 lists
$customers = $request.customers
$orders = $request.orders
$result = @()

# Loop through returned list of customer objects and check for matching orders
ForEach ($customer in $customers) {
    forEach ($order in $orders) {
        if ($order.orderDateTime -gt $timeframe.start -and $order.orderDateTime -lt $timeframe.end) {
            #If matching order is found within the dashboard timeframe, add order details along with customer details to the results array
            if ($order.customerId -eq $customer.id) {
                $result += New-Object -TypeName psObject -Property @{"Customer name" = $customer.name; "Customer Number" = $customer.id; "customer address" = $customer.address; "Customer email" = $customer.email; "Order Number" = $order.orderNumber; "Date" = $order.orderDateTime; "Currency" = $order.currency; "item" = $order.item; "Quantity" = $order.quantity; "total" = $order.total }
            }
        }
    }
}

# Return the results selecting only the most recent 10 objects 
$result | Sort-Object Date | select-Object -Last 10

Orders per day

Calls the same API and outputs every order inside the dashboard timeframe with its customer details. The tile then groups the orders by day and counts them.

#Make request to the API endpoint and convert the response from JSON to a PowerShell Object
$request = Invoke-WebRequest -URI 'https://download.squaredup.com/api/plugins/powershell/sample-data'
$request = $request.content | ConvertFrom-Json

#Separating response data into 2 lists
$customers = $request.customers
$orders = $request.orders
$result = @()

#Loop through returned list of customer objects and check for matching orders
ForEach ($customer in $customers) {
    forEach ($order in $orders) {
        #If matching order is found within the dashboard timeframe, add order details along with customer details to the results array
        if ($order.orderDateTime -gt $timeframe.start -and $order.orderDateTime -lt $timeframe.end) {
            if ($order.customerId -eq $customer.id) {
                $result += New-Object -TypeName psObject -Property @{"Customer name" = $customer.name; "Customer Number" = $customer.id; "customer address" = $customer.address; "Customer email" = $customer.email; "Order Number" = $order.orderNumber; "Date" = $order.orderDateTime; "Currency" = $order.currency; "item" = $order.item; "Quantity" = $order.quantity; "total" = $order.total }
            }
        }
    }
}

return $result 

Was this article helpful?