6

Background

I have a google sheet, who's data I need to process on my local system. The nature of processing is very tedious and long so I wrote a script for it.

Problem

I need to access the google sheet through the python script to process it further. Online it is mentioned that to read the private google sheet directly, I need to create a GCP project and within that project, I need to create a service account. After that I should download the credentials and share the google sheet with that service account email.

Two problems here are :

  1. Downloading the credentials -- insecure and my organization prohibits it.
  2. Sharing of google sheet with service account email. organization also prohibits sharing sheet with outside organization emails.

What I found as a solution

I came across a solution of impersonating a service account but I could not find anything as to how can I do that. (Would appreciate any insights on that). All the other solutions suggested to download credentials which is a big NO.

For the sharing of sheets thing, I guess we can use drive API, but same problems are with that.

I tried using gcloud auth login and gcloud auth application-default login but was getting errors

Request had insufficient authentication scopes.". Details: "Insufficient Permission: Request had insufficient authentication scopes.

using ['https://www.googleapis.com/auth/spreadsheets', https://www.googleapis.com/auth/drive'] as scopes

What I need? (Summary)

How to access google sheets API (or download the Sheet from google drive) without downloading any sort of credentials.json.

Improve this question
3
  • Think about this from a security viewpoint. If your request were possible, what would stop the world from accessing the sheet? Your request is not possible with your limitations. You will need credentials. Either Google OAuth 2 User or Service Account. @DalmTo answer is correct. Commented Mar 27, 2021 at 18:48
  • 1
    @JohnHanley The only restriction is to not download the credentials of the service account. The script can be used by multiple people in the organization and the credentials will have to be shared with each of them. Is there any way to authenticate without sharing the credentials, idk, maybe using something like gcloud auth login... Commented Mar 28, 2021 at 6:38
  • gcloud auth login stores credentials on the local machine. The same type of credentials that you say your organization prohibits. Commented Mar 28, 2021 at 6:58

3 Answers 3

Reset to default
2

problem

Your only option to access private user data is to be authorized as a user who can access the file. Either logging in using Oauth2 or using a service account

Downloading the credentials -- insecure and my organization prohibits it.

In order to use a google api you must first register your application on google developer console and download the client credentials then a user must authorize the application using Oauth2, which would mean both downloading the credentials.json file from google developer console and you getting user token credentials from the authorized user.

Sharing of google sheet with service account email. organization also prohibits sharing sheet with outside organization emails.

In order to use a service account you would again need to first register your application on google developer console and download the client credentials for the service account. Then you would need to share the sheet with the service account.

service account impersonation.

Service account impersonation is used by GSuite domain. You create a normal service account again downloading the credentials, and then the GSuite admin is able to delegate authority to the service account which will allow it to impersonate a user on the domain.

This would require you to have GSuite domain and the owner of the file being also on the GSuite domain, and you creating a project on google developer console and downloading the credentials.json for the service account, which you already stated you couldn't do.

conclusion

I guess what i am saying is there is no way with the limitations imposed by your organization for you to access a private google sheet or any private user data on googles system, via any api.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I assume you're looking at an example that looks a bit like this:

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow

SCOPES = ['https://www.googleapis.com/auth/spreadsheets',  
 'https://www.googleapis.com/auth/drive']

def main():
  flow = InstalledAppFlow.from_client_secrets_file(
    'credentials.json', SCOPES)
  creds = flow.run_local_server(port=0)

  drive_svc = build('drive', 'v3', credentials=creds)
  sheets_svc = build('sheets', 'v4', credentials=creds)

The library does it for you in two lines of code, but you can read the details of how this works at:
OAuth 2.0 for Mobile & Desktop Apps

The credentials.json file mentioned above is not an authorized set of user credentials. It consists of a client ID (public) identifier for your application and a client secret (private) known only to your application. Neither of these can be used to access your data, but they can be used to ask a user for permission to allow app access.

Your organization may have a policy disallowing data access from untrusted applications. In that case, you'll need your admin to trust your application (and your client ID). That's how the policy is intended to work.

A quick note on service accounts in Google Drive:
See the warning at Drive API - Authenticate your users. Generally speaking, service accounts should never act as themselves in Drive. They should only be used from an administrative capacity to impersonate users. They aren't, and shouldn't be relevant to your use case.

Improve this answer

4 Comments

Your solution uses an installed application and would use Oauth2 and require that the author create credentials on Google developer console and run an installed application. Which the author has already stated they can't do because of organizational red tape. This does not answer the question.
The best way to overcome organizational red tape is to explain the what you need and why. :) Otherwise, there's not a very good answer to "My organization blocks API access; how do I use APIs to access data?".
@mary What I need: to access google sheet without downloading credentials. My organization does not block access to the API. They are restrictive towards passing of credentials in form of files, which I guess you can agree, is very insecure. If there is any other way to authenticate and access sheet, without downloading credentials, I seek that. Thanks
No, I don't agree. Because the credentials in question are only a means to identify your app. They don't provide any data access. The authorization part is handled through the OAuth flow, which returns a short lived access token, which your app can use to access data. This access token (and optionally a refresh token, allowing you to refresh that access without user intervention) is never written to disk unless you explicitly do so.
0

instead of gcloud auth login try with gcloud auth login --enable-gdrive-access

another solution which i employed at the time was to access the sqllite db used by gcloud to store creds, fetch token and refresh token from there, pass it to the gcloud sheets api with sheet id to access it. I dont think this is required anymore, but just fyi

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.