So I've been thinking about this; and I realize that I don't actually have a solution for you. Below is basically thoughts about what I've run into.
The experience I have with DropBox, is that it resolves Symbolic Links, resulting in file duplication of macOS applications. iCloud is also weird, in that it alters standard file system functions from being synchronous to asynchronous, and sometimes will replace the file I've just modified with an older version (or not make my code believe that everything went okay, when in fact nothing actually happened). iCloud has this great feature also, in that if you disable it, it deletes the files from your computer.
I think the first step is to identify what exactly is going on. You can use the following to get the error code. When it fails.
IMHO, if Google are locking the file while they do their analysis on the file, this is bad practice, as Google is saying that they're more important than the user, i.e. the User can't change this file while Google is reading it. It should be the other way around, where the User has priority over Google.
But that doesn't help solve the problem.
Traditionally with Atomic saving, you would save the temporary file right next to the original file, worse case scenario the user would see that there's two files next to each other. However Apple's modern "security" protocols prevent you from doing this (although certain Apple API will continue to operate this way; and yes I've been rejected from the Mac App Store several times because of this). So your safest bet is to use the dedicated Autosave Information folder in the Library folder.
Yes, you are correct in the sense that when you come to replace the file with the temporary file, it would still fail. At that point the politeest thing to do is to inform the user that their file cannot be written too, and offer to Save elsewhere (which you only need to move the saved file there).
IMHO the ideal solution to Autosave is autosave in place with save history. When your app Autosaves, it keeps the file up to date. When the user Saves, you store a copy of the current data (or enough meta data to be able to restore their data back to this point).