Unable to download NIK collection as it needs to open with Rosetta

Once Rosetta 2 is installed, you won't be asked again. However, that error is not because of Rosetta 2. The error is because the app installer doesn't recognize the OS version and thinks it is prior to 10.7.

There's nothing that will make that work short of the developer fixing the installer, but the software is not compatible with Monterey (per jricketts link). You need one of the two newest versions, 4 or 5.

If you are talking about the NIK Collection 2 that is published by DXO, they have a compatibility matrix on their support site that indicates version 2 is only compatible with macOS 10.15 (Catalina), 10.14 (Mojave), and 10.13 (High Sierra). There is no support for macOS 12 (Monterrey) or 11 (Big Sur), thereby negating any support for Apple Silicon, such as the M1.

10.7 or newer requirements?

10.7 goes way back. In fact its own requirements may refer to the old PowerPC/Rosetta which was 10.6.8 and earlier, not 10.7 or later.

Rosetta 2, is the application style used for Intel CPU apps on M1 processors.

Even 10.15 all apps had to be 64 bit to work. A 10.7 app is highly unlikely to be 64 bit, although it could be.

I'd contact the vendor providing the App to verify which Rosetta they are talking about.