Workspace Sheet unit reference
outline, read, and find accept only Sheet Units. Use inspect for Doc, Slide, Base, and Board Units.
Reads target one remote Unit in either trunk or an explicit Worktree. Edits target a draft by Worktree and Unit ID. execute provides univerAPI, api (the same object), and workbook (an FWorkbook bound to the selected Unit). Obtain a worksheet with workbook.getActiveSheet() or workbook.getSheetByName("…"). Examples from api show use the already available univerAPI variable. Never redeclare these injected variables. execute is ESM and has no require.
Read a sheet
- Run
outlineon the Unit for a map of sheets, regions, headers, column types, formulas, errors, and objects. - Zoom with
outline, settingatto a returned ref such ass2.t1. Runreadon its printed range for cells, orfindwith terms to locate values or formulas. Use these operations before screenshots or custom statistics scripts. - Follow a continuation whenever output ends with one. Omitted is not absent. Never sum one page as if it were the whole range.
- Change cells with
executein the task's Worktree. Await calculation when formulas are involved. - Read back only the cells the answer depends on and that your
executescript did not already return. UsereadJSON fieldstype,value, andformulafor exact evidence, not display text.
Region kinds and bounds are heuristics for where to read, not business meaning. Watch subtotals: to avoid double counting, frozen, merged, errors, and via=name for defined-name references.
Refs are content-derived and bound to the revision on the first output line. Run outline again after structural edits. A large sheet appears as one block with a note: line. Read it in pages or locate cells with find.
uncached=N means formulas lack stored results. Trigger calculation with execute in a Worktree using the recipe below, then read that Worktree again; trunk shows the calculated values only after the Worktree is merged. Reading does not calculate formulas.
Read Sheet reading operations for options, refs, continuations, and JSON fields. The core Skill binds these operations to your entry.
Workspace target
Create an empty Sheet Unit or import workbook data into the selected Worktree:
Run unit create with type sheet, a name, and the destination Space in the selected Worktree, or run import with the workbook source and type sheet. Follow the core Skill for source handling.
Both commands return server-owned unitId, resourceId, and nodeId. Use the returned unitId
for every content command; never reuse an imported workbook's internal id as the Workspace address.
For original-file preservation without an editable Sheet Unit, read the Blob Skill.
Blob Resources have no unitId and cannot use this Skill's commands.
Use api find with a term and api show with an exact symbol instead of guessing Facade signatures.
Cell model: v, t, f, and number formats
A cell is structured data: { v, t, f?, s? }.
| Field | Meaning |
|---|---|
v |
Stored value, used for calculation, comparison, and writeback. |
t |
Type: 1 text, 2 number, 3 boolean, 4 forced text. |
f |
Formula source. Its cached result is stored separately in v. |
s.n.pattern |
Number format. It changes display only, never v. |
p |
Rich text; when present it overrides the displayed form of v. |
si |
Shared-formula id. Preserve it only when deep-copying an existing formula cell. |
displayValue |
Visible text rendered from v, t, and the number format. |
Use v and t for comparison and writeback; use displayValue for presentation. Never write display text back as the stored value.
Always write explicit ICellData. Bare values are inferred and can corrupt identifiers, scores, leading zeros, or date-like strings:
{ v: "text", t: 1 } { v: 42, t: 2 } { v: 1, t: 3 } // boolean; v: 0 is false
{ v: "00123", t: 4 } // forced text for IDs, scores, phone numbers, or postal codes
{ f: "=A1+B1" } // formula
{ v: "=A1+B1", t: 4 } // literal formula text, stored as text and never calculated
Dates, percentages, and currencies are numbers plus number formats, not separate types:
{ v: 44900, t: 2, s: { n: { pattern: "yyyy-MM-DD" } } } // date serial, not display text
{ v: 0.25, t: 2, s: { n: { pattern: "0%" } } }
Reading evidence
- Use
readJSON for stored values, types, and formulas. Each cell hastype,value, anddisplay, plusformulawhen present. Display text can round numbers or format dates and is not evidence of stored values. - Use
inspectwith targetrangeonly for rawICellDatafields such assiorcustom. For full styles or rich text, readgetCellDatas()throughexecute.
Common APIs
- Ranges:
sheet.getRange("A1:C9")orgetRange(row, col, numRows, numCols). Rows and columns are zero-based;"B3"equalsgetRange(2, 1). - Sheets:
getActiveSheet(),getSheetByName("Sheet1"),getSheets(). UsegetSheetName();FWorksheethas nogetName(). - Read within an editing script: prefer
getCellData()/getCellDatas()for authoritativev/t/f/s; usegetDisplayValues()for visible text,getFormula()for formulas, andgetRawValues()for stored values without trimming or normalization. For reading a Sheet, start withoutline,read, andfindabove. - Write:
setValue(cell),setValues(grid),setFormula("=…"),clearContent()for content only, andclear()for content plus formatting. - Style:
setBackgroundColor("#RRGGBB"),setFontColor("#RRGGBB"). Dimensions:getLastRow(),getLastColumn(), andsetRowCount(n)before out-of-range writes. setValues()merges cell data.{}or{ s }does not clear existingv/f/p. Clear a cell explicitly with{ v: null, f: null, p: null, si: null, custom: null }. To replace a whole region, callclearContent()first, thensetValues().
Over-grid images
Create an image through sheet.newOverGridImage(), set an explicit size and position, await
buildAsync(), then pass the built data to sheet.insertImages(). For a local image, pass the
original image data URI with api.Enum.ImageSourceType.BASE64:
const sheet = workbook.getActiveSheet();
const image = await sheet
.newOverGridImage()
.setSource(imageDataUri, api.Enum.ImageSourceType.BASE64)
.setColumn(1)
.setRow(1)
.setWidth(640)
.setHeight(360)
.buildAsync();
sheet.insertImages([image]);
Verify later with sheet.getImages()[0].toBuilder().getSource() and getSourceType().
Formulas and recalculation
Formula calculation is asynchronous, and nothing recalculates implicitly: writing a formula stores f and triggers calculation, but an immediate getValue() in the same execute call can still return null, and loading a Workspace Worktree head does not recalculate existing formulas. Formula text and cached result are separate: getFormula() reads f, while getValue() or getCellData().v reads the cache.
The completion API is onCalculationResultApplied(). Register the promise before triggering calculation, then await it:
const calculated = api.getFormula().onCalculationResultApplied();
api.getFormula().executeCalculation(); // force recalculation of existing formulas
await calculated;
return workbook.getActiveSheet().getRange("A3").getValue();
For a newly written formula, setFormula itself triggers calculation, so register the completion promise before the write and await it afterward. XLSX export stores the cached value beside each formula, so calculate before exporting. If only a final value is required and cache freshness cannot be guaranteed, writing the stored value directly is the stronger contract.
OOXML Sheet table formulas
Use explicit Excel structured-reference scopes for every Sheet table formula. Do not rely on an invented table alias or implicit intersection:
Orders[[#This Row],[Amount]](orOrders[@[Amount]]) is the scalar value in the formula row.Orders[[#Data],[Amount]]is the complete data column.Orders[Amount]is a valid column reference, but use the explicit#Dataform whenever a whole-column aggregate is intended.[@[Amount]]is valid only inside the Host table's calculated column.table[Amount]is invalid unless the real Sheet table name is exactlytable.
Copy the exact table name from the workbook's table metadata; do not use a table id, Sheet tab name,
display label, guessed case, or the word table as a placeholder. Keep current-row and full-column
references distinct when copying formulas. Await calculation, then verify both the stored formula
text and computed cell values.
Failure prevention
- Write explicit
ICellDataand specifytfor every written cell. - Do not use
getValue()/getValues()for authoritative reads. Formatted dates and currencies become display strings and booleans become1/0. UsegetCellDatas()for stored values andgetDisplayValues()for display text. - Colors must be xlsx-safe: only
"#RRGGBB"or"rgb(r,g,b)". Invalid colors can make an exported.xlsxunreadable and cannot be repaired by later overwriting the color. - Sheet tab name, Unit display name, Unit id, and resource id are distinct. Use
outlinefor Sheet tab names. Rawinspectwith targetrangerequires one explicit worksheet selector with aname:,id:, or 1-basedindex:prefix and accepts multiple A1 ranges within that worksheet. - Formulas do not trigger an implicit full recalculation. Follow the recalculation recipe above before reading calculated values or exporting.
- Copy formatted cells with a deep copy of
getCellData()or complete explicitICellData, nevergetValues()followed bysetValues(). - After importing csv or tsv, check the value type and formula of every column before computing. Types are inferred per column, so one
N/A,-, or1,234stores a whole numeric column as text, and a cell starting with=becomes forced text instead of a formula. Display text hides both.
Verification
- Values, types, and formulas: run
readwith ranges1!A1:C9and JSON output for the Sheet Unit in its Worktree. Checktype,value, andformulaafter calculation. - Styles and number formats: use
readwith style content and JSON output. For borders, full styles, or rich text, read backgetCellDatas()throughexecute. - Raw cell metadata such as
siorcustom:inspectwith targetrange, rangeA1:C9, and worksheet selectorname:<sheet-name>for the Sheet Unit in its Worktree.
Rich text (when mixed styling inside one cell is required)
Rich text stores styled runs in cell.p, not cell.v. Build it with api.newRichText(); never construct cell.p.body.textRuns by hand.
const rich = api.newRichText();
rich.insertText("Hello World");
rich.setStyle(0, 5, { bl: 1, cl: { rgb: "#FF0000" } });
workbook.getActiveSheet().getRange("A1").setRichTextValueForCell(rich);
setStyle(start, end, style)uses a half-open character range[start, end). The builder has nogetLength/getTexthelper, so derive offsets from the JavaScript strings you insert.- Common style fields:
blbold,ititalic,cltext color,bgbackground color. Colors must be xlsx-safe#RRGGBBorrgb(r,g,b). - For ranges, call
setRichTextValueswith one builder orRichTextValueper cell. - When highlighting repeated terms, find each substring and apply one style per matched range; do not style one character at a time.
- Verify by reading back
getCellData().pand checkingbody.textRunsranges andtsstyles.
Export only after formula and style verification:
Run screenshot for worksheet name <name> and range A1:H40 in the Unit's Worktree, then export with format xlsx for the same Unit. Follow the core Skill for output handling.