Google Sheets with GSpread, the Python API

Did you know Google Sheets can be used as a lightweight database?

In this article, I will show how you can run a simple Python script to automate the process of extracting data from Google Sheets & turn Google Sheets into your own database.

Let me introduce you to the awesome spread package!

Gspread provides high-level APIs for you to open/create/read/write/ format the data.

Prerequisites

• Google developer account to create api credentials
• Google service account to access spreadsheets programmatically
• gspread — Python library
• oauth2client — Python library

The Google Sheets API lets you do the following:

• Import data from any source
• Automate low-value tasks

There are three main steps involved here. Getting credentials, connecting with the sheet, and finally writing code to implement the tasks. Let's take a look.

Step 1 →Credentials

→ Go to https://console.cloud.google.com/ & create a Project

→ Enable Google Drive API by going to API &Services → Library →Type in Google Drive API in the search bar and then click “Enable”. Wait for a moment before you click. “Create Credentials” tab on the top right corner of the screen.

→ Choose Application Data as per the screenshot below & a No against Compute Engine, Kubernetes etc.

→Choose a name for Service Account & then Click “Continue”. Now you will be taken to a screen that will ask you for a role. Choose Project → Editor role. Now click “Continue “ and “Done”.

→Now go to API & Services →Credentials →Download them. Now go to Service Account down below on the same page and you will see an email that you will have to click on & remember. This will take you to another page where you will have to click on “Keys” →Create New Key → JSON. This contains client email and a bunch of things to connect. Please rename this file as “service_account.json” & store this in the same place where your python script is stored. It will look like

You will automatically download a JSON file with credentials. It may look like this:

{
    "type": "service_account",
    "project_id": "api-project-XXX",
    "private_key_id": "2cd … ba4",
    "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n",
    "client_email": "473000000000-yoursisdifferent@developer.gserviceaccount.com",
    "client_id": "473 … hd.apps.googleusercontent.com",
    ...
}

→Now go to going to API &Services → Library →Type in Google Sheet API and Enable it

Create a Project

Step 2 →Connecting to the Sheet

My Sheet has 2 columns “Name” and “Expenditure”. Click On “Share” and add your client email( the long email address ) from the Service Account page.

Step 3 →Scripting

Let us say we have a task to consolidate the contents of each sheet & retrieve all the information programmatically.

To make this a little more interesting, let us a condition to check if” Azzy” and “Anny” exist and append those to our data frame if they don't already exist.

import gspread
array = ["Sheet1", "Sheet2"]
final = []
finaldict = {"Name":[],"Expenditure":[]}
cred = "service_account.json"
url = "https://docs.google.com/spreadsheets/d/1qrZsfjfdO1u5kO3pTFIMm6CQ1uTR7pKReRakkghfZ-Q/edit#gid=0"

class API:  
  final = []
 
  def __init__(self):
        #name = 'Fruitas'
        # Initialization of the Strings
        self.sa = gspread.service_account(filename = cred)
        self.spreadsheet = sa.open_by_url(url)
      
 
  def printlist():
       # print('The name is:', cls.name)
        counter = 0
        while counter < len(array):
           wks = spreadsheet.worksheet(array[counter])
           all_values = wks.get_all_values()[1:]
           #all_values = wks.get_all_values()
           final.append(all_values)
           counter += 1
        tf= "Azzy" in final
        if tf == False:
          rows = [["Azzy", 20], ["Anny", 60]]
          final.append(rows)
        #return final
  
 
  def transform_final_list_to_df():
    df = pd.DataFrame(get_value_appended()).T
    df = pd.concat([df[0],df[1],df[2]], ignore_index=True)
    df = df.dropna()
    df = pd.DataFrame(df, columns =['Name'])
    df = df.Name.apply(pd.Series)
    df.columns =['Name', 'Expenditure']
    df['Name'] = df['Name'].str.replace(" ","")
    print(df)


API.transform_final_list_to_df()

Here is an extract of all the amazing things you can do with this package:

1. Retrieve all records from a worksheet: I wanted to retrieve cell values as a list of lists → so I have used get_all_values in the code. You can also use get_all_records to fetch values from a worksheet as a list of dictionaries.
2. Opening the sheet Opening a Spreadsheet can be done by the name of the file (sa.open(‘Name of the workbook’) ) or by the key(sa.open_by_key(‘0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE’)) or by url (sa.open_by_url(‘https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl'))
3. Adding a new worksheet → sh.add_worksheet(title=”A worksheet”, rows=100, cols=20)
4. Searching for a value Abby→cell = wks.find(“Abby”) & then print((cell.row, cell.col))
5. Searching by position → wks.sheet(1,2).value

Conclusion

Gspread is an excellent and super easy-to-use library to access Google spreadsheets. Hopefully, the examples and steps shown above will help you build your apps using Google spreadsheet.

References

1. Google Sheets API has detailed documentation that explains the use of its REST endpoints on this page.

2. Gspread package

More content at PlainEnglish.io.

Sign up for our free weekly newsletter. Follow us on Twitter, LinkedIn, YouTube, and Discord.

Interested in scaling your software startup? Check out Circuit.