Issue with BigQuery and External Table DDL (google sheet table)

[Mikael Rapp]

Hi, 

I'm facing an interesting bug(?). 

Running this code. 

===============

const sheetUrl = "https://docs.google.com/spreadsheets/d/....................."

const projectId = "......."

function tiggerExternalTableBug() {

  let query = `

    CREATE OR REPLACE EXTERNAL TABLE dev.external_table

    OPTIONS (

      format = 'GOOGLE_SHEETS',

      uris = ['${sheetUrl}']

    );

  `

  BigQuery.Jobs.query({

      query : query,

      useLegacySql: false,

      location: "EU",

    }, projectId)

}

=================== 

Yields this error. 

GoogleJsonResponseException: API call to bigquery.jobs.query failed with error: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

However, running the query directly in the BigQuery console works just fine. 

I'm I doing something crazy here or is this an issue with the scripting environment? 

Best Regards

Mikael

[Mikael Rapp]

Also get the same error trying to query an external drive table.

function tiggerExternalTableBug() {

  let query = `

    SELECT * FROM  \`dev.external_table\`

  `

  BigQuery.Jobs.query({

      query : query,

      useLegacySql: false,

      location: "EU",

    }, projectId)

}

[Clark Lind]

When you run it in the console, does it ask for permission to run while testing? If so, then you may have to add some form of authorization to the script.
Base on the Docs,  you need at least "view" access on the sheet. Also, ensure you have the correct scope to access Drive in your manifest file. 

Go to the manifest and ensure you have the proper Oauth scopes to access Drive:  ("https://www.googleapis.com/auth/drive.readonly")

Make sure the manifest is visible:
(Project settings --> Show "appsscript.json" manifest file in editor)

In the manifest (appsscript.json file), you may see something like this:

More than likely, your manifest looks something more like this:

Don't worry about what is in my example, yours will be different. Just add the scope:

To ensure the script has been reauthorized, create a simple function to run, and debug it. It will prompt you to authorize/reauthorize the new scope.

function authorize() {

    console.log("Hello. The script should prompt for authorization");

[Mikael Rapp]

Hi, 

Thanks for the elaborate response and suggestion, it was a facet about scripts i didn't know about so thanks! 

Unfortunately it didn't solve the issue (i did have to re-auth the script to grant google drive access) and got the same issue. 

Best Regards

Mikael

[Clark Lind]

My apologies..  I just re-read the first query and see you are trying to write also, not just read, so make sure you also have the full drive scope in the manifest:
https://www.googleapis.com/auth/drive

I hope that helps solve the problem for you, if not, good luck!

[Matthew Moran]

Are you running the Google Apps Script and BigQuery query with the same account? 

If not, you will likely need to provide access to the BigQuery dataset for the account running the Google Apps Script code.

In the BigQuery console, click the three dots to the right of the dataset and select "Share".

The permissions you establish will be based on what the account(s) or groups need to perform. Data Editor if you need to write data. Data Viewer if just pulling data. Data Owner for administrative permissions.

Hope this helps.

Matthew Moran

[Mikael Rapp]

Well, I can access other tables in the same dataset, it is just external (drive) tables that have this issue.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/LkC9c8jZd90/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1675f5fe-9d56-4c63-95d3-50572b235873n%40googlegroups.com.

--

Mikael Rapp

CTO | Marketing automation fanatic

+46 704 03 35 30

Kungsgatan 78
113 43 Stockholm
kvantic.com

[Mikael Rapp]

After tinkering with the scopes i got it work, Thanks for the feedback!

This set of scopes is likely excessive but with them it finally worked. 

  "oauthScopes": [

    "https://www.googleapis.com/auth/bigquery",

    "https://www.googleapis.com/auth/drive", 

    "https://www.googleapis.com/auth/spreadsheets"

  ],