2

I am new to using Google Cloud Platform and its Developer Console. Here, I am working on a project wherein I have to automate data transfer from google sheets to SQL Server daily using Task Scheduler. Here, I came across an approach which requires me to share Google Sheet to a service account to read data from it in Python. This approach would require me to share each and every sheet in my company's drive account to a service account which add an step for a manual intervention.

Is there any method or approach by which I can skip the process of sharing sheets to service account and read sheets directly from my google drive or so?

I tried below script of reading shared Google Sheets to a service account which works fine but that's not what I require.

import gspread, pyodbc, pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd


scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('service_account.json', scope)

client = gspread.authorize(creds)
sheet = client.open('Sample').sheet1

data = sheet.get_all_values()

Need you to assist me in such a methodology though if it uses an API key instead of an oauth.

Mohit Aswani
  • 185
  • 1
  • 7

1 Answers1

0

If you're using a Service account the best way to access a user's sheet without updating sharing permissions is by using user impersonation, this allows the code to perform API calls on behalf of a user instead of the Service Account.

I found this this post where the same scenario is solved using it, based on this I updated your code:

import gspread, pyodbc, pandas as pd
from google.oauth2 import service_account
# from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
domainEmail = 'Domain_Account_You_Want_To_Impersonate'
creds = service_account.Credentials.from_service_account_file('service_account.json', scopes=scope)
delegated_creds = creds.with_subject(domainEmail)

client = gspread.authorize(delegated_creds)
sheet = client.open('Sample').sheet1

data = sheet.get_all_values()

Note that:

  • Domain-wide delegation needs to be configured in the Google Workspace Admin console of the domain where the users are located.

  • The oauth2client was deprecated, I updated the code to use the new supported version of the library but it should work by adding this to your code:

delegate_creds = creds.create_delegated('Domain_Account_You_Want_To_Impersonate')

client = gspread.authorize(delegate_creds)

References:

Bryan Monterrosa
  • 1,385
  • 1
  • 3
  • 13