Google sheets missing authentication for big query...

EricPetermann · 12-12-2023 12:14 AM

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

ms4446

It sounds like you're facing an OAuth 2.0 authentication problem when using a Google Apps Script function that queries BigQuery from within Google Sheets. The error message indicates improper authentication when called from Sheets, even though it works in the Apps Script editor.

Here's how to troubleshoot and fix it:

1. Ensure Proper Scopes:

Make sure your appsscript.json manifest file has the correct scopes. BigQuery requires https://www.googleapis.com/auth/bigquery, but you might also need additional scopes like https://www.googleapis.com/auth/spreadsheets for Sheets interaction.

Example manifest:

{
  "oauthScopes": [
    "https://www.googleapis.com/auth/bigquery",
    "https://www.googleapis.com/auth/spreadsheets"
  ],
  ...
}

2. OAuth2 Library Usage:

If you're using the OAuth2 library, ensure it's set up correctly. However, BigQuery has a built-in service, so you might not need it. Use the built-in Google authentication instead.

3. Check Project Credentials:

Ensure your Apps Script project's linked Google Cloud Project has the BigQuery API enabled.
Verify credentials are properly set up in the Google Cloud Console.

4. Token Usage:

If using ScriptApp.getOAuthToken(), ensure it's passed correctly in the BigQuery API request header.
However, with built-in BigQuery service, manual token handling shouldn't be needed.

5. Deployment and Permissions:

The script should prompt for authorization the first time you run it from Sheets.
If scopes changed after deployment, reauthorize.

6. Testing and Debugging:

Test the script in the Apps Script environment for expected behavior, especially when called from Sheets.
Use the Logger or new IDE debugging features.

7. Refresh the Sheet:

After script changes, refresh the Sheet to ensure it uses the latest version.

8. Review Execution Logs:

Check the Apps Script dashboard's execution logs for additional error details when running the script from Sheets.

EricPetermann

Hello and thank you for your reply.

1. In my appscript.json I have the following additions:

"oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/bigquery",
    "https://www.googleapis.com/auth/spreadsheets"
  ],

2. I tried with and without the line

var token = ScriptApp.getOAuthToken()

but it doesn't work in sheets in both cases. I do get the Authentication request in appscript on my first run, but in sheets no pop-up ever appears.

3.I have the BigQuery API enabled in the Apps script project. I have since added the "BigQuery User Role" to my account. I am now Owner, BigQuery admin and BigQuery User.

4. I think the AuthToken line doesnt' work in Sheets for me anyay. If I add the code

var token = ScriptApp.getOAuthToken();
  if(!token || token.length === 0) {
    Logger.log('Auth required');
    return 100;
  }

this line gets skipped in AppsScript. The cell will however return "100" in the cell in Google Sheets.

5. Authorization popped up for me in Apps Script but never in Sheets. Is there any way to force it to?

6. It works just fine in Apsp Script. The query gets executed even without the AuthToken-line. I suspect this is because I actually get a pop-up for authorization and can authorize the script acoordingly in this environment.

7. I tried this after every change. Nothing seemed to work.

8. The execution log in Apps Script shows no errors. It's only when the function is called in sheets.

Is there anything else I am missing?

Best wishes,

Eric

ms4446

Here are a few additional suggestions:

Script Deployment as API Executable:
- Ensure that your script is deployed as an API executable. This is different from a standard script deployment and is necessary for it to be called from external sources like Google Sheets.
- Go to the Apps Script Editor, click on 'Deploy' > 'New deployment'. Choose 'API executable' as the deployment type. This might help with the authentication issue when the script is called from Sheets.
Explicit Authorization Flow:
- Sometimes, the authorization flow does not trigger automatically in Google Sheets. You can try creating a separate function in your Apps Script that explicitly calls for authorization.
- This function can be a simple script that requires the same scopes as your main function. Run this function directly from the Apps Script editor to trigger the authorization flow.
Spreadsheet Bound Script:
- If your script is not bound to the specific Google Sheets file, consider making it a bound script. This can sometimes resolve issues with permissions and authentication.
- To create a bound script, open your Google Sheets file, go to Extensions > Apps Script, and paste your script there.
Check for Conflicting Authorizations:
- If you have multiple Google accounts or if your script was previously authorized with different credentials, this might cause conflicts. Try clearing your browser's cache and cookies, or use an incognito window to eliminate this issue.
Manual Token Handling:
- If you're manually handling the OAuth token in your script, ensure that it's being used correctly in the BigQuery API request. However, this shouldn't be necessary if you're using the built-in BigQuery service in Apps Script.
Review Project Settings:
- Double-check that the Google Cloud Project linked to your Apps Script has the correct settings and that the BigQuery API is enabled.
- Ensure that the project number in the Google Cloud Console matches the project number in the Apps Script project settings.
Logging and Debugging:
- Enhance logging in your script to capture more details, especially when the function is called from Sheets. This might provide more insights into where the process is failing.

