.KEY File Format – Formulas

Formula and syntax tree

A formula, TSCE.FormulaArchive, is a flat array of nodes in ASTNodeArrayArchive: the tree is written out as a list, with each function or operator node giving the number of arguments it takes (AST_function_node_numArgs, AST_list_node_numArgs). The order of the list and whether it is prefix or postfix is not stated in the source (unknown). host_column and host_row say which cell hosts the formula, for relative references.

message FormulaArchive {
  1 req ASTNodeArrayArchive AST_node_array;  2 uint32 host_column;  3 uint32 host_row;
  4 bool host_column_is_negative [default false];  5 bool host_row_is_negative [default false];
  6 FormulaTranslationFlagsArchive translation_flags
}
message FormulaTranslationFlagsArchive { 1 bool excel_import_translation [default false];
  2 bool number_to_date_coercion_removal_translation [default false] }
message ASTNodeArrayArchive {
  1 rep ASTNodeArchive AST_node
  message ASTNodeArchive {
    1 req ASTNodeType AST_node_type;
    2 uint32 AST_function_node_index;  3 uint32 AST_function_node_numArgs;
    4 double AST_number_node_number;  5 bool AST_boolean_node_boolean;
    6 string AST_string_node_string;
    7 double AST_date_node_dateNum;  19 bool AST_date_node_suppress_date_format;
    20 bool AST_date_node_suppress_time_format;  21 string AST_date_node_date_time_format;
    8 double AST_duration_node_unitNum;  9 int32 AST_duration_node_unit;
    22 uint32 AST_duration_node_style;  23 uint32 AST_duration_node_duration_unit_largest;
    24 uint32 AST_duration_node_duration_unit_smallest;  29 bool AST_duration_node_use_automatic_units;
    10 bool AST_token_node_boolean;  11 uint32 AST_array_node_numCol;  12 uint32 AST_array_node_numRow;
    13 uint32 AST_list_node_numArgs;  14 ASTNodeArrayArchive AST_thunk_node_array;
    15 ASTLocalCellReferenceNodeArchive AST_local_cell_reference_node_reference;
    16 ASTCrossTableCellReferenceNodeArchive AST_cross_table_cell_reference_node_reference;
    17 string AST_unknown_function_node_string;  18 uint32 AST_unknown_function_node_numArgs;
    25 string AST_whitespace;  26 ASTColumnCoordinateArchive AST_column;
    27 ASTRowCoordinateArchive AST_row;
    28 ASTCrossTableReferenceExtraInfoArchive AST_cross_table_reference_extra_info
  }
  message ASTLocalCellReferenceNodeArchive { 1 req uint32 row_handle; 2 req uint32 column_handle;
    3 req uint32 row_is_sticky; 4 req uint32 column_is_sticky }
  message ASTCrossTableCellReferenceNodeArchive { 1 req uint32 row_handle; 2 req uint32 column_handle;
    3 req uint32 row_is_sticky; 4 req uint32 column_is_sticky; 5 req CFUUIDArchive table_id;
    6 string AST_reference_whitespace_after_sheet_name; 7 string AST_reference_whitespace_before_table_name;
    8 string AST_reference_whitespace_after_table_name; 9 string AST_reference_whitespace_before_cell_address }
  message ASTColumnCoordinateArchive { 1 req sint32 column; 2 bool absolute [default false] }
  message ASTRowCoordinateArchive { 1 req sint32 row; 2 bool absolute [default false] }
  message ASTCrossTableReferenceExtraInfoArchive { 1 req CFUUIDArchive table_id;
    2 string AST_reference_whitespace_after_sheet_name; 3 string AST_reference_whitespace_before_table_name;
    4 string AST_reference_whitespace_after_table_name; 5 string AST_reference_whitespace_before_cell_address }
}

The meaning given in the last column is read from the field names; the source gives no prose. Unknown: the table that maps AST_function_node_index to a function name (the function list is not in the sources read), the units behind AST_duration_node_unit and _style, and what the row and column handles of a local reference index.

