# google-sheets-mcp

English | [Korean](README.ko.md)

Local Model Context Protocol server for reading and writing Google Sheets through Google Sheets API v4.

This project is designed for local Codex/AI-agent workflows. It supports both service account credentials and OAuth user credentials.

## Features

- Read spreadsheet metadata and worksheet tabs.
- Read values from A1 ranges.
- Update exact ranges.
- Append rows.
- Batch update multiple ranges.
- Clear values while preserving formatting.
- Report authentication status without exposing private keys or tokens.

## MCP Tools

- `auth_status`
- `get_spreadsheet`
- `list_worksheets`
- `read_range`
- `update_range`
- `append_rows`
- `batch_update_values`
- `clear_range`

## Install

```powershell
npm.cmd install
npm.cmd run build
```

On macOS/Linux, use `npm` instead of `npm.cmd`.

## Authentication: Service Account

Recommended for automation.

1. Create or choose a Google Cloud project.
2. Enable Google Sheets API.
3. Create a service account and download a JSON key.
4. Put the JSON key at:

```text
secrets/google-service-account.json
```

5. Share each target spreadsheet with the service account email from the JSON key:

```json
{
  "client_email": "your-service-account@your-project.iam.gserviceaccount.com"
}
```

Give `Viewer` access for read-only use or `Editor` access for writes.

The `secrets/` directory is committed with only `.gitkeep`. All credential files inside it are ignored.

## Authentication: Custom Credential Path

If you do not want credentials under the repository directory, create a local `.env` file:

```env
GOOGLE_APPLICATION_CREDENTIALS=C:/secure/path/google-service-account.json
```

or provide inline JSON through a secret manager/environment variable:

```env
GOOGLE_SERVICE_ACCOUNT_JSON={"type":"service_account",...}
```

Credential lookup order:

1. `GOOGLE_APPLICATION_CREDENTIALS`
2. `GOOGLE_SERVICE_ACCOUNT_JSON`
3. `./secrets/google-service-account.json`

## Authentication: OAuth User Account

Use OAuth when the MCP should act as your Google user account.

1. Create OAuth Client credentials in Google Cloud.
2. Add this redirect URI:

```text
http://127.0.0.1:3333/oauth2callback
```

3. Create `.env`:

```env
GOOGLE_OAUTH_CLIENT_ID=your-client-id.apps.googleusercontent.com
GOOGLE_OAUTH_CLIENT_SECRET=your-client-secret
OAUTH_CALLBACK_PORT=3333
```

4. Generate a refresh token:

```powershell
npm.cmd run oauth:init
```

5. Open the printed URL, approve access, and add the printed refresh token to `.env`:

```env
GOOGLE_OAUTH_REFRESH_TOKEN=your-refresh-token
```

## Register With Codex

Build first:

```powershell
npm.cmd run build
```

Then register the MCP server:

```powershell
codex mcp add google-sheets-local -- node <path-to-google-sheets-mcp>/dist/index.js
```

Recommended `~/.codex/config.toml` entry:

```toml
[mcp_servers.google-sheets-local]
command = "node"
args = ["<path-to-google-sheets-mcp>/dist/index.js"]
cwd = "<path-to-google-sheets-mcp>"
```

Restart Codex after changing MCP configuration or credentials.

## Usage Notes

- Use `auth_status` first to verify the active authentication mode.
- Use `list_worksheets` before reading or writing ranges.
- For destructive operations, prefer reading the target range first.
- Use `batch_update_values` for large table edits to reduce Google API request count.

## Security

Never commit credentials. The following are ignored by default:

- `.env`
- `.env.*` except `.env.example`
- `credentials/`
- `secrets/*` except `secrets/.gitkeep`
- `*.service-account.json`
- `*.credentials.json`
- `*.token.json`

## License

MIT