Trouble connecting to Google Sheets

[trillich]

I see a lot of posts tagged “google-sheets” but everybody is asking about quirks USING google-sheets, whereas we are knocking on the door and can’t even get in to the party!

My goal: connect to google-sheet in a “shared-drive” (via retool cloud enterprise)

Issue: “re-auth” seems to work fine, but no sheets available to select

Configuring the google-sheets resource doesn’t have its own “test” button (like an SQL resource would). In the app, we select “read data from a spreadsheet” and then we want to select which spreadsheet:

There are dozens and dozens of sheets available to the auth-account.

With no specified-document of course when we try “save & run” we get this error:

Missing a spreadsheet ID. Select a spreadsheet from the dropdown, or enter a valid spreadsheet ID.

Steps I've taken to troubleshoot: pasted the doc-id (using “FX”)

Next we tried pasting a doc-id (of the google-doc) from its URL, being a google-doc on “My Drive”:

Error reading from selected spreadsheet. Ensure that it is an existing spreadsheet in your Google Drive, and that you have correctly setup your resource.

Then we tried taking the doc-id of the google-sheet we really want, and this one is on a “shared google drive”:

{ "error": { "code": 400, "message": "This operation is not supported for this document", "errors": [ { "message": "This operation is not supported for this document", "domain": "global", "reason": "failedPrecondition" } ], "status": "FAILED_PRECONDITION" } }

We are on Retool Cloud enterprise.

Need a whap with a clue stick Probably missing something simple…

[trillich]

Nobody else having trouble connecting to google sheets?

[ChiEn]

Hey @trillich welcome back to the Community, haven't seen you in awhile!
Thanks for reaching out, I was able to reproduce a working Google Sheets connection on my end, so let's narrow down what's happening for you. Could you head over to your "Resources" tab and check whether the Google Sheets resource shows as connected? It's worth confirming the OAuth connection is active there first, as an expired or missing token would cause this error when trying to run a query. Let me know what you see and we'll go from there!

[trillich]

Hmm!

In case it's pertinent: Our google-sheet isn't on a personal drive, it's on a shared drive. And it isn't a personal gmail account we're using but a team/workspace account. Originally we had been trying to connect to an XLSX that was uploaded directory to google drive; now we've done "save as google sheet" and are trying to connect to the google-native-sheet-doc.

Retool does show "connected" in resources. (screen cap 1)

But in the app when we try to do a "gimme some info from that api call" with a direct-paste of the doc id, it goes bonk (screen cap 2, we’ve tried with trailing slash and without)

And if instead of the doc-id, when we try the UI to give us a pop-up list of sheets to choose from, Retool is stymied completely, no sheets show up at all (screen cap 3)

[ChiEn]

Thanks for waiting @trillich Will, after looking into this a bit more, since your resource shows as connected, I think the OAuth token itself is fine, the issue is that the Google account used to authorize the resource likely does not have membership in the Shared Drive itself.

In Google Workspace, being part of the organization does not automatically grant access to every Shared Drive. Each Shared Drive has its own member list. Can you open the Shared Drive in Google Drive, click Members, and check whether the account shown in your Retool Resources settings is listed there? If it is not, either add it as a member or recreate the Retool resource using an account that is already a member of that drive. Let me know, good luck!