How do you access google sheets from python?

Keep using Python for managing online spreadsheet content

If you clicked on this article you’ve probably used Python to manage Excel file content. As a well-spread tool, Excel is a very good option when using spreadsheets. Even though Microsoft has launched OneDrive, many people still use Excel locally and Google Drive’s option, Google Sheets, for the Cloud.

Either way, the most important benefit when using a Cloud service is to move away from local files. They demand local storage, they’re difficult to share with friends, and version control is a nightmare.

In this article, I’m going to walk you through the steps needed in order for you to be able to access your Google Sheets spreadsheets and manage their content with Python.

You may be aware that Google itself offers a native option of the Google Sheets scripts editor, Spreadsheet Service, as part of the Google Apps Script. There are some methods and attributes that are very useful indeed. It also allows you to manage your project execution history and other relevant information through Google’s platform.

However, as a Python enthusiast, you’re probably aware of how easy this language is. Let’s list the benefits:

• You already use Python for other existing applications — it takes time to learn the syntax and explore the methods and attributes of a new language.
• Python has big, active communities and extensive libraries. So, as long as you can connect to this new source of data (Google Sheets), you can keep doing a lot of things.

One more good piece of news: From now on, you can check the steps needed in order to set the required access. The rest, you can keep as usual.

1. Enabling External Access to Google Sheets

In this section, we’re going to cover the steps needed in order to grant access from an external application to Google Sheets. The main goal here is to assign your Google’s project credentials to both Google Sheets and Google Drive APIs.

This is the process:

1. Set up your project on the Google Cloud Platform.
2. Create your project credentials.
3. Enable both Google Sheets API and Google Drive API.
4. Assign your project credentials to both Google Sheets API and Google Drive API.

Let’s take a look at each of them.

1.1. Set up your project on the Google Cloud Platform

After logging in to Google Cloud Platform, if this is your very first time on the platform, you’ll see the following page on the Home section:

By clicking “CREATE PROJECT”, you set up your project main information:

OK, your project has been created.

1.2. Create your project credentials

Now you need to search for “APIs & Services”. Then, click on “CREATE CREDENTIALS” and then on “Service account” option:

Next, you need to enter some basic information:

After clicking on “CREATE”, under the “Project” option, click on the “Owner” option:

At the bottom of the page, click on your Service Account email link:

Then, click on the “ADD KEY” option and “Create new key”:

Choose the JSON option:

Now you’ve downloaded your JSON project’s credentials file.

1.3. Enable both Google Sheets API and Google Drive API

You need to search for both “Google Sheets API” and “Google Drive API” (the order isn’t important).

As an example, take a look at the “Google Sheets API” page. Here, you have to click on the “ENABLE” button (make sure you also do this for the Google Drive API):

Both APIs have been enabled.

1.4. Assign your project credentials to both Google Sheets API and Google Drive API

After enabling the APIs, click on “CREATE CREDENTIALS” on the top right page of both of them (again, I printed only the Google Sheets API, below):

You’re going to be redirected to the following two screens (for each of the APIs):

After selecting the options shown, the Google Cloud Platform will identify your already-created project’s credentials and use it for the APIs:

Click “DONE” and now both APIs have been properly identified.

2. Python Script

So far, you’ve enabled for external applications to connect to Google Sheets through both Google’s APIs. Now, let’s take a look at what you need to consider in your Python script:

1. Requirements and dependencies
2. Authentication and Authorization
3. Application script

2.1. Requirements and dependencies

You might think this is the most boring part of the entire flow. You’re wrong.

Let’s be straightforward here. You need to fulfill only one requirement, which is to import two libraries: gspread and oauth2client. Ensure you have both of them locally downloaded and then this would be the first part of your Python script:

What about the dependencies? Remember the JSON file you have downloaded when creating your project’s credentials? This is one of the dependencies. It mainly covers your private key, which is needed for the authentication process.

The other one is just to share access to your Google Sheets spreadsheet with your Service Account email, as you would do with a friend, for instance. You can consult it either on your Credentials session within Google Cloud Platform or on the JSON file (“client_email”), mentioned in the last paragraph.

2.2. Authentication and authorization

So, if you have the JSON file in the same directory you’re going to execute your .py file, you can consider the following:

If, for some reason, you prefer to save your JSON file in a different directory, just make sure you indicate the full path towards it.

That’s all for the authentication and authorization session.

2.3. Application script

What I’m calling “Application script” is what you do with your data on Google Sheets.

Here’s a simple example, for us to check if you’re able to both import data Google Sheets and read data from it:

Checking:

Now you’re all set to keep using Python when managing online content on Google Sheets.

Does Google Sheets work with Python?

Can you edit Google Sheets with Python?

How do I read a Google Sheet in Pandas?

How do you access Google Sheets?