References and identifiers

message CFUUIDArchive { 1 bytes uuid_bytes;  2 uint32 uuid_w0;  3 uint32 uuid_w1;  4 uint32 uuid_w2;  5 uint32 uuid_w3 }
message CellReferenceArchive { 1 req uint32 column; 2 req uint32 row; 3 req CFUUIDArchive table_id }
message InternalCellReferenceArchive { 1 req CellCoordinateArchive coordinate; 2 req uint32 owner_id }
message RangeReferenceArchive { 1 req CFUUIDArchive table_id; 2 req uint32 top_left_column;
  3 req uint32 top_left_row; 4 req uint32 bottom_right_column; 5 req uint32 bottom_right_row }
message InternalRangeReferenceArchive { 1 req uint32 owner_id; 2 req RangeCoordinateArchive range }
message ReferenceArchive { 1 req ReferenceType reference_type; 2 CellReferenceArchive cell_reference;
  3 RangeReferenceArchive range_reference
  enum ReferenceType { CELL_REFERENCE = 1; RANGE_REFERENCE = 2; NAME_DEPENDENCY = 3; } }
message RangeCoordinateArchive { 1 req uint32 top_left_column; 2 req uint32 top_left_row;
  3 req uint32 bottom_right_column; 4 req uint32 bottom_right_row }
message CellCoordinateArchive { 1 fixed32 packedData; 2 uint32 column; 3 uint32 row }
message CellCoordinateVectorArchive { 1 rep CellCoordinateArchive cell_coordinate }
message CellCoordinateFormulaPairArchive { 1 req uint32 column; 2 req uint32 row; 3 FormulaArchive formula }
message CellCoordinateObjectPairArchive { 1 req CellCoordinateArchive cell_coordinate; 2 req TSP.Reference object }
message CellCoordinateObjectMapArchive { 1 rep CellCoordinateObjectPairArchive cell_coordinate_object_pair }
message OwnerFormulaMapArchive { 1 rep CellCoordinateFormulaPairArchive coordinate_formula_pair }
message NameTrackedReferencePair { 1 req string name; 2 TSP.Reference tracked_reference; 3 uint32 tracked_reference_id }
message NamesByTrackedReferenceArchive { 1 req CFUUIDArchive table_id; 2 rep NameTrackedReferencePair name_tracked_reference_pair }
message NamedReferenceManagerArchive {         // type 4003
  1 req TSP.Reference reference_tracker;  2 rep NamesByTrackedReferenceArchive names_by_tracked_reference_by_table [deprecated] }
message TrackedReferenceArchive { 1 req ASTNodeArrayArchive ast; 2 req uint32 formula_id }   // type 4005
message ReferenceTrackerArchive {              // type 4004
  1 req CFUUIDArchive uuid;  2 rep TSP.Reference tracked_reference [deprecated];
  3 rep TrackedReferenceArchive contained_tracked_reference }
message NumberTrackedReferencePairArchive { 1 req uint32 number; 2 TSP.Reference tracked_reference }
message NumberToTrackedReferenceMapArchive { 1 rep NumberTrackedReferencePairArchive number_tracked_reference_pair }

Cell values and formats

message CellValueArchive {
  1 req CellValueType cell_value_type;  2 BooleanCellValueArchive boolean_value;
  3 DateCellValueArchive date_value;  4 NumberCellValueArchive number_value;
  5 StringCellValueArchive string_value
  enum CellValueType { NIL_TYPE = 1; BOOLEAN_TYPE = 2; DATE_TYPE = 3; NUMBER_TYPE = 4; STRING_TYPE = 5; }
}
message BooleanCellValueArchive { 1 req bool value }
message DateCellValueArchive { 1 req double value; 2 req TSK.FormatStructArchive format;
  3 bool format_is_implicit [default true] }
