Advanced

Someone gave you a bunch of data in a spreadsheet, and you want to analyze it programmatically. If setting up a database for this seems like a daunting task to you, then don’t worry. In this article, we’ll learn how you can directly access data from your Google Sheets and use it as your database using Python.

To use Google Sheets as our database, we need three things – a Google Sheet where our data is stored, a Python script to read the data, and an API service to connect our Python script with the Google Sheet. Using the API service, we can access all the worksheets in the workbook, get all the data, insert new data, update, and delete the existing data, all in real-time. We will cover all the things you need to work with data in detail.

With just a few configurations and built-in functions, we can achieve the power of a database. We don’t need to learn database and SQL queries separately. So, let’s dive in and learn how it works.

Part 1: Creating the API Service

Step 1: The first thing we need to do is to go to the Google Developers Console. We will see a page like the following.

Step 2: Next, click on the My First Project dropdown at the top left. A pop-up will appear. Click on New Project. Now, give your project a name, like google-sheets-tutorial. Then, click on create.

Step 3: After creating a project, you will be redirected to the homepage, and you will see a notification. Click on Select Project for the project you have just created. In the dashboard, you will see a message that you have no APIs to use yet. We need to enable two APIs – one for Google Drive and another for Google Sheets to access Google Sheets from our Python script. So, click on the API Library.

On the Library page, search for Google Drive API. Click on it and then enable it.

Step 4: Repeat the same for Google Sheets API. Next, we need to add credentials. From the homepage sidebar, click on Credentials. Click on Create Credentials and select Service account.

Enter the details for your service account. Provide a name and description. The email will be generated based on the name. We will need the email later on.

Step 5: Next, grant access to the project. For the role, select Project and then select Editor. Finally, click on Done.

Step 6: Now, we will see there are still no API keys. To add that, click on Manage service accounts.

Now, click the three dots under Actions and click on the Create key option.

Select JSON and click create. This will download a JSON file to your computer. Rename this file as credentials.json. With this, we are done with creating the API service.

Part 2: Creating the Google Sheet and making it accessible

Step 1: Create a Google Sheet like the following. I have named it User Details and added two Sheets: Sheet 1 and Sheet2. Sheet1 has information about the User Name, Age, and Address.

Similarly, Sheet2 has information about the User Name and Occupation.

Step 2: Next, share this Google Sheet to make it accessible from Python script. Open the credentials.json file we downloaded earlier and copy the value of client_email from the file. Now on Google Sheets, click on the Share button and paste the email. Make sure that it is given Editor access, and then send. Now, we are ready to access this Google Sheet from our Python script.

Part 3: Creating the Python Script

Step 1: Make sure that you are using Python 3. If you are using Python 2, the syntax may vary. Now, to access the Google Sheet, first, we need a Python library called gspread. If it is not already installed, go to your command line (or terminal if you are in a Unix system) and type the following.

pip install gspread

Step 2: Once gspread is installed, create a folder in the directory of your choice and name it python-tutorial (or anything you like). Move the credentials.json file into this folder and create a main.py inside it. We will now write our Python script in this file.

The first thing to do here is import gspread and connect to the Google Sheet. Use the following snippet of code to do so.

import gspread

gc = gspread.service_account(filename='credentials.json')
workbook = gc.open('User Details')

The credentials.json has all the details related to our service account. Using this and gspread, we connect to our service account and store it in the variable gc. Now we can pass the name of our Google Worksheet User Details to gc.open to access it.

We have two worksheets. To access the first worksheet, we can do the following.

worksheet = workbook.sheet1

Part 3a: How to Read Data From Google Spreadsheets

To access the records, we can write the following.

print(worksheet.get_all_records())

Run main.py, and we get the result back as a list of dictionaries.

We can write the following to get the result back as a list of lists.

print(worksheet.get_all_values())

To read the first row of the Google Sheet, we can write the following.

print(worksheet.row_values(1))

Notice that the row index starts at 1 instead of the traditional indexing of 0 in programming. If 0 is provided as an argument to the row_values function, it will throw an error. We can do the same thing with worksheet.col_values to get the column values of our choice.

To access the value of a particular cell, we can use the get method with the cell name as the argument.

print(worksheet.get('A2'))

This will print the content of the A2 cell as a list of lists.

We can also get contents from a cell range. For instance, to get the details for the first user, we can write the following.

print(worksheet.get('A2:C2'))

Part 3b: How to Add Rows to Google Spreadsheets

We can insert new data at a row of our choice using the insert_row method. Here we need to pass the user details and the row number as an argument.

new_user = ['Kim', 35, 'Seoul']
worksheet.insert_row(new_user, 4)

Now, if we check back in our Google Sheet, we can see that the user Kim has been added in row 4.

We can also append data at the end using the append_row method. Here, we only need to pass the user details as an argument.

appended_user = ['Sadio', 60, 'Nairobi']
worksheet.append_row(appended_user)

Sadio has now been added to the last row.

Part 3c: How to Update Existing Rows on Google Spreadsheets

We added the age of Sadio age as 60. Suppose we need to update this to be 65. We can use the update_cell method to do this. We need to pass the row number, column number, and the updated value as an argument. The age of Sadio is in row 9 and column 2. So we write the following code.

worksheet.update_cell(9,2,65)

Check back in the Google Sheet, and you will see that the age has been updated to 65.

Part 3d: How to Delete Data on Google Spreadsheets

Now, suppose we want to remove the record for Sadio. We use the delete_rows method and pass the row number as an argument.

worksheet.delete_rows(9)

The record at row 9 will now have been deleted.

Part 3e: How to Access Data From All Google Worksheets

We created two worksheets in our Google Sheet. We have learned the basics of accessing data from a single worksheet. Let us now look at how we can deal with multiple worksheets. The following code snippet will loop through all the worksheets and then fetch data from all the rows in each of the worksheets.

import gspread

gc = gspread.service_account(filename='credentials.json')
workbook = gc.open('User Details')

def format(data):
	for i in range(len(data)):
		print(data[i] + '\t' + '|', end=" ")
	print('\n')

def get_worksheet_details(worksheet):
	records = worksheet.get_all_values()
	print('\n--------------------------------')
	format(records[0])
	print('\n--------------------------------')
	for record in records[1:]:
		format(record)

for i in range(len(workbook.worksheets())):
	print('Worksheet ' + str(i+1))
	worksheet = workbook.get_worksheet(i)
	get_worksheet_details(worksheet)
	print('\n\n')

len(workbook.worksheets()) will get the total number of worksheets, and then we loop through each of the worksheets. Previously, we used workbook.sheet1 to access the first sheet. Now, we achieve the same using workbook.get_worksheet(i). This will help us get the worksheet number dynamically. The two functions are used just for formatting purposes. The result of this program will be the following.

And there you go, you can now have a complete database in your Google Sheets without the hassle of setting it up and learning all the SQL queries.

Further Notes

We accessed the Google Sheet using the name of our sheet. We can also do the same by using a unique key for our sheet. To learn about how to do that, please check out this awesome video.

To learn in even more depth about what gspread can do, you can read the documentation.

Subscribe to our newsletter