Skip to content

Commit 4105be8

Browse files
steipetesteipete
authored
Merge pull request #72 from nilzzzzzz/feature/sheets-format-command
feat(sheets): add format command
2 parents 6806c8b + 3a62acf commit 4105be8

6 files changed

Lines changed: 240 additions & 0 deletions

File tree

‎CHANGELOG.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@
3232
### Added
3333

3434
- Auth: Workspace service accounts (domain-wide delegation) for all services via `gog auth service-account ...` (preferred when configured). (#54) — thanks @pvieito.
35+
- Sheets: `gog sheets format` applies cell formatting via `--format-json` + `--format-fields`.
3536

3637
### Fixed
3738

‎README.md‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -610,6 +610,7 @@ gog slides export <presentationId> --format pdf --out ./deck.pdf
610610
# Sheets
611611
gog sheets copy <spreadsheetId> "My Sheet Copy"
612612
gog sheets export <spreadsheetId> --format pdf --out ./sheet.pdf
613+
gog sheets format <spreadsheetId> 'Sheet1!A1:B2' --format-json '{"textFormat":{"bold":true}}' --format-fields 'userEnteredFormat.textFormat.bold'
613614
```
614615

615616
### Contacts
@@ -679,6 +680,9 @@ gog sheets append <spreadsheetId> 'Sheet1!A:C' 'new|row|data'
679680
gog sheets append <spreadsheetId> 'Sheet1!A:C' 'new|row|data' --copy-validation-from 'Sheet1!A2:C2'
680681
gog sheets clear <spreadsheetId> 'Sheet1!A1:B10'
681682

683+
# Format
684+
gog sheets format <spreadsheetId> 'Sheet1!A1:B2' --format-json '{"textFormat":{"bold":true}}' --format-fields 'userEnteredFormat.textFormat.bold'
685+
682686
# Create
683687
gog sheets create "My New Spreadsheet" --sheets "Sheet1,Sheet2"
684688
```

‎internal/cmd/sheets.go‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@ type SheetsCmd struct {
2828
Update SheetsUpdateCmd `cmd:"" name:"update" help:"Update values in a range"`
2929
Append SheetsAppendCmd `cmd:"" name:"append" help:"Append values to a range"`
3030
Clear SheetsClearCmd `cmd:"" name:"clear" help:"Clear values in a range"`
31+
Format SheetsFormatCmd `cmd:"" name:"format" help:"Apply cell formatting to a range"`
3132
Metadata SheetsMetadataCmd `cmd:"" name:"metadata" help:"Get spreadsheet metadata"`
3233
Create SheetsCreateCmd `cmd:"" name:"create" help:"Create a new spreadsheet"`
3334
Copy SheetsCopyCmd `cmd:"" name:"copy" help:"Copy a Google Sheet"`

‎internal/cmd/sheets_format.go‎

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,100 @@
1+
package cmd
2+
3+
import (
4+
"context"
5+
"encoding/json"
6+
"fmt"
7+
"os"
8+
"strings"
9+
10+
"google.golang.org/api/sheets/v4"
11+
12+
"github.com/steipete/gogcli/internal/outfmt"
13+
"github.com/steipete/gogcli/internal/ui"
14+
)
15+
16+
type SheetsFormatCmd struct {
17+
SpreadsheetID string `arg:"" name:"spreadsheetId" help:"Spreadsheet ID"`
18+
Range string `arg:"" name:"range" help:"Range (eg. Sheet1!A1:B2)"`
19+
FormatJSON string `name:"format-json" help:"Cell format as JSON (Sheets API CellFormat)"`
20+
FormatFields string `name:"format-fields" help:"Format field mask (eg. userEnteredFormat.textFormat.bold)"`
21+
}
22+
23+
func (c *SheetsFormatCmd) Run(ctx context.Context, flags *RootFlags) error {
24+
u := ui.FromContext(ctx)
25+
account, err := requireAccount(flags)
26+
if err != nil {
27+
return err
28+
}
29+
30+
spreadsheetID := strings.TrimSpace(c.SpreadsheetID)
31+
rangeSpec := cleanRange(c.Range)
32+
if spreadsheetID == "" {
33+
return usage("empty spreadsheetId")
34+
}
35+
if strings.TrimSpace(rangeSpec) == "" {
36+
return usage("empty range")
37+
}
38+
if strings.TrimSpace(c.FormatJSON) == "" {
39+
return fmt.Errorf("provide format JSON via --format-json")
40+
}
41+
formatFields := strings.TrimSpace(c.FormatFields)
42+
if formatFields == "" {
43+
return fmt.Errorf("provide format fields via --format-fields")
44+
}
45+
46+
var format sheets.CellFormat
47+
if err := json.Unmarshal([]byte(c.FormatJSON), &format); err != nil {
48+
return fmt.Errorf("invalid format JSON: %w", err)
49+
}
50+
51+
rangeInfo, err := parseA1Range(rangeSpec)
52+
if err != nil {
53+
return err
54+
}
55+
if strings.TrimSpace(rangeInfo.SheetName) == "" {
56+
return fmt.Errorf("format range must include a sheet name")
57+
}
58+
59+
svc, err := newSheetsService(ctx, account)
60+
if err != nil {
61+
return err
62+
}
63+
64+
sheetIDs, err := fetchSheetIDMap(ctx, svc, spreadsheetID)
65+
if err != nil {
66+
return err
67+
}
68+
sheetID, ok := sheetIDs[rangeInfo.SheetName]
69+
if !ok {
70+
return fmt.Errorf("unknown sheet %q in format range", rangeInfo.SheetName)
71+
}
72+
73+
req := &sheets.BatchUpdateSpreadsheetRequest{
74+
Requests: []*sheets.Request{
75+
{
76+
RepeatCell: &sheets.RepeatCellRequest{
77+
Range: toGridRange(rangeInfo, sheetID),
78+
Cell: &sheets.CellData{
79+
UserEnteredFormat: &format,
80+
},
81+
Fields: formatFields,
82+
},
83+
},
84+
},
85+
}
86+
87+
if _, err := svc.Spreadsheets.BatchUpdate(spreadsheetID, req).Do(); err != nil {
88+
return err
89+
}
90+
91+
if outfmt.IsJSON(ctx) {
92+
return outfmt.WriteJSON(os.Stdout, map[string]any{
93+
"range": rangeSpec,
94+
"fields": formatFields,
95+
})
96+
}
97+
98+
u.Out().Printf("Formatted %s", rangeSpec)
99+
return nil
100+
}

‎internal/cmd/sheets_format_test.go‎

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,106 @@
1+
package cmd
2+
3+
import (
4+
"context"
5+
"encoding/json"
6+
"io"
7+
"net/http"
8+
"net/http/httptest"
9+
"strings"
10+
"testing"
11+
12+
"google.golang.org/api/option"
13+
"google.golang.org/api/sheets/v4"
14+
15+
"github.com/steipete/gogcli/internal/ui"
16+
)
17+
18+
func TestSheetsFormatCmd(t *testing.T) {
19+
origNew := newSheetsService
20+
t.Cleanup(func() { newSheetsService = origNew })
21+
22+
var gotRepeat *sheets.RepeatCellRequest
23+
24+
srv := httptest.NewServer(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
25+
path := strings.TrimPrefix(r.URL.Path, "/sheets/v4")
26+
path = strings.TrimPrefix(path, "/v4")
27+
switch {
28+
case strings.HasPrefix(path, "/spreadsheets/s1") && r.Method == http.MethodGet:
29+
w.Header().Set("Content-Type", "application/json")
30+
_ = json.NewEncoder(w).Encode(map[string]any{
31+
"spreadsheetId": "s1",
32+
"sheets": []map[string]any{
33+
{"properties": map[string]any{"sheetId": 42, "title": "Sheet1"}},
34+
},
35+
})
36+
return
37+
case strings.Contains(path, "/spreadsheets/s1:batchUpdate") && r.Method == http.MethodPost:
38+
var req sheets.BatchUpdateSpreadsheetRequest
39+
if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
40+
t.Fatalf("decode batchUpdate: %v", err)
41+
}
42+
if len(req.Requests) != 1 || req.Requests[0].RepeatCell == nil {
43+
t.Fatalf("expected repeatCell request, got %#v", req.Requests)
44+
}
45+
gotRepeat = req.Requests[0].RepeatCell
46+
w.Header().Set("Content-Type", "application/json")
47+
_ = json.NewEncoder(w).Encode(map[string]any{})
48+
return
49+
default:
50+
http.NotFound(w, r)
51+
return
52+
}
53+
}))
54+
defer srv.Close()
55+
56+
svc, err := sheets.NewService(context.Background(),
57+
option.WithoutAuthentication(),
58+
option.WithHTTPClient(srv.Client()),
59+
option.WithEndpoint(srv.URL+"/"),
60+
)
61+
if err != nil {
62+
t.Fatalf("NewService: %v", err)
63+
}
64+
newSheetsService = func(context.Context, string) (*sheets.Service, error) { return svc, nil }
65+
66+
flags := &RootFlags{Account: "a@b.com"}
67+
u, uiErr := ui.New(ui.Options{Stdout: io.Discard, Stderr: io.Discard, Color: "never"})
68+
if uiErr != nil {
69+
t.Fatalf("ui.New: %v", uiErr)
70+
}
71+
ctx := ui.WithUI(context.Background(), u)
72+
cmd := &SheetsFormatCmd{}
73+
if err := runKong(t, cmd, []string{
74+
"s1",
75+
"Sheet1!B2:C3",
76+
"--format-json", `{"textFormat":{"bold":true}}`,
77+
"--format-fields", "userEnteredFormat.textFormat.bold",
78+
}, ctx, flags); err != nil {
79+
t.Fatalf("format: %v", err)
80+
}
81+
82+
if gotRepeat == nil {
83+
t.Fatal("expected repeatCell request")
84+
}
85+
if gotRepeat.Fields != "userEnteredFormat.textFormat.bold" {
86+
t.Fatalf("unexpected fields: %s", gotRepeat.Fields)
87+
}
88+
if gotRepeat.Range == nil {
89+
t.Fatalf("missing range")
90+
}
91+
if gotRepeat.Range.SheetId != 42 {
92+
t.Fatalf("unexpected sheet id: %d", gotRepeat.Range.SheetId)
93+
}
94+
if gotRepeat.Range.StartRowIndex != 1 || gotRepeat.Range.EndRowIndex != 3 {
95+
t.Fatalf("unexpected row range: %#v", gotRepeat.Range)
96+
}
97+
if gotRepeat.Range.StartColumnIndex != 1 || gotRepeat.Range.EndColumnIndex != 3 {
98+
t.Fatalf("unexpected column range: %#v", gotRepeat.Range)
99+
}
100+
if gotRepeat.Cell == nil || gotRepeat.Cell.UserEnteredFormat == nil || gotRepeat.Cell.UserEnteredFormat.TextFormat == nil {
101+
t.Fatalf("missing format data: %#v", gotRepeat.Cell)
102+
}
103+
if !gotRepeat.Cell.UserEnteredFormat.TextFormat.Bold {
104+
t.Fatalf("expected bold text format, got %#v", gotRepeat.Cell.UserEnteredFormat.TextFormat)
105+
}
106+
}

