This requires a DM call on the meaning of 'wielder' in the sentence
Treat a light weapon as an object two size categories smaller than its wielder, a one-handed weapon as one size category smaller than its wielder, and a two-handed weapon as an object of the same size as its wielder.
If 'wielder' means 'hypothetical intended wielder', then yes, as written the spell can give you a Collosal minion.
That's stretching the interpretation somewhat, though: if the spell meant 'intended wielder' one would expect the author to write 'intended wielder'. The normal English meaning of that word in that context would be 'current wielder'.
Unfortunately, the spell doesn't require that the weapon is being wielded, so this interpretation also runs into difficulties.
First off, there's no room for shenanigans with an active wielder who isn't the caster:
A weapon held or carried by another creature can't be affected by this spell.
So no getting your giant friend to hold the weapon while you cast.
One reasonable interpretation would be that animating a weapon counts as magically wielding the weapon, and another would be to rule that the somatic component of the spell is 'hold the weapon and wave it around' - both of these lead to 'wielder' effectively meaning 'caster'. In that case, a two-handed weapon is always
treat[ed] as an object of the same size as its wielder.
Medium caster (=wielder): Medium animated object (at best - one-handed or light weapons are Small/Tiny). Even if that 'Medium' object is a greatsword the size of a tree, that doesn't matter to its stats.
The final option I can see is that 'wielder' really does just mean 'wielder', but the spell can somehow still be cast on an unwielded weapon - I leave the definition of 'the same size as [thing-that-does-not-exist]' to any DM bold enough to rule this way.
One other thing to bear in mind is that even if this does work per interpretation 1, it requires the availability of a Collosal weapon to animate.
Most 5th level parties probably aren't fighting anything that will drop one of those (or if they are, it's unlikely they're getting the loot...), and there's no obvious reason for weapons that size to be available for sale in places inhabited by Medium folks.
You could try a custom build, of course, but the logical response from a town blacksmith would be "I could try, maybe, but that's larger than my smithy and there's not enough iron in the whole town". You're more likely to get a sword-shaped-object than a real weapon, and it's perfectly reasonable for the spell to fizzle on those.
You could certainly find someone who could make one, but you're looking at either a really good Medium smith, or a Collosal one, and both of those are 'quest reward' territory, not 'I buy it in my next downtime'.