I'm struggling to set up a Google Sheets integration in my self-hosted version (v.3.200) and I'm unclear where expected functionality ends and bugs begin. The functionality I'm after is allowing users to run a query, then export the data it to a new sheet of a Google spreadsheet of their choosing from their drive.
I've created a Google Sheets resource supplying details of the app I created in the google console. The app's scopes are "/auth/spreadsheets" (see, edit, delete all spreadsheets) and "/auth/drive" (see, edit delete all contents of google drive).
Even when I try the resource in edit mode for my retool app, it seems unable to "list all available spreadsheets". It just does nothing. I concluded that I don't have the right scopes, but I can't find a list of the ones that should be enabled for this resource to work. Given that Google Sheets API has no endpoint to list files (that's a capability belonging to the Drive API) I granted the app the drive scopes I mentioned, but to no avail.
In fact when I publish my app and visit it, although retool does immediately prompt me to auth with Google (which is frustratingly repetitive because I'm already using Google SSO for Retool auth) it takes me through the google approval screens, then throws me back to retool, and nothing appears to have changed. The app screen refreshes and I'm prompted to auth with Google again. It's a loop.
The google sheets integration feels quite lacking. Is there going to be more support for any of the batch methods? As it stands I can't create a new sheet in a designated spreadsheet. Technically I can duplicating it with the "copy sheet to other spreadsheet" method, but that requires a donor sheet, which is adding hurdles for users. Plus I noticed a few of the fields retool has you fill in, like Sheet names, are redundant!
So in summary:
- Unclear documentation of necessary scopes for Retool's baked-in Google Sheets resource
- Authentication loop with no feedback
- Some useful capabilities missing even if it did work, that mean replacing it with a self-designed REST API resource requires multiple steps to authenticate, create a new sheet in a spreadsheet, then append data do it.
- Seemingly redundant fields unused by underlying Google Sheets API requested of app developer
The sheets integration feels a little half-baked and the conclusion I'm drawing is that I've done something wrong. Can anyone shed any light?