1package sheet23import (4 "bytes"5 "encoding/json"6 "errors"7 "fmt"8 "slices"9 "strings"10 "unicode/utf8"11)1213// Macros are kept with the workbook, as Google Sheets keeps a14// spreadsheet's Apps Script: each is a named Starlark script with an15// optional Ctrl+Alt+Shift+digit shortcut. The engine only stores them;16// recording and running them is the UI's (internal/ui, internal/macro).17// Changing the list is an undo step like any other edit, so the file's18// modified flag follows it.1920// MacroAPI is the version of the scripting API macros are written for.21// A file records it with each macro, so a later build that changes the22// API can tell old scripts from new ones.23const MacroAPI = 12425// Macro is a saved macro.26type Macro struct {27 Name string28 Key string // the shortcut's digit, "0" to "9", or "" for none29 Source string // the Starlark script30 API int // the scripting API it was written for, MacroAPI or older31}3233// maxMacroName keeps names short enough for menus and the palette.34const maxMacroName = 603536// ValidMacroName reports why name can't name a macro, or nil.37func ValidMacroName(name string) error {38 switch {39 case strings.TrimSpace(name) == "":40 return errors.New("a macro needs a name")41 case utf8.RuneCountInString(name) > maxMacroName:42 return fmt.Errorf("macro names are at most %d characters", maxMacroName)43 case strings.ContainsAny(name, "\n\r\t"):44 return errors.New("macro names are one line")45 }46 return nil47}4849// Macros returns the saved macros in the order they were added.50func (w *Workbook) Macros() []Macro { return slices.Clone(w.macros) }5152// Macro finds a macro by name, ignoring case.53func (w *Workbook) Macro(name string) (Macro, bool) {54 if i := w.macroIndex(name); i >= 0 {55 return w.macros[i], true56 }57 return Macro{}, false58}5960// MacroForKey finds the macro a shortcut digit runs.61func (w *Workbook) MacroForKey(key string) (Macro, bool) {62 for _, mc := range w.macros {63 if key != "" && mc.Key == key {64 return mc, true65 }66 }67 return Macro{}, false68}6970func (w *Workbook) macroIndex(name string) int {71 return slices.IndexFunc(w.macros, func(mc Macro) bool { return strings.EqualFold(mc.Name, name) })72}7374// SaveMacro stores mc in place of the macro named old, or adds it when75// old is "", as one undo step labelled label. Names are unique ignoring76// case, and so are shortcuts.77func (w *Workbook) SaveMacro(old string, mc Macro, label string) error {78 if err := ValidMacroName(mc.Name); err != nil {79 return err80 }81 mc.Name = strings.TrimSpace(mc.Name)82 if mc.API == 0 {83 mc.API = MacroAPI84 }85 at := len(w.macros)86 if old != "" {87 if at = w.macroIndex(old); at < 0 {88 return fmt.Errorf("there's no macro named %s", old)89 }90 }91 if i := w.macroIndex(mc.Name); i >= 0 && i != at {92 return fmt.Errorf("there's already a macro named %s", w.macros[i].Name)93 }94 if other, ok := w.MacroForKey(mc.Key); ok && !strings.EqualFold(other.Name, old) {95 return fmt.Errorf("Ctrl+Alt+Shift+%s already runs %s", mc.Key, other.Name)96 }97 if mc.Key != "" && (len(mc.Key) != 1 || mc.Key[0] < '0' || mc.Key[0] > '9') {98 return fmt.Errorf("a macro's shortcut is a digit, not %q", mc.Key)99 }100 w.changeMacros(label, func() {101 if at == len(w.macros) {102 w.macros = append(w.macros, mc)103 } else {104 w.macros[at] = mc105 }106 })107 return nil108}109110// DeleteMacro removes the named macro as an undo step, reporting whether111// there was one.112func (w *Workbook) DeleteMacro(name string) bool {113 i := w.macroIndex(name)114 if i < 0 {115 return false116 }117 w.changeMacros("delete macro "+w.macros[i].Name, func() {118 w.macros = slices.Delete(w.macros, i, i+1)119 })120 return true121}122123// changeMacros runs fn, which changes the macro list, as an undo step.124func (w *Workbook) changeMacros(label string, fn func()) {125 s := w.sheets[w.Active()]126 w.change(s, label, Rect{}, func() {127 if st := w.hist.open; st != nil && st.macros == nil {128 before := slices.Clone(w.macros)129 st.macros = &before130 }131 w.macros = slices.Clone(w.macros) // steps keep the old slice132 fn()133 })134}135136// MacroOrigin identifies the computer the workbook's macros were made or137// trusted on, as saved in the file; "" when unknown. The UI compares it138// with its own to decide whether to ask before running a macro from a139// file made elsewhere.140func (w *Workbook) MacroOrigin() string { return w.macroOrigin }141142// SetMacroOrigin records where the macros were made or trusted. It isn't143// an edit: it changes nothing a user sees, and is saved with the next144// save.145func (w *Workbook) SetMacroOrigin(origin string) { w.macroOrigin = origin }146147// Begin opens an undo step that stays open until the returned function148// is called, for changes made over several calls that undo together,149// such as a macro run. Changes in between join it as in a Batch, and150// Undo and Redo do nothing while it is open. Call Settle to see the151// values of formulas changed so far.152func (w *Workbook) Begin(c Change) (end func()) {153 w.begin(c.Sheet, c.Label, c.Focus)154 done := false155 return func() {156 if !done {157 done = true158 w.finish()159 }160 }161}162163// Settle recalculates what the open step has changed so far, so formulas164// read inside it (by a macro, or a command it runs) see current values.165// Outside a step there is nothing pending.166func (w *Workbook) Settle() {167 h := &w.hist168 if h.open == nil || len(h.dirty) == 0 && !w.structural {169 return170 }171 dirty := h.dirty172 h.dirty = nil173 if w.structural {174 w.structural = false175 w.recalcAll()176 } else {177 w.recalc(dirty)178 }179}180181// fileMacro is a macro, one per line, its script a JSON string:182//183// {"name": "Totals", "key": "1", "api": 1, "source": "select(\"B9\")\nenter(\"=SUM(B2:B8)\")\n"}184type fileMacro struct {185 Name string `json:"name"`186 Key string `json:"key,omitempty"`187 API int `json:"api"`188 Source string `json:"source"`189}190191// macrosLines is the "macros" field: a list with one macro per line.192func (w *Workbook) macrosLines() string {193 var b strings.Builder194 b.WriteString(`"macros": [`)195 for i, mc := range w.macros {196 if i > 0 {197 b.WriteByte(',')198 }199 fmt.Fprintf(&b, "\n {\"name\": %s, ", plainJSON(mc.Name))200 if mc.Key != "" {201 fmt.Fprintf(&b, "\"key\": %s, ", plainJSON(mc.Key))202 }203 fmt.Fprintf(&b, "\"api\": %d, \"source\": %s}", mc.API, plainJSON(mc.Source))204 }205 b.WriteString("\n ]")206 return b.String()207}208209// plainJSON is s as a JSON string, leaving <, > and & as they are so210// formulas in scripts stay readable.211func plainJSON(s string) string {212 var b bytes.Buffer213 enc := json.NewEncoder(&b)214 enc.SetEscapeHTML(false)215 enc.Encode(s)216 return strings.TrimSuffix(b.String(), "\n")217}218219// readMacros keeps a file's macros. A macro whose name clashes or is220// invalid is an error, as for named ranges; its script isn't checked221// until it runs.222func (w *Workbook) readMacros(macros []fileMacro) error {223 for _, fm := range macros {224 if err := ValidMacroName(fm.Name); err != nil {225 return fmt.Errorf("macro %q: %w", fm.Name, err)226 }227 if w.macroIndex(fm.Name) >= 0 {228 return fmt.Errorf("macro %q is defined twice", fm.Name)229 }230 key := fm.Key231 if _, taken := w.MacroForKey(key); taken || len(key) != 1 || key[0] < '0' || key[0] > '9' {232 key = "" // a clash or a key this build can't bind: keep the macro, drop the shortcut233 }234 w.macros = append(w.macros, Macro{Name: fm.Name, Key: key, Source: fm.Source, API: max(fm.API, 1)})235 }236 return nil237}