Creating BigQuery table from Google Sheet using Java API - access denied

Question

A Google drive sheet has been created (from XLS) using Drive API - by an App Engine application, with default service account. The newly created document has been shared with individuals and access to file has been confirmed.

File file = driveService.files().create(fileMetadata, inputStreamContent)
        .setFields("id")
        .execute();
    Logger.info("Created file: %s", file.getId());
    BatchRequest batch = driveService.batch();
    Permission userPermission = new Permission()
        .setType("user")
        .setRole("writer")
        .setEmailAddress("[email protected]");
    driveService.permissions().create(file.getId(), userPermission)
        .setFields("id")
        .execute();

Now I would like to create a BigQuery table from this Google Sheet. So I've got Drive API enabled obviously for previous step. I have adjusted BigQuery service to have Credentials with necessary scope created:

private static final List<String> SCOPES = asList(DriveScopes.DRIVE, 
DriveScopes.DRIVE_READONLY, SheetsScopes.SPREADSHEETS, AUTH, BIGQUERY);
GoogleCredentials googleCredentials = AppEngineCredentials.getApplicationDefault().createScoped(SCOPES); 
BigQueryOptions options = BigQueryOptions.newBuilder().setCredentials(googleCredentials).build(); 
BigQuery bigQuery = options.getService();

But still no luck when I call the controller to ingest the sheet with this code:

ExternalTableDefinition tableDefinition = ExternalTableDefinition
        .of(String.format(GOOGLE_DRIVE_LOCATION_FORMAT, fileId), categoryMappingSchema(),
            GoogleSheetsOptions.newBuilder().setSkipLeadingRows(FIRST_ROW).build());
    TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

    Table table = bigQuery.create(tableInfo);

The error I'm getting suggests that the scope has not been provided to the credentials.

Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

Am I missing something?

Before jumping into the code, have you checked all three steps as outlined here? stackoverflow.com/questions/40731823/… — Graham Polley
Thanks @GrahamPolley - yes saw this question before, followed the instructions as described — Konstantin

Konstantin Konstantin · Accepted Answer · 2018-09-09T23:38:58

I suspect there's a problem with ADC - when I initialize Credentials from the json key, it works as expected:

InputStream inputStream = new ChannelInputStream(inputChannel);
            bqCredentials = GoogleCredentials
                .fromStream(inputStream)
                .createScoped(BQ_SCOPES);

This approach did not work:

GoogleCredentials googleCredentials = AppEngineCredentials.getApplicationDefault().createScoped(SCOPES);

Creating BigQuery table from Google Sheet using Java API - access denied

1 Answers