# Google Sheets Automation with gspread

This guide demonstrates how to read and write data to Google Sheets using the `gspread` library. It uses a Service Account for server-to-server authentication, which is ideal for bots and automation scripts.

**Modules Used:**
*   `gspread`: A Python API for Google Sheets.
*   `google-auth`: For handling authentication.
*   [[programming/python/modules/argparse-module|argparse]]: To handle command-line arguments.

## Prerequisites

1.  **Google Cloud Console**: Create a new project.
2.  **Enable APIs**: Enable **Google Sheets API** and **Google Drive API**.
3.  **Service Account**:
    *   Go to "Credentials" -> "Create Credentials" -> "Service Account".
    *   Name it (e.g., "sheets-bot").
    *   Go to the "Keys" tab of the new service account -> "Add Key" -> "Create new key" -> "JSON".
    *   Download the file and rename it to `service_account.json`.
4.  **Share the Sheet**:
    *   Open the Google Sheet you want to automate.
    *   Click "Share".
    *   Paste the **client_email** found inside your `service_account.json` file.
    *   Give it "Editor" access.

## Installation

```bash
pip install gspread
```

## The Code

Save this as `sheets_bot.py`.

```python
import gspread
import argparse

def interact_with_sheet(filename, action, row_data=None):
    # 1. Authenticate using the Service Account
    # gspread looks for 'service_account.json' in the current directory by default
    try:
        gc = gspread.service_account(filename='service_account.json')
    except Exception as e:
        print(f"Authentication Error: {e}")
        print("Ensure 'service_account.json' is in the current directory.")
        return

    try:
        # 2. Open the Spreadsheet
        # You can open by title, url, or key. Title is easiest if unique.
        sh = gc.open(filename)
        
        # Select the first worksheet (tab)
        worksheet = sh.sheet1

        if action == "read":
            print(f"Reading data from '{filename}'...")
            # Get all records as a list of dictionaries
            records = worksheet.get_all_records()
            for i, record in enumerate(records, start=2): # Row 1 is header
                print(f"Row {i}: {record}")
                
        elif action == "add":
            if row_data:
                print(f"Adding row to '{filename}'...")
                worksheet.append_row(row_data)
                print("Row added successfully.")
            else:
                print("Error: No data provided to add.")

        elif action == "update":
            # Example: Update cell B2
            print(f"Updating cell B2 in '{filename}'...")
            worksheet.update_acell('B2', 'Updated via Python')
            print("Cell updated.")

    except gspread.exceptions.SpreadsheetNotFound:
        print(f"Error: Spreadsheet '{filename}' not found. Did you share it with the service account email?")
    except Exception as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Google Sheets Automation")
    parser.add_argument("filename", help="Name of the Google Sheet")
    parser.add_argument("action", choices=['read', 'add', 'update'], help="Action to perform")
    parser.add_argument("--data", nargs='+', help="Data to add (for 'add' action)")
    
    args = parser.parse_args()
    
    interact_with_sheet(args.filename, args.action, args.data)
```

## Usage

1.  **Read Data**:
    ```bash
    python sheets_bot.py "My Spreadsheet" read
    ```

2.  **Add a Row**:
    ```bash
    python sheets_bot.py "My Spreadsheet" add --data "John Doe" "john@example.com" "Active"
    ```

3.  **Update a Cell (Hardcoded B2)**:
    ```bash
    python sheets_bot.py "My Spreadsheet" update
    ```

[[programming/python/python]]