‎internal/cmd/sheets_validation_more_test.go‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -201,3 +201,31 @@ func TestSheetsClearMetadataCreate_ValidationErrors(t *testing.T) {
201201
t.Fatalf("expected create missing title error")
202202
}
203203
}
204+
205+
func TestSheetsFormat_ValidationErrors(t *testing.T) {
206+
u, uiErr := ui.New(ui.Options{Stdout: io.Discard, Stderr: io.Discard, Color: "never"})
207+
if uiErr != nil {
208+
t.Fatalf("ui.New: %v", uiErr)
209+
}
210+
ctx := ui.WithUI(context.Background(), u)
211+
flags := &RootFlags{Account: "a@b.com"}
212+
213+
if err := (&SheetsFormatCmd{}).Run(ctx, flags); err == nil {
214+
t.Fatalf("expected format missing spreadsheetId error")
215+
}
216+
if err := (&SheetsFormatCmd{SpreadsheetID: "s1"}).Run(ctx, flags); err == nil {
217+
t.Fatalf("expected format missing range error")
218+
}
219+
if err := (&SheetsFormatCmd{SpreadsheetID: "s1", Range: "Sheet1!A1"}).Run(ctx, flags); err == nil {
220+
t.Fatalf("expected format missing format-json error")
221+
}
222+
if err := (&SheetsFormatCmd{SpreadsheetID: "s1", Range: "Sheet1!A1", FormatJSON: "{\"textFormat\":{\"bold\":true}}"}).Run(ctx, flags); err == nil {
223+
t.Fatalf("expected format missing format-fields error")
224+
}
225+
if err := (&SheetsFormatCmd{SpreadsheetID: "s1", Range: "Sheet1!A1", FormatJSON: "nope", FormatFields: "userEnteredFormat.textFormat.bold"}).Run(ctx, flags); err == nil {
226+
t.Fatalf("expected format invalid json error")
227+
}
228+
if err := (&SheetsFormatCmd{SpreadsheetID: "s1", Range: "A1:B2", FormatJSON: "{\"textFormat\":{\"bold\":true}}", FormatFields: "userEnteredFormat.textFormat.bold"}).Run(ctx, flags); err == nil {
229+
t.Fatalf("expected format missing sheet name error")
230+
}
231+
}

0 commit comments

Comments
 (0)