Toolcog home
Search ⌘ + K

Command Palette

Search for a command to run...

Sign In

Google Sheets is organized around a single primary identifier: the spreadsheetId. Everything you do—reading values, changing structure, searching developer metadata, copying sheets—starts with a spreadsheetId. Within a spreadsheet there are sheets (tabs) identified by a numeric sheetId and a human-facing title (sheet name). Many operations accept A1 notation ranges (which can include a sheet name), and some accept richer data filters or developer metadata references instead of A1 ranges.

Key entities and how they relate

  • Spreadsheet: the top-level resource. Use spreadsheetId for almost every call. The spreadsheet resource (spreadsheets.get) contains sheets[], developerMetadata[], and high-level properties.

  • Sheet (tab): referenced by sheetId (numeric) and title (string). Structural changes (add, delete, reorder, resize) are performed against the spreadsheet via structural requests; many value operations use sheet names inside A1 notation.

  • Value ranges: reading and writing cell content uses A1/R1C1 ranges or data filters. Value operations target a spreadsheetId and a range (or multiple ranges) or a DataFilter payload for filter-based access.

  • Developer metadata: metadata entries attached to the spreadsheet, sheets, or ranges. Each entry has a numeric metadataId you can get or search for; developer metadata is a first-class way to locate ranges without brittle A1 strings.

What to call first (entry points)

  • If you need sheet names, sheetIds, or developer metadata IDs, call spreadsheets.get (omit includeGridData unless you need cell-level grid data). The sheets array in the response gives sheetId, title, index, and existing developer metadata entries.

  • To read cell values only, call spreadsheets.values.get (single range) or spreadsheets.values.batchGet (multiple ranges). These require spreadsheetId and A1/R1C1 range values.

  • To write or update values, decide between single-range updates and batch operations: simple writes use spreadsheets.values.update; multi-range or many-row operations use spreadsheets.values.batchUpdate or spreadsheets.values.append for append semantics.

  • To perform structural or formatting changes (add a sheet, move columns, set frozen rows, add named ranges, create charts), call spreadsheets.batchUpdate with a requests[] array of spreadsheet-level Requests. This endpoint is the entry point for any change that is not purely value-level.

  • To create a brand new spreadsheet, call spreadsheets.create with the desired properties and optional sheets definitions.

Common user tasks — how to accomplish them (operation patterns)

Read values

  • Single range: call spreadsheets.values.get with spreadsheetId and range.
  • Multiple ranges at once: call spreadsheets.values.batchGet with ranges[].
  • When you need formulas, return raw formulas with valueRenderOption=FORMULA. For date/time formatting choices, use dateTimeRenderOption.

Write or update values

  • Replace a range: call spreadsheets.values.update with the range and body containing values. Choose valueInputOption=RAW for literal text or USER_ENTERED to interpret as user input (formulas, dates).
  • Update multiple ranges in one request: call spreadsheets.values.batchUpdate with a data[] array and a single valueInputOption for the whole batch.
  • Append rows to a table: call spreadsheets.values.append with a range that identifies the table and valueInputOption. append will insert after the last row of that logical table. Use insertDataOption=INSERT_ROWS to ensure new rows are added rather than overwriting.
  • If you need the updated values returned, set includeValuesInResponse=true and pick responseValueRenderOption and responseDateTimeRenderOption to control how returned values look.

Clear values

  • Clear a single range: call spreadsheets.values.clear.
  • Clear multiple explicit ranges: call spreadsheets.values.batchClear with ranges[].
  • Clear by data filters: use spreadsheets.values.batchClearByDataFilter and pass a dataFilters[] body.

Structural changes (sheets, columns, formats, named ranges, charts)

  • Bundle structural/formatting changes into one atomic request by calling spreadsheets.batchUpdate with a requests[] array. Each entry is a specific Request (addSheet, deleteSheet, updateSheetProperties, repeatCell, addDeveloperMetadata, etc.). The response returns a replies[] array with one reply per request in order.

Copy a sheet to another spreadsheet

  • Call spreadsheets.sheets.copyTo with spreadsheetId (source) and sheetId. The request body specifies the destination spreadsheet (typically destinationSpreadsheetId). The call returns the new sheet's SheetProperties in the destination (including its new sheetId).

