Can python read google sheets?

For many data science/visualization projects I work on, setting up a SQL database (or something similar) is overkill. On the opposite side of the spectrum, using local Excel files makes things more difficult to share and replicate. Google Sheets are often an excellent middle-ground, providing an easy-to-use collaborative platform with a familiar Excel-like interface.

Accessing Google sheet data using OAuth and the Google Python API is a straightforward process, thanks to the (per usual) excellent Google documentation. First, we need to setup OAuth credentials on our Google Drive account in order to access the worksheet.

Next, we need to install the Google API client libraries for Python. We can do this in an (ideally, in an activated Python virtual environment) using pip.

Obviously, in a tutorial about accessing Google sheet data, you’re going to need a sheet to work with — I’ll be using my “Volcanic Wines” sheet (based on the awesome volcanic activity data available from the Smithsonian). For the next steps, you’re going to need the sheet ID, which you can get from the URL, and then the name of the worksheet.

Get the spreadsheet ID from the Google Docs URLGet the Google Sheet name of interest

Now, create a new Python script to retrieve the data (make sure the ‘client_secret.json’ file is saved in the same working directory as the script, or provide an explicit path). Update the spreadsheet ID and worksheet names in the code below with the relevant values for your spreadsheet.

Final Python code for accessing Google sheet data and converting to Pandas dataframe

Run the script, and you should get your sheet data returned as a dataframe — stay-tuned for an upcoming set of tutorials that will walk through the creation and deployment of a Plotly Dash web app using this Volcanic Wine data!

Final Pandas dataframe returned from Google Sheet

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

Image via Shutterstock under license to Frank Andrade

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”

Image by author

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

Image by author

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.

Image by author

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

Image by author

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”

Image by author

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

Image by author

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

Image by author

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)

Image by author

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

Image by author

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.”

Image by author

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”

Image by author

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

Can Python access Google Sheets?

How do I send data from Google Sheets to Python?

How do I automate a Google Sheet in Python?

Can you edit Google Sheets with Python?