MCP Server Google Sheets — service account, valueInputOption, batch updates, named ranges

Service account authentication and client setup

The Google Sheets API uses OAuth 2.0. For server-to-server MCP tools, service accounts are the correct auth method — they don't require user interaction and don't expire like user tokens. Store the service account JSON key in an environment variable (base64-encoded), not as a committed file.

import { google, sheets_v4 } from 'googleapis';
import { z } from 'zod';

// Decode service account key from env — stored as base64 to avoid newline issues
const serviceAccountKey = JSON.parse(
  Buffer.from(process.env.GOOGLE_SERVICE_ACCOUNT_B64!, 'base64').toString('utf-8')
);

// Create an auth client scoped to Sheets read/write
const auth = new google.auth.GoogleAuth({
  credentials: serviceAccountKey,
  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});

// Module-level Sheets client
const sheets: sheets_v4.Sheets = google.sheets({ version: 'v4', auth });

// IMPORTANT: the service account email (serviceAccountKey.client_email) must be
// added as an editor on each spreadsheet via the Sheets sharing dialog.
// Without this, all API calls return 403 "The caller does not have permission".
// The spreadsheet ID is in the URL: https://docs.google.com/spreadsheets/d/{ID}/edit

server.tool(
  'sheets_read_range',
  {
    spreadsheet_id: z.string().min(1),
    range:          z.string().describe('A1 notation or named range, e.g. "Sheet1!A1:D20" or "MyNamedRange"'),
    render:         z.enum(['FORMATTED_VALUE', 'UNFORMATTED_VALUE', 'FORMULA'])
                     .default('UNFORMATTED_VALUE'),
  },
  async ({ spreadsheet_id, range, render }) => {
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId: spreadsheet_id,
      range,
      // FORMATTED_VALUE: what the user sees (with currency symbols, date formats)
      // UNFORMATTED_VALUE: raw numbers and strings (no locale formatting)
      // FORMULA: the formula text if the cell contains a formula
      valueRenderOption: render,
    });

    const values = response.data.values ?? [];
    return {
      content: [{
        type: 'text',
        text: JSON.stringify({
          range:  response.data.range,
          values,
          rows:   values.length,
          cols:   values[0]?.length ?? 0,
        }),
      }],
    };
  }
);

The range field accepts A1 notation with an optional sheet name prefix (Sheet1!A1:D20) or a named range defined in the spreadsheet's Data → Named Ranges menu. If you omit the sheet name in A1 notation, the API reads from the first sheet. Always include the sheet name to avoid ambiguity when the spreadsheet has multiple sheets.

Writing values: USER_ENTERED vs RAW and append vs update

The valueInputOption controls how Google Sheets interprets the strings you write. USER_ENTERED mimics a user typing the value: strings starting with = become formulas, numbers are parsed, dates auto-detect common formats. RAW stores the string exactly as provided — the only safe option when writing LLM-generated content that might accidentally contain formula-like strings.

server.tool(
  'sheets_write_range',
  {
    spreadsheet_id: z.string(),
    range:          z.string().describe('A1 notation range to write to, e.g. "Sheet1!A2:C5"'),
    values:         z.array(z.array(z.union([z.string(), z.number(), z.null()])))
                     .describe('2D array: outer array = rows, inner array = columns'),
    input_option:   z.enum(['USER_ENTERED', 'RAW']).default('RAW'),
  },
  async ({ spreadsheet_id, range, values, input_option }) => {
    const response = await sheets.spreadsheets.values.update({
      spreadsheetId:    spreadsheet_id,
      range,
      valueInputOption: input_option,
      requestBody: { values },
    });

    return {
      content: [{
        type: 'text',
        text: JSON.stringify({
          updatedRange:  response.data.updatedRange,
          updatedRows:   response.data.updatedRows,
          updatedCols:   response.data.updatedColumns,
          updatedCells:  response.data.updatedCells,
        }),
      }],
    };
  }
);

// Append: adds rows AFTER the last row with data in the range
// Use this for log-style inserts where you don't know the next empty row
server.tool(
  'sheets_append_rows',
  {
    spreadsheet_id: z.string(),
    range:          z.string().describe('Range to detect the table end, e.g. "Sheet1!A:Z"'),
    rows:           z.array(z.array(z.union([z.string(), z.number(), z.null()]))),
  },
  async ({ spreadsheet_id, range, rows }) => {
    const response = await sheets.spreadsheets.values.append({
      spreadsheetId:    spreadsheet_id,
      range,
      valueInputOption: 'RAW',
      // INSERT_ROWS: inserts new rows for the appended data (safe — never overwrites)
      // OVERWRITE: overwrites adjacent cells if there's data (dangerous)
      insertDataOption: 'INSERT_ROWS',
      requestBody: { values: rows },
    });

    return {
      content: [{
        type: 'text',
        text: JSON.stringify({
          updatedRange: response.data.updates?.updatedRange,
          updatedRows:  response.data.updates?.updatedRows,
        }),
      }],
    };
  }
);

// Batch write: multiple ranges in a single API call
// Reduces quota usage vs one call per range
server.tool(
  'sheets_batch_write',
  {
    spreadsheet_id: z.string(),
    writes: z.array(z.object({
      range:  z.string(),
      values: z.array(z.array(z.union([z.string(), z.number(), z.null()]))),
    })).min(1).max(20),
  },
  async ({ spreadsheet_id, writes }) => {
    const response = await sheets.spreadsheets.values.batchUpdate({
      spreadsheetId: spreadsheet_id,
      requestBody: {
        valueInputOption: 'RAW',
        data: writes.map(w => ({ range: w.range, values: w.values })),
      },
    });

    return {
      content: [{
        type: 'text',
        text: JSON.stringify({
          totalUpdatedCells: response.data.totalUpdatedCells,
          updatedRanges:     response.data.responses?.map(r => r.updatedRange),
        }),
      }],
    };
  }
);

