No Module Powershell

No Module Powershell is a set of PowerShell scripts for Excel files, JSON, XML, HTML, and web requests. You copy the scripts into a folder and load the ones a job needs. There is no setup program.

I wrote them in 2023, on a data-preparation team. We tested APIs with Micro Focus UFT One. An API is a request one program sends to another. UFT One was slow, and the company was going to retire it. A new team was building a dashboard to take its place. While that change was in progress, our team had little access to the dashboard. Company policy and security checks made the wait longer.

The computers were virtual desktops. A virtual desktop is a remote Windows machine the company provides. We were allowed to save files on those machines. We were not allowed to install or update software. PowerShell was already installed. The version was 5.1.19041.3693.

I ran the same API test in that PowerShell. In UFT One the average time was 1 minute and 20 seconds. In PowerShell the average time was 8 seconds. I showed the project manager those times, and the manager chose to invest in the work. I then built the framework the team used for the checks that still had to run during the change.

The source is public on GitHub.

The shape of a run#

The check we needed had a fixed shape. We prepared the input in Excel. A main script read that workbook. N is the number of data rows, so the body of the run executes once for each row. On that row the main script calls n scripts. The cells of the row are the parameters of each call. When the n scripts for the row have finished, the main script writes the output row, then takes the next row. The file changes while later rows are still running.

start
read input excel
while more of N rows
read the row as n parameters
while more of n scripts
  call script i with n parameters
write the output row
end

At work, the main script was the structure. Each service was its own script, one brick in that structure. A service might create a personal information record, a credit card, a VAT number, or an ESG questionnaire. A VAT number is a tax number. ESG here means a questionnaire about environmental, social, and governance topics. The main script passed the arguments into the service function and used the value that came back.

A service script could be called from every main script that needed it. When one service failed, the repair was in that one file, and every main script that called it received the repair.

Those service scripts stayed inside the company. The public repository holds the shared functions, and a few samples that run the same loop against a public practice service.

Loading a script#

The framework had to handle more than one kind of text. The inputs were Excel workbooks. The services answered with JSON, XML, or HTML. JSON is text made of names and values. XML is text made of nested tags. HTML is the text of a web page, and in this work it arrived as a service reply.

PowerShell can add those jobs as a module. A module is a package you install. On the virtual desktops, installing a package was not allowed, and the PowerShell version stayed where it was. Each job became a .ps1 file in a folder. You keep the folder with your script and load the file.

The load line is a dot, a space, and a path. The sample 1_automation_test_from_file.ps1 starts with this line.

. '.\Excel\Excel.ps1'

The same file also loads .\Json\Json.ps1 and .\HTTP\HTTP.ps1. The dot runs the file inside the current script, so its functions are still there after the file ends. Those three paths start from the folder you are in when PowerShell starts.

Excel.ps1 reaches Excel through COM. The call is New-Object -ComObject Excel.Application. COM is the Windows mechanism that lets a script control another program. The functions need the Excel program on the machine. They exist because we could save a script, and we could not install an Excel module. Each read or write opens Excel, touches the first worksheet, then closes Excel again.

Web requests go through Invoke-WebRequest, which Windows PowerShell 5.1 already contains. HTTP.ps1 is a thin wrapper around that command. Invoke-HttpPostRequest sends the body with the content type application/json.

How one row is sent#

The sample that follows Figure 1 is 1_automation_test_from_file.ps1. It lives in 1 - Tests and use cases/Use cases. The folder name has two spaces after the hyphen. The script reads a workbook, sends one POST request for each data row, and writes two values from the reply back onto that row.

The names sit on the first row. The script reads each later row by those names, runs the request, and writes the reply onto the same row. Figure 2 is that loop for this file. Here n is 1, one call on each row. The parameters are userId, title, and the fixed body.

start
copy the workbook
while more of N rows
read userId and title
POST userId, title, and the fixed body
write id and title on that row
end

Suppose one data row has userId set to 1 and title set to hello. Those are the header names the sample reads. The sentence sent as the body is always This is a body text. That sentence is written in the script. It does not come from a cell.

