Sheets Commands

Read and write spreadsheets with Google Sheets API

Overview

The Sheets service provides access to Google Sheets v4 API for reading and writing spreadsheet data, managing sheets, and applying formatting.

API: sheets (v4)

Common Use Cases

• Read and write cell data
• Create and manage spreadsheets
• Append rows to sheets
• Format cells and ranges
• Create charts and pivot tables

Helper Commands

+append

Append rows to a spreadsheet:

Simple values

gws sheets +append --spreadsheet SPREADSHEET_ID --values 'Alice,100,true'

Multiple rows (JSON)

gws sheets +append --spreadsheet SPREADSHEET_ID --json-values '[["Alice",100],["Bob",200]]'

+read

Read values from a range:

gws sheets +read --spreadsheet SPREADSHEET_ID --range 'Sheet1!A1:B10'

Command Examples

Create a Spreadsheet

Empty spreadsheet

gws sheets spreadsheets create --json '{"properties": {"title": "Q1 Budget"}}'

With initial sheets

gws sheets spreadsheets create --json '{
  "properties": {"title": "Sales Report"},
  "sheets": [
    {"properties": {"title": "January"}},
    {"properties": {"title": "February"}}
  ]
}'

Example Response

{
  "spreadsheetId": "1abc...",
  "properties": {
    "title": "Q1 Budget",
    "locale": "en_US",
    "timeZone": "America/Los_Angeles"
  },
  "sheets": [
    {
      "properties": {
        "sheetId": 0,
        "title": "Sheet1",
        "index": 0,
        "gridProperties": {"rowCount": 1000, "columnCount": 26}
      }
    }
  ]
}

Read Values

Single range

gws sheets spreadsheets values get --params '{"spreadsheetId": "SPREADSHEET_ID", "range": "Sheet1!A1:B10"}'

Example Response

{
  "range": "Sheet1!A1:B10",
  "majorDimension": "ROWS",
  "values": [
    ["Name", "Score"],
    ["Alice", "95"],
    ["Bob", "87"],
    ["Charlie", "92"]
  ]
}

Write Values

Update range

gws sheets spreadsheets values update \
  --params '{"spreadsheetId": "SPREADSHEET_ID", "range": "Sheet1!A1:B2", "valueInputOption": "RAW"}' \
  --json '{"values": [["Name", "Score"], ["Alice", "95"]]}'

Append rows

gws sheets spreadsheets values append \
  --params '{"spreadsheetId": "SPREADSHEET_ID", "range": "Sheet1!A1", "valueInputOption": "USER_ENTERED"}' \
  --json '{"values": [["Bob", "87"], ["Charlie", "92"]]}'

Clear Values

gws sheets spreadsheets values clear --params '{"spreadsheetId": "SPREADSHEET_ID", "range": "Sheet1!A1:B10"}'

Batch Update

Multiple operations

gws sheets spreadsheets batchUpdate --params '{"spreadsheetId": "SPREADSHEET_ID"}' --json '{
  "requests": [
    {
      "updateCells": {
        "range": {"sheetId": 0, "startRowIndex": 0, "endRowIndex": 1},
        "rows": [{"values": [{"userEnteredValue": {"stringValue": "Header"}}]}],
        "fields": "userEnteredValue"
      }
    },
    {
      "repeatCell": {
        "range": {"sheetId": 0, "startRowIndex": 0, "endRowIndex": 1},
        "cell": {"userEnteredFormat": {"textFormat": {"bold": true}}},
        "fields": "userEnteredFormat.textFormat.bold"
      }
    }
  ]
}'

Add Sheet

gws sheets spreadsheets batchUpdate --params '{"spreadsheetId": "SPREADSHEET_ID"}' --json '{
  "requests": [
    {
      "addSheet": {
        "properties": {
          "title": "March",
          "gridProperties": {"rowCount": 100, "columnCount": 10}
        }
      }
    }
  ]
}'

Delete Sheet

gws sheets spreadsheets batchUpdate --params '{"spreadsheetId": "SPREADSHEET_ID"}' --json '{
  "requests": [{"deleteSheet": {"sheetId": SHEET_ID}}]
}'

Format Cells

Bold header row

gws sheets spreadsheets batchUpdate --params '{"spreadsheetId": "SPREADSHEET_ID"}' --json '{
  "requests": [{
    "repeatCell": {
      "range": {"sheetId": 0, "startRowIndex": 0, "endRowIndex": 1},
      "cell": {"userEnteredFormat": {"textFormat": {"bold": true}}},
      "fields": "userEnteredFormat.textFormat.bold"
    }
  }]
}'

Set column width

gws sheets spreadsheets batchUpdate --params '{"spreadsheetId": "SPREADSHEET_ID"}' --json '{
  "requests": [{
    "updateDimensionProperties": {
      "range": {"sheetId": 0, "dimension": "COLUMNS", "startIndex": 0, "endIndex": 1},
      "properties": {"pixelSize": 200},
      "fields": "pixelSize"
    }
  }]
}'

Create Named Range

gws sheets spreadsheets batchUpdate --params '{"spreadsheetId": "SPREADSHEET_ID"}' --json '{
  "requests": [{
    "addNamedRange": {
      "namedRange": {
        "name": "SalesData",
        "range": {"sheetId": 0, "startRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 3}
      }
    }
  }]
}'

Sort Range

gws sheets spreadsheets batchUpdate --params '{"spreadsheetId": "SPREADSHEET_ID"}' --json '{
  "requests": [{
    "sortRange": {
      "range": {"sheetId": 0, "startRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 3},
      "sortSpecs": [{"dimensionIndex": 1, "sortOrder": "DESCENDING"}]
    }
  }]
}'

Get Spreadsheet Metadata

gws sheets spreadsheets get --params '{"spreadsheetId": "SPREADSHEET_ID"}'

Value Input Options

When writing data, the valueInputOption parameter controls how values are interpreted:

• RAW: Values are stored as-is (strings remain strings)
• USER_ENTERED: Values are parsed as if typed by a user (formulas, dates, numbers auto-detected)

Store as string

gws sheets spreadsheets values update \
  --params '{"spreadsheetId": "ID", "range": "A1", "valueInputOption": "RAW"}' \
  --json '{"values": [["=SUM(B1:B10)"]]}'

Parse as formula

gws sheets spreadsheets values update \
  --params '{"spreadsheetId": "ID", "range": "A1", "valueInputOption": "USER_ENTERED"}' \
  --json '{"values": [["=SUM(B1:B10)"]]}'

Resources

• spreadsheets - Spreadsheet operations
• spreadsheets.values - Read/write cell values
• spreadsheets.sheets - Sheet management
• spreadsheets.developerMetadata - Custom metadata

Use gws sheets <resource> --help to see all available methods.

Related Commands

Drive

Manage spreadsheet files

Docs

Work with documents