Let’s talk about how to use Python in Google Sheets. Have you ever found yourself spending hours on minor tasks, such as copying and pasting data? If so, you know how frustrating and inconvenient it can be to use complex functions for simple tasks. This is especially true when working on larger projects, where time is of the essence.
Fortunately, there is a solution that can save you time and hassle when dealing with data. By using Python, you can automate the process of extracting and uploading data to Google Sheets. With a simple script, you can streamline these tasks and focus on more important aspects of your project.
In this article, I detail everything you need to know about using Python in Google Sheets.
Table of Contents
What is Python?
Python is a computer programming language used to create websites, develop software, automate tasks, and analyze data. Python is a general-purpose programming language, which means it can be used to create a wide variety of programs and is not focused on a specific problem.
Note that, while this article discusses Python in Google Sheets, I created another guide. Because of the recent news, I wanted to expand this to cover another area where you can use the same programming language. Here’s my guide on using Python in Excel.
How is Using Python Helpful?
Python is built with features that facilitate data analysis and visualization, which is why it is frequently used as the de facto language for data science and machine learning applications.
If a user proceeds to use Python in Google spreadsheet, they will find it simple to integrate their data with data analysis libraries like NumPy or Pandas or data visualization libraries like Matplotlib or Seaborn. Overall, using Python and Google Sheets together enables an advanced level of data handling while automating simpler tasks to increase efficiency.
Methods for Uploading Python Data into Google Sheets
There are several methods for using Python scripts with Google Sheets. In this guide, I include screenshots and specific examples to show you how to use it. Let’s start with the basics.
You can upload the data using either the Python Google API client or pip packages, such as:
- Gsheets
- Pygsheets
- Ezsheets
- Gspread
Now that we’ve covered the fundamentals of Python, let’s look at how you can use Python to read and write data in Google Sheets.
Prerequisite for Using Python with Google Sheets
To read from and write to Google Sheets using Python, we must first create a service account.
What is a Service Account?
A service account is a special type of account in the Google Cloud Platform that is designed to be used by applications, virtual machines, or other services instead of individual users. These accounts can be used to access APIs, perform automated tasks, and manage resources within a Google Cloud project.
How to Create a Service Account
Creating a service account isn’t as complicated as it may sound, simply follow the steps below:
Step 1: Go to the Google Cloud Console home page and click on the projects menu.
Step 2: From this menu, select “New Project.”
Step 3: Fill in the required fields and press the “Create” button. Once the project is created, you will be redirected to the project home page.
Step 4: From the right panel on the home page, go to “Enabled API and Services” and click on “+ Enable API and Services.”
Step 5: After clicking “Enable API and services” you will be redirected to the API library. From here, find the Google Drive API and click the “Enable” button. Repeat for the Google Sheets API.
Step 6: Next, select “Create Credentials” from the top menu on the project home page.
Step 7: From the credentials options, select “Service account.”
Step 8: Name the service account and click “Create and Continue.”
Step 9: Grant the service account with “Editor” access
Step 10: Click on “Done” at the bottom
Step 11: Next, in the services account menu, click on “Keys” and then “Add Key.”
Step 12: Select “JSON” as the key and click on “Create”
Step 13: The service account credentials will be generated and downloaded in the form of a JSON file. You will need this JSON file later (See step 7 below).
Creating a Python Environment in Google Sheets
Once you’ve set up a service account, you need to create a new directory for your Python project. Here’s how:
Step 1: Open a command terminal or the CMD application on your system. You can type in CMD in the Windows search bar on the bottom left of your screen. On macOS, you can press CMD + Space, type in “Terminal,” and then press “Return.”
Step 2: Using your system’s terminal or command line, run this command mkdir python-to-google-sheets to create a new project directory called python-to-google-sheets.
Step 3: Run the command cd python-to-google-sheets to navigate to the new project directory.
Step 4: After that, create a virtual Python environment for the project using the following command: python -m venv env.
Step 5: Once you have created the environment, you can open the python-to-google-sheets directory in a code editor. (Note: You can use any code editor you prefer, for this demonstration, we used Visual Studio Code).
Step 6: In the code editor, open the env directory and create a new file by clicking on the new file icon next to the directory name.
Step 7: Copy and paste the contents of the service account JSON file into the new file and name it “credentials.json.”
Step 8: Next, create an auth.py file and paste the following script inside it to provide this Python project with access to your Google Cloud service account:
from __future__ import print_function
from googleapiclient.discovery import build
from google.oauth2 import service_account
SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES)
spreadsheet_service = build('sheets', 'v4', credentials=credentials)
drive_service = build('drive', 'v3', credentials=credentials)
Activating the Environment
Once, you have set up your environment, you need to activate it before you can start using it. For macOS and Linux systems, you can run the following command in the terminal or CMD application: Source env/bin/activate
If you are using a Windows device, open the CMD application and follow the steps below to activate your environment:
Step 1: Run the following command cd path-to-env. In this case, the command is: cd python-to-google-sheets/env/Scripts
Step 2: Once in the Scripts directory, run the following command on Windows: activate.bat
NOTE: Do not close the terminal or CMD application, as you will need it to run the Python scripts explained below.
Installing Google Client Libraries
After activating your environment, you need to install the required Google client libraries into it.
Step 1: In the env directory, create a requirement.txt file and add the following dependencies to it.
- google-api-python-client==1.7.9
- google-auth-httplib2==0.0.3
- google-auth-oauthlib==0.4.0
Step 2: In the terminal or CMD application, open the env directory in your project and run the following command to install the packages: pip install -r requirements.txt
Creating a Spreadsheet in Google Sheets Using Python
Once, you have activated your Python environment, it is time to start using it.
Step 1: Create a create-sheets.py file and add the following script to it:
from __future__ import print_function
from auth import spreadsheet_service
from auth import drive_service
def create():
spreadsheet_details = {
'properties': {
'title': 'New Test Sheet'
}
}
sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details,
fields='spreadsheetId').execute()
sheetId = sheet.get('spreadsheetId')
print('Spreadsheet ID: {0}'.format(sheetId))
permission1 = {
'type': 'user',
'role': 'writer',
'emailAddress': 'your_email'
}
drive_service.permissions().create(fileId=sheetId, body=permission1).execute()
return sheetId
create()
This script creates a new sheet called “New Test Sheet.” You can update the sheet title and email address objects according to your preferences.
Step 2: Open the terminal or CMD application that is currently running your environment, and navigate to the env directory. From here, run the following command: create-sheets.py
Step 3: Go to sheets.google.com and look for the new sheet.
Append a List to Google Sheets with Python
Now that you have created a new sheet, you can now easily write data to it. The following code will help you manually append a list into a single row in a Google Sheet.
Step 1: In the env directory, create a new file called update-sheets.py
Step 2: Paste the script provided below inside the new file. This script will update a single row A5:H5 with the data specified in the script.
NOTE: Remember to replace the existing values with relevant data and the ‘your_spreadsheetID’ object with the spreadsheet id you retrieved when running the create-sheets.py command in the terminal or CMD application.
from __future__ import print_function
from auth import spreadsheet_service
from auth import drive_service
def read_range():
range_name = 'Sheet1!A1:H10' # retrieve data from existing sheet
spreadsheet_id = 'your_spreadsheetID'
result = spreadsheet_service.spreadsheets().values().get(
spreadsheetId=spreadsheet_id, range=range_name).execute()
rows = result.get('values', [])
print('{0} rows retrieved.'.format(len(rows)))
print('{0} rows retrieved.'.format(rows))
return rows
def write_range():
spreadsheet_id = 'your_spreadsheetID' # get the ID of the existing sheet
range_name = 'Sheet1!A2:H2' # the range to update in the existing sheet
values = [[Ben, Stiller, 50, 'Male', 'New Jersey', 'USA', '98989898989', 'j11292@example.com']] # new row of data
value_input_option = 'USER_ENTERED'
body = {
'values': values
}
result = spreadsheet_service.spreadsheets().values().update(
spreadsheetId=spreadsheet_id, range=range_name,
valueInputOption=value_input_option, body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))
if __name__ == '__main__':
write_range()
read_range()
Step 3: From the terminal or CMD application, run the following command: update-sheets.py
Step 4: View the changes in the New Test Sheet.
Append Multiple Lists to Google Sheets with Python
Once you’ve updated the first row of your Google sheet, you can use the script provided below to manually append multiple lists to different rows in your Google sheet.
Step 1: Create a new file called update-multi.py in the env directory.
Step 2: Paste the following script into it:
from __future__ import print_function
from auth import spreadsheet_service
from auth import drive_service
def read_range():
range_name = 'Sheet1!A1:H8' # read an empty row for new data
spreadsheet_id = 'your_spreadsheetID'
result = spreadsheet_service.spreadsheets().values().get(
spreadsheetId=spreadsheet_id, range=range_name).execute()
rows = result.get('values', [])
print('{0} rows retrieved.'.format(len(rows)))
print('{0} rows retrieved.'.format(rows))
return rows
def write_range():
spreadsheet_id = 'your_spreadhseetID' # get the ID of the existing sheet
range_name = 'Sheet1!A1:H3' # update the range for three rows
values = [
['John', 'John', '20', 'Male', 'New Jersey', 'USA', '98989898989', 'j11292@example.com'], # new row of data
['Jane', 'Doe', '30', 'Female', 'California', 'USA', '1234567890', 'jane.doe@example.com'], # new row of data
['Bob', 'Smith', '25', 'Male', 'Texas', 'USA', '5555555555', 'bob.smith@example.com'], # new row of data
]
value_input_option = 'USER_ENTERED'
body = {
'values': values
}
result = spreadsheet_service.spreadsheets().values().update(
spreadsheetId=spreadsheet_id, range=range_name,
valueInputOption=value_input_option, body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))
if __name__ == '__main__':
write_range()
read_range()
Step 3: Run the command update-multi.py from the terminal or CMD application
Step 4: View the changes in the New Test Sheet
Transferring Data Between Two Google Sheets Using Python
This method really demonstrates the power of using Python with Google Sheets. It can handle up to ten million cells and transfer data from one sheet to another within seconds. Essentially, the code below reads data from sheet 1 and writes it into sheet 2.
Step 1: Go to an existing sheet from which you want to transfer data into your New Test Sheet and retrieve the sheet ID. In this case, we will transfer data from an existing Contacts sheet into the New Test Sheet.
Step 2: In the env directory, create a new file called transfer.py
Step 3: Paste the following script into it.
from __future__ import print_function
from auth import spreadsheet_service
from auth import drive_service
def read_range():
range_name = 'Sheet1!A1:H100' # read an empty row for new data
spreadsheet_id = 'spreadsheet id of the sheet to read from (In this case, the “Contacts” sheet)'
result = spreadsheet_service.spreadsheets().values().get(
spreadsheetId=spreadsheet_id, range=range_name).execute()
rows = result.get('values', [])
print('{0} rows retrieved.'.format(len(rows)))
print('{0} rows retrieved.'.format(rows))
return rows
def write_range(rows):
spreadsheet_id = 'spreadsheet id of the sheet to write into (In this case, the “New Test Sheet” sheet)' # get the ID of the existing sheet
range_name = 'Sheet1!A1:H100' # update the range in the existing sheet
values = rows # use the rows retrieved from the read_range function
value_input_option = 'USER_ENTERED'
body = {
'values': values
}
result = spreadsheet_service.spreadsheets().values().update(
spreadsheetId=spreadsheet_id, range=range_name,
valueInputOption=value_input_option, body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))
if __name__ == '__main__':
rows = read_range()
write_range(rows)
Step 4: From the terminal or CMD application, run the command transfer.py
Step 5: View the changes in the New Test Sheet.
That’s it! You have successfully integrated your Python environment with Google Sheets and tested it using various methods. This environment can now serve as the gateway through which you can interact with your Google Sheets. Needless to say, there are numerous other options that you can view on developers.google.com and use in your Python project.
Additional Resources
Below are two essential resources you may find useful when handling data in Google Sheets using Python:
- Import CSV into Google Sheets: This can be really useful if you have data in CSV files and you want to import it into your Google Sheets file.
- Use Macros in Google Sheets: This will help you save time, automate tasks, and eliminate repetitive processes.
In addition, you may also want to check out these amazing and cheap Udemy courses to master data handling in Google Sheets.
Frequently Asked Questions about Using Python in Google Sheets
I regularly field questions about this topic. Whether it’s how to learn Python or how to access something specific in Google Sheets, I know how to solve the problem. Below, I listed the most common questions and their answers.
Can You Use Python with Google Sheets?
Yes. I regularly use Python with Google Sheets. It’s one of the best ways to automate complex and time-consuming processes. When you use Python with Google Sheets, you can integrate your data with data analysis libraries like NumPy or Pandas or data visualization libraries like Matplotlib or Seaborn. Check out the article above to learn how to write in Google Sheets using Python.
Can You Use Python to Automate Google Sheets?
Yes. It is possible for Python to connect to Google Sheets and help users automate some Google Sheets tasks like data handling. The list of Python packages available includes Gspread, Pygsheets, Ezsheets, as well as Google’s very own google-api-python-client.
Conclusion
In conclusion, using Python with Google Sheets can greatly simplify the task of working with data by automating the process of extracting and uploading data. Python is a powerful programming language that provides a wide range of tools and libraries for data analysis and visualization.
By integrating Python with Google Sheets, users can leverage these tools to perform complex analyses and create visualizations that can be shared with others. Using Python with Google Sheets can save time and streamline workflows, allowing users to focus on more important aspects of their projects.
Related:
