sheets

Documentation

Index

• Variables
• func Shortcuts() []common.Shortcut

Variables

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 {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		if token == "" {
			return common.FlagErrorf("specify --url or --spreadsheet-token")
		}

		var values interface{}
		if err := json.Unmarshal([]byte(runtime.Str("values")), &values); err != nil {
			return common.FlagErrorf("--values invalid JSON, must be a 2D array")
		}
		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 := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		appendRange := runtime.Str("range")
		if appendRange == "" && runtime.Str("sheet-id") != "" {
			appendRange = runtime.Str("sheet-id")
		}
		var values interface{}
		json.Unmarshal([]byte(runtime.Str("values")), &values)
		appendRange = normalizePointRange(runtime.Str("sheet-id"), appendRange)
		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 := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}

		var values interface{}
		json.Unmarshal([]byte(runtime.Str("values")), &values)

		appendRange := runtime.Str("range")
		if appendRange == "" && runtime.Str("sheet-id") != "" {
			appendRange = runtime.Str("sheet-id")
		}

		if appendRange == "" {
			var err error
			appendRange, err = getFirstSheetID(runtime, token)
			if err != nil {
				return err
			}
		}
		appendRange = normalizePointRange(runtime.Str("sheet-id"), appendRange)

		data, err := runtime.CallAPI("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 SheetCreate = common.Shortcut{
	Service:     "sheets",
	Command:     "+create",
	Description: "Create a spreadsheet (optional header row and initial data)",
	Risk:        "write",
	Scopes:      []string{"sheets:spreadsheet:create", "sheets:spreadsheet:write_only"},
	AuthTypes:   []string{"user", "bot"},
	Flags: []common.Flag{
		{Name: "title", Desc: "spreadsheet title", Required: true},
		{Name: "folder-token", Desc: "target folder token"},
		{Name: "headers", Desc: "header row JSON array"},
		{Name: "data", Desc: "initial data JSON 2D array"},
	},
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		if headersStr := runtime.Str("headers"); headersStr != "" {
			var headers []interface{}
			if err := json.Unmarshal([]byte(headersStr), &headers); err != nil {
				return common.FlagErrorf("--headers invalid JSON, must be a 1D array")
			}
		}
		if dataStr := runtime.Str("data"); dataStr != "" {
			var rows [][]interface{}
			if err := json.Unmarshal([]byte(dataStr), &rows); err != nil {
				return common.FlagErrorf("--data invalid JSON, must be a 2D array")
			}
		}
		return nil
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		return common.NewDryRunAPI().
			POST("/open-apis/sheets/v3/spreadsheets").
			Body(map[string]interface{}{"title": runtime.Str("title")})
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		title := runtime.Str("title")
		folderToken := runtime.Str("folder-token")
		headersStr := runtime.Str("headers")
		dataStr := runtime.Str("data")
		var allRows []interface{}

		if headersStr != "" {
			var headers []interface{}
			if err := json.Unmarshal([]byte(headersStr), &headers); err != nil {
				return common.FlagErrorf("--headers invalid JSON, must be a 1D array")
			}
			if len(headers) > 0 {
				allRows = append(allRows, headers)
			}
		}

		if dataStr != "" {
			var rows []interface{}
			if err := json.Unmarshal([]byte(dataStr), &rows); err != nil {
				return common.FlagErrorf("--data invalid JSON, must be a 2D array")
			}
			if len(rows) > 0 {
				allRows = append(allRows, rows...)
			}
		}

		createData := map[string]interface{}{"title": title}
		if folderToken != "" {
			createData["folder_token"] = folderToken
		}

		data, err := runtime.CallAPI("POST", "/open-apis/sheets/v3/spreadsheets", nil, createData)
		if err != nil {
			return err
		}

		spreadsheet, _ := data["spreadsheet"].(map[string]interface{})
		token, _ := spreadsheet["spreadsheet_token"].(string)

		if len(allRows) > 0 && token != "" {
			appendRange, err := getFirstSheetID(runtime, token)
			if err != nil {
				return err
			}
			if _, err := runtime.CallAPI("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": allRows,
				},
			}); err != nil {
				return err
			}
		}

		runtime.Out(map[string]interface{}{
			"spreadsheet_token": token,
			"title":             title,
			"url":               spreadsheet["url"],
		}, nil)
		return nil
	},
}

