How to Access Google Sheets Data with Python and the Google Sheets API?

In this first part of the series, we’ll focus on setting up the Google API so Python can access responses from a Google Form stored in Google Sheets. You’ll learn how to create a Google Cloud project, enable the Sheets API, generate credentials, and read form data using Python. This foundational step is essential before moving on to generating PDF invoices and sending them via email, which will be covered in the next tutorial.

Prerequisite:

This tutorial is part of the Automated Sales Invoice Series.

📚 View the Complete Automated Sales Invoice Series

                                                                                                                                  ➡ Next Part

Preliminary 

Before I begin, it is recommended to activate the virtual environment before installing the relevant dependencies.

python -m venv venv
venv\Scripts\activate
pip install pygsheets dotenv

The file structure is as shown in the diagram below:

The sales invoice folder contains .env, gsheet.json, and gsheet.py files as in the above diagram. Let us dive into each file.

Since the API key is confidential from public access and these keys will be separated from the gsheet.py, it will be stored in the .env file with the location path, such as GSHEET_PATH = "c:/xxxx/gsheet_json."

While the gsheet.json is a file containing all relevant keys required to access the Google services, which is downloaded from the Google Cloud Console, I will explain it later.

Finally, gsheet.py is the main Python file that I will work on in this tutorial.

Step 1: Create a Google Form and link it to a Google Sheet.

Open a Google Form and set up the relevant question for the answer

1) Who is the customer? - short answer
2) What is the address? - paragraph
3) What is the email address? - short answer
4) What is the description of the goods or services? - dropdown
a) Develop a desktop app
b) Develop a web app
c) Create an automation
5) What is the rate? - short answer
6) How much is the quantity? - short answer

All questions should be marked as required.

Once the Google Form question is ready, click the "Responses" tab and continue by clicking the link to the sheet in the right corner. It pops up a window to either create a new spreadsheet or use the existing spreadsheet and select either one. Then the spreadsheet with the responding heading was created.

Step 2: Google Cloud Console configuration

First of all, let me head over to the Google Cloud Console and log in to the account. Then, click Open Project. If it's your first time, proceed with clicking 'New Project' in the top right corner of the window. Then fill in the project name, so I will name it sales_invoice and click "Create" to proceed. 

Following, I need to activate the Google Sheets API. In the centre search bar, I input the Google Sheet API, then the API appeared, and it was enabled by default, with a checkmark next to "enabled" on the right side. If it isn't enabled, simply click "enable it." Repeat the same process to add the Google Drive API.

Next, open the window sidebar, then choose APIs and services, and when the options expand, click the credentials. In the credential window, go to the top and click "+ Create Credential." Then the drop-down appears; simply click Service account.

Now, I can go ahead and configure the service account. I named the input field sales_invoice and continued clicking the "create and continue" button, as well as the "done" button down below. It takes me back to the credential window, where, under the service account section, I have noticed that an email account link was displayed, and I chose the one that I created and clicked on it. Then I click the Keys tab and click the "Add Key" button, and drop-down, giving me 2 options, and I choose "Create New Key." A pop-up menu appears, and I choose the JSON key type. The JSON file was downloaded, and I renamed it to gsheet.json and copied and pasted it into my sales_invoice folder as the file structure above.

Finally, I have set the GSHEET_PATH = "sales_invoice\\gsheet.json" in my .env file, where the actual gsheet.json is located. 

Step 3: Connect and share the service account email with Google Sheets.

Back in Google Cloud Console, click the sidebar and select IAM & Admin, and a dropdown will appear; go ahead and select Service Account. Copy the service account email from the service account and navigate to the Google Sheet linked to the Google Form. Then, in the top right corner of the Google Sheet, click the share button. A pop-up window will appear indicating 'Share Invoice_Form'; paste the service account email into the input field, and then click the "Done" button.

Step 4: Input the respective field in the Google Form

Now I can fill out the form with the following details:

1) Who is the customer? - Orange Inc
2) What is the address? - 12 Convent Street, West Australia
3) What is the email address? - howappandweb@gmail.com
4) What is the description of the goods or services? - Create an automation
5) What is the rate? - 1000
6) How much is the quantity? - 1

Then, I hit the Google Form submit button.

Step 5: Retrieve data from the Google Sheets

Now, I am in gsheet.py and ready to set up the get_gsheet_data function. 

(a) Link to the gsheet_path, which is stored in the .env file, to get the permission to read the data.

(b) Connect and print out the GSheet connection once it is ready.

(c) Open and print out the shared Google Sheet file.

(d) Skip the header row and only the content will be read. 

(d) Loop over the data_rows, retrieve the row, and print out the row data retrieved.

import pygsheets
import dotenv
import os

dotenv.load_dotenv()

def get_gsheet_data():
  # Get the Google Service Account JSON file path from environment variable
  path = os.getenv("GSHEET_PATH")
  
  # Authorize pygsheets using the service account file
  gc = pygsheets.authorize(service_account_file = path)
  print('gsheets connected')
  
  # Open the Google Spreadsheet named 'Invoice_form'
  sh = gc.open('Invoice_form')
  
  # Open the specific worksheet (tab) named 'Form_responses'
  wk = sh.worksheet_by_title('Form_responses')
  print("Gsheet opened")
  
  # Get all values from the worksheet
  content = wk.get_all_values(include_tailing_empty=False)
  
  # Skip the header row (first row)
  # This slices from index 1 to the end, excluding the first row
  data_rows = content[1:] 
  
  # Loop through each data row
  for row in data_rows:
    # Only print non-empty rows
    if any(cell.strip() for cell in row):
      # Print the entire row
      print(row)  
      
# Call the function
get_gsheet_data()

The result is as shown below:

Final wrap-up 

In this tutorial, we connected a Google Form to a Google Sheet, then used a Python script with PyGsheets to access the spreadsheet via a service account. By properly handling the service account JSON, sharing the sheet with the account, and using either open_by_title() or the exact sheet name, we retrieved the form responses, skipped headers, and processed the data rows in Python for further automation or reporting.

Published: Feb 2026

Last Updated: Feb 2026

About the Author

Kelvin Loh is a Python developer focused on Flask, desktop applications, and business automation solutions. He shares practical tutorials and real-world coding projects to help developers and small businesses build useful applications.

🚀 Place Order Now 🖼️ View Real Projects ❓ Ask a Question First 💰Try the 98% Open Rate Method