Problem: Unable to access Google sheets in Shared drives
This has been posted multiple times in the forum before but all closed without a proper resolution.
One of the main issues not understood in the previous topics raised is the confusion of what a Shared drive is, hence also confusion on how to address it.
This is what it looks like:
As you can see the files under Shared drives are not exactly the same as the files that are shared directly by one user to another which will show under “Shared with me”.
Shared drive is a Gsuite feature not available to free gmail and even to the basic Gsuite subscription (as far as I know), it needs an upgraded version of Gsuite. Here is the help article for Shared Drives: https://support.google.com/a/users/answer/7212025?visit_id=638192941615170665-2267983714&hl=en&rd=1
Workflow example for this case is like this:
Google Authentication > Google Sheets Connection > Google Sheets Reader
Even with max permissions for the file, the Google Sheets Reader will not be able to find the Shared drive file i.e. it is not in the list of files in the “Select existing spreadsheet” interface.