Unable to save a query as a view table

Question

I have a query that runs and can see the results. But while trying to save the query as a view table, I get error message saying

Failed to save view. No suitable credentials found to access Google Drive. Contact the table owner for assistance.

I think the problem is caused by a table used in the query. The table is uploaded from a google sheet (with source URI), own by me. I have tried to enable Google Drive API from the project but no luck. Not sure how I can give BigQuery access to Google Drive.

score 2 · Answer 1 · answered Apr 15 '16 at 18:53

I suspect the problem you are hitting is one of OAuth Scopes. In order to talk to the Google Drive API to read data, you need to use credentials that were granted access to that API.

If you are using the BigQuery web UI and have not explicitly granted access to Drive, it won't work. For example, the first time I tried to "Save to Google Sheets", the BigQuery UI popped up an OAuth prompt asking me to grant access to my Google Drive. After this it could save the results. Try doing this to make sure your credentials have the Drive scope and then "Save View" again.

If you are using your own code to do this, you should request scope 'https://www.googleapis.com/auth/drive' in addition to the 'https://www.googleapis.com/auth/bigquery' scope you are already using to talk to BigQuery.

If you are using the bq client, it has been updated to request this scope, but you may need to re-initialize your authentication credentials. You can do this with bq init --delete_credentials to remove the credentials, then your next action we re-request credentials.

Running a query in the UI that requires drive authorization should also prompt you to authorize with the drive scope. There should be an additional "Authorize" button underneath the error that mentions a lack of suitable credentials. — Danny Kitt, Apr 15 '16 at 19:03
I am using web UI to save the view. Although I don't recall if I have granted access to my Google Drive, I am able to save a table to Google Sheets. So I believe BigQuery have access to my Google Drive already. Not sure if there is any way I can re-initialize my credentials via web UI? — cjen, Apr 18 '16 at 13:59
When you create a view it seems BQ runs the query in the background to check it's validity. Whatever credentials it uses to test the query seem to be missing access to the resource in google sheets. I would say this is a bug. — Eric Uldall, Apr 19 '16 at 01:03

Jan Krynauw · Answer 2 · 2016-08-02T11:44:33.610

Using Google App Script this worked for me:

function saveQueryToTable() {
  var projectId = '...yourprojectid goes here...';
  var datasetId = '...yourdatesetid goes here...';
  var sourceTable = '...your table or view goes here...';
  var destTable = '...destination table goes here...';
var myQuery;
  
  //just a random call to activate the Drive API scope
  var test = Drive.Properties.list('...drive file id goes here...')
  
  //list all tables for the particular dataset
  var tableList = BigQuery.Tables.list(projectId, datasetId).getTables();
  
  //if the table exist, delete it
  for (var i = 0; i < tableList.length; i++) {
    if (tableList[i].tableReference.tableId == destTable) { 
      BigQuery.Tables.remove(projectId, datasetId, destTable);
      Logger.log("DELETED: " + destTable);
    }
  };
 
 myQuery =  'SELECT * FROM [PROJECTID:DATASETID.TABLEID];'
 .replace('PROJECTID',projectId)
 .replace('DATASETID',datasetId)
 .replace('TABLEID',sourceTable)
  
 var job = {
    configuration: {
      query: {
        query: myQuery,
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: destTable
        }
      }
    }
  };

  var queryResults = BigQuery.Jobs.insert(job, projectId);
  Logger.log(queryResults.status);
}

The 'trick' was a random call to the Drive API to ensure both the BigQuery and Drive scopes are included.

Google Apps Script Project Properties

Unable to save a query as a view table

2 Answers2

Linked