larksuite--cli/shortcuts/sheets/backward/lark_sheets_cell_data.go

// Copyright (c) 2026 Lark Technologies Pte. Ltd.
// SPDX-License-Identifier: MIT

package backward

import (
	"context"
	"encoding/json"
	"fmt"

	"github.com/larksuite/cli/errs"
	"github.com/larksuite/cli/internal/validate"
	"github.com/larksuite/cli/shortcuts/common"
)

func parseValues2DJSON(raw string) ([][]interface{}, error) {
	var rows [][]interface{}
	if err := json.Unmarshal([]byte(raw), &rows); err != nil {
		return nil, errs.NewValidationError(errs.SubtypeInvalidArgument, "--values invalid JSON, must be a 2D array").WithParam("--values")
	}
	if rows == nil {
		return nil, errs.NewValidationError(errs.SubtypeInvalidArgument, "--values invalid JSON, must be a 2D array").WithParam("--values")
	}
	return rows, nil
}

var SheetRead = common.Shortcut{
	Service:     "sheets",
	Command:     "+read",
	Description: "Read spreadsheet cell values",
	Risk:        "read",
	Scopes:      []string{"sheets:spreadsheet:read"},
	AuthTypes:   []string{"user", "bot"},
	Flags: []common.Flag{
		{Name: "url", Desc: "spreadsheet URL"},
		{Name: "spreadsheet-token", Desc: "spreadsheet token"},
		{Name: "range", Desc: "read range (<sheetId>!A1:D10, A1:D10 with --sheet-id, or a single cell like C2)"},
		{Name: "sheet-id", Desc: "sheet ID"},
		{Name: "value-render-option", Desc: "render option: ToString|FormattedValue|Formula|UnformattedValue"},
	},
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		if _, err := validateSheetManageToken(runtime); err != nil {
			return err
		}
		if err := validateSheetRangeInput(runtime.Str("sheet-id"), runtime.Str("range")); err != nil {
			return err
		}
		if r := runtime.Str("range"); r != "" {
			if rangeSheetID, _, ok := splitSheetRange(r); ok && runtime.Str("sheet-id") != "" && rangeSheetID != runtime.Str("sheet-id") {
				return errs.NewValidationError(errs.SubtypeInvalidArgument, "--range sheet ID %q does not match --sheet-id %q", rangeSheetID, runtime.Str("sheet-id")).WithParam("--range")
			}
		}
		return nil
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		token, _ := validateSheetManageToken(runtime)
		readRange := runtime.Str("range")
		if readRange == "" {
			// Sheet-only selector: pass the bare sheet id through verbatim.
			// Routing it via the range normalizer mangles ids that look
			// A1-ish (e.g. "shtABC123" -> "shtABC123!shtABC123:shtABC123").
			readRange = runtime.Str("sheet-id")
		} else {
			readRange = normalizePointRange(runtime.Str("sheet-id"), readRange)
		}
		return common.NewDryRunAPI().
			GET("/open-apis/sheets/v2/spreadsheets/:token/values/:range").
			Set("token", token).Set("range", readRange)
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token, _ := validateSheetManageToken(runtime)

		readRange := runtime.Str("range")
		if readRange == "" {
			// Sheet-only selector: keep the resolved sheet id verbatim (see DryRun).
			readRange = runtime.Str("sheet-id")
			if readRange == "" {
				var err error
				readRange, err = getFirstSheetID(runtime, token)
				if err != nil {
					return err
				}
			}
		} else {
			readRange = normalizePointRange(runtime.Str("sheet-id"), readRange)
		}

		params := map[string]interface{}{}
		renderOption := runtime.Str("value-render-option")
		if renderOption != "" {
			params["valueRenderOption"] = renderOption
		}

		data, err := runtime.CallAPITyped("GET", fmt.Sprintf("/open-apis/sheets/v2/spreadsheets/%s/values/%s", validate.EncodePathSegment(token), validate.EncodePathSegment(readRange)), params, nil)
		if err != nil {
			return err
		}
		runtime.Out(data, nil)
		return nil
	},
}

