How do i read a google spreadsheet in pandas?

How to read, update, and create a Google spreadsheet and build a DataFrame pandas.

Index

1. Create a project in Google Developers.
2. Active API Google Sheets and Google Drive.
3. Keys for authenticating: Local or Service
4. Create Spreadsheet.
5. Script Python + Pandas.

Create a project in Google Developers

We need to create a project in order to use the Google Sheets API resources.

Nothing works without that project.

Link: https://console.developers.google.com/project

1. Create a project.

2. Set a name for the project.

Google Sheets API

Now we start API to interchange sheets with the project, then a type of API is chosen: public or a private one.

1. In the search bar find the "Google Sheets API".

2. Set an "Enable" option.

3. Come back to the main page and choose "OAuth consent screen".

4. We need to define what type of API is chosen; the public one allows any person to access your API, the private one is accessed only by the user who has the code.

In this example, the public API has been chosen.

5. After selecting a type, a name for the API has to be set.
There are other options; for this example it is not necessary to set it.

6. Enable the Google Drive API.

Up to now we have:

* Created a project in Google Developers.

* Activated API Sheets and defined a type of API.

We have created the base of the project.

Keys

Authenticating — We need to generate keys to access the spreadsheets. There are two kinds of keys.

1 — OAuth client ID:

Requires the user’s consent so the app can only access the user’s data.

Recommended for authenticating and developing a local application.

2 — Service account:

Enables a server-to-server function, that is an app-level authentication using the robot accounts.

Recommended for automatic authentication, used generally in production applications (authentication is no longer local). We have created a specific access key for these cases.

Creating a local key

1. In Credentials, choose "+ Create Credentials" and OAuth client Id.

2. Select an option of the device that will be authenticated, in our case: Desktop app.

3. Download the key.

Creating a remote key

1. In Credentials choose “+ Create Credentials” and service account.

2. Set a name for your key.

The rest of the steps are not compulsory, so we can miss it out.

3. Successfully created key.

Now to generate .json file, click on the name of the key, and we reach the Keys section.

1. Make sure to use the right key you have selected and create a new key.

2. Choose the type of file, .json.

3. The key will be saved on your computer.

4. Save the generated e-mail in the key, so that it can be accessed in the spreadsheet.

Sheets

There are no mistery here — we have created spreadsheets so we need to pay attention to two points:

To the Spreadsheet key:
This key identifies the code, it is necessary for the operation of the code. This function is necessary to identify the operation of the code.

Access the remote email key:
You must grant access to the email key by using the remote key.

Python

To create a Python code, you can follow these three examples to realize how the whole process works. Be sure to have the authentication keys in your project folder.

To execute these examples, it is necessary to install the following libs:

!pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib!pip install gspread!pip install pandas

1st example — Read the spreadsheet local key.
Based in google code, we adapted to return one the pandas dataframe.
For more details, see the Google code.

For the first time authentification, the Google service will create a token.pickle file, which will not be required the second time.

2nd example — Read the spreadsheet remote key.
This example needs a service key. The reading function is rewritten just to facilitate the authentication and the understanding of the process.

From this point on the token pickle file will no longer be required.
See how the code is easily recognized.

3rd example — Read, updated and create spreadsheet (Service key).

By now we can already understand how the authentication models (location and service) work.

In this script, the code is abstracted in four functions:
Authentication — Authentification can be created with APIs.
Reading — A specific spreadsheet can be read.
Update — A specific tab has been updated. It is necessary that this tab is already in use.
Creates and inserts the data — Anew spreadsheet has been created and the DataFrame inserted. It is important to share an email which will access the new spreadsheet. As a service email is authenticated, the email must not be shared. This is the way to access the spreadsheet.

Thanks for reading this article.

The author is available to receive feedbacks.

Follow him on LikedIn or Github.

How do I read a Google Sheet in Python?

How do I read a Google Sheet in Jupyter notebook?

How do I connect Google Sheets to Python?

How do I read data in Google Sheets?