The problem in the OP is solved and I think I know the cause.
TL'DR - it was likely caused by me up upgrading to Dropbox's "new MacOS app" which it prompts you to do via a notification in the app. Reinstalled the regular MacOS version solved the problem.
Here are the details -
I called Apple Support about a bunch of things going on with my new Mac (all minor and resolved-ish) and they needed to transfer me to a special group that handles iMovie, got disconnected before they ever gave an opinion on the issue and before calling them back I tried putting an iMovie Library file on Google drive to see if the same problem in the OP occurs. It did not, the iMovie Library file opened normally.
Given that, I contacted Dropbox support rather than call back to Apple.
It look a month, mainly I think due to some staffing issues they were having at the senior tech level, but they advised uninstalling dropbox via terminal commands and then reinstalling. One of the terminal commands errored out, and then they said to just download the Dropbox app again and do a normal install.
That fixed the problem. Now, here is where it get's interesting. Dropbox offered no opinion on the root cause of the problem, but I am a software developer and though I don't do dev on Macs, I can do a cause-effect analysis.
I am not 100% sure what this "new MacOS app" that I reference above in the TL'DR. When talking to a Dropbox senior tech about a different issue last year they told me about it and said it uses something called "file pile" and it's the direction their app is moving in. I can't find the term "filepile" associated with MacOS in a Google search.
In any event, when I was setting up my new MacBook, I clicked on the upgrade Dropbox to "new MacOS app" because I wanted to start fresh with the latest versions of everything.
What I figured out as the troubleshooting was playing out is that upgrading to the Dropbox "new MacOS app" resulted in the Dropbox folder moving from /users/<username>/dropbox to
/Users/<username>/Library/CloudStorage
When I reinstalled the latest release of the "regular" dropbox app, that resulted in a Dropbox folder at /users/<username>/dropbox becoming synced with the Dropbox cloud. At this point, as mentioned, moving an iMovie Library file to this dropbox folder and clicking on it worked fine.
At that point /Users/<username>/Library/CloudStorage/Dropbox existed but it was "orphaned", not connected to the Dropbox cloud and I ultimately deleted this folder on advice from Dropbox support.
My theory is that MacOS considers files in this "CloudStorage" folder to be cloud related and so it pops the error mentioned in the OP.