Google Sheets is a web-based spreadsheet application provided by Google, allowing users to create, edit, and share spreadsheets online. Using the Google Sheets API through Python enables programmatically interacting with Google Sheets, automating data manipulation tasks, importing/exporting data, and integrating Sheets with other applications and workflows, enhancing productivity and enabling seamless data management and analysis.
To get started there are some permissions we need to set up , in this guide we will set up and example project to get us started. To follow along with this guide please navigate to https://console.cloud.google.com/ in your browser and select CREATE PROJECT as shown below.
A new tab will open prompting you to give your project an appropriate project name.
After Project creation, you should see view like in the image below. Please click ENABLE APIS AND SERVICES button to add access to selected APIs.
As we progress further, we'll need to ensure that our project is granted access to the required APIs. Specifically, we'll need to enable access to the Google Sheets API and Google Drive API, allowing our project to interact with these APIs. First search for 'Google Drive' :
Click enable when the dialogue box below shows up.
Complete the steps above for Google Sheets API also. Then click on the CREATE CREDENTIALS button shown below.
Choose the service account option from the dropdown menu.
Select Owner role when prompted.
Create a new key which will allow us to interact with Google Sheets through Python.
Select JSON.
Save the credentials as credentials.json and save them in the working directory of the project on your computer.
{
"type": "service_account",
"project_id": "",
"private_key_id": "",
"private_key": "",
"client_email": "",
"client_id": "",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": ""
}
OK now we are able to interact with Google sheets through Python. In the next article we will show how to change and retrieve data from an example worksheet.
Install the following packages to follow along with the next articles in this series.
- Open your command line or terminal.
- Make sure you have Python and pip (Python package installer) installed on your system. You can check by running the following commands:
python --version
pip --version
If Python is not installed, you can download and install it from the official Python website (https://www.python.org/downloads/). Pip is usually installed along with Python.
3. Once you have confirmed that Python and pip are installed, you can install gspread
and oauth2client
by running the following command:
pip install gspread oauth2client
This command will download and install both libraries and their dependencies.
4. Wait for the installation to complete. You should see a message indicating that the installation was successful. After following these steps, you should have both the gspread
and oauth2client
libraries installed and ready to use in your Python environment.