In this article, we’ll learn how you can integrate GSheets with Python in an easy, step-by-step tutorial.
Introduction
A lot of the times while we work with code, we end up realizing that we need a place to store the information produced from the code or utilized by it.
Eventually upon looking further, we come to a realization, that the solution that’s used worldwide is something known as a database.
You also figure out gradually that these databases can be accessed via your code, and being ecstatic, you can’t wait to try it out.
Fast forward a few weeks later, you realize that there are a lot of options for databases and that Google Sheets that you’ve always used also acts as a database.
With all the operations of Creating, Reading, Updating and Deleting, or abbreviated, CRUD.
Well, here’s the good news, you’re right!
Google Sheets is a software that allows us to view, edit and collaborate online to write down records that are used in our daily life.
But, here’s the thing that a lot of people don’t realize. Google also provides us with the feature of accessing the Sheets from the comfort of our code.
We can perform all the operations that we could in the browser, through our code!
However, like always, there’s a need for the starting step, in order to understand how the code works.
So, now that you know what’s coming next, let’s get to working with Google Sheets!
Installing Google Python Client API
In order to get started with working with Google Sheets through Python, we’ll first need to ensure that we have the functionality and the capability to run it.
That being said, here’s the pip installation command for the Google Client Library,
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
We’re going to be using a few other modules, which make it easier to work with the records, and is sort of a roundabout to working with Google Sheets directly,
pip install gspread oauth2client
This should work for most instances of Python, however, if you seem to have any issues, you might want to look into the installation page.
Steps to integrate GSheets with Python
Next up, we’ll be looking at the steps to integrate GSheets with Python. Follow the steps below:
1. Setting up a project in GCP
In order to work with the GSheets API, we’ll first need to create and use a project that we’ve made in the Google Cloud Platform, or also known as GCP.
The Google Cloud Platform is a suite of cloud computing services, by definition, and in order to interact with the API, which is based on the cloud, we need to work with GCP as a pre-requisite.
So, do keep in mind that GCP, and your Google Drive are linked, and this means that if you create a project and just enable the Drive API, we’ll be able to 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. The method to doing that, is by simply clicking on the select a project indicated by the red arrow in the image, and creating a new project once the window pops up.
You can choose to put any name as your project title, and select the no organization option if you aren’t connecting this project to a particular organization.
If you’re not already redirected to the dashboard, you can access it 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.
A simple solution to going around and trying to find the API would be to use 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 must align up with, Google Drive API, Web Server, Application Data, and a No to the Compute Engine integration, to look something like this,
Then, we create a service account with our input of the service account name, it can be anything, and the role as editor.
We allot our permissions to be one as 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
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.
4. Accessing Gsheets with Python
Finally!
We now reach the part where we actually start working on the code.
So, flashback to the start of the article where we installed a few additional modules, now’s the time that we get to use them.
The gspread module is essentially a Python API for the Google Sheets module and consists of a decent documentation, and the oauth2client module is simply a library to work with OAuth.
The oauth2client library is now considered deprecated, and has been done so in favor of the google-auth library.
However, it will suffice for our example of working with the sheet. Let us first import the modules to begin working on the script!
# Authentication and access modules
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# pprint is used for pretty printing the output
# It is not vital to the script in any way, but rather for us
# to view the examples
from pprint import pprint
Next, we add in the scopes for the access to the sheets,
# sheet access
scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
This defines the pages from where we retrieve our spreadsheets. Once we’re done with this, the script will know where to look for our documents, hence the term scope.
Next, we proceed to work on providing and authorizing our credentials for the service account using the oauth2client module.
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
Now, the sheet that I’ve created is named as tester, and am using the records from the first sheet, so, the assigning the particular sheet to a variable is followed through in such a manner,
sheet = client.open("tester").sheet1
And, we’re done!
Now, we’ve established a connection to the sheet, and the rest of our code is to test the functionality of the spreadsheet.
It is advisable to test it out on your own sheet/example, as this is quite the hands-on experience.
# retrieves all records
print("Retrieving all records.... ")
data = sheet.get_all_records()
pprint(data)
# retrieving specific row values
print("\nRetrieving information from row 3... ")
row = sheet.row_values(3)
pprint(row)
# retrieving specific column values
print("\nRetrieving information from column 3... ")
col = sheet.col_values(3)
pprint(col)
# 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", "[email protected]", "33YEP4554"]
sheet.insert_row(insertDetails, 1)
# updating details in the sheet
print("\nUpdating details in the sheet...")
sheet.update_cell(2, 2, "[email protected]")
In the end, we insert a value into the sheet through the sheet.insert_row, and specify that it was part of row 1.
Here’s the output from the command line,
[{'33YEP4554': 2222333312,
'Adithya': 'Murthy',
'[email protected]': '[email protected]'},
{'33YEP4554': 8098776633,
'Adithya': 'John',
'[email protected]': '[email protected]'},
{'33YEP4554': 123456789,
'Adithya': 'Boiler',
'[email protected]': '[email protected]'},
{'33YEP4554': 2524523233,
'Adithya': 'lalith',
'[email protected]': '[email protected]'},
{'33YEP4554': 2654432266,
'Adithya': 'swathi',
'[email protected]': '[email protected]'},
{'33YEP4554': 6666634232,
'Adithya': 'chinnu',
'[email protected]': '[email protected]'},
{'33YEP4554': 6345311456,
'Adithya': 'aditya',
'[email protected]': '[email protected]'}]
Retrieving information from row 3...
['John', '[email protected]', '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...
'[email protected]'
Inserting details into the sheet...
Updating details in the sheet...
And, here’s an image of the spreadsheet itself,
5. Moving forward
Now that we’ve worked with the basic principle of the sheets module, it would be wise to be able to automate this to some degree.
A few examples would be of entering in attendance of people that show up at the venue through the command line, or just adding in all the records of a file into the Google Sheet, with a little bit of formatting the input.
The gspread documentation consists of a lot more features than the ones discussed here in this article, ranging from updating cells to deleting entire worksheets.
Conclusion
Working with Google Sheets using Python opens up a lot of doors when it comes to recording details, may it be with utilizing it as a database, a collaborative record taking, or even just updating details such that the non-coder folk can understand it.
Overall, a brilliant way to get started with working with Google’s APIs.
That being said, it may also be a great idea to look into some other modules that can help you work with databases and configuration, like SQL, Pandas, and the dotenv module.
Also, before you check into those, here’s the link to the script that we’ve developed today – Gist Link.