Google Spreadsheet Advanced

Advanced Google Spreadsheet operations for bulk data manipulation

Overview

Google Spreadsheet Advanced tools provide comprehensive functionality for bulk operations on Google Sheets. These tools complement the basic Google Sheets tools with advanced features for managing multiple rows, sheets, and complex data operations.

Key Features

• GOOGLE_SPREADSHEET_ADD_ROW
  • Add a single row to a Google Sheet
• GOOGLE_SPREADSHEET_ADD_ROWS
  • Add multiple rows to a Google Sheet at once
• GOOGLE_SPREADSHEET_UPDATE_ROW
  • Update a specific row in a Google Sheet
• GOOGLE_SPREADSHEET_UPDATE_ROWS
  • Update multiple rows in a Google Sheet
• GOOGLE_SPREADSHEET_GET_ROWS
  • Retrieve multiple rows from a Google Sheet
• GOOGLE_SPREADSHEET_ADD_SHEET
  • Create a new sheet within a Google Spreadsheet
• GOOGLE_SPREADSHEET_REMOVE_SHEET
  • Delete a sheet from a Google Spreadsheet

Authentication

For further details, click here.

You need to set up Google Sheets API credentials. Follow these steps:

1. Go to Google Cloud Console
2. Create a new project or select an existing one
3. Enable the Google Sheets API
4. Create service account credentials and download the JSON key file
5. Share your Google Spreadsheet with the service account email

Note: Treat API credentials as sensitive information and never commit them to public repositories.

Example: Bulk Data Operations

- id: add_multiple_rows
  name: add_multiple_rows
  tool: GOOGLE_SPREADSHEET_ADD_ROWS
  config:
    - name: credentials
      value: "{{secrets.GOOGLE_SHEETS_CREDENTIALS}}"
  input:
    - name: spreadsheet_id
      value: "your_spreadsheet_id_here"
    - name: sheet_name
      value: "Data"
    - name: values
      value: [
        ["John Doe", "[e-mail]", "Manager", "2024-01-15"],
        ["Jane Smith", "[e-mail]", "Developer", "2024-01-16"],
        ["Bob Johnson", "[e-mail]", "Designer", "2024-01-17"]
      ]

- id: update_multiple_rows
  name: update_multiple_rows
  tool: GOOGLE_SPREADSHEET_UPDATE_ROWS
  config:
    - name: credentials
      value: "{{secrets.GOOGLE_SHEETS_CREDENTIALS}}"
  input:
    - name: spreadsheet_id
      value: "your_spreadsheet_id_here"
    - name: sheet_name
      value: "Data"
    - name: start_row
      value: 2
    - name: values
      value: [
        ["John Doe Updated", "[e-mail]", "Senior Manager", "2024-01-15"],
        ["Jane Smith Updated", "[e-mail]", "Senior Developer", "2024-01-16"]
      ]

Example: Sheet Management

- id: create_new_sheet
  name: create_new_sheet
  tool: GOOGLE_SPREADSHEET_ADD_SHEET
  config:
    - name: credentials
      value: "{{secrets.GOOGLE_SHEETS_CREDENTIALS}}"
  input:
    - name: spreadsheet_id
      value: "your_spreadsheet_id_here"
    - name: sheet_name
      value: "MonthlyReport_{{current_date}}"

- id: setup_headers
  name: setup_headers
  tool: GOOGLE_SPREADSHEET_ADD_ROW
  config:
    - name: credentials
      value: "{{secrets.GOOGLE_SHEETS_CREDENTIALS}}"
  input:
    - name: spreadsheet_id
      value: "your_spreadsheet_id_here"
    - name: sheet_name
      value: "MonthlyReport_{{current_date}}"
    - name: values
      value: ["Name", "Email", "Department", "Start Date", "Status"]

Example: Data Migration Workflow

- id: get_source_data
  name: get_source_data
  tool: GOOGLE_SPREADSHEET_GET_ROWS
  config:
    - name: credentials
      value: "{{secrets.GOOGLE_SHEETS_CREDENTIALS}}"
  input:
    - name: spreadsheet_id
      value: "source_spreadsheet_id"
    - name: sheet_name
      value: "SourceData"
    - name: start_row
      value: 2
    - name: end_row
      value: 1000

- id: process_data
  name: process_data
  tool: PYTHON_SANDBOX_RUN
  input:
    - name: script
      value: |
        import json
        
        # Get data from previous step
        source_data = {{steps.get_source_data.result.values}}
        
        # Transform data
        processed_data = []
        for row in source_data:
            if len(row) >= 3 and row[0]:  # Validate row has minimum required data
                processed_data.append([
                    row[0].strip().title(),  # Clean and format name
                    row[1].strip().lower() if len(row) > 1 else "",  # Email to lowercase
                    row[2].strip() if len(row) > 2 else "",  # Department
                    "Active"  # Default status
                ])
        
        print(json.dumps({"processed_data": processed_data}))

- id: migrate_to_target
  name: migrate_to_target
  tool: GOOGLE_SPREADSHEET_ADD_ROWS
  config:
    - name: credentials
      value: "{{secrets.GOOGLE_SHEETS_CREDENTIALS}}"
  input:
    - name: spreadsheet_id
      value: "target_spreadsheet_id"
    - name: sheet_name
      value: "MigratedData"
    - name: values
      value: "{{steps.process_data.result.processed_data}}"

Example: Conditional Updates

- id: get_current_data
  name: get_current_data
  tool: GOOGLE_SPREADSHEET_GET_ROWS
  config:
    - name: credentials
      value: "{{secrets.GOOGLE_SHEETS_CREDENTIALS}}"
  input:
    - name: spreadsheet_id
      value: "your_spreadsheet_id_here"
    - name: sheet_name
      value: "Employees"

- id: filter_and_update
  name: filter_and_update
  tool: PYTHON_SANDBOX_RUN
  input:
    - name: script
      value: |
        import json
        
        # Get current data
        current_data = {{steps.get_current_data.result.values}}
        
        # Find rows that need updating (example: inactive employees)
        updates = []
        for i, row in enumerate(current_data):
            if len(row) >= 4 and row[3] == "Inactive":
                # Update status to "Terminated" and add termination date
                updated_row = row.copy()
                updated_row[3] = "Terminated"
                updated_row.append("2024-01-31")  # Add termination date
                updates.append({
                    "row_index": i + 2,  # +2 because sheets are 1-indexed and we skip header
                    "data": updated_row
                })
        
        print(json.dumps({"updates": updates}))

- id: apply_updates
  name: apply_updates
  tool: GOOGLE_SPREADSHEET_UPDATE_ROWS
  config:
    - name: credentials
      value: "{{secrets.GOOGLE_SHEETS_CREDENTIALS}}"
  input:
    - name: spreadsheet_id
      value: "your_spreadsheet_id_here"
    - name: sheet_name
      value: "Employees"
    - name: start_row
      value: "{{steps.filter_and_update.result.updates[0].row_index}}"
    - name: values
      value: "{{steps.filter_and_update.result.updates}}"

Tips and Best Practices

• Always validate data before bulk operations to prevent errors
• Use batch operations (ADD_ROWS, UPDATE_ROWS) for better performance
• Consider creating backups before large update operations
• Use meaningful sheet names when creating new sheets
• Implement error handling for large datasets
• Monitor API quota usage for high-volume operations