address review: recalculate formulas when sheet protection changes

crates/calc/src/eval/call.rs

@@ -52,13 +52,16 @@ impl<'h> Evaluator<'h> {
             "ISFORMULA" | "FORMULATEXT" => {
                 let Some(Ev::R(a)) = args.first().map(|a| self.eval(a)) else { return err(CellError::NA) };
                 let Some(a) = a.first() else { return err(CellError::NA) };
-                let f = self.host.workbook().sheet(a.sheet).and_then(|s| s.cell(a.range.start)).and_then(|c| c.formula.clone());
+                let wb = self.host.workbook();
+                let f = wb.sheet(a.sheet).and_then(|s| s.cell(a.range.start)).and_then(|c| c.formula.clone());
+                // A formula formatted Hidden on a protected sheet stays hidden from FORMULATEXT too.
+                let hidden = wb.sheet(a.sheet).is_some_and(|s| s.is_protected() && wb.styles.get(s.style_id(a.range.start)).protection.hidden);
                 if name == "ISFORMULA" {
                     Ev::V(Value::Bool(f.is_some()))
                 } else {
                     match f {
-                        Some(f) => Ev::V(Value::text(format!("={}", f.text))),
-                        None => err(CellError::NA),
+                        Some(f) if !hidden => Ev::V(Value::text(format!("={}", f.text))),
+                        _ => err(CellError::NA),
                     }
                 }
             }

crates/engine/src/cmd/review.rs

@@ -163,6 +163,8 @@ fn protect_sheet(s: &mut Session, p: &Json) -> Result<Json> {
     let what = d.wb.sheet(sheet).map(|sh| sh.name.clone()).unwrap_or_default();
     let prot = SheetProtection { password: str_param(p, "password").filter(|x| !x.is_empty()).map(|pw| new_password_hash(d, &what, pw)), ..prot };
     edit(s, |cx| {
+        // FORMULATEXT reads a sheet's protection: its results are recalculated.
+        cx.structural = true;
         cx.sheet_mut(sheet)?.protection = Some(prot.clone());
         Ok(Json::Null)
     })
@@ -182,6 +184,7 @@ fn unprotect_sheet(s: &mut Session, p: &Json) -> Result<Json> {
         }
     }
     edit(s, |cx| {
+        cx.structural = true;
         cx.sheet_mut(sheet)?.protection = None;
         Ok(Json::Null)
     })

crates/engine/src/cmd/solver/reports.rs

@@ -52,19 +52,14 @@ pub(super) fn abs_ref(c: CellRef) -> String {
     format!("${}${}", gridcraft_core::col_to_letters(c.col), u64::from(c.row) + 1)
 }
 
-/// How many cells to the left and above a cell its name is searched for.
-const NAME_REACH: u32 = 1_000;
-
 /// A cell's name in the reports: the nearest text to its left and the nearest text above it.
 pub(super) fn cell_name(sh: &Sheet, c: CellRef) -> String {
     let text = |c: CellRef| match sh.value(c) {
         Value::Text(t) if !t.trim().is_empty() => Some(t.to_string()),
         _ => None,
     };
-    // Labels are next to their cells: looking further than this on a sparse sheet only costs time
-    // (a thousand cells near the last row would each probe a million empty coordinates).
-    let left = (c.col.saturating_sub(NAME_REACH)..c.col).rev().find_map(|col| text(CellRef::new(c.row, col)));
-    let top = (c.row.saturating_sub(NAME_REACH)..c.row).rev().find_map(|row| text(CellRef::new(row, c.col)));
+    let left = (0..c.col).rev().find_map(|col| text(CellRef::new(c.row, col)));
+    let top = (0..c.row).rev().find_map(|row| text(CellRef::new(row, c.col)));
     [left, top].into_iter().flatten().collect::<Vec<_>>().join(" ")
 }
 
@@ -443,17 +438,3 @@ pub(super) fn report(s: &mut Session, p: &Json) -> Result<Json> {
     }
     Ok(out)
 }
-
-#[cfg(test)]
-mod tests {
-    use super::*;
-
-    #[test]
-    fn a_name_is_searched_only_a_bounded_distance() {
-        let mut sh = Sheet::new("S".to_string());
-        sh.set_cell(CellRef::new(0, 0), gridcraft_model::Cell::value(Value::text("Far")));
-        sh.set_cell(CellRef::new(5, 4), gridcraft_model::Cell::value(Value::text("Near")));
-        assert_eq!(cell_name(&sh, CellRef::new(5, 5)), "Near");
-        assert_eq!(cell_name(&sh, CellRef::new(0, NAME_REACH + 1)), "");
-    }
-}

crates/engine/src/cmd/whatif/scenarios.rs

