Search code examples
pythongoogle-colaboratory

Authorizing Google Drive service account to write pandas df to Google Sheets

I am using Google Co.lab notebook to write a pandas dataframe to a Google Sheet in my personal Google Drive account.

I have created a services account with the Google Drive API and created a API key, which is housed in Google Drive (My Drive/project/scrapers/utils/auth_key.json). I want to authenticate with Drive Services so I can use the Drive API to move/write Sheets into a specific folder, per this question.

I'm having issues with authentication for the service account:

import os
import gspread

# Mount Google Drive

from google.colab import drive
drive.mount("/content/gdrive")

# Authenticate service account

from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://spreadsheets.google.com/feeds']
filepath = os.path.expanduser('/content/gdrive/My Drive/project/utils/api/service_key.json')
credentials = ServiceAccountCredentials.from_json_keyfile_name(filepath, scope)
gc = gspread.authorize(credentials)

# Specify spreadsheet name and desired folder

title = 'test'  # Spreadsheet name.
my_path = "/project/scrapers/csv_output" # File path
destFolderId = "/content/gdrive" + "/My Drive" + my_path # Full file path

# Write spreadsheet to desired folder

from apiclient import discovery

drive_service = discovery.build('drive', 'v3', credentials=credentials)
file_metadata = {
    'name': title,
    'mimeType': 'application/vnd.google-apps.spreadsheet',
    'parents': [destFolderId]
}
file = drive_service.files().create(body=file_metadata).execute()

This results in the error <HttpError 403 when requesting https://www.googleapis.com/drive/v3/files?alt=json returned "Insufficient Permission: Request had insufficient authentication scopes.".

I think that this means my service account doesn't have the right permission settings, but after hours of research I'm unsure how to fix. I have given the email associated with the service account access to the Google Drive folder, and the service account has Owner privileges. Please help!

Solution

  • once mount is complete drive.mount('/content/gdrive') file can be accessed like

    /content/gdrive/My Drive/Test_Data/elephant.jpg

    for

    ServiceAccountCredentials.from_json_keyfile_name(filepath, scope)

    verify API and its scope.