Google Sheets API Integration for Automation

Google Sheets is a powerful tool for collaborative tasks and teamwork to manage and analyze data. You can integrate the Google Sheets API to automate tasks such as data entry, summarization, report generation, and real-time data synchronization. In this article, we will show you how to do Google Sheets API integration for automation.

Step 1: Enable Google Sheets API

Sign in to Google Cloud Console

• Go to Google Cloud Console.
• Log in with your Google account.

Create a New Project

• From the Project Dropdown at the top left (next to “Google Cloud”) >> select New Project.
• Enter a Project Name (e.g., “Google Sheets Automation”).
• Click Create.

To Open API Library:

• In the Cloud Console, go to APIs & Services >> select Library.

Search for Google Sheets API:

• Type Google Sheets API in the search bar.
• Click on it and then click Enable.

Step 2: Set Up API Credentials

• Go to Credentials
  • In the Cloud Console, go to APIs & Services >> select Credentials.
• Create Credentials
  • Click Create Credentials.
  • Select Service Account from the dropdown.

• Set Up the Service Account
  • Enter a Name for the service account.
  • Click Done.

• Generate a Key for the Service Account
  • In the list of service accounts, click on the one you just created.
  • Go to the Keys tab >> select Add Key >> select Create New Key.

• • Choose JSON and click Create.
  • A file named credentials.json will be downloaded. Keep this file safe.

Step 3: Share Your Google Sheet with the Service Account

Open Your Google Sheet

• Create a new Google Sheet or open an existing one.

Share with the Service Account Email

• Go back to the Cloud Console and locate your service account.
• Copy the Email of the service account.
• In your Google Sheet, click Share (top-right).
• Paste the service account email and click Done.

Step 4: Install Required Python Libraries

You’ll need to install google-auth and google-api-python-client libraries to interact with the Google Sheets API.

• Open your terminal (Command Prompt, PowerShell, or any terminal on your machine).
• Run the following command:

pip install --upgrade google-auth google-auth-oauthlib google-auth-httplib2 google-api-python-client

This will install the necessary libraries to work with the API.

Step 5: Authenticate with Google Sheets API

You can run the Python script for Google Sheets API integration in any Python environment. We will use the Visual Studio Code.

Open Visual Studio Code:

• Open the folder where you saved the credentials.json file there.
• Create a Python file named sheets_api.py and insert the following code:

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

# Load credentials from the JSON file
credentials = service_account.Credentials.from_service_account_file(
    'credentials.json',  # Path to your downloaded credentials.json file
    scopes=['https://www.googleapis.com/auth/spreadsheets']
)

# Build the Sheets API service
service = build('sheets', 'v4', credentials=credentials)

print("Google Sheets API is connected!")

• To run the script, insert the following command.

python sheets_api.py

If the connection is built properly you will see a message: “Google Sheets API is connected!”

Step 6: Automate Tasks

You can automate the read, write, append, etc tasks using the API.

1. Read Data from Google Sheets

To read data from your Google Sheets, add this to your script:

#Read data from Google Sheets

spreadsheet_id = 'your-spreadsheet-id'  # Replace with your actual sheet ID
range_name = 'Sheet1!A1:G71'  # Specify the range to read

# Retrieve data from the sheet
result = service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id,
    range=range_name
).execute()

values = result.get('values', [])
print("Data from Google Sheet:", values)

• Replace ‘your-spreadsheet-id’ with the actual ID of your Google Sheet.
• You can find this in your sheet’s URL: https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit#gid=0
• Run the script again to see the retrieved data in the terminal.

2. Write Data to Google Sheets

You can also write data to your Google Sheets by using Python Scrip through API integration. Add this to your script to write data:

#Write data to the Google Sheet
data = [
    ['Name', 'Age', 'Department', 'Salary'],
    ['Alice', 30, 'HR', 60000],
    ['Bob', 25, 'Engineering', 70000],
    ['Charlie', 35, 'Sales', 80000],
]

body = {
    'values': data
}

service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id,
    range='Sheet2!A1',
    valueInputOption='RAW',
    body=body
).execute()

• Run the script, and you’ll see the data updated in your Google Sheet.

Schedule Your Script for Automation

You can automate the script using a scheduler or you can use the App Script and add a trigger to schedule the script.

• Windows: Use Task Scheduler.
• macOS/Linux: Use cron jobs.

Examples of Automation:

• You can automatically pull data from multiple sources and generate daily summaries.
• Fetch raw data, clean it, and write back the cleaned data.
• Sync data from an external database to Google Sheets.

Conclusion

By using the Google Sheets API, you can automate various tasks, including tasks that are repetitive, data entry, update records, and integrating Sheets with other services. This setup streamlines workflows and reduces manual effort. Start experimenting and practicing API integration to unlock new potentials.