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

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, 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, which handles OAuth authentication and scopes required by the Google Sheets API.

Shared utilities in 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, 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:

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:

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. The SheetsFormatCmd.Run method applies CellFormat structures to specified ranges through the RepeatCellRequest API operation.

Command Structure:

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:

{
  "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 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:

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:

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:

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

Make the header row bold:

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:

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 \
    [email protected] \
    --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 and specialized sub-commands.
• Use gogcli sheets insert (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) to apply CellFormat structures via JSON, supporting text styling, background colors, and number formats.
• The ForceSendFields mechanism in 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, 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, the mask is normalized to userEnteredFormat.* paths and combined with ForceSendFields logic in 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 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 and sheets_format.go.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}