Beyond Excel: Building a Zero-Cost, Serverless Inquiry Management System with Only PowerShell

Introduction

"Someone has the file open and I can't edit it (read-only)"
This is a stress everyone in IT or general affairs departments who uses Excel for task management has faced at least once.

"Rows are deleted before I know it"
"I don't know which one is the latest version"

Even if you consider implementing SaaS like kintone or Jira to avoid these troubles, the "budget barrier" and "security review barrier" are high, and you end up going back to Excel management... Have you ever had that experience?

This time, I'll talk about how I created a GUI-based management system that works just by placing it in a shared folder, solving this "exclusive control problem" using only standard Windows features (PowerShell + VBScript).
No special servers or software installations are required at all.

1. Why Build a GUI App with "PowerShell"?

Many engineers tend to think, "If I'm making a tool, I'll use Python or C#." However, in financial or enterprise environments with strict security policies, that's not always possible.

• Installation of development environments is prohibited (No Visual Studio, no Python)
• Importing external libraries is prohibited (Cannot pip install)
• Internet connection restrictions (Cannot use Google APIs, etc.)

The only weapon left in such a "straightjacket" environment is PowerShell, which is pre-installed on all Windows PCs.
Actually, because PowerShell can fully utilize the functions of the .NET Framework (Windows Forms), you can easily create applications with a rich GUI (interface), not just text processing.

2. System Overview

The configuration of the "Inquiry Management System" built this time is as follows:

• Frontend: PowerShell (Windows Forms)
  • The screen where users input and view data.
• Database: CSV file (placed in a shared folder)
  • The place where data is accumulated instead of Excel.
• Launcher: VBScript
  • A wrapper to launch the application without showing the PowerShell-specific "black screen" even for a second.

By just placing this in a shared folder on a file server, all employees can use it without any installation.

3. Implementation Point 1: Active Directory Integration

To minimize input effort, the system automatically determines "who launched it" the moment it is executed. Rather than just a simple login ID ($env:USERNAME), it queries Active Directory to automatically retrieve even the person's "name (Kanji)".

AD Integration

# ※ Code image (excerpt)
$searcher = [adsisearcher]"(samaccountname=$env:USERNAME)"
$result = $searcher.FindOne()
$userName = $result.Properties["displayname"][0] # Get the display name (Kanji) on AD

This eliminates the need for users to type their own names every time, improving the accuracy of the records.

4. Implementation Point 2: "Pseudo Exclusive Control" via CSV

The biggest challenge in shared folder operations is "data regression due to simultaneous updates." If User B registers or updates another case while User A has the screen open, and User A saves their work, User B's updates could be lost. To prevent this in an environment where a database is unavailable, I implemented an approach similar to "Optimistic Locking." Specifically, the logic is "the moment the save button is pressed, the latest data on the server is reloaded, and only the user's specific target row is merged in."

Implementation Logic Flow

1. Save Button Click

   • Triggered when the user clicks "Save" on the UI.

2. Reload Latest CSV

   • Instead of relying on data held in memory, the latest CSV file is retrieved from the server using Import-Csv.

3. Row Identification and Replacement by ID

   • It searches the reloaded data for the row matching the "Inquiry ID" the user is currently editing.
   • Only that specific row is overwritten with the input values from the screen.
   • ※ By doing this, "changes made by others to other rows (different inquiries)" are preserved.

4. Write to CSV

   • The merged data is written back using Export-Csv.

# Logic image (conceptual code)
$currentID = $txtBox_ID.Text

# 1. Import the latest state from the server immediately before saving
$latestData = Import-Csv $csvPath -Encoding Default

# 2. Look for the row with the same ID as the one being edited and replace only that one
foreach ($row in $latestData) {
    if ($row.ID -eq $currentID) {
        $row.Status = $cmbBox_Status.Text
        $row.Comment = $txtBox_Comment.Text
        break
    }
}

# 3. Write back (since $latestData includes changes made by others, they are not lost)
$latestData | Export-Csv $csvPath -Encoding Default -NoTypeInformation

This mechanism prevents data loss except in the extremely rare scenario where "exactly the same inquiry is edited at exactly the same moment," significantly increasing safety in file-based operations.

5. Implementation Point 3: Automatic Generation of Weekly Reports

To reduce the work involved in reporting to supervisors, I also implemented a function to automatically generate HTML reports from the accumulated data. It automatically aggregates "Incomplete Cases" and the "Number of cases handled this week," outputting them in a clean table format.

Weekly Report

# Image of HTML generation logic
$sb = New-Object System.Text.StringBuilder
$sb.Append("<html><body><h1>Weekly Inquiry Report</h1><table>...")
foreach ($row in $data) {
    $sb.Append("<tr><td>$($row.ID)</td><td>$($row.Title)</td>...</tr>")
}

Summary: For those who want to finish with copy-paste

As explained above, using PowerShell makes it possible to improve operational efficiency without purchasing expensive tools. However, writing hundreds of lines of GUI code, implementing exclusive control logic, and testing AD integration is a very demanding task.

"I understand how it works, but I want to use it in my workplace right away."
"I want to save time on writing scripts."

For those people, I plan to distribute a package on Zenn Book that includes the "full version of the configured source code" and the "installation manual" for the system described here.

• InquiryManager.ps1: The main system (GUI and logic fully implemented)
• Launcher.vbs: A startup launcher that avoids showing the black screen
• Installation Manual: A text guide for folder structure and initial setup