sheets_add_conditional_formatting
Add conditional formatting rules to a Google Sheet
LABSCO SUMMARY
The read tools are built to not blow up your context.
Most Sheets servers stop at values; this one goes deep on structure while working hard to stay compact — sheets_get_formatting_compact and the run-length-encoded validation and border reads cut typical output by an order of magnitude, and sheets_get_full_sheet_snapshot gets a whole sheet's layout in one call. That matters because reading a formatted sheet cell-by-cell is exactly what floods an agent's context. Note it uses a service account, so a sheet has to be shared with that account before any tool can touch it.
What it is
A server over the Google Sheets API: 44 tools spanning cell values, sheet and table management, formatting, charts, and a set of read tools that pull a sheet's structure and formatting back compactly.
What you get
- Values read and written: sheets_get_values and sheets_batch_get_values, sheets_update_values and its batch form, sheets_append_values (which overwrites empty cells unless you set insertDataOption to INSERT_ROWS), and sheets_clear_values, with sheets_get_metadata and sheets_check_access to orient first
- Spreadsheet and sheet lifecycle: sheets_create_spreadsheet, sheets_insert_sheet, sheets_delete_sheet, sheets_duplicate_sheet, sheets_copy_to another spreadsheet, sheets_update_sheet_properties, and batch delete of several sheets at once
- Native Google Sheets tables with typed columns and dropdowns — sheets_add_table, sheets_update_table, sheets_delete_table and sheets_get_tables by tableId
- Formatting: sheets_format_cells for colors, fonts, alignment and number formats, sheets_update_borders, sheets_merge_cells and sheets_unmerge_cells, sheets_add_conditional_formatting, and batch versions for many ranges in one call
- Charts created, updated and deleted with sheets_create_chart / sheets_update_chart / sheets_delete_chart, plus sheets_insert_link, sheets_insert_date with locale support, and row and column inserts and deletes
- Structure reads that keep output small: sheets_get_full_sheet_snapshot pulls all structural and formatting metadata in one API call, sheets_get_sheet_structure returns dimensions and merges without per-cell data, and sheets_get_formatting_compact run-length-encodes formatting to cut output 90%+
- Targeted inspection: sheets_get_data_validation surfaces checkboxes and dropdowns, sheets_get_basic_filter reads the AutoFilter, sheets_get_border_map draws a visual border grid, and sheets_compare_ranges diffs the formatting of two equal-sized ranges to verify a repeated pattern
Requirements
A Google Cloud project id in GOOGLE_PROJECT_ID and a service account with access to the sheets you use. Authenticate three ways: GOOGLE_APPLICATION_CREDENTIALS pointing at the key JSON file, GOOGLE_SERVICE_ACCOUNT_KEY as a JSON string, or GOOGLE_PRIVATE_KEY plus GOOGLE_CLIENT_EMAIL. Node.js 20+, run as npx mcp-gsheets over stdio. Share each spreadsheet with the service account's email for it to have access.
Setup effort
One command plus credentials — npx -y mcp-gsheets@latest, then supply credentials
USE CASES
01
Pulling data out of a sheet
Read the metadata to find the sheet and range, get the values you need in one or several ranges, and confirm you have write access before changing anything.
02
Writing results back
Update a range in place, append new rows to the bottom of a table without overwriting what is there, and clear a stale block when you need to.
03
Making a report readable
Format the header and data cells, add conditional formatting to highlight the outliers, and drop in a chart so the numbers tell their own story.
04
Understanding a sheet before you touch it
Grab a one-shot snapshot of the sheet's structure and formatting, read its data validation to find the dropdowns and checkboxes, and see the AutoFilter that is applied — compactly, not cell by cell.
05
Verifying a repeated pattern
Compare the formatting of a data block against a template row to confirm every row matches, getting back only the cells that differ.
44 tools
Counted once with only startup setup. Other versions, modes or optional credentials may expose a different list.
sheets_add_table
Create a native Google Sheets table with typed columns and optional dropdown values
sheets_append_values
Append values to the end of a table in a Google Sheets spreadsheet. IMPORTANT: By default, this will OVERWRITE existing empty cells. To INSERT new rows instead, set insertDataOption to "INSERT_ROWS".
sheets_batch_delete_sheets
Delete multiple sheets from a Google Sheets spreadsheet in a single operation
sheets_batch_format_cells
Format multiple cell ranges in a Google Sheet in a single operation
sheets_batch_get_values
Get values from multiple ranges in a Google Sheets spreadsheet
sheets_batch_update_values
Update values in multiple ranges of a Google Sheets spreadsheet
sheets_check_access
Check access permissions for a spreadsheet. Returns information about what operations are allowed.
sheets_clear_values
Clear values in a specified range of a Google Sheets spreadsheet
sheets_compare_ranges
Compare cell formatting between two ranges of identical dimensions. Useful for verifying repeated patterns, e.g. "do all data rows 6–85 have identical formatting?" or "is row 10 formatted identically to the template row 5?". Returns a diff listing only the cells and properties that differ between the two ranges. Cells are compared position-by-position; rangeA and rangeB must have the same number of rows and columns. Use fields to restrict comparison to specific format properties.
sheets_copy_to
Copy a sheet to another Google Sheets spreadsheet
sheets_create_chart
Create a chart in a Google Sheets spreadsheet. Sheet names with spaces should be quoted in ranges (e.g., "My Sheet"!A1:B5). Position uses overlayPosition with anchorCell containing sheetId, rowIndex, and columnIndex.
sheets_create_spreadsheet
Create a new Google Sheets spreadsheet
sheets_delete_chart
Delete a chart from a Google Sheets spreadsheet
sheets_delete_columns
Delete one or more columns from a Google Sheet using a full-column A1 range
sheets_delete_rows
Delete one or more rows from a Google Sheet using a full-row A1 range
sheets_delete_sheet
Delete a sheet from a Google Sheets spreadsheet
sheets_delete_table
Delete a native Google Sheets table by tableId
sheets_duplicate_sheet
Duplicate a sheet within a Google Sheets spreadsheet
sheets_format_cells
Format cells in a Google Sheet (colors, fonts, alignment, number formats)
sheets_get_basic_filter
Read the Basic Filter (AutoFilter) configuration for a sheet, including the filtered range, sort specs, and per-column filter criteria (hidden values, conditions, color filters). Returns hasBasicFilter: false if no filter is applied.
sheets_get_border_map
Returns a visual tabular map of borders for a range. Instead of per-cell JSON with 4 separate border objects, returns compact grids showing which cells have top/bottom/left/right borders and their styles. Solves the ambiguity between "right border of cell N" vs "left border of cell N+1". Output: a horizontal-lines grid and a vertical-lines grid, each as a 2D array of line styles. Set includeStyle:true to include color and width details (larger output).
sheets_get_conditional_formatting
Read conditional formatting rules and banded ranges (alternating row/column colors) for a sheet. CF formulas are normalized to English locale (semicolons → commas) by default. Each rule with a formula includes a "_formulaLocaleRaw" field with the original unmodified formula. Set normalizeFormulas:false to get raw formulas as returned by the API.
sheets_get_data_validation
Read data validation rules (checkboxes, dropdown lists, custom formulas, etc.) from a sheet or range. Returns a compact list of unique validation rules grouped by their cell ranges (run-length encoded). Useful for discovering checkboxes (BOOLEAN), dropdown lists (ONE_OF_LIST / ONE_OF_RANGE), number constraints, and custom formula validations.
sheets_get_formatting_compact
Returns cell formatting for a range as compact A1Range→format pairs. Adjacent cells with identical formatting are collapsed into rectangular ranges (run-length encoded). Reduces output by 90%+ compared to per-cell formatting for typical sheets. Use instead of sheets_get_full_sheet_snapshot when you only need formatting data. Supported fields: backgroundColor, backgroundColorStyle, textFormat, horizontalAlignment, verticalAlignment, wrapStrategy, textRotation, numberFormat, padding, borders.
sheets_get_full_sheet_snapshot
One-shot tool: reads all structural and formatting metadata for a sheet in a single API call. Returns: sheet properties (frozen rows/cols, dimensions, tab color), merged cells, column widths, row heights, banded ranges, and optionally cell-level formatting for a specified range (includeFormattingRange). compactMode is OFF by default — full per-cell detail is returned unless compactMode:true is provided. When compactMode is ON, adjacent cells with identical formatting are collapsed into range descriptors (90%+ smaller output). Conditional formatting rules are included by default (includeConditionalFormatting:true); set to false to exclude them. CF formulas are normalized to English locale (commas). Use fields to limit which format properties are returned. Use this before programmatically recreating a sheet.
sheets_get_merged_cells
Get all merged cell ranges for a specific sheet. Returns each merge as A1 notation and GridRange coordinates.
sheets_get_metadata
Get metadata about a Google Sheets spreadsheet including sheet names, IDs, and properties
sheets_get_sheet_dimensions
Get column widths (pixelSize), row heights (pixelSize), hidden columns/rows, and frozen row/column counts for a sheet.
sheets_get_sheet_formatting
Read cell formatting (background color, text color, font family, font size, bold, italic, horizontal/vertical alignment, wrapStrategy, textRotation, numberFormat) for a range. Returns a 2D array matching the requested range rows/columns. Set useEffectiveFormat=true to get the resolved/inherited format instead of the user-entered one.
sheets_get_sheet_structure
Lightweight tool returning ONLY structural/dimensional metadata for a sheet — no per-cell data. Returns: sheet dimensions, frozen rows/cols, tab color, sheet index, column widths array, row heights array, hidden columns/rows, and all merge ranges in A1 notation. Use this instead of sheets_get_full_sheet_snapshot when per-cell formatting is not needed. Much faster and cheaper — single API call with minimal field mask.
sheets_get_tables
Read native Google Sheets tables for a spreadsheet or a specific sheet
sheets_get_values
Get values from a specified range in a Google Sheets spreadsheet
sheets_insert_date
Insert properly formatted dates in Google Sheets with locale support and automatic detection
sheets_insert_link
Insert clickable links in Google Sheets cells with custom display text
sheets_insert_rows
Insert new rows at a specific position with optional data
sheets_insert_sheet
Add a new sheet to an existing Google Sheets spreadsheet
sheets_merge_cells
Merge cells in a Google Sheet
sheets_unmerge_cells
Unmerge cells in a Google Sheet
sheets_update_borders
Update borders of cells in a Google Sheet
sheets_update_chart
Update an existing chart in a Google Sheets spreadsheet
sheets_update_sheet_properties
Update properties of a sheet in a Google Sheets spreadsheet
sheets_update_table
Update an existing native Google Sheets table by tableId
sheets_update_values
Update values in a specified range of a Google Sheets spreadsheet. Examples:
- Fixed range "A1:C3" - must provide exactly 3 rows
- Flexible range "A1" - will expand to fit all provided rows
- To update rows 42-74 (33 rows), use "A42" not "A42:E53"
IMPORTANT: Empty rows in your data array still count as rows!
BEFORE YOU INSTALL
What to know first
Tools you get
44 tools when we connected. GSHEETS_TOOLSETS trims the surface (core alone is 11 tools, core plus sheets 20), and GSHEETS_READ_ONLY=true removes every write tool, leaving 16. A misspelled toolset name stops startup.
Requires
- npx on PATH
- Node.js v20 or higher
- GOOGLE_PROJECT_ID and a service account JSON key (GOOGLE_APPLICATION_CREDENTIALS, GOOGLE_SERVICE_ACCOUNT_KEY, or GOOGLE_PRIVATE_KEY plus GOOGLE_CLIENT_EMAIL)
- Each spreadsheet shared with the service account email, with Editor permission
CONTEXT COST
Heavy Standing cost
Rated by the size of the 44 tool definitions that ride along on every turn, not by how many there are
INSTALLATION
Connect your client
Server setup
TERMINAL
npx -y mcp-gsheets@latestMCP Clients
Claude Code
CLAUDE CODE · JSON
{
"mcpServers": {
"mcp-gsheets": {
"command": "npx",
"args": [
"mcp-gsheets@latest"
],
"env": {
"GOOGLE_PROJECT_ID": "<YOUR_GOOGLE_PROJECT_ID>",
"GOOGLE_APPLICATION_CREDENTIALS": "<YOUR_GOOGLE_APPLICATION_CREDENTIALS>"
}
}
}
}Claude Desktop, Cursor, Cline
JSON
{
"mcpServers": {
"mcp-gsheets": {
"command": "npx",
"args": [
"-y",
"mcp-gsheets@latest"
],
"env": {
"GOOGLE_PROJECT_ID": "<YOUR_GOOGLE_PROJECT_ID>",
"GOOGLE_APPLICATION_CREDENTIALS": "<YOUR_GOOGLE_APPLICATION_CREDENTIALS>"
}
}
}
}Any MCP client — local build
ANY MCP CLIENT — LOCAL BUILD · TERMINAL
git clone https://github.com/freema/mcp-gsheets.git
cd mcp-gsheets
npm install
npm run buildAdd this block to your client's config; <YOUR_PATH_TO_MCP_GSHEETS> is the full path of that folder:
ANY MCP CLIENT — LOCAL BUILD · JSON
{
"mcpServers": {
"mcp-gsheets": {
"command": "node",
"args": [
"<YOUR_PATH_TO_MCP_GSHEETS>/dist/index.js"
],
"env": {
"GOOGLE_PROJECT_ID": "<YOUR_GOOGLE_PROJECT_ID>",
"GOOGLE_APPLICATION_CREDENTIALS": "<YOUR_GOOGLE_APPLICATION_CREDENTIALS>"
}
}
}
}
