I'm trying to connect BigQuery to a BI Tool (Cognos Analytics) through a JDBC driver. It all works OK, until I try to read a gSheet generated table. Then I get the following error:
Data source adapter error: java.sql.SQLException: [Simba][BigQueryJDBCDriver](100033) Error getting job status.
[Simba][BigQueryJDBCDriver](100033) Error getting job status.
[Simba][BigQueryJDBCDriver](100033) Error getting job status.
400 Bad Request
{
"code" : 400,
"errors" : [ {
"domain" : "global",
"message" : "Error while reading table: <GSHEET-TABLE>, error message: Failed to read the spreadsheet. Error code: PERMISSION_DENIED",
"reason" : "invalid"
} ],
"message" : "Error while reading table: <GSHEET-TABLE>, error message: Failed to read the spreadsheet. Error code: PERMISSION_DENIED",
"status" : "INVALID_ARGUMENT"
}
My connection string is the following:
jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<MY-PROJECT-ID>;OAuthType=0;OAuthServiceAcctEmail=<CLIENT-EMAIL-FROM-JSON>;OAuthPvtKeyPath=<PATH-TO-JSON>;Timeout=60; RequestGoogleDriveScope=1;
The Service Account has full Admin ownership of the entire project. Does somebody know how to access gSheets generated tables?