Today we’ll learn to work with Google Sheets in Python. We’ll be working with the google-auth module that allows you to integrate Google products into your Python code.
Table of Contents
Introduction to Google Authentication
As developers, we go through a large number of APIs looking for the right one to send requests and retrieve information for our analysis or work.
But, in order to access their functionality, we require some sort of proof that we are allowed to use this data.
This is referred to as Authentication, and is used to verify the user who wishes to access data.
And, one of the modules that are rising in popularity in this field recently, is the google-auth module.
The google-auth module provides the ability to authenticate the user with Google’s APIs, along with an integration of several HTTP libraries.
This is performed by utilizing a list of credentials that are provided to you by the Google Cloud Platform.
This means that you can now use these credentials to log into your account during the Script’s execution.
The google-auth module is one that allows for us to work with the Compute Engine, Container Engine, and the App Engine through a Python Script.
Installing the Google Authentication to Access Google Sheets
In order to work with the google-auth module, we’ll first need to install the module using the pip package manager:
pip install --upgrade google-auth
This allows for us to now work with the module in our Python Scripts.
It might be important to note that a prerequisite module to work with the Google API with ease would be the gspread module,
pip install gspread
Setting up the authentication environment
Let us begin by working on an example, interacting with the Sheets API as well as Sheets by Google.
1. Setting up a project in GCP
In order to work with the Sheets API, we’ll need to create and use a project that we’ve made in the Google Cloud Platform.
The Google Cloud Platform is a suite of cloud computing services, by definition, and in order to interact with the Google API, which is based on the cloud, we’ll need to interact with the GCP console first.
So, keeping in mind that GCP, and Google Drive are linked, we can enable the Drive API, and then receive the credentials required for the API to interact with the Script and retrieve data.
So, first things first! We’ll have to log in to our respective accounts, leading up to the Google Cloud Console.
Now, let’s create a project. We can do that by simply clicking on the select a project button, and creating a new project.
You can choose to put any name as your project title, we’ll be using tester for this example.
We can access the dashboard through the sidebar, ( Home -> Dashboard ).
2. Enabling the Drive API
Now that we’ve created the project, it’s time to bring in the API.
We can do that by using the search bar on top, querying for the Google Drive API.
If you’ve found the right one, it should look a little something like this,
Now that we’re here, let’s enable it, which should automatically lead to the API’s Overview page.
Next, you’d want to go to the credentials section through the sidebar.
overview -> credentials.
Let us generate our credentials according to our project, this means that our responses should be –
- Google Drive API
- Web Server
- Application Data
- No to the Compute Engine integration,
This will create a service account, which we need to provide the role as an editor.
We allot the permissions as an editor, as this allows us to perform all the CRUD operations but, doesn’t allow us to delete the entire file itself.
We also want this file to be a JSON file, as we will be using in this example.
Then, we also go ahead and enable the Sheets API, in a similar fashion, but don’t however, create any credentials.
3. Working with creds.json to access Google Sheets
Now, you might want to get back to the credentials that you’ve downloaded in the JSON format, and store it in the same directory as the one where we’ll be creating the script.
We’ll be renaming the credentials file as creds.json in this project, but, keep in mind that you can rename it, however, will need to rename it whenever the file is mentioned or utilized.
Now, inside the creds.json, we will receive a bunch of keys and values, but, we’ll only need to worry about the client_email key. Copy the value for this key, as we’ll be needing to share our Google Sheet with this email ID in order to access it’s contents.
Next, either create a new sheet, or use an existing sheet and add the email through the Share option present in the right-top corner of the page.
You should end up with something like this by the end of this step.
This allows the Google Service account to be able to access and modify the sheet that has been provided by you.
Using the Google-auth Module in Python
Now, we finally get to the part where we begin writing code to work with the resources that we can retrieve from the Google Sheets with Python.
1. Initializing the authentication
In order to work with the google-auth module, we’ll first need to initialize it. This can be done using the following commands,
# importing the Python API for google sheets
import gspread
# importing oauth2 from google-auth to authenticate our service account
from google.oauth2 import service_account
# pretty printing our output in the console
from pprint import pprint
After we’ve done this, we can use the methods that we’ve imported in the rest of the script.
2. Defining the credentials and scope
Now, we define the credentials from the JSON file that we received from the Google Cloud Platform, along with the scope from where the details are to be retrieved.
# used to retrieve credentials from the *creds.json* file
creds = service_account.Credentials.from_service_account_file("creds.json")
scoped_credentials = creds.with_scopes(
[
"https://spreadsheets.google.com/feeds",
'https://www.googleapis.com/auth/spreadsheets',
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/drive"
]
)
client = gspread.authorize(scoped_credentials)
3. Accessing Google Sheets through the Script
Now, we can access all the sheets that are connected to the account, which was done in the previous step, via the share option,
# We're accessing the sheet 1 of the tester document
sheet = client.open("tester").sheet1
# retrieves all records
print("\nRetrieving all records...")
data = sheet.get_all_records()
pprint(data)
4. Modifying the Sheet
The Sheets can also be modified through the four CRUD operations, which are features of the gspread module.
# retrieving specific cell
print("\nRetrieving value of a cell placed at (1, 2) in the sheet...")
cell = sheet.cell(1, 2).value
pprint(cell)
# inserting details into the sheet
print("\nInserting details into the sheet...")
insertDetails = ["Adithya", "testemail@gmail.com", "33YEP4554"]
sheet.insert_row(insertDetails, 1)
# updating details in the sheet
print("\nUpdating details in the sheet...")
sheet.update_cell(2, 2, "dat.adithya@gmail.com")
5. Final Output
Upon executing this script, we should receive an output that provides us with the full list of the records, a specific cell, and insert and update the sheet in the end.
The output that I received in my case was,
[{'33YEP4554': 2222333312,
'Adithya': 'Murthy',
'testemail@gmail.com': 'murthyhere@gmail.com'},
{'33YEP4554': 8098776633,
'Adithya': 'John',
'testemail@gmail.com': 'johnnyboi@yahoo.in'},
{'33YEP4554': 123456789,
'Adithya': 'Boiler',
'testemail@gmail.com': 'boilerplate@email.com'},
{'33YEP4554': 2524523233,
'Adithya': 'lalith',
'testemail@gmail.com': 'lalith.14bcm4243@gmail.com'},
{'33YEP4554': 2654432266,
'Adithya': 'swathi',
'testemail@gmail.com': 'swathi@rediffmail.com'},
{'33YEP4554': 6666634232,
'Adithya': 'chinnu',
'testemail@gmail.com': 'lamba@yahoo.com'},
{'33YEP4554': 6345311456,
'Adithya': 'aditya',
'testemail@gmail.com': 'adinayaki@gmail.com'}]
Retrieving information from row 3...
['John', 'johnnyboi@yahoo.in', '8098776633']
Retrieving information from column 3...
['33YEP4554',
'2222333312',
'8098776633',
'123456789',
'2524523233',
'2654432266',
'6666634232',
'6345311456']
Retrieving value of a cell placed at (1, 2) in the sheet...
'testemail@gmail.com'
Inserting details into the sheet...
Updating details in the sheet...
And, here’s an image of the spreadsheet itself,
Moving forward
The google-auth module is one that is considered as a parent of the oauth2client module and as such contains a lot more features that can be studied in depth here.
Providing support for features that can allow access to Google Cloud’s Compute Engines and App Engines through the Python Script.
Defining environment variables specifically for Google Authentication along with options to import from a JSON file.
Here’s the full script for the google-auth module testing that we’ve done today.
Conclusion
APIs are useful in order to work with information from a particular application or a service.
However, things can be made simpler by understanding which modules to work with and which ones to import, in order to work with the code in a more sensible fashion.
Authorization of credentials is a large part of being able to access objects, and items in a service.
It would also be for the best if you looked into options that provide you with scalability for the future, as well as support for the module.
This is what the google-auth module offers us with it’s features, and it’s community.
