feat(sheets): add --safe-formula and --verify flags to update command

alexknowshtml · claude · alexknowshtml · commit 5cdc419ed6a6 · 2026-06-19T13:32:04.000Z
--safe-formula reads a formula from a file, avoiding shell ! expansion
on cross-tab references (e.g. =IMPORTRANGE!). Forces USER_ENTERED mode.

--verify reads the updated range back after write and warns on any
spreadsheet error token: #ERROR!, #REF!, #NAME?, #VALUE!, #DIV/0!,

Co-Authored-By: Claude Sonnet 4.6 &lt;noreply@anthropic.com&gt;
diff --git a/internal/cmd/sheets.go b/internal/cmd/sheets.go
@@ -4,6 +4,7 @@ import (
 	"context"
 	"fmt"
 	"io"
+	"os"
 	"strings"
 
 	"google.golang.org/api/drive/v3"
@@ -176,6 +177,8 @@ type SheetsUpdateCmd struct {
 	ValueInput         string   `name:"input" help:"Value input option: RAW or USER_ENTERED" default:"USER_ENTERED"`
 	ValuesJSON         string   `name:"values-json" help:"Values as JSON 2D array"`
 	CopyValidationFrom string   `name:"copy-validation-from" help:"Copy data validation from an A1 range or named range (e.g. 'Sheet1!A2:D2' or MyNamedRange) to the updated cells"`
+	SafeFormula        string   `name:"safe-formula" help:"Path to file containing a formula; avoids shell ! expansion and forces USER_ENTERED mode"`
+	Verify             bool     `name:"verify" help:"After write, read the cell back and warn if it contains a spreadsheet error (e.g. #ERROR!, #REF!, #NAME?)"`
 }
 
 func (c *SheetsUpdateCmd) Run(ctx context.Context, flags *RootFlags) error {
@@ -191,8 +194,16 @@ func (c *SheetsUpdateCmd) Run(ctx context.Context, flags *RootFlags) error {
 	}
 
 	var values [][]interface{}
+	safeFormulaUsed := false
 
 	switch {
+	case strings.TrimSpace(c.SafeFormula) != "":
+		content, readErr := os.ReadFile(c.SafeFormula)
+		if readErr != nil {
+			return fmt.Errorf("read --safe-formula file: %w", readErr)
+		}
+		values = [][]interface{}{{strings.TrimSpace(string(content))}}
+		safeFormulaUsed = true
 	case strings.TrimSpace(c.ValuesJSON) != "":
 		b, err := resolveInlineOrFileBytes(c.ValuesJSON, stdinReader(ctx))
 		if err != nil {
@@ -210,7 +221,7 @@ func (c *SheetsUpdateCmd) Run(ctx context.Context, flags *RootFlags) error {
 	}
 
 	valueInputOption := strings.TrimSpace(c.ValueInput)
-	if valueInputOption == "" {
+	if valueInputOption == "" || safeFormulaUsed {
 		valueInputOption = sheetsDefaultValueInputOption
 	}
 
@@ -256,6 +267,26 @@ func (c *SheetsUpdateCmd) Run(ctx context.Context, flags *RootFlags) error {
 		}
 	}
 
+	if c.Verify && strings.TrimSpace(resp.UpdatedRange) != "" {
+		getResp, getErr := svc.Spreadsheets.Values.Get(spreadsheetID, resp.UpdatedRange).
+			ValueRenderOption("FORMATTED_VALUE").Do()
+		if getErr != nil {
+			u.Err().Errorf("--verify: failed to read back cell: %v", getErr)
+		} else {
+			errorTokens := []string{"#ERROR!", "#REF!", "#NAME?", "#VALUE!", "#DIV/0!", "#N/A", "#NUM!"}
+			for _, row := range getResp.Values {
+				for _, cell := range row {
+					val := fmt.Sprintf("%v", cell)
+					for _, token := range errorTokens {
+						if strings.Contains(val, token) {
+							u.Err().Printf("--verify: cell %s contains spreadsheet error: %s", resp.UpdatedRange, val)
+						}
+					}
+				}
+			}
+		}
+	}
+
 	if outfmt.IsJSON(ctx) {
 		return outfmt.WriteJSON(ctx, stdoutWriter(ctx), map[string]any{
 			"updatedRange":   resp.UpdatedRange,
