How to use a service account to authorize google sheets?

Question

I am trying to open a private google sheet using python. The end goal here is to read that private sheet data into a json object. I have made sure to create a google cloud project, enable the API's, and service account. The service account email has been shared and added as an editor. I also created OAuth keys for a desktop application. This is required since the file is private.

I know I need to somehow request a token to use for access to the sheets API, but I am at a loss for how to create a request, and utilize the client_secret file generated from OAuth keys. I figured the googleAPI would have a function where you can pass this file directly, but I am lost in documentation.

Any insight would be appreciated!

Answer 1

All you need to do is supply the library with the location of the clientSecret.json file you should have downloaded from Google cloud console. This method should build the service for you and you can make the requests to the api. It will handle all the authorization.

from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials


def get_service(api_name, api_version, scopes, key_file_location):
    """Get a service that communicates to a Google API.

    Args:
        api_name: The name of the api to connect to.
        api_version: The api version to connect to.
        scopes: A list auth scopes to authorize for the application.
        key_file_location: The path to a valid service account JSON key file.

    Returns:
        A service that is connected to the specified API.
    """

    credentials = ServiceAccountCredentials.from_json_keyfile_name(
            key_file_location, scopes=scopes)

    # Build the service object.
    service = build(api_name, api_version, credentials=credentials)

    return service

The best example I know of for service account authentication with python is the Google analytics quickstart If you have any issues altering it for google sheets let me know i can try and help.

Calling it should be something like this.

def main():
    # Define the auth scopes to request.
    scope = 'https://www.googleapis.com/auth/spreadsheets'
    key_file_location = '<REPLACE_WITH_JSON_FILE>'

    # Authenticate and construct service.
    service = get_service(
            api_name='sheets',
            api_version='v4',
            scopes=[scope],
            key_file_location=key_file_location)

    data = your_method_to_call_sheets(service)

How to create clientSecret.json remember to enable the google sheets api under libary