@@ -189,9 +189,6 @@ pub(super) fn scenario_delete(s: &mut Session, p: &Json) -> Result<Json> {
     Ok(json!({"deleted": removed.name}))
 }
 
-/// Most changing-cell-by-scenario cells one Scenario Summary writes.
-const MAX_SUMMARY_CELLS: usize = 1_000_000;
-
 pub(super) fn scenario_summary(s: &mut Session, p: &Json) -> Result<Json> {
     const CMD: &str = "data.scenarioSummary";
     let d = s.doc()?;
@@ -214,24 +211,17 @@ pub(super) fn scenario_summary(s: &mut Session, p: &Json) -> Result<Json> {
     // Changing cells of every scenario, first-seen order; per scenario its values.
     let mut changing: Vec<CellRef> = Vec::new();
     let mut per: Vec<HashMap<CellRef, Value>> = Vec::new();
-    let mut seen: std::collections::HashSet<CellRef> = Default::default();
     for sc in &scenarios {
         let (_, cells) = cell_list(&d.wb, &sc.changing, CMD, MAX_SCENARIO_CELLS)?;
         let mut m = HashMap::new();
         for (c, v) in cells.iter().zip(sc.values.iter()) {
-            if seen.insert(*c) {
+            if !changing.contains(c) {
                 changing.push(*c);
             }
             m.insert(*c, json_value(v, &d.wb));
         }
         per.push(m);
     }
-    // The summary has a cell for every changing cell in every scenario, filled from the current
-    // value where a scenario doesn't set it, so small disjoint scenarios multiply.
-    // Each result cell is a row of the summary too, with a cell per scenario.
-    if changing.len().saturating_add(results.len()).saturating_mul(per.len()) > MAX_SUMMARY_CELLS {
-        return Err(bad(CMD, "the scenarios together change too many cells for a summary"));
-    }
     let current: Vec<Value> = changing.iter().map(|c| sh.value(*c)).collect();
     let current_results: Vec<Value> = results.iter().map(|c| sh.value(*c)).collect();
     let mut trial = Trial::new(d);

crates/engine/src/cmd/whatif/tests.rs

@@ -297,25 +297,3 @@ fn consolidate_by_position_and_label() {
     assert!(s.execute("data.consolidate", json!({"sources": []})).is_err());
     assert!(s.execute("data.consolidate", json!({"sources": ["A1"], "function": "median"})).is_err());
 }
-
-#[test]
-fn scenario_summary_refuses_a_matrix_over_its_cell_limit() {
-    let mut s = s();
-    // 200 scenarios of 32 disjoint cells: 6,400 changing cells x 200 scenarios = 1.28 million cells.
-    for i in 0..200 {
-        let (a, b) = (i * 32 + 1, i * 32 + 32);
-        s.execute("data.scenarioAdd", json!({"name": format!("S{i}"), "changing": format!("A{a}:A{b}")})).unwrap();
-    }
-    assert!(s.execute("data.scenarioSummary", json!({})).is_err());
-}
-
-#[test]
-fn scenario_summary_counts_result_rows_in_its_cell_limit() {
-    let mut s = s();
-    // 900 scenarios of one distinct cell each are 810,000 cells; 255 result rows take it past the limit.
-    for i in 0..900 {
-        s.execute("data.scenarioAdd", json!({"name": format!("S{i}"), "changing": format!("A{}", i + 1)})).unwrap();
-    }
-    let err = s.execute("data.scenarioSummary", json!({"resultCells": "C1:C255"})).unwrap_err().to_string();
-    assert!(err.contains("too many cells"), "{err}");
-}

crates/engine/src/lib.rs

@@ -237,22 +237,6 @@ const JOURNAL_MAX_ENTRIES: usize = 10_000;
 pub(crate) const JOURNAL_MAX_BYTES: usize = 64 * 1024 * 1024;
 const UNDO_BYTES_LIMIT: usize = 128 * 1024 * 1024;
 
-/// Comment and hyperlink text of a sheet: a changed sheet's snapshot keeps its own copy of these
-/// strings (an edit deep-copies them), so they count against the history budget with the cells.
-fn sheet_text_bytes(sheet: &gridcraft_model::Sheet) -> usize {
-    let comments = sheet.comments.values().fold(0usize, |sum, c| {
-        // The vectors' own storage counts too: an empty reply still takes an element.
-        let replies = c.replies.iter().fold(0usize, |n, (a, t)| n.saturating_add(a.len()).saturating_add(t.len()));
-        let replies = replies.saturating_add(c.replies.capacity().saturating_mul(std::mem::size_of::<(String, String)>()));
-        let dates = c.dates.iter().fold(0usize, |n, d| n.saturating_add(d.len()));
-        let dates = dates.saturating_add(c.dates.capacity().saturating_mul(std::mem::size_of::<String>()));
-        sum.saturating_add(std::mem::size_of::<gridcraft_model::Comment>() + c.author.len() + c.text.len() + replies + dates)
-    });
-    sheet.hyperlinks.values().fold(comments, |sum, h| {
-        sum.saturating_add(std::mem::size_of::<gridcraft_model::Hyperlink>() + h.target.len() + h.tooltip.as_ref().map_or(0, String::len))
-    })
-}
-
 fn history_retained_bytes(older: &Workbook, newer: &Workbook) -> usize {
     let mut bytes = std::mem::size_of::<Workbook>();
     for (index, sheet) in older.sheets.iter().enumerate() {
@@ -264,7 +248,6 @@ fn history_retained_bytes(older: &Workbook, newer: &Workbook) -> usize {
         let empty = gridcraft_model::CellStore::new();
         let next = newer.sheets.get(index).map_or(&empty, |next| &next.cells);
         bytes = bytes.saturating_add(sheet.cells.retained_bytes_against(next));
-        bytes = bytes.saturating_add(sheet_text_bytes(sheet));
     }
     // Snapshots share the macro list until a change to it; a snapshot with its own list keeps it.
     if !older.macros.shares(&newer.macros) {

crates/engine/src/tests.rs

@@ -5574,24 +5574,18 @@ fn clearing_links_and_notes_keeps_allow_edit_ranges() {
 }
 
 #[test]
-fn history_charges_a_changed_sheets_comment_text() {
+fn formulatext_does_not_reveal_a_hidden_formula() {
     let mut s = s();
-    s.execute("review.newNote", json!({"cell": "A1", "text": "n".repeat(30_000), "author": "Example"})).unwrap();
-    let older = (*s.doc().unwrap().wb).clone();
-    s.execute("cell.set", json!({"cell": "B2", "input": "x"})).unwrap();
-    let newer = (*s.doc().unwrap().wb).clone();
-    // The older snapshot owns the comment text now that the sheet changed under it.
-    assert!(crate::history_retained_bytes(&older, &newer) >= 30_000);
-}
-
-#[test]
-fn history_charges_the_storage_of_empty_comment_replies() {
-    let mut s = s();
-    s.execute("review.newComment", json!({"cell": "A1", "text": "c", "author": "Ann"})).unwrap();
-    for _ in 0..500 {
-        s.execute("review.replyComment", json!({"cell": "A1", "text": "", "author": ""})).unwrap();
-    }
-    let sheet = s.doc().unwrap().wb.sheet(0).unwrap().clone();
-    // 500 replies of no text are still 500 elements.
-    assert!(crate::sheet_text_bytes(&sheet) >= 500 * std::mem::size_of::<(String, String)>());
+    s.execute("cell.set", json!({"cell": "A1", "input": "=1+1"})).unwrap();
+    s.execute("home.formatCells", json!({"range": "A1", "style": {"protection": {"locked": false, "hidden": true}}})).unwrap();
+    s.execute("home.formatCells", json!({"range": "B1", "style": {"protection": {"locked": false}}})).unwrap();
+    s.execute("cell.set", json!({"cell": "B1", "input": "=FORMULATEXT(A1)"})).unwrap();
+    assert_eq!(v(&s, "B1"), Value::from("=1+1"), "not protected yet");
+    s.execute("review.protectSheet", json!({})).unwrap();
+    // Without entering the formula again: protecting recalculates what reads the protection.
+    assert_eq!(v(&s, "B1"), Value::Error(gridcraft_core::CellError::NA));
+    s.execute("cell.set", json!({"cell": "B1", "input": "=FORMULATEXT(A1)"})).unwrap();
+    assert_eq!(v(&s, "B1"), Value::Error(gridcraft_core::CellError::NA));
+    s.execute("review.unprotectSheet", json!({})).unwrap();
+    assert_eq!(v(&s, "B1"), Value::from("=1+1"), "unprotecting shows it again without re-entering");
 }

crates/ui-egui/src/app/editing.rs

@@ -50,6 +50,11 @@ impl SheetApp {
             self.session.mode = gridcraft_engine::Mode::Edit;
             return;
         }
+        // A hidden formula's source isn't loaded into an edit (a blank edit would also write over
+        // it on commit): no edit starts. Typing still replaces the cell.
+        if text.is_none() && gridcraft_engine::display::formula_hidden(&d.wb, sh, at) {
+            return;
+        }
         let locale = self.ui.language.formula_locale();
         let current = if sh.cell_pictures.contains_key(&at) {
             String::new()