message NumberCellValueArchive { 1 req double value; 2 req int32 unit; 3 req TSK.FormatStructArchive format;
  4 bool format_is_implicit [default true] }
message StringCellValueArchive { 1 req string value; 2 req TSK.FormatStructArchive format;
  3 bool format_is_implicit [default true] }
message TSCE.FormatStructArchive {
  1 req uint32 format_type;  2 uint32 decimal_places;  3 string currency_code;  4 uint32 negative_style;
  5 bool show_thousands_separator;  6 bool use_accounting_style;  7 uint32 duration_style;
  8 uint32 base;  9 uint32 base_places;  10 bool base_use_minus_sign;  11 uint32 fraction_accuracy;
  12 bool suppress_date_format;  13 bool suppress_time_format;  14 string date_time_format;
  15 uint32 duration_unit_largest;  16 uint32 duration_unit_smallest
}

TSCE.FormatStructArchive has the same sixteen fields as the first sixteen of TSK.FormatStructArchive; the TSK one (see document messages) adds fields 17 onwards (custom format and control settings). The cell value messages above use the TSK one.

Calculation engine and dependency tracking

message CalculationEngineArchive {             // type 4000
  1 bool base_date_1904;  2 req DependencyTrackerArchive dependency_tracker;
  3 TSP.Reference named_reference_manager;  4 ExcelImportDateMode excel_import_date_mode [default DATE_MODE_1900];
  5 string saved_locale_identifier
  enum ExcelImportDateMode { DATE_MODE_1900 = 1; DATE_MODE_1900_LEGACY = 2; DATE_MODE_1904_LEGACY = 3; }
}
message DependencyTrackerArchive { 1 rep FormulaOwnerInfoArchive formula_owner_info; 2 rep CellReferenceArchive dirty_leaf;
  4 rep InternalCellReferenceArchive internal_dirty_leaf; 3 OwnerIDMapArchive owner_id_map }
message FormulaOwnerInfoArchive { 1 req CFUUIDArchive formula_owner_id; 2 CellDependenciesArchive cell_dependencies;
  3 RangeDependenciesArchive range_dependencies; 4 VolatileDependenciesArchive volatile_dependencies;
  5 SpanningDependenciesArchive spanning_column_dependencies; 6 SpanningDependenciesArchive spanning_row_dependencies;
  8 WholeOwnerDependenciesArchive whole_owner_dependencies; 7 TSP.Reference formula_owner }
message OwnerIDMapArchive { 1 rep OwnerIDMapArchiveEntry map_entry;
  message OwnerIDMapArchiveEntry { 1 req uint32 internal_owner_id; 2 req CFUUIDArchive owner_id } }
message CellDependenciesArchive { 1 rep CellRecordArchive cell_record; 2 uint32 num_dirty_cells }
message CellRecordArchive {
  1 req uint32 column;  2 req uint32 row;  3 uint32 dirty_self_plus_precedents_count [default 0];
  4 bool is_in_a_cycle [default false];  5 rep EdgeArchive edge [deprecated];
  6 bool contains_a_formula [default true];  7 bool has_calculated_precedents [default false];
  8 bool calculate_precedents_on_next_recalc [default false, deprecated];
  10 bool translate_for_excel_import_on_next_recalc [default false];  9 EdgesArchive edges
}
message EdgeArchive { 1 req uint32 column; 2 req uint32 row; 3 req bool is_precedent; 4 CFUUIDArchive owner_id }
message EdgesArchive { 9 rep uint32 packed_edge_without_owner; 10 rep uint32 packed_edge_with_owner;
  11 rep CFUUIDArchive owner_id_for_edge; 12 rep uint32 internal_owner_id_for_edge }