var SheetExport = common.Shortcut{
	Service:     "sheets",
	Command:     "+export",
	Description: "Export a spreadsheet (async task polling + optional download)",
	Risk:        "read",
	Scopes:      []string{"docs:document:export", "drive:file:download"},
	AuthTypes:   []string{"user", "bot"},
	Flags: []common.Flag{
		{Name: "url", Desc: "spreadsheet URL"},
		{Name: "spreadsheet-token", Desc: "spreadsheet token"},
		{Name: "file-extension", Desc: "export format: xlsx | csv", Required: true},
		{Name: "output-path", Desc: "local save path"},
		{Name: "sheet-id", Desc: "sheet ID (required for CSV)"},
	},
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		if token == "" {
			return common.FlagErrorf("specify --url or --spreadsheet-token")
		}
		return nil
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		return common.NewDryRunAPI().
			POST("/open-apis/drive/v1/export_tasks").
			Body(map[string]interface{}{"token": token, "type": "sheet", "file_extension": runtime.Str("file-extension")}).
			Set("token", token).Set("ext", runtime.Str("file-extension"))
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}

		fileExt := runtime.Str("file-extension")
		outputPath := runtime.Str("output-path")
		sheetIdFlag := runtime.Str("sheet-id")

		if outputPath != "" {
			if _, err := validate.SafeOutputPath(outputPath); err != nil {
				return output.ErrValidation("unsafe output path: %s", err)
			}
		}

		exportData := map[string]interface{}{
			"token":          token,
			"type":           "sheet",
			"file_extension": fileExt,
		}
		if sheetIdFlag != "" {
			exportData["sub_id"] = sheetIdFlag
		}

		data, err := runtime.CallAPI("POST", "/open-apis/drive/v1/export_tasks", nil, exportData)
		if err != nil {
			return err
		}
		ticket, _ := data["ticket"].(string)

		fmt.Fprintf(runtime.IO().ErrOut, "Waiting for export task to complete...\n")
		var fileToken string
		for i := 0; i < 50; i++ {
			time.Sleep(600 * time.Millisecond)
			pollResult, err := runtime.RawAPI("GET", "/open-apis/drive/v1/export_tasks/"+ticket, map[string]interface{}{"token": token}, nil)
			if err != nil {
				continue
			}
			pollMap, _ := pollResult.(map[string]interface{})
			pollData, _ := pollMap["data"].(map[string]interface{})
			pollResult2, _ := pollData["result"].(map[string]interface{})
			if pollResult2 != nil {
				ft, _ := pollResult2["file_token"].(string)
				if ft != "" {
					fileToken = ft
					break
				}
			}
		}

		if fileToken == "" {
			return output.Errorf(output.ExitAPI, "api_error", "export task timed out")
		}

		fmt.Fprintf(runtime.IO().ErrOut, "Export complete: file_token=%s\n", fileToken)

		if outputPath == "" {
			runtime.Out(map[string]interface{}{
				"file_token": fileToken,
				"ticket":     ticket,
			}, nil)
		}

		apiResp, err := runtime.DoAPI(&larkcore.ApiReq{
			HttpMethod: http.MethodGet,
			ApiPath:    fmt.Sprintf("/open-apis/drive/v1/export_tasks/file/%s/download", validate.EncodePathSegment(fileToken)),
		}, larkcore.WithFileDownload())
		if err != nil {
			return output.ErrNetwork("download failed: %s", err)
		}

		safePath, pathErr := validate.SafeOutputPath(outputPath)
		if pathErr != nil {
			return output.ErrValidation("unsafe output path: %s", pathErr)
		}
		os.MkdirAll(filepath.Dir(safePath), 0755)
		if err := validate.AtomicWrite(safePath, apiResp.RawBody, 0644); err != nil {
			return output.Errorf(output.ExitInternal, "api_error", "cannot create file: %s", err)
		}

		runtime.Out(map[string]interface{}{
			"saved_path": safePath,
			"size_bytes": len(apiResp.RawBody),
		}, 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 {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		if token == "" {
			return common.FlagErrorf("specify --url or --spreadsheet-token")
		}
		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 := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		sheetIdFlag := runtime.Str("sheet-id")
		findCondition := map[string]interface{}{
			"range":             sheetIdFlag,
			"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(sheetIdFlag, 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", sheetIdFlag).Set("find", runtime.Str("find"))
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}

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

		findCondition := map[string]interface{}{
			"range":             sheetIdFlag,
			"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(sheetIdFlag, runtime.Str("range"))
		}

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

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

var SheetInfo = common.Shortcut{
	Service:     "sheets",
	Command:     "+info",
	Description: "View spreadsheet and sheet information",
	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"},
	},
	Validate: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		if token == "" {
			return common.FlagErrorf("specify --url or --spreadsheet-token")
		}
		return nil
	},
	DryRun: func(ctx context.Context, runtime *common.RuntimeContext) *common.DryRunAPI {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		return common.NewDryRunAPI().
			GET("/open-apis/sheets/v3/spreadsheets/:token").
			Set("token", token)
	},
	Execute: func(ctx context.Context, runtime *common.RuntimeContext) error {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}

		spreadsheetData, err := runtime.CallAPI("GET", fmt.Sprintf("/open-apis/sheets/v3/spreadsheets/%s", validate.EncodePathSegment(token)), nil, nil)
		if err != nil {
			return err
		}

		// Get sheets info (best-effort)
		var sheetsData interface{}
		sheetsResult, sheetsErr := runtime.RawAPI("GET", fmt.Sprintf("/open-apis/sheets/v3/spreadsheets/%s/sheets/query", validate.EncodePathSegment(token)), nil, nil)
		if sheetsErr == nil {
			if sheetsMap, ok := sheetsResult.(map[string]interface{}); ok {
				if d, ok := sheetsMap["data"].(map[string]interface{}); ok {
					sheetsData = d
				}
			}
		}

		runtime.Out(map[string]interface{}{
			"spreadsheet": spreadsheetData,
			"sheets":      sheetsData,
		}, nil)
		return 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 {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		if token == "" {
			return common.FlagErrorf("specify --url or --spreadsheet-token")
		}
		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 := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		readRange := runtime.Str("range")
		if readRange == "" && runtime.Str("sheet-id") != "" {
			readRange = runtime.Str("sheet-id")
		}
		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 := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}

		readRange := runtime.Str("range")
		if readRange == "" && runtime.Str("sheet-id") != "" {
			readRange = runtime.Str("sheet-id")
		}

		if readRange == "" {
			var err error
			readRange, err = getFirstSheetID(runtime, token)
			if err != nil {
				return err
			}
		}
		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.CallAPI("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 {
		token := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		if token == "" {
			return common.FlagErrorf("specify --url or --spreadsheet-token")
		}

		var values interface{}
		if err := json.Unmarshal([]byte(runtime.Str("values")), &values); err != nil {
			return common.FlagErrorf("--values invalid JSON, must be a 2D array")
		}
		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 := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}
		writeRange := runtime.Str("range")
		if writeRange == "" && runtime.Str("sheet-id") != "" {
			writeRange = runtime.Str("sheet-id")
		}
		var values interface{}
		json.Unmarshal([]byte(runtime.Str("values")), &values)
		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 := runtime.Str("spreadsheet-token")
		if runtime.Str("url") != "" {
			token = extractSpreadsheetToken(runtime.Str("url"))
		}

		var values interface{}
		json.Unmarshal([]byte(runtime.Str("values")), &values)

		writeRange := runtime.Str("range")
		if writeRange == "" && runtime.Str("sheet-id") != "" {
			writeRange = runtime.Str("sheet-id")
		}

		if writeRange == "" {
			var err error
			writeRange, err = getFirstSheetID(runtime, token)
			if err != nil {
				return err
			}
		}
		writeRange = normalizeWriteRange(runtime.Str("sheet-id"), writeRange, values)

		data, err := runtime.CallAPI("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
	},
}

Functions

func Shortcuts

func Shortcuts() []common.Shortcut

Shortcuts returns all sheets shortcuts.