var SheetWrite = common.Shortcut{
	Service:     "sheets",
	Command:     "+write",
	Description: "Write to spreadsheet cells (overwrite mode)",
	Risk:        "write",
	Scopes:      []string{"sheets:spreadsheet:write_only", "sheets:spreadsheet:read"},
	AuthTypes:   []string{"user", "bot"},
	Flags: []common.Flag{
		{Name: "url", Desc: "spreadsheet URL"},
		{Name: "spreadsheet-token", Desc: "spreadsheet token"},
		{Name: "range", Desc: "write range (<sheetId>!A1:D10, A1:D10 with --sheet-id, or a single cell like C2)"},
		{Name: "sheet-id", Desc: "sheet ID"},
		{Name: "values", Desc: "2D array JSON", Required: true},
	},
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		if _, err := validateSheetManageToken(runtime); err != nil {
			return err
		}

		if _, err := parseValues2DJSON(runtime.Str("values")); err != nil {
			return err
		}
		if err := validateSheetRangeInput(runtime.Str("sheet-id"), runtime.Str("range")); err != nil {
			return err
		}
		return nil
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		token, _ := validateSheetManageToken(runtime)
		writeRange := runtime.Str("range")
		values, _ := parseValues2DJSON(runtime.Str("values"))
		if writeRange == "" {
			// Sheet-only selector: build the write rect from the selector's
			// A1 instead of treating the bare sheet id as a cell anchor.
			writeRange = normalizeWriteRange(runtime.Str("sheet-id"), "", values)
		} else {
			writeRange = normalizeWriteRange(runtime.Str("sheet-id"), writeRange, values)
		}
		return common.NewDryRunAPI().
			PUT("/open-apis/sheets/v2/spreadsheets/:token/values").
			Body(map[string]interface{}{"valueRange": map[string]interface{}{"range": writeRange, "values": values}}).
			Set("token", token)
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token, _ := validateSheetManageToken(runtime)

		values, err := parseValues2DJSON(runtime.Str("values"))
		if err != nil {
			return err
		}

		writeRange := runtime.Str("range")
		if writeRange == "" {
			// Sheet-only selector: build the write rect from the selector's
			// A1 (see DryRun). Resolve the first sheet when none was given.
			sel := runtime.Str("sheet-id")
			if sel == "" {
				var err error
				sel, err = getFirstSheetID(runtime, token)
				if err != nil {
					return err
				}
			}
			writeRange = normalizeWriteRange(sel, "", values)
		} else {
			writeRange = normalizeWriteRange(runtime.Str("sheet-id"), writeRange, values)
		}

		data, err := runtime.CallAPITyped("PUT", fmt.Sprintf("/open-apis/sheets/v2/spreadsheets/%s/values", validate.EncodePathSegment(token)), nil, map[string]interface{}{
			"valueRange": map[string]interface{}{
				"range":  writeRange,
				"values": values,
			},
		})
		if err != nil {
			return err
		}
		runtime.Out(data, nil)
		return nil
	},
}

var SheetAppend = common.Shortcut{
	Service:     "sheets",
	Command:     "+append",
	Description: "Append rows to a spreadsheet",
	Risk:        "write",
	Scopes:      []string{"sheets:spreadsheet:write_only", "sheets:spreadsheet:read"},
	AuthTypes:   []string{"user", "bot"},
	Flags: []common.Flag{
		{Name: "url", Desc: "spreadsheet URL"},
		{Name: "spreadsheet-token", Desc: "spreadsheet token"},
		{Name: "range", Desc: "append range (<sheetId>!A1:D10, A1:D10 with --sheet-id, or a single cell like C2)"},
		{Name: "sheet-id", Desc: "sheet ID"},
		{Name: "values", Desc: "2D array JSON", Required: true},
	},
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		if _, err := validateSheetManageToken(runtime); err != nil {
			return err
		}

		if _, err := parseValues2DJSON(runtime.Str("values")); err != nil {
			return err
		}
		if err := validateSheetRangeInput(runtime.Str("sheet-id"), runtime.Str("range")); err != nil {
			return err
		}
		return nil
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		token, _ := validateSheetManageToken(runtime)
		appendRange := runtime.Str("range")
		if appendRange == "" {
			// Sheet-only selector: pass the bare sheet id through verbatim
			// (see SheetRead.DryRun for the normalizer-mangling rationale).
			appendRange = runtime.Str("sheet-id")
		} else {
			appendRange = normalizePointRange(runtime.Str("sheet-id"), appendRange)
		}
		values, _ := parseValues2DJSON(runtime.Str("values"))
		return common.NewDryRunAPI().
			POST("/open-apis/sheets/v2/spreadsheets/:token/values_append").
			Body(map[string]interface{}{"valueRange": map[string]interface{}{"range": appendRange, "values": values}}).
			Set("token", token)
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token, _ := validateSheetManageToken(runtime)

		values, err := parseValues2DJSON(runtime.Str("values"))
		if err != nil {
			return err
		}

		appendRange := runtime.Str("range")
		if appendRange == "" {
			// Sheet-only selector: keep the resolved sheet id verbatim (see DryRun).
			appendRange = runtime.Str("sheet-id")
			if appendRange == "" {
				var err error
				appendRange, err = getFirstSheetID(runtime, token)
				if err != nil {
					return err
				}
			}
		} else {
			appendRange = normalizePointRange(runtime.Str("sheet-id"), appendRange)
		}

		data, err := runtime.CallAPITyped("POST", fmt.Sprintf("/open-apis/sheets/v2/spreadsheets/%s/values_append", validate.EncodePathSegment(token)), nil, map[string]interface{}{
			"valueRange": map[string]interface{}{
				"range":  appendRange,
				"values": values,
			},
		})
		if err != nil {
			return err
		}
		runtime.Out(data, nil)
		return nil
	},
}