message VolatileDependenciesArchive {
  all rep uint32 column/row pairs: 1,2 volatile_time_cell_column/row  3,4 volatile_random_cell_column/row
  15,16 volatile_locale_cell_column/row  5,6 volatile_location_cell_column/row
  7,8 volatile_compass_cell_column/row  9,10 calculated_dependency_cell_column/row
  13 rep CellReferenceArchive volatile_geometry_cell_reference;  11 rep CellCoordinateArchive volatile_geometry_cell
}
message RangeDependenciesArchive { 1 RTreeArchive r_tree; 2 rep RangeBackDependencyArchive back_dependency }
message RangeBackDependencyArchive { 1 req uint32 cell_coord_row; 2 req uint32 cell_coord_column;
  3 RangeReferenceArchive range_reference; 4 InternalRangeReferenceArchive internal_range_reference }
message RTreeArchive { 1 req uint32 data_file_id; 2 req uint32 data_size; 3 req uint32 data_num_dims;
  4 req uint32 data_elem_size; 5 req uint32 data_elem_real_size; 6 req uint32 data_max_nodes;
  7 req uint32 data_min_nodes; 8 req RTreeNodeArchive root }
message RTreeNodeArchive { 1 req uint32 level; 2 req uint32 count;
  3 rep RTreeInternalNodeContentsArchive internal_node_contents; 4 rep RTreeLeafNodeContentsArchive leaf_node_contents }
message RTreeInternalNodeContentsArchive { 1 rep uint32 min; 2 rep uint32 max; 3 req RTreeNodeArchive child }
message RTreeLeafNodeContentsArchive { 1 rep uint32 min; 2 rep uint32 max; 3 req CellReferenceArchive cell_reference }
message SpanningDependenciesArchive {
  1 rep uint32 column;  2 rep RangeContext range_context;  3 rep CellReferenceArchive cell;
  4 RangeCoordinateArchive total_range_for_deleted_table;  5 RangeCoordinateArchive body_range_for_deleted_table;
  6 rep ReferringColumnToLocalCells referring_column_to_local_cells;
  7 rep ReferringColumnToRemoteCells referring_column_to_remote_cells
  enum RangeContext { DEFAULT = 0; INCLUDE_HEADERS = 1; }
  message ReferringColumnToLocalCells { 1 req uint32 column; 2 req RangeContext range_context; 3 rep CellCoordinateArchive cell_coordinate }
  message ReferringColumnToRemoteCells { 1 req uint32 column; 2 req RangeContext range_context;
    3 rep InternalCellReferenceArchive internal_cell_reference }
}
message WholeOwnerDependenciesArchive { 1 rep InternalCellReferenceArchive dependent_cell }

The engine is a document-level object (type 4000). Which dependency lists hold what is read from the field names only.

Rewriting formulas after an edit

When rows or columns are inserted, deleted, moved or sorted, or cells are merged, formulas that refer to the changed area must be rewritten. The messages below describe the change; the undo commands that carry them are on the table undo commands page.

message FormulaRewriteSpecArchive {
  1 req RewriteType rewrite_type;  2 TectonicShiftArchive tectonic_shift;  3 RewriteSortMappingArchive sort_mapping;
  4 RangeMovedInfoArchive range_moved_info;  5 CFUUIDArchive deleted_owner_id;
  6 TableResizedInfoArchive table_resized_info;  7 CellMergeInfoArchive cell_merge_info
  enum RewriteType { TECTONIC_SHIFT = 1; REMAP_WITH_SORT_MAPPING = 2; MOVE_RANGE = 3; DELETE_OWNER = 4;
    TABLE_RESIZE = 5; CELL_MERGE = 6; }
}
message TectonicShiftArchive {
  1 req uint32 grid_coordinate_offset_x;  2 req uint32 grid_coordinate_offset_y;
  3 req bool grid_coordinate_offset_x_is_negative;  4 req bool grid_coordinate_offset_y_is_negative;
  5 RangeCoordinateArchive range_for_shift;  6 RangeCoordinateArchive range_for_deletion;
  7 RangeCoordinateArchive inserted_range;  8 req bool apply_to_absolute;  9 req bool inserting_after;
  10 req bool ignore_sticky_bits;  11 RangeCoordinateArchive table_range;  12 CFUUIDArchive table_id
}
message RewriteSortMappingArchive { 1 req CFUUIDArchive table_id; 2 req TSK.ShuffleMappingArchive shuffle_mapping; 3 req bool explode_ranges }
message RangeMovedInfoArchive { 1 CFUUIDArchive table_id; 2 RangeCoordinateArchive from_range; 3 RangeCoordinateArchive to_range;
  4 RangeReferenceArchive from_range_reference; 5 RangeReferenceArchive to_range_reference }
