Google Spreadsheets OAuth Authentication with Python

Introduction

This article shows you how to connect to Google Spreadsheets API using
OAuth with Python. OAuth (Open Authorization) is an open standard
protocol used for token-based authentication and authorization,
allowing third-party applications to access a user's resources without
exposing their credentials. What this means in practical terms is that
you can allow an external application to access your google
spreadsheets without having to share your account name and
password. The way this works is: the external application redirects
you to a Google consent screen which you can approve or refuse. Once
you approve the access request, Google then hands an approval token
to the application which can be used for accessing your spreadsheets.

We are using this method to connect a user's spreadsheets to their
account on Teramine Forms. This way, when a
user fills in a form, the data is uploaded immediately to the
specified google spreadsheet without manual intervention.

Google API Docs are hard to understand

The main motivation for this article is that: in 2024, we had to spend
considerable time wading through Google API docs to implement this
functionality. Part of the reason was that some of the Google API docs
are out of date or just plain wrong, which you discover when
attempting to use them. For example, even authenticating with Google
servers for invoking spreadsheet related functions was a bit of a pain
because the information required comes from various different sources,
as you will see in the article later.

So we hope that this article will ease the process for anyone trying
to connect to a Google spreadsheet to create or update data.

Setup Oauth Consent Screen

The first step is to go to Google Cloud
Console, login with your account
credentials, and create the OAuth consent screen. Follow the procedure
outlined in this
document
and complete the procedure. Follow through the procedure to create an
OAuth 2.0 Client ID too as shown in the picture below. Finally
download the OAuth Client information as JSON.

The downloaded file will be named something like
client_secret_<something>.json. Save it in a safe place. We are
going to need it for authentication.

Here is a sample just so you know what it looks like:

{
    "web": {
        "client_id": "577xxxxx.apps.googleusercontent.com",
        "project_id": "teramine-forms",
        "auth_uri": "https://accounts.google.com/o/oauth2/auth",
        "token_uri": "https://oauth2.googleapis.com/token",
        "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
        "client_secret": "xxxxxxxxxxxx",
        "redirect_uris": [
            "https://example.com/oauth/callback/googlesheets/"
        ]
    }
}

The fields client_id and client_secret are specific to you
application, and the redirect_uris will contain values you specified
with you configured the "OAuth Consent Screen". It need not take the
same form as shown above and it can be anything specific to your
application. When a user consents to allowing access to your
application, Google will invoke this URL and pass a code parameter
which is the approval token you application can use to retrieve an
access token.

Congratulations! You have completed the first step in preparing for
Google Spreadsheets Authentication.

Using Google API for Authentication

We will now look into authenticating an application using the
client_secret.json file we just downloaded. For this purpose we will
use the official Google API.

Installing the Official Google API for Python

Begin by installing the official Google API.

pip install google-api-python-client google-auth-httplib2 google-auth-oauthlib

This step assumes that you have setup a Python virtual environment for
you application. If this is not the case, please look at other
documents on the Internet how to setup a Python virtual environment.

Authenticate with Google to retrieve an access token

The next step is to authenticate using the Google API and retrieve an
access token. The access token is what actually allows invocation
of the spreadsheet API. The access token has a short time out period
of a few minutes, so when that expires you need to use a refresh
token which allows you to refresh the access token.

Creating a Flow object

We begin by importing the following library into the sample code.

import google_auth_oauthlib.flow

Next is to create a flow object as follows. Note that we use the
client_secret.json file we saved in the previous step. It has been
renamed here from client_secret_<something>.json to
client_secret.json for easier access.

You can also see the scopes that we need for the application. You
should have specified these scopes when you configured the Google
OAuth configuration screen.

flow = google_auth_oauthlib.flow.Flow.from_client_secrets_file(
    'client_secret.json',
    scopes=["https://www.googleapis.com/auth/userinfo.profile",
            "https://www.googleapis.com/auth/userinfo.email",
            "https://www.googleapis.com/auth/spreadsheets",
            "https://www.googleapis.com/auth/drive",
            "openid"])
flow.redirect_uri = "https://example.com/oauth/callback/gsheets/"

We are using the userinfo.profile and userinfo.email scopes too,
but you could remove them if not needed.

The spreadsheets scope is required for reading and writing a Google
Spreadsheet.

The drive scope is needed for being able to create a Google
Spreadsheet.

We are also setting the redirect_uri attribute of the flow object
to the same value that was specified during OAuth Configuration. Any
other value will result in an error.

Redirect user to the consent screen.

Next we ask the flow object for the authorization URL. In a web
application, you would redirect the user to this authorization URL so
they can login to their Google account and allow access. Or, for
testing purposes, you could just print the authorization URL, paste it
into a browser, and allow access acting as a user.

print(flow.authorization_url()[0])

Collecting the access code from Google

The authorization URL leads to a Google server which takes the user
through a process of collecting the user's consent to allow access to
your application. It shows information about what exactly is being
requested and asks whether you want to allow access.

