Trying to setup a Google Spreadsheet data source. First used an existing client_secret.json that has a service account that already has access to the sheet and is functioning with highcharts. Kept getting spreadsheet not found error. Confirmed the sheet was shared with the service account email address.
I then setup a new service account following the instructions provided, shared the spreadsheet with that service account. Still get the “not found” error. The spreadsheet is in a Team Drive so I went to the root of the team drive and gave the service account view access there.
When “testing” the connection with the json file, both work.
The query I’m running is as stated in the doc, sheet ID [PIPE] sheet #
17kBJ8P0keWmFLuf7pq9haRPCOjPIhKJjbY1ThlyiLCg|0
I’m clueless as to what the problem could be… please help!
Technical details:
Redash Version: 5.0.2+b5486
Browser/OS: Chrome 73.0.3683.86
How did you install Redash: Install today using AMI linked from Redash site.
Hi! Thank you for helping! I’m so very close to getting a dashboard that has taken me weeks to put together using Highcharts in a matter of hours with Redash! This is the only datasource left
Thinking it might be a bug with version 5 I went ahead a deployed the latest version using the docker image… redid all the steps for providing access to the sheet and added the data source… same problem not sure where to look for any hints as to what the problem could be. I’m going to try creating a sheet outside of team drive to see if that makes a difference.
I created a spreadsheet at the top level of my drive and I am able to query it. I rechecked perms on the Team Drive… the service account has view access to the entire drive and I checked the spreadsheet perms too.
I used the json credentials file (the one used to setup the data source) in a PHP script leveraging the GoogleSheets API and I can access the sheets in Team Drive.
I took a spreadsheet out of the Team Drive and moved to My Drive.
That removes all the perms, so I shared it with the service account again.
The ID doesn’t change.
Retried the Query and it worked like a charm.
Unfortunately, one of the sheets is feed by a form and accessed by many members of that Team Drive so I can’t move it out of there. I can workaround it but would be neat if Team Drive files can be used to.
I found a easy, workable, workaround - GoogleSheets has an importrange() formula that lets you copy the content from other spreadsheets into an active sheet. I created a new spreadsheet outside of Team Drive, used importrange to get data I wanted from the one in Team Drive, and then used that. It updates that range on access so it says up-to-date. Sweet.
not sure where to look for any hints as to what the problem could be. I’m going to try creating a sheet outside of team drive to see if that makes a difference.
(redash7)