# How to Insert Rows and Format Cells with the gogcli Sheets API

> Learn to insert rows and format cells using the gogcli Sheets API. Easily add rows with gogcli sheets insert and customize cell styles with gogcli sheets format.

- Repository: [Peter Steinberger/gogcli](https://github.com/steipete/gogcli)
- Tags: how-to-guide
- Published: 2026-02-16

---

**Use `gogcli sheets insert` to add rows or columns by specifying the spreadsheet ID, sheet name, dimension type, and start index, and use `gogcli sheets format` with JSON cell format definitions and field masks to apply bold text, colors, or number formats.**

The `gogcli` command-line tool provides a robust interface for automating Google Sheets operations without writing custom API clients. When working with the **gogcli Sheets API**, you can programmatically insert dimensions and apply complex cell formatting through straightforward CLI commands. This guide examines the actual implementation in the `steipete/gogcli` repository to show you how to leverage `sheets insert` and `sheets format` effectively.

## Architecture of the gogcli Sheets Commands

The Sheets functionality in `gogcli` follows a layered architecture that separates CLI parsing from API execution. In [`internal/cmd/sheets.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets.go), the root `SheetsCmd` struct registers sub-commands including `Insert` and `Format`. The service factory variable `newSheetsService` (line 18) delegates to `googleapi.NewSheets` in [`internal/googleapi/sheets.go`](https://github.com/steipete/gogcli/blob/main/internal/googleapi/sheets.go), which handles OAuth authentication and scopes required by the Google Sheets API.

Shared utilities in [`internal/cmd/sheets_validation.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_validation.go) provide critical helper functions:
- `fetchSheetIDMap` resolves human-readable sheet titles to internal `SheetId` integers
- `parseSheetRange` converts A1-style notation (e.g., `Sheet1!A1:B2`) into `sheets.GridRange` objects

## Inserting Rows and Columns with gogcli sheets insert

The `sheets insert` command, implemented in [`internal/cmd/sheets_insert.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_insert.go), creates empty rows or columns at specified positions. The `SheetsInsertCmd.Run` method constructs an `InsertDimensionRequest` that communicates directly with the Google Sheets API.

**Command Structure:**

```bash
gogcli sheets insert <spreadsheetId> <sheet> <dimension> <start> [--count=n] [--after]

```

**Key Parameters:**
- `<dimension>`: Either `rows` or `cols`
- `<start>`: 1-based index where insertion occurs
- `--count`: Number of rows/columns to insert (default 1)
- `--after`: Shift insertion point by one (insert after the specified index)

**Implementation Details:**

The command translates 1-based user input to 0-based API indices. When inserting before row 5 (`start = 5`), the code sets `startIndex = 4` and `endIndex = 7` for a count of 3. The `InsertDimensionRequest` includes:

```go
InsertDimension: &sheets.InsertDimensionRequest{
    Range: &sheets.DimensionRange{
        SheetId:    sheetID,
        Dimension:  apiDimension, // "ROWS" or "COLUMNS"
        StartIndex: startIndex,
        EndIndex:   endIndex,
    },
    InheritFromBefore: inheritFromBefore,
},

```

## Formatting Cells with gogcli sheets format

Cell formatting in `gogcli` uses the `sheets format` command defined in [`internal/cmd/sheets_format.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_format.go). The `SheetsFormatCmd.Run` method applies `CellFormat` structures to specified ranges through the `RepeatCellRequest` API operation.

**Command Structure:**

```bash
gogcli sheets format <spreadsheetId> <range> --format-json='<JSON>' --format-fields='<mask>'

```

**JSON Format Definition:**

The `--format-json` parameter accepts either an inline JSON string or a file path prefixed with `@`. The JSON maps to the Google Sheets API `CellFormat` structure:

```json
{
  "textFormat": {"bold": true},
  "backgroundColor": {"red": 0.9, "green": 0.9, "blue": 0.9},
  "numberFormat": {"type": "CURRENCY", "pattern": "$#,##0.00"}
}

```

**Field Mask Handling:**

The `--format-fields` parameter specifies which fields to update, supporting both full paths (`userEnteredFormat.textFormat.bold`) and short forms (`textFormat.bold`). The helper functions in [`internal/cmd/sheets_format_fields.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_format_fields.go) normalize these masks:

- `normalizeFormatMask` converts short paths to full `userEnteredFormat.*` paths
- `applyForceSendFields` ensures zero-value fields are sent by populating `ForceSendFields` slices

The Google Sheets API ignores zero-value fields unless explicitly listed in `ForceSendFields`. The `forceSendJSONField` function resolves dot-paths, allocates missing nested structs, and appends the correct field names to force-send lists.

**Request Construction:**

The final `RepeatCellRequest` includes:

```go
RepeatCell: &sheets.RepeatCellRequest{
    Range: gridRange,
    Cell: &sheets.CellData{
        UserEnteredFormat: &format,
    },
    Fields: formatFields,
},

```

## Practical Code Examples

**Insert 3 rows before row 5 in the "Budget" sheet:**

```bash
gogcli sheets insert 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upg \
    Budget rows 5 --count=3

```

*Implementation detail:* The command sets `startIndex = 4` and `endIndex = 7` (0-based) and sends an `InsertDimensionRequest` with `Dimension: "ROWS"`.

**Insert a single column after column B:**

```bash
gogcli sheets insert 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upg \
    Sheet1 cols 2 --after

```

**Make the header row bold:**

```bash
gogcli sheets format 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upg \
    Sheet1!A1:Z1 \
    --format-json='{"textFormat":{"bold":true}}' \
    --format-fields='textFormat.bold'

```

*Technical note:* The mask `textFormat.bold` normalizes to `userEnteredFormat.textFormat.bold`. The `applyForceSendFields` helper ensures the `Bold` field appears in `ForceSendFields`, guaranteeing the API updates the cell style.

**Apply background color and currency format from a file:**

```bash
cat > format.json <<'EOF'
{
  "backgroundColor": {"red":0.9,"green":0.9,"blue":0.9},
  "numberFormat": {"type":"CURRENCY","pattern":"$#,##0.00"}
}
EOF

gogcli sheets format 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upg \
    Expenses!B2:B20 \
    --format-json=@format.json \
    --format-fields='backgroundColor, numberFormat.type, numberFormat.pattern'

```

## Summary

- The **gogcli Sheets API** provides direct CLI access to Google Sheets operations through [`internal/cmd/sheets.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets.go) and specialized sub-commands.
- Use **`gogcli sheets insert`** ([`internal/cmd/sheets_insert.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_insert.go)) to add rows or columns by specifying 1-based indices, with optional `--after` positioning and `--count` for batch insertion.
- Use **`gogcli sheets format`** ([`internal/cmd/sheets_format.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_format.go)) to apply `CellFormat` structures via JSON, supporting text styling, background colors, and number formats.
- The **`ForceSendFields`** mechanism in [`internal/cmd/sheets_format_fields.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_format_fields.go) ensures zero-value fields (like `bold: false` or `red: 0`) are transmitted to the API, preventing silent ignore of formatting directives.
- Both commands support **`--dry-run`** mode for safe payload inspection before API execution.

## Frequently Asked Questions

### How does gogcli handle 1-based vs 0-based indexing when inserting rows?

The `gogcli sheets insert` command accepts 1-based indices from users for consistency with spreadsheet UI conventions. In [`internal/cmd/sheets_insert.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_insert.go), the `SheetsInsertCmd.Run` method converts these to 0-based indices for the Google Sheets API by subtracting 1 from the start position. If the `--after` flag is used, the insertion point shifts by one additional index to place new dimensions after the specified row or column.

### Why does the format command require both --format-json and --format-fields?

The `--format-json` parameter provides the complete `CellFormat` structure values, while `--format-fields` acts as a field mask telling the API which specific properties to update. This separation prevents unintentional overwriting of existing formatting. According to [`internal/cmd/sheets_format.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_format.go), the mask is normalized to `userEnteredFormat.*` paths and combined with `ForceSendFields` logic in [`internal/cmd/sheets_format_fields.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_format_fields.go) to ensure zero-values are properly transmitted.

### What happens if I omit the --format-fields mask when formatting cells?

Omitting the field mask causes the Google Sheets API to ignore the formatting request or apply unpredictable defaults. The `RepeatCellRequest` constructed in [`internal/cmd/sheets_format.go`](https://github.com/steipete/gogcli/blob/main/internal/cmd/sheets_format.go) requires the `Fields` parameter to specify which cell properties to modify. Without this mask, the API cannot distinguish between intentionally setting a value to zero/false versus leaving the field unspecified, resulting in no visible changes to the spreadsheet.

### Can I preview the API payload before executing insert or format commands?

Yes, both `gogcli sheets insert` and `gogcli sheets format` support the `--dry-run` flag. When enabled, the commands construct the full `BatchUpdateSpreadsheetRequest` payload and output it as formatted JSON without calling `svc.Spreadsheets.BatchUpdate`. This allows you to verify indices, ranges, and format structures before modifying live spreadsheet data, as implemented in both [`sheets_insert.go`](https://github.com/steipete/gogcli/blob/main/sheets_insert.go) and [`sheets_format.go`](https://github.com/steipete/gogcli/blob/main/sheets_format.go).