How to Read and Write Google Sheets Using Google Sheets API in Python

Import Necessary Libraries and Set Up Credentials

• Ensure you have the `google-auth`, `google-auth-oauthlib`, `google-auth-httplib2`, and `google-api-python-client` libraries installed.

 

• Import the libraries in your Python script:

from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

Create a Service Account

• Create a service account key file in JSON format. This step generally involves downloading a JSON file from the Google Cloud Console that contains your service account credentials.

 

• Authorize the service account for accessing your Google Sheets by sharing the specific sheet with the service account email present in your JSON credentials file.

Initialize the Sheets Service

• Set up the credentials and build the Sheets API service:

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = 'path/to/your/service/account/key.json'

credentials = Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = build('sheets', 'v4', credentials=credentials)

Read from Google Sheets

• Specify the spreadsheet ID and range you want to read from:

spreadsheet_id = 'your_spreadsheet_id'
range_name = 'Sheet1!A1:D10'  # Adjust the range as necessary

result = service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id, range=range_name).execute()
values = result.get('values', [])

if not values:
    print('No data found.')
else:
    for row in values:
        print(row)

Write to Google Sheets

• Prepare the data you want to write in a list of lists format (where each sub-list represents a row):

data = [
    ['Header1', 'Header2', 'Header3'],
    ['Value1', 'Value2', 'Value3'],
    ['Value4', 'Value5', 'Value6']
]

body = {
    'values': data
}

• Use the `update` function to write data to the specified range:

range_write = 'Sheet1!A1:C3'  # Adjust the range as necessary

result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_write,
    valueInputOption='RAW', body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))

Handle Errors and Exceptions

• Wrap your code in try-except blocks to handle potential errors in network requests, API limits, etc.:

try:
    # Your sheets reading/writing logic here
except Exception as e:
    print('Failed to read/write Google Sheet:', str(e))

Advanced Features and Settings

• Consider using batch updates if you are performing multiple read and write operations to minimize API calls and enhance performance.

 

• Explore additional functionalities such as manipulating formatting, conditional formatting, and charts through the Google Sheets API for more advanced uses.

 

• Monitor the Google Sheets API quota and handle API requests efficiently to avoid hitting limits.

By following these steps, you can effectively read from and write to Google Sheets using Python, leveraging Google Sheets API to automate and manage your data with ease.