var SheetFind = common.Shortcut{
	Service:     "sheets",
	Command:     "+find",
	Description: "Find cells in a spreadsheet",
	Risk:        "read",
	Scopes:      []string{"sheets:spreadsheet:read"},
	AuthTypes:   []string{"user", "bot"},
	Flags: []common.Flag{
		{Name: "url", Desc: "spreadsheet URL"},
		{Name: "spreadsheet-token", Desc: "spreadsheet token"},
		{Name: "sheet-id", Desc: "sheet ID", Required: true},
		{Name: "find", Desc: "search text", Required: true},
		{Name: "range", Desc: "search range (<sheetId>!A1:D10, or A1:D10 / C2 with --sheet-id)"},
		{Name: "ignore-case", Type: "bool", Desc: "case-insensitive search"},
		{Name: "match-entire-cell", Type: "bool", Desc: "match entire cell"},
		{Name: "search-by-regex", Type: "bool", Desc: "regex search"},
		{Name: "include-formulas", Type: "bool", Desc: "search formulas"},
	},
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		if _, err := validateSheetManageToken(runtime); err != nil {
			return err
		}
		if err := validateSheetRangeInput(runtime.Str("sheet-id"), runtime.Str("range")); err != nil {
			return err
		}
		if r := runtime.Str("range"); r != "" {
			if rangeSheetID, _, ok := splitSheetRange(r); ok && runtime.Str("sheet-id") != "" && rangeSheetID != runtime.Str("sheet-id") {
				return errs.NewValidationError(errs.SubtypeInvalidArgument, "--range sheet ID %q does not match --sheet-id %q", rangeSheetID, runtime.Str("sheet-id")).WithParam("--range")
			}
		}
		return nil
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		token, _ := validateSheetManageToken(runtime)
		sheetID := runtime.Str("sheet-id")
		findCondition := map[string]interface{}{
			"range":             sheetID,
			"match_case":        !runtime.Bool("ignore-case"),
			"match_entire_cell": runtime.Bool("match-entire-cell"),
			"search_by_regex":   runtime.Bool("search-by-regex"),
			"include_formulas":  runtime.Bool("include-formulas"),
		}
		if runtime.Str("range") != "" {
			findCondition["range"] = normalizePointRange(sheetID, runtime.Str("range"))
		}
		return common.NewDryRunAPI().
			POST("/open-apis/sheets/v3/spreadsheets/:token/sheets/:sheet_id/find").
			Body(map[string]interface{}{
				"find":           runtime.Str("find"),
				"find_condition": findCondition,
			}).
			Set("token", token).Set("sheet_id", sheetID).Set("find", runtime.Str("find"))
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token, _ := validateSheetManageToken(runtime)

		sheetID := runtime.Str("sheet-id")
		findText := runtime.Str("find")

		findCondition := map[string]interface{}{
			"range":             sheetID,
			"match_case":        !runtime.Bool("ignore-case"),
			"match_entire_cell": runtime.Bool("match-entire-cell"),
			"search_by_regex":   runtime.Bool("search-by-regex"),
			"include_formulas":  runtime.Bool("include-formulas"),
		}
		if runtime.Str("range") != "" {
			findCondition["range"] = normalizePointRange(sheetID, runtime.Str("range"))
		}

		reqData := map[string]interface{}{
			"find_condition": findCondition,
			"find":           findText,
		}

		data, err := runtime.CallAPITyped("POST", fmt.Sprintf("/open-apis/sheets/v3/spreadsheets/%s/sheets/%s/find", validate.EncodePathSegment(token), validate.EncodePathSegment(sheetID)), nil, reqData)
		if err != nil {
			return err
		}
		runtime.Out(data, nil)
		return nil
	},
}

