Skip to content

sheets-formatting.md

Latest commit

 

History

History
161 lines (122 loc) · 5.49 KB

sheets-formatting.md

File metadata and controls

161 lines (122 loc) · 5.49 KB

Sheets Formatting

read_when:

  • Adding or reviewing Google Sheets formatting commands.
  • Using conditional formatting or alternating color banding from automation.

gog sheets format applies direct cell formatting. Use the advanced formatting commands when the spreadsheet should keep applying styling as data changes.

For direct formatting, --format-json is enough for ordinary formats; gog infers the Sheets field mask from the JSON:

gog sheets format "$spreadsheet_id" 'Sheet1!A1:C1' \
  --format-json '{"textFormat":{"bold":true},"backgroundColor":{"red":0.85,"green":0.9,"blue":1}}'

Use --format-fields when you want an explicit field mask or need to force a specific nested field:

gog sheets format "$spreadsheet_id" 'Sheet1!A1:C1' \
  --format-json '{"textFormat":{"bold":false}}' \
  --format-fields textFormat.bold

Data Validation

Set dropdown, checkbox, number, date, range, or custom-formula validation:

gog sheets validation set "$spreadsheet_id" 'Sheet1!B2:B100' \
  --type ONE_OF_LIST --value Open --value Done --strict
gog sheets validation set "$spreadsheet_id" 'Sheet1!C2:C100' --type BOOLEAN
gog sheets validation set "$spreadsheet_id" 'Sheet1!D2:D100' \
  --type NUMBER_BETWEEN --value 1 --value 100
gog sheets validation set "$spreadsheet_id" 'Sheet1!E2:E100' \
  --type ONE_OF_RANGE --value 'Lists!A2:A20'

Inspect or clear rules:

gog sheets validation get "$spreadsheet_id" 'Sheet1!B2:E100' --json
gog sheets validation clear "$spreadsheet_id" 'Sheet1!B2:E100'

Google Sheets stores table dropdowns on the table column rather than ordinary cells. When a target fully selects a table-managed dropdown column, pass --filtered-rows-included to set or clear it. Clearing converts that table column to text. Validation-only copy/paste preserves table-managed dropdown definitions when copying from a table column into ordinary cells.

Cell Links

Write a single whole-cell link, multiple independently linked text runs, or a batch of cells:

gog sheets links set "$spreadsheet_id" 'Sheet1!A1' \
  https://example.com "Project"
gog sheets links set "$spreadsheet_id" 'Sheet1!B1' \
  --runs-json '[{"text":"Docs","uri":"https://docs.example.com"},{"text":" / "},{"text":"Issues","uri":"https://issues.example.com"}]'
gog sheets links set "$spreadsheet_id" \
  --cells-json '[{"cell":"Sheet1!C1","url":"mailto:owner@example.com","text":"Owner"}]'

Read links back from a range:

gog sheets links get "$spreadsheet_id" 'Sheet1!A1:C1' --json

The read command emits one result per URL, so a rich-text cell can appear more than once with the same A1 address.

Conditional Formats

Add a rule to a sheet-qualified range:

gog sheets conditional-format add "$spreadsheet_id" 'Sheet1!A2:C' \
  --type text-eq \
  --expr done \
  --format-json '{"backgroundColor":{"red":0.85,"green":0.94,"blue":0.82}}'

Supported rule shortcuts:

  • text-eq, text-contains, text-starts-with, text-ends-with
  • number-eq, number-gt, number-gte, number-lt, number-lte
  • blank, not-blank
  • custom-formula

Use --format-fields when the JSON contains zero or false values that must be sent explicitly:

gog sheets conditional-format add "$spreadsheet_id" 'Sheet1!A2:C' \
  --type custom-formula \
  --expr '=$C2=TRUE' \
  --format-json '{"textFormat":{"bold":false}}' \
  --format-fields textFormat.bold

List rules:

gog sheets conditional-format list "$spreadsheet_id" --json
gog sheets conditional-format list "$spreadsheet_id" --sheet Sheet1

Remove one rule by index, or all rules from a sheet:

gog sheets conditional-format clear "$spreadsheet_id" --sheet Sheet1 --index 0 --force
gog sheets conditional-format clear "$spreadsheet_id" --sheet Sheet1 --all --force

clear --all deletes from the highest index down so lower indexes do not shift under the batch request.

Banding

Apply default alternating row colors:

gog sheets banding set "$spreadsheet_id" 'Sheet1!A1:C20'

Override row or column banding with Sheets API BandingProperties JSON:

gog sheets banding set "$spreadsheet_id" 'Sheet1!A1:C20' \
  --row-properties-json '{"firstBandColorStyle":{"rgbColor":{"red":1,"green":1,"blue":1}},"secondBandColorStyle":{"rgbColor":{"red":0.96,"green":0.98,"blue":1}}}'

List and clear banded ranges:

gog sheets banding list "$spreadsheet_id" --json
gog sheets banding clear "$spreadsheet_id" --id 123456 --force
gog sheets banding clear "$spreadsheet_id" --sheet Sheet1 --all --force

Command Pages