1. Copy the workbook

   The script looks for file.xlsx in the folder that contains the script file. That path is separate from the three load lines above. Copy-ExcelFile -Unique copies the workbook and adds a timestamp to the copy, in the form yyyyMMdd-HHmmss. The original file stays as it was. The rest of the run uses the copy.

2. Skip the header row

   Get-ExcelRowCount returns the last used row in column A. The sample starts the loop at row 2, so row 1 stays the header, and the loop continues through that last row.

3. Read the row by its header names

   Get-ExcelRowData -MatchHeader reads row 1 as the names and returns the chosen row as a set of names and values. The cell under userId is $rowData["userId"]. The cell under title is $rowData["title"].

4. Send the POST request

   The script builds one JSON object from those two cells and the fixed body, then Invoke-HttpPostRequest sends it to https://jsonplaceholder.typicode.com/posts. JSONPlaceholder is a public service for practice requests. A successful reply includes an id for the new post.

5. Read id and title from the reply

   Invoke-HttpPostRequest returns the object from Invoke-WebRequest. Test-JsonString asks for text, and PowerShell supplies the body of that reply. When the text is JSON, the script converts it. Get-JsonProperty then reads id and title. If a name is missing, the function returns the empty string this script passed as the default.

6. Save the row before the next one

   Set-ExcelRowData writes those two values on the same row, starting at column 3. The id lands in column C and the title lands in column D. Each cell is stored as text. The function saves the workbook and closes Excel before the loop moves on. That save is the live update: the copy on disk already contains the finished row while later rows are still running.

The lines that turn the example row into the request are these.

$rowData = Get-ExcelRowData -FilePath $copyFilePath -RowIndex $i -MatchHeader
$JsonObject = [PSCustomObject]@{
    userId = $rowData["userId"]
    title  = $rowData["title"]
    body   = "This is a body text"
}

Create-ExcelFile can start a new workbook. Pass -Headers and it writes those names on row 1. The sample does not call it. The sample copies a workbook that already has a header row, and it leaves that row in place.

A second request on the same row#

2_automation_test_from_file.ps1 uses the same loop and then makes a second call. After the POST, it sends a GET request to https://jsonplaceholder.typicode.com/posts/{id}/comments, using the id from the first reply. It writes three values from column 3: the id, the title, and the number of comments. 3_automation_test_from_file.ps1 follows the same shape. Its workbook, 3_automation_test_from_file.xlsx, sits next to the script.

Names down the first column#

A sheet can carry its names down column 1. Get-ExcelColumnData -MatchFirstColumn reads that column as the names and returns the chosen column as a set of names and values. The sample scripts use rows, so their names stay on row 1.

What else the folder contains#

XML.ps1 is for a reply made of tags. Test-XMLString tries to load the text with System.Xml.XmlDocument. The example in that function is a root tag books with one book inside it, and the title Book One. When the text loads, the function returns true. Get-XmlElement then takes an XPath, which is a path through the tags, and returns the matching nodes. For that sample document the path //book/title points at the title.

HtmlParser.ps1 is for an HTML reply. Get-HtmlElementById searches the text for a tag with a given id. The example in the function uses the id duplicateId. Add -InnerContent when you want the text inside the tag. The function joins what it finds into one string.

The same repository has a few more scripts around that core.

GoogleSheetsAPI.ps1 repeats the row operations against a Google Sheet, including Get-GoogleSheetRowData and Set-GoogleSheetRowData. GoogleBypassPKCS8.ps1 reads a private-key file through Parse-PKCS8PrivateKey, so that Sheets script can sign in on PowerShell 5.1.

List.ps1 and HashMap.ps1 hold small helpers for a list and for a map of names to values. XAML.ps1 can build a Windows window from XAML. XAML is a text format for a screen layout. The samples in the repository do not open a window.

Notes for the functions are in 0 - Documentation. The Excel notes are 0 - Documentation/Excel/Excel.md. The repository is licensed under GPL-3.0. The license text is the file LICENSE.

Where to read the code#

The paragraphs above name the file for each behavior. The tree is that same set. The repository is DoktorSAS/NoModulePowershell.