var SheetReplace = common.Shortcut{
	Service:     "sheets",
	Command:     "+replace",
	Description: "Find and replace cell values in a spreadsheet",
	Risk:        "write",
	Scopes:      []string{"sheets:spreadsheet:write_only", "sheets:spreadsheet:read"},
	AuthTypes:   []string{"user", "bot"},
	Flags: []common.Flag{
		{Name: "url", Desc: "spreadsheet URL"},
		{Name: "spreadsheet-token", Desc: "spreadsheet token"},
		{Name: "sheet-id", Desc: "sheet ID", Required: true},
		{Name: "find", Desc: "search text or regex pattern", Required: true},
		{Name: "replacement", Desc: "replacement text", Required: true},
		{Name: "range", Desc: "search range (<sheetId>!A1:D10, or A1:D10 with --sheet-id)"},
		{Name: "match-case", Type: "bool", Desc: "case-sensitive search"},
		{Name: "match-entire-cell", Type: "bool", Desc: "match entire cell content"},
		{Name: "search-by-regex", Type: "bool", Desc: "use regex search"},
		{Name: "include-formulas", Type: "bool", Desc: "search in formulas"},
	},
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		if _, err := validateSheetManageToken(runtime); err != nil {
			return err
		}
		if err := validateSheetRangeInput(runtime.Str("sheet-id"), runtime.Str("range")); err != nil {
			return err
		}
		if r := runtime.Str("range"); r != "" {
			if rangeSheetID, _, ok := splitSheetRange(r); ok && runtime.Str("sheet-id") != "" && rangeSheetID != runtime.Str("sheet-id") {
				return errs.NewValidationError(errs.SubtypeInvalidArgument, "--range sheet ID %q does not match --sheet-id %q", rangeSheetID, runtime.Str("sheet-id")).WithParam("--range")
			}
		}
		return nil
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		token, _ := validateSheetManageToken(runtime)
		sheetID := runtime.Str("sheet-id")
		findCondition := map[string]interface{}{
			"range":             sheetID,
			"match_case":        runtime.Bool("match-case"),
			"match_entire_cell": runtime.Bool("match-entire-cell"),
			"search_by_regex":   runtime.Bool("search-by-regex"),
			"include_formulas":  runtime.Bool("include-formulas"),
		}
		if runtime.Str("range") != "" {
			findCondition["range"] = normalizeSheetRange(sheetID, runtime.Str("range"))
		}
		return common.NewDryRunAPI().
			POST("/open-apis/sheets/v3/spreadsheets/:token/sheets/:sheet_id/replace").
			Body(map[string]interface{}{
				"find_condition": findCondition,
				"find":           runtime.Str("find"),
				"replacement":    runtime.Str("replacement"),
			}).
			Set("token", token).Set("sheet_id", sheetID)
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token, _ := validateSheetManageToken(runtime)

		sheetID := runtime.Str("sheet-id")
		findCondition := map[string]interface{}{
			"range":             sheetID,
			"match_case":        runtime.Bool("match-case"),
			"match_entire_cell": runtime.Bool("match-entire-cell"),
			"search_by_regex":   runtime.Bool("search-by-regex"),
			"include_formulas":  runtime.Bool("include-formulas"),
		}
		if runtime.Str("range") != "" {
			findCondition["range"] = normalizeSheetRange(sheetID, runtime.Str("range"))
		}

		data, err := runtime.CallAPITyped("POST",
			fmt.Sprintf("/open-apis/sheets/v3/spreadsheets/%s/sheets/%s/replace",
				validate.EncodePathSegment(token),
				validate.EncodePathSegment(sheetID),
			),
			nil,
			map[string]interface{}{
				"find_condition": findCondition,
				"find":           runtime.Str("find"),
				"replacement":    runtime.Str("replacement"),
			},
		)
		if err != nil {
			return err
		}
		runtime.Out(data, nil)
		return nil
	},
}