Create a spreadsheet

  • Call spreadsheets.create with a properties object and optional sheets definitions. The response is the created spreadsheet resource including spreadsheetId.

Developer metadata lookup and use

  • To find metadata entries that match criteria, call spreadsheets.developerMetadata.search with a search request; results include metadataId and location. Use spreadsheets.developerMetadata.get with metadataId and spreadsheetId to retrieve a specific entry.
  • Developer metadata can be used in DataFilter payloads to target ranges in subsequent getByDataFilter, batchGetByDataFilter, batchClearByDataFilter, and batchUpdateByDataFilter calls.

Using data filters instead of A1 notation

  • If sheet names change or ranges are dynamic, prefer DataFilters (by developer metadata or grid range) for stability. Endpoints that accept DataFilters include spreadsheets.getByDataFilter, spreadsheets.values.batchGetByDataFilter, spreadsheets.values.batchUpdateByDataFilter, and spreadsheets.values.batchClearByDataFilter.

Non-obvious behaviors and gotchas

  • spreadsheetId is required for nearly every call; fetch it first from the user or by creating a spreadsheet. Use spreadsheets.get when you need sheetId values or to inspect developerMetadata.

  • sheetId vs title: many user requests reference a sheet by name. Some API calls require the numeric sheetId. Use spreadsheets.get to map titles to sheetIds. When referring to a range in A1 notation, include the sheet name (for example, Sheet1!A1:B10). If the sheet name contains spaces or special characters, surround it with single quotes: 'Quarter 1'!A1:B10.

  • Two different update models exist—structural spreadsheets.batchUpdate and value-level spreadsheets.values.*. Do not confuse them:

    • Use spreadsheets.batchUpdate for structural/formatting/chart/metadata requests (it accepts a requests[] array of operations and returns replies[]).
    • Use spreadsheets.values.update / append / batchUpdate / clear for cell values only. Value-batch endpoints take different request shapes and return different response types.

  • spreadsheets.get has an includeGridData flag. Turning it on returns full grid data (cell-level objects including formatting, userEnteredValue, effectiveValue, etc.) and can be very large. If you only need sheet metadata, omit it. For formulas specifically, you can often use spreadsheets.values.get with valueRenderOption=FORMULA instead of includeGridData.

  • spreadsheets.batchUpdate is atomic per call. When you send multiple Requests, the API applies them in order and returns a replies[] array aligned with your requests[]. Use that ordering to correlate replies to requests.

  • spreadsheets.values.append treats the range as a table anchor. It finds the last row of that logical table and appends after it. If your range is not a table, or if you want to ensure new rows are inserted, specify insertDataOption=INSERT_ROWS.

  • valueInputOption matters: RAW writes exactly what you send; USER_ENTERED parses the input like the Sheets UI—formulas and date parsing are applied. Choose deliberately.

  • Response rendering options differ by endpoint:

    • Use valueRenderOption / dateTimeRenderOption on value reads to control returned formatting.
    • Use responseValueRenderOption / responseDateTimeRenderOption on write/append calls when you request returned values.

  • Developer metadata IDs are numeric and created when metadata is added. Search for metadata entries (via search) to obtain metadataId before calling developerMetadata.get.

  • Copying a sheet into another spreadsheet returns the new sheet's SheetProperties (including its sheetId in the destination). If you need that sheetId for subsequent operations, read it from the copy response.

  • Large responses can be trimmed with the fields query parameter. When you only need sheet titles or sheetIds, request only those fields to reduce payload size.

Practical sequencing patterns

  • Inspect spreadsheet: call spreadsheets.get (no includeGridData) to learn sheets[], sheetIds, and developerMetadata[].
  • Read values: call spreadsheets.values.get or batchGet with properly quoted A1 ranges or use data filters found via metadata search.
  • Prepare structural changes and apply them together: build a requests[] array and call spreadsheets.batchUpdate once to apply them atomically.
  • For many value changes across ranges, batch them with spreadsheets.values.batchUpdate to reduce round trips.
  • When copying between spreadsheets, call spreadsheets.sheets.copyTo and use the returned sheetId from the response for further edits in the destination.

Focus on spreadsheetId first, map sheet names to sheetId when needed, choose the correct endpoint family (structural spreadsheets.batchUpdate vs value-level spreadsheets.values.*), and prefer DataFilters/developer metadata when ranges must be robust to sheet renames or moving content.