Content
# 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
```bash
curl -fsSL https://raw.githubusercontent.com/dipankar/gsheet-cli/main/install.sh | bash
```
Or with options:
```bash
./install.sh --prefix ~/.local --completions
```
### From Source
```bash
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
```bash
# 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
```bash
gsheet config profile add mysheet --spreadsheet "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
```
### 3. Start Using
```bash
# 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
```bash
# 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
```bash
# 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
```bash
# 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
```bash
# 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
```bash
# 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
```bash
# 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`:
```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:
```bash
# 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`:
```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
# 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
```bash
cargo build
cargo test
cargo clippy
```
### Running Tests
```bash
# 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](LICENSE) for details.
---
Made with Rust by [Dipankar](https://github.com/dipankar)
