I've had the same problem... The issue is a bit obscure and clearly even the Apple sales people aren't completely up on this topic. As others have said the SuperDrive requires more power than is available through standard USB ports. So Apple prevents it from working without a "direct" connection. So that means even your typical USB-C to USB-A style hubs will not work.
I believe someone here has claimed that Apple's USB-C to USB-A cable will work. I have not tested it, but it certainly has the highest chance of working:
https://www.apple.com/shop/product/MJ1M2AM/A/usb-c-to-usb-adapter
You can find more info on Apple's support page:
Mac computers that can use the Apple USB SuperDrive - Apple Support
A USB hub with external power should theoretically work. However, I have confirmed that OS X still blocks this as it cannot know that your hub has extra power. One solution may be to try some of these old tricks to tweak OS X to support this:
http://www.mnott.de/how-to-run-a-superdrive-on-a-usb-hub/
However, I have not tested this first-hand either.
Apple is clearly abandoning spinning disk technologies, which I can't really fault them for. But overall it's kinda dumb when all you want to do is rip a CD you purchased at a concert or whatever. On the other hand if you're just looking for a CD/DVD reader, you can find them for $25 on Amazon.