In the fast-paced world of IT consulting, efficiency and accuracy are paramount. One common challenge faced by IT professionals is the need to perform and document various types of application and workload analysis tasks for numerous individual applications. These tasks, when done manually, can be incredibly time-consuming and prone to errors. However, with the help of GitHub Copilot, this process can be significantly streamlined, reducing both effort and the potential for mistakes.
The Business Problem
The specific business problem at hand involves the need to perform and document three different types of application/workload level analysis activities for over 200 individual applications. Manually creating each document with predefined metadata is not only time-consuming but also error-prone.
The Business Solution
An automated solution was developed to create each document analysis type based on defined document templates configured with custom document properties. These properties are inserted in the required places of the document template.
How It Was Solved
The solution involved several key components:
- Excel Spreadsheet: An Excel spreadsheet was used to track overall analysis tasks and completion status, containing all the applications.
- Word Document Template: A Word document template was formatted to support the analysis efforts, with fields based on custom document properties. The values for these properties were sourced from the Excel spreadsheet.
- PowerShell Script: A PowerShell script was created to enumerate each row of the spreadsheet, extract the required column values, create a copy of the Word document, and set the correct custom document properties. The script also updated the Excel workbook to indicate which documents were created.
The Role of GitHub Copilot
GitHub Copilot played a crucial role in generating the bulk of the PowerShell script. With Copilot assistance, the entire script was created and tested in less than three hours. Removing potential days of iterative testing and user toil.
A Visual Studio Code (VS Code) workspace was created and populated with the basic template and reference files. A Copilot chat session was created, and basic context was set for the VS Code repository. The chat session was then configured to use “Agent” mode and the Claude Sonnet 3.7 model.
GitHub Copilot Interaction Process
- Initial script creation
Outline what the script needs to do in terms of initial functional and technical requirements. Craft a Copilot prompt describing what to accomplish and the outlined requirements.
Example GitHub Copilot Prompt:
I need a PowerShell script to automate the creation of a Word document based on an existing document that has a defined set of custom document properties
- The script should use an Excel xlsx file as its data source, MainReference_Data.xslx is a good reference source.
- The path/name of the Excel file should be a named script argument.
- The file token_map.txt defines the mapping of columns in the Excel to the Word documents’ custom properties.
- The existing token_map.txt maps to the column names in the first row of the workbook.
- The script needs to open a specific Word document, specified as a named script argument, update the document’s custom properties with values from the Excel, refresh and update all fields in the document, and save as a new document using the Excel’s “Application / System” column as part of the document name.
- The new document name should be “Application Name” – File Name of Reference Document Template.”
- A log file should be created when the script is run.
- The log file should contain a list of all documents created and the values used.
- A second log file should be created when the script is run, which contains a list of Application Names that document failed creation, and should also include error messages.
- Adding further refinements
After testing the generated script, it was discovered that there needs to be the ability to support multiple worksheets containing data in the Excel workbook.
Example GitHub Copilot Prompt:
Refactor the PowerShell script to further enhance working with an Excel document as a data source file.
- Add an additional parameter to specify the Excel workbook sheet to use as a data source
- Continued refinement of functionality
Let’s now add the ability to update the tracking status column of a document that has been created or not.
Example GitHub Copilot Prompt:
Update the script to update a column in the Excel workbook once a document has been created
- Add argument for document type, valid values are “Type1”, “Type2”, “Type3.”
- Depending on the document type, update the corresponding row column to a value of “Yes.”
- document type “Type1” maps to Excel column “Type 1 Analysis Created”
- document type “Type2” maps to Excel column “Type 2 Analysis Created”
- document type “Type3” maps to Excel column “Type 3 Analysis Created”
- Final functionality change
It would be exceptional if the script could be run multiple times to support fixing failed document creation problems and not have it duplicate existing documents.
Example GitHub Copilot Prompt:
Update the script to check if the document already exists in the target output location
- If the document already exists, check if the spreadsheet column is “Yes”; if not, set it to “Yes” and continue to the next row
Conclusion
This case demonstrates how GitHub Copilot can be leveraged to quickly solve a daily IT business problem with minimal software development effort. By automating the creation of analysis documents, significant time and effort were saved, and the potential for errors was greatly reduced. GitHub Copilot proved to be an invaluable tool in streamlining this process, showcasing its potential to enhance productivity and efficiency in the IT industry.