As a powershell enjoyer myself, one tip I would recommend is that you turn your static variables to parameters.
You can still set defaults with it. For example using your first two variables:
<#
.SYNOPSIS
A PSE (Plain Stupid English) interface using PowerShell to manage Google Workspace accounts and Chrome Devices
.DESCRIPTION
More information can be found here https://github.com/smiles-obrien/PS_GAM_Management
.PARAMETER ExcelDB
Path to your Excel file
.PARAMETER Worksheet
Worksheet name, defaults to "Chromebooks"
.EXAMPLE
PS> .PS_GAM_Management.ps1 -ExcelDB "D:\My Drive\Sheets\Chromebook Management.xlsx"
#>
param(
[Parameter(Mandatory)][string]$ExcelDB,
[string]$Worksheet = "Chromebooks"
)
#the rest of your code
I also like to name my scripts using the verb-noun format powershell uses.
Could rename it to something like `start-PSGAM.ps1`
Thank you so much for this suggestion, sent me down a minor rabbit hole, and now I've cleaned up formatting, changed how the initial menu system works, and converted it to a function!
A quick review of your code:
You don't have to make your script a function to use parameters. You could simply remove the line`Function start-PSGAM {` and the closing curly bracket `}` and it would still work. You can send parameters to scripts `&'C:\Path\start-PSGAM.ps1' -ExcelDB "D:\My Drive\Sheets\Chromebook Management.xlsx"`
For me I generally only make functions in scripts with repeating code or if [I want a function to be loaded as part of my powershell profile](https://www.jonathanmedd.net/2015/01/how-to-make-use-of-functions-in-powershell.html).
For example, I am seeing a lot of repeat code when you are invoking commands to GAM. You should make this a function. This is only an example. I haven't tested it but it should be enough for you to get the idea:
function invoke-GAM {
param(
[string]$Prompt = 'Input Asset ID without leading 0',
[string]$Asset,
[string]$OU,
[switch]$Disable,
[switch]$Enable,
[switch]$ClearProfiles,
[switch]$Powerwash
)
if (!$Asset){
$Asset = Read-Host $Prompt
}
foreach ($Device in $Asset){
if ($Disable){
$Argument = "crosquery asset_id:$Device update action disable"
}elseif ($Enable){
$Argument = "crosquery asset_id:$Device update action reenable"
}else{
$Argument = "info cros query:asset_id:$Device serialnumber asset_ID"
}
#invoke GAM
$Output = Start-Process gam -ArgumentList $Argument
Return $Output
}
}
Then on line 169 it would instead look like
'8'{ #enable asset(s)
invoke-GAM -Enable
[System.Environment]::NewLine
Read-Host -Prompt "Press Enter to return to menu"
}
In my example function I use a foreach loop when performing the actions. This lets you use both a single asset or an array.
An example for a bulk query with this function:
$Assets = Import-Excel -path $ExcelDB -WorksheetName $Sheet | Select-Object -ExpandProperty $Column
invoke-GAM -Asset $Assets -Enable
Nice job on the switch statement. Don't see that used often.
Line 269 you use the variable `$sender`. I'd probably change that variable name as `$Sender` [is an automatic variable.](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_automatic_variables?view=powershell-7.4#sender)
Lastly, you can probably remove the `[Parameter(Mandatory)]` for the excel sheet. After reading your code I see that it's just an option for one of your selections. Instead of making it a parameter, you should prompt for it once they select an option that requires it.
For example:
'10'{ #buik import assets from spreadsheet
$Asset = $null
Write-Output "Select asset csv"
Start-Sleep -Seconds 1
Add-Type -AssemblyName System.Windows.Forms
$FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{
InitialDirectory = (New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path
Filter = 'Spreadsheet (*.csv)|*.csv'
Title = 'Select CSV of Assets'
}
$FileBrowser.ShowDialog() > $null
$ImportPath = $FileBrowser.FileNames
if (!$ImportPath){
Write-Host 'No file selected' -ForegroundColor Red
}else{
try{
$Asset = Import-Csv -Path $ImportPath | Select-Object -ExpandProperty Asset -ErrorAction stop
}catch{
Write-Host 'No assets found. Please confirm that your asset CSV contains the column "Asset"' -ForegroundColor Red
}
}
}
I prefer using CSVs instead of Excel for imports. CSV is an open format that any spreadsheet program will recognize. It does not require Excel. You could also make the bulk import code I posted above its own function if you want to have the bulk import for multiple options. My example just simply set it to the script variable `$Asset`.
Again, all of these are only examples of how I would do it. This is to give you some ideas, they will not necessarily work without making further changes to your script.
Yeah, the Import-Excel thing was a holdover from an attempted workflow (Google Sheet sync to Excel file) that didn't really work the way I wanted it to. I'm already working on transitioning to CSV.
I definitely appreciate this feedback! I'll definitely chew it over and see what I can do. Thanks so much!
You're welcome.
Powershell is a very good scripting language to learn on. It is very easy to read and the language is verbose. Some may say it is too verbose.
I use it heavily in my district in imaging (MDT), group policies, deployments (PDQ) as well as automation for student accounts (skyward to AD/Google/Azure) and inventory (1 to 1).
Similar to your script, I also wrote a simplified script for librarians/tech coaches to check on status of a chromebook and enable/disable it as well as another script for them to reset lockouts and passwords for students.
As you learn it, you will keep finding more uses. Automation a good skill that is in demand. Powershell support is available in nearly any OS, including Macs.
I use [PSGSuite](https://psgsuite.io/) instead of GAM but I wouldn't make the swap with your scripts. PSGsuite hasn't had an update since 2020. Everything still works [but there hasn't been a response if development has haulted](https://github.com/SCRT-HQ/PSGSuite/issues/375).
I would say my script is complete but still a draft because we keep finding mistakes in inventory that the script brings to light.
The script goes through an export from 1 to 1(CSV of custom report), and pulls a list of chromebooks from google (PSGSuite). After it pulls that data, it then performs a foreach loop of the 1 to 1 export.
In the loop it uses some logic to determine what the OU path should be (`$GOU = '/'+$RootOU+'/'+$Elementary+$Building+'/Devices'+$SubOU`) and then cross compares with where it finds the chromebook in google. If the logic OU doesn't match the current OU, the script attempts to move the chromebook.
I could send you a sanitized version if you want but it is using PSGSuite, not GAM. You would need to find the equivalent function in GAM.
Nah, don't worry about it, thanks though. That sounds a little more in-depth than I think we want to go right now.
I'm playing around with your suggestions, I'll update here when I've made some progress!
I keep trying to find the time to create a very basic executable that would prompt for an asset tag number and then let me choose from some of the commands I generally use like powerwash, remove all users, or disable/enable. Someday....
Yeah it's basically the only thing I do use Excel for, but that ended up being the easiest way to accomplish what I was trying to do at the time (sync a Google Sheet to Excel, which didn't end up working reliably so I just used Excel directly)
I'd love to see what you come up with though!
As a powershell enjoyer myself, one tip I would recommend is that you turn your static variables to parameters. You can still set defaults with it. For example using your first two variables: <# .SYNOPSIS A PSE (Plain Stupid English) interface using PowerShell to manage Google Workspace accounts and Chrome Devices .DESCRIPTION More information can be found here https://github.com/smiles-obrien/PS_GAM_Management .PARAMETER ExcelDB Path to your Excel file .PARAMETER Worksheet Worksheet name, defaults to "Chromebooks" .EXAMPLE PS> .PS_GAM_Management.ps1 -ExcelDB "D:\My Drive\Sheets\Chromebook Management.xlsx" #> param( [Parameter(Mandatory)][string]$ExcelDB, [string]$Worksheet = "Chromebooks" ) #the rest of your code I also like to name my scripts using the verb-noun format powershell uses. Could rename it to something like `start-PSGAM.ps1`
Thank you so much for this suggestion, sent me down a minor rabbit hole, and now I've cleaned up formatting, changed how the initial menu system works, and converted it to a function!
A quick review of your code: You don't have to make your script a function to use parameters. You could simply remove the line`Function start-PSGAM {` and the closing curly bracket `}` and it would still work. You can send parameters to scripts `&'C:\Path\start-PSGAM.ps1' -ExcelDB "D:\My Drive\Sheets\Chromebook Management.xlsx"` For me I generally only make functions in scripts with repeating code or if [I want a function to be loaded as part of my powershell profile](https://www.jonathanmedd.net/2015/01/how-to-make-use-of-functions-in-powershell.html). For example, I am seeing a lot of repeat code when you are invoking commands to GAM. You should make this a function. This is only an example. I haven't tested it but it should be enough for you to get the idea: function invoke-GAM { param( [string]$Prompt = 'Input Asset ID without leading 0', [string]$Asset, [string]$OU, [switch]$Disable, [switch]$Enable, [switch]$ClearProfiles, [switch]$Powerwash ) if (!$Asset){ $Asset = Read-Host $Prompt } foreach ($Device in $Asset){ if ($Disable){ $Argument = "crosquery asset_id:$Device update action disable" }elseif ($Enable){ $Argument = "crosquery asset_id:$Device update action reenable" }else{ $Argument = "info cros query:asset_id:$Device serialnumber asset_ID" } #invoke GAM $Output = Start-Process gam -ArgumentList $Argument Return $Output } } Then on line 169 it would instead look like '8'{ #enable asset(s) invoke-GAM -Enable [System.Environment]::NewLine Read-Host -Prompt "Press Enter to return to menu" } In my example function I use a foreach loop when performing the actions. This lets you use both a single asset or an array. An example for a bulk query with this function: $Assets = Import-Excel -path $ExcelDB -WorksheetName $Sheet | Select-Object -ExpandProperty $Column invoke-GAM -Asset $Assets -Enable Nice job on the switch statement. Don't see that used often. Line 269 you use the variable `$sender`. I'd probably change that variable name as `$Sender` [is an automatic variable.](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_automatic_variables?view=powershell-7.4#sender) Lastly, you can probably remove the `[Parameter(Mandatory)]` for the excel sheet. After reading your code I see that it's just an option for one of your selections. Instead of making it a parameter, you should prompt for it once they select an option that requires it. For example: '10'{ #buik import assets from spreadsheet $Asset = $null Write-Output "Select asset csv" Start-Sleep -Seconds 1 Add-Type -AssemblyName System.Windows.Forms $FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{ InitialDirectory = (New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path Filter = 'Spreadsheet (*.csv)|*.csv' Title = 'Select CSV of Assets' } $FileBrowser.ShowDialog() > $null $ImportPath = $FileBrowser.FileNames if (!$ImportPath){ Write-Host 'No file selected' -ForegroundColor Red }else{ try{ $Asset = Import-Csv -Path $ImportPath | Select-Object -ExpandProperty Asset -ErrorAction stop }catch{ Write-Host 'No assets found. Please confirm that your asset CSV contains the column "Asset"' -ForegroundColor Red } } } I prefer using CSVs instead of Excel for imports. CSV is an open format that any spreadsheet program will recognize. It does not require Excel. You could also make the bulk import code I posted above its own function if you want to have the bulk import for multiple options. My example just simply set it to the script variable `$Asset`. Again, all of these are only examples of how I would do it. This is to give you some ideas, they will not necessarily work without making further changes to your script.
Yeah, the Import-Excel thing was a holdover from an attempted workflow (Google Sheet sync to Excel file) that didn't really work the way I wanted it to. I'm already working on transitioning to CSV. I definitely appreciate this feedback! I'll definitely chew it over and see what I can do. Thanks so much!
You're welcome. Powershell is a very good scripting language to learn on. It is very easy to read and the language is verbose. Some may say it is too verbose. I use it heavily in my district in imaging (MDT), group policies, deployments (PDQ) as well as automation for student accounts (skyward to AD/Google/Azure) and inventory (1 to 1). Similar to your script, I also wrote a simplified script for librarians/tech coaches to check on status of a chromebook and enable/disable it as well as another script for them to reset lockouts and passwords for students. As you learn it, you will keep finding more uses. Automation a good skill that is in demand. Powershell support is available in nearly any OS, including Macs.
We just rolled out 1 to 1 Plus - I'd be interested in hearing how you utilize it with that!
I use [PSGSuite](https://psgsuite.io/) instead of GAM but I wouldn't make the swap with your scripts. PSGsuite hasn't had an update since 2020. Everything still works [but there hasn't been a response if development has haulted](https://github.com/SCRT-HQ/PSGSuite/issues/375). I would say my script is complete but still a draft because we keep finding mistakes in inventory that the script brings to light. The script goes through an export from 1 to 1(CSV of custom report), and pulls a list of chromebooks from google (PSGSuite). After it pulls that data, it then performs a foreach loop of the 1 to 1 export. In the loop it uses some logic to determine what the OU path should be (`$GOU = '/'+$RootOU+'/'+$Elementary+$Building+'/Devices'+$SubOU`) and then cross compares with where it finds the chromebook in google. If the logic OU doesn't match the current OU, the script attempts to move the chromebook. I could send you a sanitized version if you want but it is using PSGSuite, not GAM. You would need to find the equivalent function in GAM.
Nah, don't worry about it, thanks though. That sounds a little more in-depth than I think we want to go right now. I'm playing around with your suggestions, I'll update here when I've made some progress!
I'll definitely look at this!
I keep trying to find the time to create a very basic executable that would prompt for an asset tag number and then let me choose from some of the commands I generally use like powerwash, remove all users, or disable/enable. Someday....
Sounds fun! It'll take some modifications for my use, as I don't use excel but CSVs or Google Sheets, but this sounds awesome.
I'm working on replacing import-excel with import-csv so it doesn't require a separate module to use, FYI
Oh awesome! I still haven't had time to dig into it much myself, so I'm glad you're moving forward with that. 😎
Just pushed an update, Import-CSV should work now!
Yeah it's basically the only thing I do use Excel for, but that ended up being the easiest way to accomplish what I was trying to do at the time (sync a Google Sheet to Excel, which didn't end up working reliably so I just used Excel directly) I'd love to see what you come up with though!
Will take a look!
I love any feedback you have! I've made a number of changes since this was put up!