Once you agree to allow access, Google redirects the user again back
to the redirect_uri specified during OAuth configuration. Included
in the redirect URL is a code. Presumably, you have an application
running at that URL which can process the request parameters passed by
Google and extract the code parameter. This code represents user
allowing access to your application.

The following shows the URL invoked by Google after authorization is
successful. This example shows https://example.com as the website
domain, but your application would have its own domain.

url = 'https://example.com/oauth/callback/gsheets/?state=qf0xxxxxx8K7&code=4/0AQlxxxxxn4w&scope=email%20profile%20https://www.googleapis.com/auth/drive%20openid%20https://www.googleapis.com/auth/spreadsheets%20https://www.googleapis.com/auth/userinfo.profile%20https://www.googleapis.com/auth/userinfo.email&authuser=0&prompt=consent'

Here is some code which processes the URL to extract the code passed
in by Google after user authorizes your application.

from urllib.parse import urlparse, parse_qsl, parse_qs, urlencode, urlunparse
code = parse_qs(urlparse(url).query)['code'][0]

And this code can be passed on to the flow object created earlier to
retrieve the token which can be saved for future access. The token is
saved to a local file called token.json

token = flow.fetch_token(code=code)
with open('token.json', 'w') as fp:
    json.dump(token, fp, indent=2)

Obtaining the refresh token

Google returns the refresh token along with the access token the first
time the user authorizes the application. However, if you would like
to make sure the refresh token is always included, you can pass the
additional parameter prompt=consent when redirecting the user to
Google for authorization.

Here is some code that can be used for the purpose. It mangles the
authorization URL returned by the flow object to add the parameter
prompt=consent before redirecting the user. The url printed on the
last line of this code fragment has prompt=consent included.

parse_res = urlparse(flow.authorization_url()[0])
qarr = parse_qsl(pres.query)
qarr.append(('prompt', 'consent'))
pres = pres._replace(query = urlencode(qarr))
url = urlunparse(pres)
print(url)

Authenticating using the access token

At this point, we have two JSON files - a client_secret.json and a
token.json. We can now go ahead and use these to authenticate with
the Google API.

The first step is to import the required Google library.

import google.oauth2.credentials

Next we declare a dictionary called cred_props in which we will
store the parameters required for authentication. We begin by copying
the access_token and refresh_token into this dictionary.

cred_props = {}
with open('token.json') as fp:
    x = json.load(fp)
    cred_props['access_token'] = x['access_token']
    cred_props['refresh_token'] = x['refresh_token']

The library will use the access_token if it is still valid and
obtain a new one using the refresh_token if the access_token has
expired.

Next we load the contents of client_secret.json and pick up some
more parameters from it: token_uri, client_id and client_secret.

with open('client_secret.json') as fp:
    x = json.load(fp)
    cred_props['token_uri'] = x['web']['token_uri']
    cred_props['client_id'] = x['web']['client_id']
    cred_props['client_secret'] = x['web']['client_secret']

With cred_props built as the library needs it, we obtain a
Credentials object as follows using the google.oauth2.credentials
module. Note that we pass both the access_token and the
refresh_token so the library can refresh when needed (as explained
earlier).

credentials = google.oauth2.credentials.Credentials(cred_props['access_token'],
                                                    refresh_token=cred_props['refresh_token'],
                                                    token_uri=cred_props['token_uri'],
                                                    client_id=cred_props['client_id'],
                                                    client_secret=cred_props['client_secret'])

And this credentials object is what we need to invoke methods from
the Google spreadsheet API.

Using the Google Spreadsheet API

Let us now fetch some data from a Google spreadsheet using the Google
API. Begin by importing the required library.

from googleapiclient.discovery import build

The way the API works is: obtain a service object for the desired
service - Google Docs or Google Drive or Google Spreadsheets or
anything else. Here we create a service object to access Google
spreadsheets.

service = build('sheets', 'v4', credentials=credentials)

Using this service object and a spreadsheet ID, we can fetch the data
in the spreadsheet as follows. The spreadsheet ID is the component
between /d/ and /edit in the spreadsheet URL. For example, in the
following spreadsheet URL, the spreadsheet ID is 1Fouxxxxxxt88 (not
a real spreadsheet URL or an ID, just an example).

https://docs.google.com/spreadsheets/d/1Fouxxxxxxt88/edit?gid=0#gid=0

Use the service object and the spreadsheet ID to obtain a result
object as follows:

result = service.spreadsheets().values().get(spreadsheetId=sheetId, range='Sheet1').execute()

This call fetches the data in the sheet Sheet1 of the specified
spreadsheetId. Access values from the sheet as follows:

values = result.get("values", [])
if not values: raise Exception("No data found.")
for row in values: print(row)

This fetches each row of data in the spreadsheet as a list. Do with
it what you will.

And that is how to use OAuth 2.0 to connect to the Google Spreadsheet
API and fetch values from a sheet.