I dont like the way Google Sheets handles Pivot Tables. That's my biggest bug bear, as I can never get it to accept tables that are refreshed with new rows to be part of the pivot table for a static report, or some such.
In my day job, I use a lot of PowerBI, PowerQuery and SQL, so that's another rabbit hole all together.
Yeah, it sounds like you have some pretty specialized needs. I wouldn't be shocked if there is an add on for sheets that allows you to do what you want, but honestly what's the point of fiddling with it if Excel does the job.
I've been slowly implementing Python (and Pandas) into my data work, so perhaps one day, I won't even need a GUI :D
There's a certain joy to making a report or piece of analysis, delivering it, then never needing to do anything to maintain it ever again, unless business requirements change.
Unfortunately, they often do.
That sounds way over my head! I haven't coded since college close to 25 years ago and I think the last language I used was either COBOL or Assembly.
I've only been at it for about 9 months or so, It is surprisingly easy to pick up, but there's a pretty steep learning curve once you get beyond the basics.