Can i automate google sheets with python?

A guide to creating spreadsheets and saving data to Google Sheets with Python.

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

Google Sheets is a cloud-based spreadsheet application that can store data in a structured way just like most database management systems. You can also edit and delete data with a couple of clicks and, on top of that, access your data online.

Although Google Sheets has limitations such as the numbers of cells you can use (up to 10 million cells) and API calls you can make per minute, it’s still a good option that you can get for free.

In this guide, I’ll show you how to use Google Sheets with Python. We’ll learn how to create a spreadsheet and save data to it using Python, so you can turn Google Sheets into your own database.

Table of Contents
1. Enable APIs and download credentials
2. Turning Google Sheets into a Database with Python
 - Connect to Google Sheets
 - Create a blank spreadsheet
 - Sharing a spreadsheet
 - Open the spreadsheet and upload data
3. Bonus: Open Any Google Sheet File with Python

If you don’t feel like reading, you can watch my video instead!

1. Enable Google Drive and Google Sheet API and download credentials

To connect Google Sheets with Python, we have to download our credentials in a JSON file. To do so, we have to start a new project.

Create a new project

To create a new project, go to this website and follow the steps below (you need to be already logged into your Google account).

1. Select a Project
2. Click on “New Project”
3. A new page will load. There you have to write the name of your new project and click on “Create”

4. Once you create your project, you’ll see the image below

Click on “Select Project.” You should see now a new page.

Enable Google Drive API for the new project, create credentials, and download it

After selecting the project, go to the left panel and click on “APIs & Services” and then select “Library” as shown below.

You should see the page below now. There we have to search Google Drive API and enable it.

Once it’s enabled, we have to click on “Create Credentials” on the upper-right corner.

After this, we have to select the options below and click on “Next”

Then we give a name to our service account and click on “Create and Continue”

Now we have to select the role. In this case, we select “Editor” from the “Project” option and then click on “Done.”

After this, we have to open the left panel again click on “APIs & Services” and now select select “Credentials”

At the bottom of the page, you’ll see a “Service Account” section. Here we have to click on our client email (keep in mind that all the files we’ll create in the section will be saved in this client email)

Then we have to go to the “Keys” section and click on “Create new key”

Then click on “Create” and a JSON file with your credentials will be downloaded to your computer. I’ll rename this file as “gs_credentials.json” You have to move this JSON file to the same folder where your Python script is located.

Finally, we have to search Google Sheets API and enable it as we previously did for Google Drive API.

2. Turning Google Sheets into a Database with Python

Connect to Google Sheets

To work with Google Sheets in Python, first, we have to install gspread and oauth2client. In addition to that, we’ll use Pandas to read our local data and update it to Google Sheets.

pip install gspread
pip install oauth2client
pip install pandas

Now let’s import the libraries and connect to Google Sheets.

In the code above, we enable access to specific links in the scope list, then in the credentials variable we write the name of the JSON file we downloaded before.

Finally, we create a client variable.

Create a blank spreadsheet

Let’s create a new spreadsheet named “NewDatabase”

To do so, we have to use the .create method of the client variable we defined before.

sheet = client.create("NewDatabase")

Note that this new sheet is only visible to the service account we created before. To access this sheet with our own Google account, we must share it with our email

Sharing a spreadsheet

Let’s share this sheet with our own Google account. To do so, run the code below.

sheet.share('your_email_goes_here', perm_type='user', role='writer')

After running the code above, the sheet will be in the “Shared with me” section in your Google Drive.

Open the spreadsheet and upload data

Now it’s time to open the sheet and upload data to it. To do so, we use the .open method and select sheet1.

Then we read any CSV file we have with Pandas. In this case, I’ll use a CSV file that you can find Google Drive or my Github, but feel free to use any CSV file you want.

Finally, we upload the data to our sheet using the .update method.

# Open the spreadsheet
sheet = client.open("NewDatabase").sheet1# read csv with pandas
df = pd.read_csv('football_news')# export df to a sheet
sheet.update([df.columns.values.tolist()] + df.values.tolist())

That’s it! Now your sheet should have the same data there’s in the CSV file. You can find all the code written in this guide on my Github.

Bonus: Open Any Google Sheet File with Python

Now it’s time to open any Google Sheet file (even those that were not created by our client email).

To do so, open any Google Sheet file, go to the upper-right corner and click on “Share.”

A new window will pop up. Insert your client email that we got before (which is also in the JSON file we downloaded) and click on “Send”

Great! Now that we have permission, you can open this using the same code we wrote before.

How does Google Sheets work with Python?

Can Google Sheets be automated?

How do I write in Google Sheets using Python?

Can Python update Google sheet?