Named ranges and sheet metadata

Named ranges are defined once in the spreadsheet and can be referenced in API calls by name instead of A1 notation. They survive column insertions, row deletions, and sheet renames that would silently shift A1-notation references. The spreadsheets.get endpoint returns all defined named ranges along with their current resolved ranges.

server.tool(
  'sheets_list_named_ranges',
  { spreadsheet_id: z.string() },
  async ({ spreadsheet_id }) => {
    const meta = await sheets.spreadsheets.get({
      spreadsheetId: spreadsheet_id,
      // includeGridData: false — don't fetch cell data, just metadata
      includeGridData: false,
    });

    const namedRanges = (meta.data.namedRanges ?? []).map(nr => ({
      name:        nr.name,
      namedRangeId: nr.namedRangeId,
      range: {
        sheetId:          nr.range?.sheetId,
        startRowIndex:    nr.range?.startRowIndex,
        endRowIndex:      nr.range?.endRowIndex,
        startColumnIndex: nr.range?.startColumnIndex,
        endColumnIndex:   nr.range?.endColumnIndex,
      },
    }));

    const sheets_list = (meta.data.sheets ?? []).map(s => ({
      sheetId:    s.properties?.sheetId,
      title:      s.properties?.title,
      index:      s.properties?.index,
      rowCount:   s.properties?.gridProperties?.rowCount,
      columnCount: s.properties?.gridProperties?.columnCount,
    }));

    return {
      content: [{
        type: 'text',
        text: JSON.stringify({ namedRanges, sheets: sheets_list }),
      }],
    };
  }
);

// Structural operations use spreadsheets.batchUpdate (NOT values.batchUpdate)
// — add a new sheet, set column widths, apply number formats
server.tool(
  'sheets_add_sheet',
  {
    spreadsheet_id: z.string(),
    title:          z.string().min(1).max(100),
    rows:           z.number().int().min(1).max(10000).default(1000),
    columns:        z.number().int().min(1).max(500).default(26),
  },
  async ({ spreadsheet_id, title, rows, columns }) => {
    // spreadsheets.batchUpdate — structural requests (add/delete sheets, formatting)
    // NOT the same as spreadsheets.values.batchUpdate (cell data writes)
    const response = await sheets.spreadsheets.batchUpdate({
      spreadsheetId: spreadsheet_id,
      requestBody: {
        requests: [{
          addSheet: {
            properties: {
              title,
              gridProperties: { rowCount: rows, columnCount: columns },
            },
          },
        }],
      },
    });

    const addedSheet = response.data.replies?.[0]?.addSheet?.properties;
    return {
      content: [{
        type: 'text',
        text: JSON.stringify({
          sheetId: addedSheet?.sheetId,
          title:   addedSheet?.title,
          index:   addedSheet?.index,
        }),
      }],
    };
  }
);

The key distinction to internalize: spreadsheets.batchUpdate takes a requests array of structural operations (AddSheetRequest, UpdateCellsRequest for formatting, DeleteDimensionRequest). spreadsheets.values.batchUpdate takes a data array of range-value pairs. Both are called "batchUpdate" in the API but they live under different resource paths and accept completely different request bodies.

Quota management and error handling

The Sheets API quota is 100 read requests and 100 write requests per 100 seconds per Google account (service account counts as one account). For high-frequency MCP tools that read or write on every tool call, the quota is generous — but bulk operations that loop without delays can exhaust it in seconds. The 429 response from Google includes a Retry-After header you should respect.

async function sheetsRequestWithRetry<T>(
  fn: () => Promise<T>,
  maxRetries = 3
): Promise<T> {
  let attempt = 0;

  while (true) {
    try {
      return await fn();
    } catch (err: any) {
      // Google API errors have err.code (HTTP status) and err.errors array
      const status = err.code ?? err.status;

      if (status === 429 || status === 503) {
        if (attempt >= maxRetries) throw err;

        // Respect Retry-After if provided; else exponential backoff
        const retryAfter = err.response?.headers?.['retry-after'];
        const waitMs = retryAfter
          ? parseInt(retryAfter) * 1000
          : Math.min(1000 * 2 ** attempt, 32_000);

        await new Promise(r => setTimeout(r, waitMs));
        attempt++;
        continue;
      }

      if (status === 403) {
        // Insufficient permission — check service account was shared on the spreadsheet
        throw new Error(
          `Google Sheets 403: ensure service account ${serviceAccountKey.client_email} ` +
          `has been added as an Editor on spreadsheet ${err.config?.url ?? ''}`
        );
      }

      throw err;
    }
  }
}

// Example usage in a tool handler
server.tool(
  'sheets_safe_read',
  { spreadsheet_id: z.string(), range: z.string() },
  async ({ spreadsheet_id, range }) => {
    const response = await sheetsRequestWithRetry(() =>
      sheets.spreadsheets.values.get({
        spreadsheetId:     spreadsheet_id,
        range,
        valueRenderOption: 'UNFORMATTED_VALUE',
      })
    );

    return {
      content: [{ type: 'text', text: JSON.stringify(response.data.values ?? []) }],
    };
  }
);