Google sheets missing authentication for big query in apps script

[EricPetermann]

Hello everyone,

I am currenthy having trouble with creating an apps script function that queries bigquery when trying to use it in sheets. Essentially I have written a function in appscript that executes a 

BigQuery.Jobs.query

and outputs a single value e.g. the sum of all queried data. When I run this function directly in apps script it works just fine with the correct result when checking with Logger.

However, when putting the function in a cell in my google sheet in order to have the returned value in the cell I get the following error instead: 

GoogleJsonResponseException: API call to bigquery.jobs.query failed with error: Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project (line 10).

The link did not really help me, but it seems like an authentication issue. 

What I have done:

- Added  "https://www.googleapis.com/auth/bigquery" to the appscript.json

- Added the OAuth2 Library to appscript

- Added var token = ScriptApp.getOAuthToken(); to the code.

On the last point. It seems when I run the query in app script a token is actually retrieved, yet when running in sheets I seem to retrieve no token.

Any help would be greatly appreciated.

Best, Eric