Python and Google Sheets - Getting started with gspread

Google Sheets

Google Sheets is Google's version of Microsoft Excel. The fundamental difference is that Google Sheets is only accessed in your browser. Excel has both a desktop version, and a Browser based version.

Google Sheets is used by 160-180 million users compared to Excel which has 1.100-1500 million users. For once, Google is the little brother regarding spreadsheets. So why do people use Sheets instead of big daddy Excel?

The perks of Google Sheets

• Free for personal use
• Very easy to use in collaborating with others
• Very secure

What is gspread and why use it

Gspread is a Python API for Google Sheets.

Google already has an API for Google sheets, so why use Gspread? Python users who need to connect to Google Sheets should use Gspread because they crafted it explicitly for that purpose. Gspread is based on the Google Sheets API and gives a much easier toolset for quickly interacting with Google Sheets.

Getting started with gspread

Requirements

• A Google account
• Python 3 installed
• Able to install new Python modules with pip

There are two ways that you can authenticate to Google Sheets.You can setup a Project and give gspread access to your entire account with OAuth Client ID, or you can set up a Bot using a Service Account. 

Creating a Service account means exactly that, it is a separate account and you will have to share all of your spreadsheets that you want to edit to this account. 

We will walk through the setup of both methods in this article.

TLDR;

• Use a service account for safer sharing and access to specific spreadsheets you share to it
• Use OAuth Client ID for accessing all of your own spreadsheets

Setup a Project

Go to Google Cloud Console and create a new project

Give the Project a name, for example Gspread-OAuth Client ID and optionally add it to an Organization, but this is not needed.

Enable API access

Go to APIs and services in your newly created projects dashboard.

Click on the + button "ENABLE APIS AND SERVICES"

Search for "Google Drive API" and enable it, and after that search for and enable "Google Sheets API"

OAuth Client ID for accessing all of your own spreadsheets

If you'd rather setup a Service account, go to Service Account.

OAuth Consent Screen

Navigate to the APIs and services menu on the left and click on "OAuth consent screen". Create an External user type.

Name the app, for example Gspread-OAuth Client ID and enter your Gmail address in the "User support email" field and in the "Developer contact information field". Click on "Save and continue" and do the same on the Scopes tab. Add your Gmail email again to the Test user field and then save and continue. Review your information in the summary and then click on "Back to dashboard". 

Create and download credentials

Navigate to “APIs & Services > Credentials” and then click on "Create credentials" and choose "OAuth client ID". Select "Desktop app" as the Application type and name it, for "example Desktop client 1". Create the credentials.

Download the JSON file and rename it to "credentials.json". Create a folder named gspread in your %APPDATA% and move the file to the folder. %APPDATA% is a variable used in Windows that goes to your accounts appdata folder. For example, on my computer it is C:\Users\Conny\AppData\Roaming. 

If you are using other operating system than Windows, put it in the folder ~/.config/ the full path would then be ~/.config/gspread/credentials.json.

All done! You can now go the Using gspread section if you do not need to create a Service Account. 

Service Account for shared spreadsheets

A service account is a separate bot account that you have to share sheets to in order for the bot to get access to your sheets that you need to edit. 

Creating a Service Account

Navigate to “APIs & Services > Credentials” and click on “Create credentials > Service account”.

Fill in the details of the service account and click on "Done"

Adding a key to your Service Account

Go back to your newly created Service Account and click on the "Keys" tab and click on "Add key" and "Create new key".

Create a key of type JSON. It will download the key to your computer.

Adding the credentials to your computer

Rename the downloaded key to "service_account.json". Create a folder named gspread in your %APPDATA% and move the file to the folder. %APPDATA% is a variable used in Windows that goes to your accounts appdata folder. For example, on my computer it is C:\Users\Conny\AppData\Roaming. 

If you are using other operating system than Windows, put it in the folder ~/.config/ the full path would then be ~/.config/gspread/service_account.json.

Sharing spreadsheets to the Service Account

Create a new Google Sheet and give it the name "Gspread Service Account test". Enter "Hello from cell A1" in Cell A1. Share it with the Service Accounts email. You can find the email from the downloaded key file under "client_email". We will open this spreadsheet with the bot later.

All set! Let's move on to trying out gspread.

Using gspread

Install gspread

Open up the Command Prompt or your terminal and write pip install gspread

Word of caution

Be extremely cautious not to disclose the authorized credentials and credentials file to anyone. If anyone gets access to these files, they can access and edit (including deleting!) your content on Google Drive.

Authenticating

The first time that you run the script, a browser window will open and prompt you to sign in with your google account. The config directory is where gspread stores authorized credentials, along with credentials.json. You only need to authorize the browser once, and it will remember your credentials.

Opening spreadsheet with gspread OAuth Client ID

First we import gspread, and then we authenticate with the oauth() method. We then open the spreadsheet and print the contents of cell A1 on the first sheet.

import gspread

gc = gspread.oauth()

sh = gc.open("Example spreadsheet,")

print(sh.sheet1.get('A1'))

Creating spreadsheet with gspread OAuth Client ID

Creating a spreadsheet is like opening a spreadsheet. We just need to change the open() to create() instead.

import gspread

gc = gspread.oauth()

sh = gc.create("My new spreadsheet")

print(sh.sheet1.get("A1"))

Opening spreadsheet with gspread Service Account

Instead of using oauth() we now use service_account() for authenticating. Remember the spreadsheet we created earlier? When we run this code, we get the output [['Hello from cell A1']].

import gspread

gc = gspread.service_account()

sh = gc.open("Gspread Service Account test")

print(sh.sheet1.get('A1'))

Creating a spreadsheet with gspread Service Account

When we create a spreadsheet with the service account, we need to share the spreadsheet to our account. Remember that the Service Account is a separate account. Just as you would share the spreadsheet with another person, you have to do this as well with gspread using the share() method.

import gspread

gc = gspread.service_account()

sh = gc.create("Example spreadsheet Service Account")

sh.share("your_account@gmail.com", perm_type="user", role="writer")

print(sh.sheet1.get("A1"))

After sharing to your email, you will get a notice.

Storing the credentials in your script folder

To specify an alternate location for the credentials file, change the path to credentials.json and authorized_user.json in the oauth() method.

import gspread

gc = gspread.oauth(
    credentials_filename="credentials.json",
    authorized_user_filename="authorized_user.json",
)

sh = gc.create("Spreadsheet")

print(sh.sheet1.get("A1"))

Sources: https://docs.gspread.org/en/latest/oauth2.html

Code is available on my GitHub