EricPetermann

Hello and thanks for the reply again.

@ms4446 wrote:

Script Deployment as API Executable:
Ensure that your script is deployed as an API executable. This is different from a standard script deployment and is necessary for it to be called from external sources like Google Sheets.
Go to the Apps Script Editor, click on 'Deploy' > 'New deployment'. Choose 'API executable' as the deployment type. This might help with the authentication issue when the script is called from Sheets.

I tried this and the process worked. I still get the same error in sheets however.

@ms4446 wrote:
Explicit Authorization Flow:
Sometimes, the authorization flow does not trigger automatically in Google Sheets. You can try creating a separate function in your Apps Script that explicitly calls for authorization.
This function can be a simple script that requires the same scopes as your main function. Run this function directly from the Apps Script editor to trigger the authorization flow.

I wrote a function that removes authorization using

ScriptApp.invalidateAuth(); and wrote another one that calls for authorization using an UI-element. Using this technique I can now force authorization in Google Sheets directly and it does display the correct authorization scopes (see image)

.

Accepting still makes it so the script runs fine in the Apps Script Editor but gives the same authorization error when trying to run it in Sheets.

@ms4446 wrote:

Spreadsheet Bound Script:
If your script is not bound to the specific Google Sheets file, consider making it a bound script. This can sometimes resolve issues with permissions and authentication.
To create a bound script, open your Google Sheets file, go to Extensions > Apps Script, and paste your script there.

The Apps Script was created as a bound script originally.

@ms4446 wrote:
Check for Conflicting Authorizations:
If you have multiple Google accounts or if your script was previously authorized with different credentials, this might cause conflicts. Try clearing your browser's cache and cookies, or use an incognito window to eliminate this issue.

The script is currently only being run and authorized by my own account.

@ms4446 wrote:
Manual Token Handling:
If you're manually handling the OAuth token in your script, ensure that it's being used correctly in the BigQuery API request. However, this shouldn't be necessary if you're using the built-in BigQuery service in Apps Script.

I have since removed the manual token handling. When using the OAuth2 Library command

ScriptApp.getOAuthToken()

doesn't generate a token when the script is run through sheets and only in Apps Script (I checked with Logger in Apps Script and return in Sheets).

@ms4446wrote:

Review Project Settings:
Double-check that the Google Cloud Project linked to your Apps Script has the correct settings and that the BigQuery API is enabled.
Ensure that the project number in the Google Cloud Console matches the project number in the Apps Script project settings.

The project numbers match up (I can even click the number in AppScript which leads me to my GCP Panel). The Bigquery API is also enabled and shows 0 Errors.

Best wishes,

Eric

mhawksey

You mentioned you added other BigQuery roles to your project but not 'BigQuery Job User' - my suggestion would be to test if adding this role resolves the issue

EricPetermann

I have the roles "BigQuery Admin", "BigQuery Job User" and "Owner", but it does not work. I have had these roles since before my previousr reply.

mhawksey

These are great instructions, on note to add to @ms4446 very clear trouble shooting tips is regarding

@ms4446 wrote:
Verify credentials are properly set up in the Google Cloud Console.

A problem I've seen in the past that returns the same error is in you GCP you need the 'BigQuery Job User' role added to the account running the script

ms4446

@mhawksey ,

You're absolutely right. Ensuring that the account running the script has the appropriate roles in Google Cloud Platform (GCP) is a crucial step. The 'BigQuery Job User' role is necessary for executing jobs in BigQuery, including queries, load jobs, export jobs, etc.

Google sheets missing authentication for big query in apps script