This is where third-party APIs come in. With proper agreements in place, I needed to pull down massive volumes of data and cache them on my side of the fence. These are one-off events - at least, let's hope they are.
In moments like this, the toolbox becomes a set of utility scripts that are easily modified for changing scenarios while staying independent of web-facing codebases. PowerShell is a nice option for this.
Now, I probably shouldn't be surprised, but the process of building and running PowerShell scripts in Visual Studio is different from the typical "make changes, launch IIS to verify" process on a local system. Not only did Copilot handle it, but it did it well!
The Approach
The data will be consumed block by block - in this particular scenario, season by season. Thus, I need scripts to process data one season at a time. I can't do it all at once because there are a few matching issues between my original data and what is coming in from the API. Plus, this also breaks things up so I can back up the database after each season is completed, which is pretty smart.
Making Life Easier for PowerShell
Given that the data was static, I did not hit the API every time. Instead, I grabbed the entire dataset and saved it to a JSON file in the project. This gave Copilot complete visibility into the data at all times. If an anomaly occurred, it could easily reference the data point in question, avoiding constant round trips. This also made it easier for me to see the JSON.
Writing the Scripts
This process started with detailed instructions of what I needed to do. Properly written and covering all the bases, the instructions led to Copilot coming up with surprisingly accurate scripting files. I set all my files to preview the data first, so I knew the changes it was about to make, helping me avoid disaster.
I was pleasantly surprised when the first script ran - in preview mode - and it worked.
Round 1
I burned some tokens getting Round 1 in place, but it went rather smoothly. I corrected a few anomalies that were independent outliers, but once I had bulk scenarios, I was able to adjust the scripts effectively.
I created a 4-step process. All 4 scripts were created and ran successfully. These same scripts need to be used in succession for each season. So, it is important that they work correctly and only need minor changes, such as date ranges, to complete the next season.
Round 2
I didn't get back to working on the project until a couple of days later. This is where I ran into some issues with Copilot. I honestly think I got an agent that just wasn't in the mood. Looking back, I should have switched my Copilot from Auto to Claude Sonnet 5 when it started struggling.
There was also a 24-hour break. This isn't the first time I've taken a break for a day or two and found Copilot struggling to pick up where it left off.
The trouble began when I needed to use a bulk script - one that would process the entire season - for just a handful of records. Copilot wanted to create a different script because there were so few records. That makes sense, so I gave the go-ahead, assuming it would copy the logic from the master (the large one). It really struggled with this, missing a handful of key conditions. It also seemed to send the PowerShell command window into a tailspin - it suddenly started beeping erratically and then locked up the PowerShell file.
At this point, I decided to take a break. I saved all communications to a handoff file - as I always do - and then shut everything down. I even rebooted. I then returned, adding my initial instructions again with more detail about what we needed to do. This time, it made the changes and got it right. I may take some responsibility here for not being more direct and insistent that the new file needed everything from the old one. Honestly, it's hard to understand how it didn't pick up all the conditions I needed.
Nonetheless, Round 2 was finished, and I was determined to make sure that never happened again.
Round 2+
All successive rounds have completed without issue. That's not to say it hasn't been some work, but Copilot has been behaving properly. I also updated instructions and made sure Copilot created master files - in their own folder - never to be touched except for date changes. I also added specific notes for each file.
Token Costs
After Round 2, it has been costing me $1.96-$2.49 per season that I complete. I believe it is a little cheaper putting the JSON file in the project folder instead of downloading it every time. It takes about 1.5 hours for me to complete a season, but a lot of that is validation, manual fixes where they make sense, and some research.
Conclusion and Suggestions
Proving Copilot can handle PowerShell utility scripting is a nice feather to have in the cap. I should have assumed it could. It is nice to know that it can. Here are some things to keep in mind before you start a similar project using PowerShell scripting.
- Always use handoff notes so the agent can pick up where it left off. Create it after your initial instructions. Tell Copilot to update it when you take a break — always! Make sure you add the handoff file to your repo's .gitignore. Never send them upstream. Also, be sure you know where Copilot is putting the handoff notes. It should create one file in your root, but I have seen it use multiple files before and forget where they were.
- For large JSON processes like this, consider downloading the payload and storing it in your solution folder, but outside of your project. This allows your scripts to read the JSON locally and always have clear visibility to it, instead of having to go and get it each time. This will probably save on tokens, but I haven't proven that either. Regardless, it is handy to have it locally. It could cut down on API metering too!
- In situations like this, it is always wise to instruct Copilot to confirm before deleting or changing anything. This is a very important instruction to give Copilot, and you should remind it every once in a while. Tell it this at the start, before you create the handoff file.
- Back up the database often! It can't be said enough. If a really bad mistake is made, you have a recent restore point. It also helps if you realize later that something went wrong.
- If this is a web-facing application, then commit often, if not fully push to the repo. This is good advice when using AI-assisted coding tools regardless of the project. Branches are great until you work on one for two hours and something catastrophic happens. Hey, the Copilot tool is pretty good, but I have also seen it go on tilt before.