gsheet-cli

A blazing-fast CLI for Google Sheets with local DuckDB caching and MCP server support

Features

• Full Sheets API - Read, write, format cells and ranges with A1 notation
• 14 Chart Types - Line, bar, pie, scatter, bubble, candlestick, and more
• Pivot Tables - Create, update, and refresh pivot tables programmatically
• Apps Script - Manage script projects, versions, deployments, and execution
• Local Caching - DuckDB-powered offline access with bi-directional sync
• Dual Auth - OAuth 2.0 interactive flow or service account for automation
• MCP Server - Expose tools for Claude and other AI assistants
• Multiple Outputs - Table, JSON, or CSV output formats
• Profiles - Named spreadsheet profiles for quick switching
• Shell Completions - Bash, Zsh, Fish, and PowerShell support

Installation

Quick Install

curl -fsSL https://raw.githubusercontent.com/dipankar/gsheet-cli/main/install.sh | bash

Or with options:

./install.sh --prefix ~/.local --completions

From Source

git clone https://github.com/dipankar/gsheet-cli.git
cd gsheet-cli
cargo build --release
cp target/release/gsheet ~/.local/bin/

Quick Start

1. Authenticate

# Interactive OAuth (opens browser)
gsheet auth login

# Or use a service account
gsheet auth login --service-account /path/to/key.json

2. Set Up a Profile

gsheet config profile add mysheet --spreadsheet "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"

3. Start Using

# List sheets
gsheet -p mysheet sheet list

# Read a range
gsheet -p mysheet range get "Sheet1!A1:D10"

# Write values
gsheet -p mysheet cell set "A1" "Hello, World!"

Usage Examples

Working with Data

# Get cell value
gsheet -s SPREADSHEET_ID cell get "B2"

# Set range from JSON
gsheet -s SPREADSHEET_ID range set "A1:B2" --json '[["Name","Age"],["Alice",30]]'

# Append row to sheet
gsheet -s SPREADSHEET_ID cell append "Sheet1" "John" "Doe" "john@example.com"

# Find cells matching pattern
gsheet -s SPREADSHEET_ID range find "A1:Z100" --pattern "error|warning"

Sheet Management

# Create a new sheet
gsheet -s SPREADSHEET_ID sheet create "Q1 Report"

# Duplicate sheet
gsheet -s SPREADSHEET_ID sheet duplicate "Template" "January"

# Freeze header row
gsheet -s SPREADSHEET_ID sheet freeze --sheet "Data" --rows 1

# Set tab color
gsheet -s SPREADSHEET_ID sheet tab-color "Important" --color "#FF0000"

Formatting

# Bold headers
gsheet -s SPREADSHEET_ID format bold "A1:Z1"

# Set background color
gsheet -s SPREADSHEET_ID format color "A1:D1" --bg "#4285F4" --fg "#FFFFFF"

# Apply borders
gsheet -s SPREADSHEET_ID format border "A1:D10" --style solid --color "#000000"

# Merge cells
gsheet -s SPREADSHEET_ID format merge "A1:D1"

# Number format
gsheet -s SPREADSHEET_ID format number "B2:B100" --pattern "#,##0.00"

Charts

# Create a column chart
gsheet -s SPREADSHEET_ID chart create --type column --data "A1:B10" --title "Sales"

# Create a pie chart
gsheet -s SPREADSHEET_ID chart create --type pie --data "A1:B5" --title "Distribution"

# Create a bubble chart
gsheet -s SPREADSHEET_ID chart bubble --x-data "A1:A10" --y-data "B1:B10" --sizes "C1:C10"

Pivot Tables

# Create pivot table
gsheet -s SPREADSHEET_ID pivot create \
  --source "Data!A1:E1000" \
  --anchor "Pivot!A1" \
  --rows "0" \
  --values "2:SUM,3:AVERAGE"

# Refresh pivot table
gsheet -s SPREADSHEET_ID pivot refresh "Pivot!A1"

Apps Script

# Run a function
gsheet script run SCRIPT_ID "myFunction" --args '["param1", 42]'

# Create and deploy
gsheet script create --title "My Script" --parent-id SPREADSHEET_ID
gsheet script version create SCRIPT_ID --description "Initial release"
gsheet script deploy create SCRIPT_ID --version 1

Configuration

Configuration is stored in ~/.config/gsheet-cli/config.toml:

[auth]
method = "oauth"
token_cache_path = "~/.config/gsheet-cli/tokens"

[cache]
enabled = true
ttl_seconds = 300
auto_sync = true

[output]
format = "table"
color = "auto"

[spreadsheets.mysheet]
id = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
description = "My main spreadsheet"

Global Flags

Flag	Description
-s, --spreadsheet <ID>	Spreadsheet ID
-p, --profile <NAME>	Use named profile
-o, --output <FORMAT>	Output format: table, json, csv
-c, --config <PATH>	Custom config file
--no-cache	Bypass cache
-q, --quiet	Suppress output
-v, -vv, -vvv	Increase verbosity

MCP Server

Start the MCP server for AI assistant integration:

# stdio transport (default)
gsheet mcp

# HTTP transport
gsheet mcp --transport http --port 8080

Available MCP Tools

Tool	Description
read_range	Read values from a range
write_range	Write values to a range
list_sheets	List all sheets
create_sheet	Create a new sheet
format_range	Apply formatting
run_macro	Execute Apps Script function

Claude Desktop Integration

Add to ~/.config/claude/claude_desktop_config.json:

{
  "mcpServers": {
    "gsheet": {
      "command": "gsheet",
      "args": ["mcp"]
    }
  }
}

Command Reference

Command	Description
auth	Authentication management
sheet	Sheet/tab operations
cell	Single cell operations
range	Range operations
format	Cell formatting
chart	Chart operations
pivot	Pivot table operations
script	Apps Script management
cache	Cache management
config	Configuration management
mcp	Start MCP server
completions	Generate shell completions

Run gsheet <command> --help for detailed usage.

Shell Completions

# Bash
gsheet completions bash > ~/.local/share/bash-completion/completions/gsheet

# Zsh
gsheet completions zsh > ~/.zfunc/_gsheet

# Fish
gsheet completions fish > ~/.config/fish/completions/gsheet.fish

# PowerShell
gsheet completions powershell > $PROFILE.CurrentUserAllHosts

Development

Prerequisites

• Rust 1.70+
• Google Cloud project with Sheets API enabled
• OAuth 2.0 credentials or service account key

Building

cargo build
cargo test
cargo clippy

Running Tests

# Unit tests
cargo test

# With logging
RUST_LOG=debug cargo test

Project Structure

src/
├── api/          # Google Sheets API client
├── auth/         # OAuth and service account auth
├── cache/        # DuckDB caching layer
├── cli/          # Command-line interface
├── config/       # Configuration management
├── mcp/          # MCP server implementation
├── error.rs      # Error types
├── lib.rs        # Library entry point
└── main.rs       # CLI entry point

Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing)
5. Open a Pull Request

License

MIT License - see LICENSE for details.

---

Made with Rust by Dipankar