Category Archives: GCP AWS Google Cloud ML AI

Permission denied while getting drive credentials from BigQuery table in Cloud Functions via SQL

If you have a BigQuery external table which uses for example a Google Sheets file as source, AND you try to read / join this table in BigQuery SQL you may get the error in BigQuery job failure log:

google.api_core.exceptions.Forbidden: 403 Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

Seems like no drive scope is the default, so BQ clients that need these scopes should be passing it in via the client_options.

So in this original solution post: https://github.com/googleapis/google-auth-library-python/issues/1204

This is the solution which worked for me too:

EricSeastrand commented on May 27, 2024• 
For anyone else facing this, here's the exact code that worked for me:

from google.cloud import bigquery
client = bigquery.Client(client_options={
    "scopes": ['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/cloud-platform']
})
results = client.query_and_wait(sql)

During the bigquery.Client connection I had to pass the above scopes and there is no problem with authorizations in Sheets/Drive access anymore.

Some day I may post more on the topic …..

Google Cloud scheduler call to Cloud functions 401 error

I used to run some functions developed and deployed on Google Cloud Run Functions.

Some of them are scheduled to run via Cloud Scheduler.

Last time I decided to copy the definition of the Cloud Scheduler job from bot 2 to my new bot3 function. And I started to get 401 unauthenticated errors for bot3, but the same definition was working fine for bot2.

The error looks like below:

debugInfo: "URL_ERROR-ERROR_AUTHENTICATION. Original HTTP response code number = 401"
jobName: "projects/....../locations/europe-west6/jobs/......"
status: "UNAUTHENTICATED"
targetType: "HTTP"

and in the Cloud Function log is the misleading:

The request was not authorized to invoke this service. Read more at https://cloud.google.com/run/docs/securing/authenticating Additional troubleshooting documentation can be found at: https://cloud.google.com/run/docs/troubleshooting#401

BUT – actually the problem is that during the copy of the scheduler job definition, the “Audience” value IS NOT updated with the new function URL (in my case bot3), but the old function URL is kept. It seems like it was autofilled in bot2 definition though and I didn’t noticed it. So be sure that URL and Audience are properly set!

If you fix this the 401 error is solved and gone!!!

I hope I saved you some time. Post one thanks in comments if so …

Google Drive API v3 (googleapiclient.errors.HttpError 404 File not found)

If you try to upload a file in a shared drive (in Google Drive) you may get the above error.

This is my experience and solution as of February 2024 using Python 3.10.

from googleapiclient.discovery import build
from googleapiclient.http import MediaFileUpload
from google.oauth2 import service_account

from io import BytesIO
from googleapiclient.http import MediaIoBaseUpload
from google.cloud import translate_v2 as translate

## Credentials of service account
credentials = service_account.Credentials.from_service_account_info(service_account_info, scopes=['https://www.googleapis.com/auth/drive'])

## API Client Service
service = build("drive", "v3", credentials=credentials)

# buffer_memory=BytesIO(b"some initial binary data: \x00\x01") # BytesIO() new_body
buffer_memory=BytesIO() # BytesIO() new_body
buffer_memory.write(new_body.encode('utf-8'))

## Prepare the file in memory (you can upload local file too with MediaBase Upload)
media_body = MediaIoBaseUpload(buffer_memory, mimetype='text/html', 
        chunksize=1024*1024, resumable=False)
                body = {
                        'title': file_name,
                        "name": file_name,
                        "mimeType": "application/vnd.google-apps.document",
                        "driveId": "0APee............PVA",
                        "parents": ['0APe.............PVA'],
                    }

## Upload file
returned_fields="id, name, mimeType, size, webViewLink, exportLinks"
upload_response=service.files().create( body=body,                                                            
   media_body=media_body,
   supportsAllDrives=True,
   fields=returned_fields).execute()

## Share the created file with user
user_permission = {
                    "type": "user",
                    "role": "writer",
                    "emailAddress": share_user,
                }
                perm_response = service.permissions().create(
                    fileId=uploaded_file_id,
                    body=user_permission,
                    fields="id"
                ).execute()
   

The important difference between uploading in private Google drive is that you have to use the following parameters for Shared Drives:

supportsAllDrives=True
driveId -> Id of Shared Drive
parents -> Folder or Shared Drive

Mime Types: List of the supported Mime type here

Tip:

If you want to convert the uploaded file to Google Drive native format use the following parameter:

"mimeType": "application/vnd.google-apps.document",

Google documentation: https://developers.google.com/drive/api/guides/manage-uploads

And this article on StackOverflow as well helped to find the solution although some parameters are wrong or deprecated already: https://stackoverflow.com/questions/67622131/google-drive-api-v3-googleapiclient-errors-httperror-404-file-not-found