1

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("personal.email@gmail.com");
    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?

Konstantin
  • 389
  • 3
  • 14
  • Before jumping into the code, have you checked all three steps as outlined here? https://stackoverflow.com/questions/40731823/encountered-an-error-while-globbing-file-pattern-error-when-using-bigquery-api/40731962#40731962 – Graham Polley Sep 07 '18 at 22:39
  • Thanks @GrahamPolley - yes saw this question before, followed the instructions as described – Konstantin Sep 09 '18 at 23:37

1 Answers1

0

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);
Konstantin
  • 389
  • 3
  • 14