How to manage Google Sheets with Python and gspread.

7 min readOct 14, 2020

Whether you have a gazillion spreadsheets, you’re tired of manually collecting information from, or want to write a script to do some of your tasks for you. I will walk you through how to automate and manage your Google Sheets with Python.

I made this very detailed, so almost anyone can do it. Feel free to go as fast or slow as you want to.

My implementation of this is just an example, and since this is in pure Python, the possibilities are limitless. Let’s get started!

Getting setup.

We will need to get the Google Sheets API and the gspread library before starting coding, so let’s do that first.

First, let's get the Google Sheets API.

Creating a project.

First, go to the Google Developer Console, and create a new project by clicking “Create Project.”

2. Give that project a name and click Create. Now, your page should refresh, and in the top left, you’ll see the name of your project and a new button: Enable APIs and Services. Click that.

3. Search for Google Sheets in the text box to find the API we need, click on Google Sheets API, and click Enable.

Creating the “account”.

4. Having that enabled, we need to create a service account that will make that changes we request in code onto an actual Google Sheet. Do this by going to Credentials on the left side, clicking Create Credentials, then Service Account.

5. Complete all required fields with whatever name you please

6. VERY IMPORTANT: make sure on the next screen you select this Account’s Role as Project > Editor. Continue and save your new account.

Getting the key.

7. You should be taken back to the credentials screen and see a new account under Service Accounts. Click on the pencil icon at the far right of that new account bar to edit the new account.

8. Under Keys, click Add Key > Create New Key.

9. Select JSON and click Create. A popup window will appear to save this file. Save it to your project directory/folder.

10. Now open that JSON file you just saved in any text editor (Atom, VS Code, etc.) and look for the line that starts with client_email, and copy the email that follows — in my case, this is medium-testing@medium-test-project-290416.iam.gserviceaccount.com

Finally, go to all of the Google Sheets that you will want to manage with this script and Share them with that email and give editor permissions to that email.
You can do this at a later stage if you’d like also.

Installing gspread and oauth2client.

Gspread is a Python library developed to interact with that API we just setup. Some great people have done the hard work for us. The other, oauth2client, is a package that communicates with gspread to get into the spreadsheets we need.

First, you’ve gotta have Python installed. If you don’t, do that.

Next, make sure you have pip installed. You can test this by running this command in Terminal:

pip3 --version

If you get a version number spit back to you, you’re good to go. If not, run the following two commands to install pip.

curl https://bootstrap.pypa.io/get-pip.py -o get-pip.pypython3 get-pip.py

Now, let's get gspread and oauth2client. Run the following command in Terminal to install the library.

pip3 install gspread
pip3 install --upgrade oauth2client

Alright, done with that!

Setting up our Python file.

Now comes the fun part. Let’s get our Python file setup.

Create a new file, we’ll call this spreadsheet.py. Make sure you create this in the same folder as the client_secret.json file we downloaded earlier. Now open it in your favorite text editor.

Include the following import statements at the top so we can actually use the gspread and oauth2client libraries we installed previously:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient import discovery

Next, we need to set up a block of code to link to that account we made in the Google API so we can access and change some spreadsheets.

Let’s add that now:

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']credentials = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)client = gspread.authorize(credentials)

The combination of these three variables allows us to use that Service Account to control the sheets we have shared with it through that client variable.

Get to coding.

Now you are all set up to do some really cool automation with your spreadsheets.

The most common commands you’ll use are:

Opening a spreadsheet
Accessing a worksheet (those tabs along the bottom)
Updating a cell

sh = client.open(%SPREADSHEET_NAME%)
worksheet1 = sh.sheet1
worksheet1.update_cell(ROW, COLUMN, %VALUE%)

TIP: In my use case, I wanted to do the same actions across many spreadsheets, so I opened all of the spreadsheets I needed and placed them into an array — then, by iterating through that array I could get all of the information I needed.

Using Python, you can do some more complex computations, write logic around cell contents, and use the Python string methods to view specific text content.

You can view the official gspread documentation here, for all that you might want to do with your spreadsheets!

ONE TRICK:
In trying to have my script process over 20 spreadsheets and loop through many different read and write methods to those spreadsheets, the API would time out, throw an error, and stop the program. To fix this, include this line of code:

time.sleep(100)

This will make your program stop for 100s before continuing. I included this after I opened all of my spreadsheets and at the start of my for loop to iterate throught that array, so that after executing all actions on one spreadsheet it would take a break. This allowed me to run the program and come back later once it was done.

Adding console.log lines to provide an update where your script is at in console was also useful for me.

BONUS: Creating a new sheet using Python.

This was THE HARDEST thing for me to figure out when I was doing this so here’s what to do.

First, you’ll want to create a folder on Google Drive to store this spreadsheet you want to make. Do that and go into that folder. Then share that folder with the email in your client_secret.json.

Looking at the address (URL) bar, you should see something like this:

https://drive.google.com/drive/u/0/folders/%DESTINATION_FOLDER_ID%

In place of %DESTINATION_FOLDER_ID% will be a set of letters and numbers, copy that and store it to a new variable called destFolderId. I did this up near the client variable.

Next, we need to create two variables, drive_service and body as shown below:

drive_service = discovery.build('drive', 'v3', credentials=credentials)body = {
    'name': %SPREADSHEET_TITLE%,
    'mimeType': 'application/vnd.google-apps.spreadsheet',
    'parents': [destFolderId]
}

Place your desired spreadsheet title in place of %SPREADSHEET_TITLE%.

The drive_service will allow us to build the new sheet using the credentials we have already established, and the body variable will provide the build method with the information it needs about where to put it and the name to give it. See that the body variable uses that destFolderId variable we just created.

Then, with the following lines, we can create a new spreadsheet and open it into a variable to everything we might need.

new_sheet = drive_service.files().create(body=body).execute()
sheet_id = new_sheet['id']
sh = client.open_by_id(sheet_id)

Then you are free to open the first worksheet and write to that newly created sheet, all using Python!

There is a sample project on my Github to see this all together.

I enjoyed learning how to do all this in my time at North Point Partners and hope the labor of my meticulous documentation analysis and Stack Overflow searching has made this super easy for someone else to have everything in one place.

Thanks for reading and have fun!

Calvin