message TableResizedInfoArchive { 1 req CFUUIDArchive table_id; 2 req RangeCoordinateArchive new_table_size; 3 RangeCoordinateArchive old_table_size }
message CellMergeInfoArchive { 1 req CFUUIDArchive table_id; 2 req RangeCoordinateArchive merged_range }
message FormulaRewriteCommandArchive {         // type 4001
  1 TSP.Reference table_rewrite_command;  2 TSK.CommandArchive super }
message TrackedReferencesRewriteCommandArchive {  // type 4002
  1 NumberToTrackedReferenceMapArchive references_in_reference_tracker [deprecated];
  2 NumberToTrackedReferenceMapArchive original_tracked_reference_copies [deprecated];
  3 FormulaRewriteSpecArchive rewrite_spec;  4 req TSP.Reference reference_tracker;  5 req TSK.CommandArchive super }

“Tectonic shift” is the name the source gives to a shift of the cell grid caused by inserting or deleting rows or columns.

Older expression nodes (TST)

The TST framework also defines formula nodes as separate messages: a base ExpressionNodeArchive and subclasses that each hold it as super. They are in the type list (6182 to 6197, 6235) and TST.FormulaArchive (field 1, expressionTree) points at a tree of them. Which version writes them is not stated in the source.

message ExpressionNodeArchive {                // type 6182
  1 rep TSP.Reference children;  2 uint64 first_index [default 0];  3 uint64 last_index [default 0] }
BooleanNodeArchive [6183]:  1 req super; 2 req bool value
NumberNodeArchive [6184]:  1 req super; 2 req double number
StringNodeArchive [6185]:  1 req super; 2 req string value
IdentifierNodeArchive [6235]:  1 req super; 2 string identifier
ArrayNodeArchive [6186]:  1 req super; 2 uint32 columns; 3 uint32 rows
ListNodeArchive [6187]:  1 req super
OperatorNodeArchive [6188]:  1 req super; 2 req uint32 operatorChar
PostfixOperatorNodeArchive [6194], PrefixOperatorNodeArchive [6195]:  1 req OperatorNodeArchive super
FunctionNodeArchive [6189]:  1 req super; 2 req uint32 functionIndex; 3 string invalidFunctionName
FunctionEndNodeArchive [6196]:  1 req super
DateNodeArchive [6190]:  1 req super; 2 req string value; 3 string format
ReferenceNodeArchive [6191]:  1 req super; 2 req TSCE.RangeReferenceArchive rangeReference; 3 req uint32 stickyBits; 4 string hostTableID
DurationNodeArchive [6192]:  1 req super; 2 req double timeinterval; 3 req uint32 smallest; 4 req uint32 largest; 5 req uint32 style
ArgumentPlaceholderNodeArchive [6193]:  1 req super; 2 req uint32 functionIndex; 3 req uint32 argumentIndex; 4 uint32 mode
EmptyExpressionNodeArchive [6197]:  1 req super

Unknown: the table behind operatorChar (presumably a character code for the operator; not stated) and functionIndex.

Source

TSCEArchives.proto and the node messages of TSTArchives.proto from iWorkFileFormat (Messages/Proto), read in full; type numbers from Common.json. The sources contain no list of formula functions, no description of how the node array is ordered, and nothing newer than Keynote 6.0.