Public API proposal

[wojciechczerniak]

Description

A proposal for a public API. It will be easier to discuss it here.

Requirements

• Sheets are sometimes used as a number, sometimes as a string. This should be unified.
• addSheet` method should not generate an ID. Instead, it should accept a name as an argument. This way we can name it the same as Handsontable instance and use this for multiple worksheets support.
• Add general-purpose and lifecycle methods: destroy, updateConfig
• Add better formula support: registerFormula, getFormula, listAllFormulas
• Add better language support: registerLanguage, getLanguage, listAllLanguages
• Add helpers for formulas: calculateFormula, validateFormula, normalizeFormula
• Add value accesors: getCellValue, getCellFormula, getRangeValues, getRangeFormulas
• Add helpers for address translations
• Add events to notify about graph / value changes: valueUpdated, formulaUpdated
• Remove methods that are unnecessary as a public API: getSheetDimensions, getSheetsDimensions, getStats, forceApplyPostponedTransformations, disableNumericMatrices, getSheetsDimensions, getSheetDimensions
• isItPossibleToChangeContent is a neat idea, but it will work only for a single cell in cooperation with setCellContent. How do we validate if we can proceed with setMultipleCellContents ?
• Export all enums, types and errors as statics so they are availble for developers
• API methods addColumns and removeColumns have inconsistent signatures. Same for addRow, removeRow, isItPossibleToAddColumns, isItPossibleToAddColumns.

API for V1

• Constructor

  public static buildFromSheets(sheets: Sheets, config?: Config): HyperFormula;
  public static buildFromArray(sheet: Sheet, config?: Config): HyperFormula;
  public static buildEmpty(config?: Config): HyperFormula;
  
  public constructor(config?: Config): HyperFormula;

• Destroy instance Add a method to destroy engine instance #45

  public destroy(): void;

• Sheet operations AddSheet should return generated name #26 / AddSheet helpers have different sytnax #28

  public addSheet(name: string): string;
  public removeSheet(name: string);
  public isItPossibleToAddSheet(name: string): boolean;
  public isItPossibleToRemoveSheet(name: string): boolean;

• Sheet helpers Worksheet helpers and name to id translation #32

  public sheetId(sheetName: string): number | undefined;
  public sheetName(sheetId: number): string | undefined;
  public doesSheetExist(sheetName: string): boolean;
  public numberOfSheets(): number;
  public renameSheet(sheetId: number, newName: string): void;

• Cell getters

  public getCellValue(address: SimpleCellAddress): CellValue; 
  public getCellFormula(address: SimpleCellAddress): CellFormula;
  public getCellSerialized(address: SimpleCellAddress): CellValue;

• Cells value setters Set cell value methods #31

  public setCellsContents(cellAddress: SimpleCellAddress, values: CellValue[][]): CellValueChange[];
  public isItPossibleSetCellContents(cellAddress: SimpleCellAddress, width = 1, height = 1): boolean;

• Sheet set content There is no API to replace sheet content #33

  public isItPossibleToSetSheetContent(sheet: string): boolean;
  public setSheetContent(sheet: string, values: CellValue[][]): CellValueChange[];

• Cell helpers Cell helpers: hasValue, hasFormula, getType #30

  public getCellType(address: SimpleCellAddress): CellType
  public doesCellHaveSimpleValue(address: SimpleCellAddress): boolean 
  public doesCellHaveFormula(address: SimpleCellAddress): boolean
  public isCellEmpty(address: SimpleCellAddress): boolean 
  public isCellPartOfMatrix(address: SimpleCellAddress): boolean 
  public getCellValueType(address: SimpleCellAddress): CellValueType

• Create / remove row / column with noncontigous support Move operations on Columns, Rows #29

  type Index  = [number, number] // [startIndex, amount]
  
  public addRows(sheet: number, ...indexes: Index[]): CellValueChange[]
  public removeRows(sheet: number, ...indexes: Index[]): CellValueChange[]
  public addColumns(sheet: number, ...indexes: Index[]): CellValueChange[]
  public removeColumns(sheet: number, ...indexes: Index[]): CellValueChange[]
  
  public isItPossibleToAddRows(sheet: number, ...indexes: Index[]): CellValueChange[]
  public isItPossibleToRemoveRows(sheet: number, ...indexes: Index[]): CellValueChange[]
  public isItPossibleToAddColumns(sheet: number, ...indexes: Index[]): CellValueChange[]
  public isItPossibleToRemoveColumns(sheet: number, ...indexes: Index[]): CellValueChange[]

• Address translation helpers Address translation helpers #22

  public simpleCellAddressToString(address: SimpleCellAddress, sheet: number): string
  public simpleCellAddressFromString(stringAddress: string, sheet: number): SimpleCellAddress

• Batch operations Batch operations #18

  public suspendEvaluation(): void
  public resumeEvaluation(): void
  public isEvaluationSuspended(): boolean
  public batch(callback: function): CellValueChange[]
  public rebuildAndRecalculate(): void

• Export version and buildDate from package.json Export version and build date as static properties #19

  static version: string; // semver
  static buildDate: string;

• moveRows / moveColumns / moveSheets methods Move operations on Columns, Rows #29

  public moveRows(sheet: number, ... ? ...): CellValueChange[]
  public isItPossibleToMoveRows(sheet: number, ... ? ...): CellValueChange[]
  
  public moveColumns(sheet: number, ... ? ...): CellValueChange[]
  public isItPossibleToMoveColumns(sheet: number, ... ? ...): CellValueChange[]

• Formulas helpers Formula helpers #24

   public calculateFormula(formula: string): any;
   public validateFormula(formula: string): boolean; 
   public normalizeFormula(formula: string): string;

• Events Add events to notify about value and formula updates #135

  public on(eventName: string | enum, callback): void;
  public once(eventName: string | enum, callback): void;
  public off(eventName: string | enum, callback): void;

• Named Expressions Named Expressions: API and structure (aka Stage 1) #239

   public addNamedExpression(name: string, value, scope, { comment, visibility })
   public removeNamedExpression(name: string, scope: string)
   public updateNamedExpression(name: string, value, scope, { comment, visibility })
   public getNamedExpressionValue(name string, scope: string)
   public getNamedExpressionFormula(name: string, scope: string)
   public listAllNamedExpressions(scope?: string)

• Get all sheet data Get all sheets data #178

  public getAllSheetsFormulas(): Record<string, Maybe<string>[][]>
  public getAllSheetsValues(): Record<string, CellValue[][]>
  public getAllSheetsSerialized(): Record<string, CellValue[][]>
  
  public getSheetSerialized(sheet: number): CellValue[][]
  public getSheetFormulas(sheet: number): Maybe<string>[][]
  public getSheetValues(sheet: number): CellValue[][]

• Range getters Cell range value and formulas getters #34

  public getRangeValues(range: AbsoluteCellRange): CellValue[][]
  public getRangeFormulas(range: AbsoluteCellRange): CellValue[][]
  public getRangeSerialized(range: AbsoluteCellRange): CellValue[][]

• Update configuration There is no way to update config after engine initialization #117

  public updateConfig(config: ConfigParams): void
  public getConfig(): ConfigParams

• Custom formulas API Add support for custom functions #37

  public static registerFormula(formula: Formula): void
  public static getFormulas(): string[]

• Custom languages API Languages support #36

  public static registerLanguage(code: string, lang: RawTranslationPackage): void
  public static getLanguages(code: string): TranslationPackage
  public static getRegisteredLanguagesCodes(): string[]
  public static unregisterLanguage(code: string): void

[wojciechczerniak]

Note: new static buildDate: string;, nice to have, non blocking

[wojciechczerniak]

Change propositon to handle noncontiguous rows / columns CRUD @bardek8

type Index  = [number, number] // [startIndex, amount]
public addRows(sheet: number, ...indexes: Index[]): CellValueChange[]
public removeRows(sheet: number, ...indexes: Index[]): CellValueChange[]
public addColumns(sheet: number, ...indexes: Index[]): CellValueChange[]
public removeColumns(sheet: number, ...indexes: Index[]): CellValueChange[]
public isItPossibleToAddRows(sheet: number, ...indexes: Index[]): CellValueChange[]
public isItPossibleToRemoveRows(sheet: number, ...indexes: Index[]): CellValueChange[]
public isItPossibleToAddColumns(sheet: number, ...indexes: Index[]): CellValueChange[]
public isItPossibleToRemoveColumns(sheet: number, ...indexes: Index[]): CellValueChange[]

Change proposition to handle batch operations @bardek8

engine.batch((e) => {
    e.addRows(...)
    e.moveCells(...)
})

[wojciechczerniak]

Excerpt from #12. If we have worksheet name, we will need to translate it to ID for most of the API methods.

public getSheetID(sheetName: string): number;
public getSheetName(sheetID: number): string;

[wojciechczerniak]

It would be helpful to get the type of value that was coerced from a string. We wouldn't have to do it on our own and both data sources will be easier to keep in sync.

Also, we would need an event on data type change to notify the Handsontable.

[wojciechczerniak]

According to the spec, we need a method to replace worksheet. setMultipleCellContents is close it would not clear data if they are out of it's range. New method: setSheetContent or similar is needed.

[wojciechczerniak]

I have removed setCulture methods from the list as this was simplified to #42. Moved recalculate methods to batch operations from formula helpers, they were never a part of #24. Get all data method #178 was added yesterday,

[wojciechczerniak]

Added Named Expressions to the API list #126

[wojciechczerniak]

Updated the Batch Operations #18 methods list. Up for a discussion in #18

[wojciechczerniak]

#31 marked as done.

[f1ames]

@wojciechczerniak did you have any thoughts/discussions (or do you have any plans) on an API method which allows to get cells affected/covered by the specific formula?

So for example a method where one can pass a valid formula (probably as a string, extracted earlier from any cell or just created manually) and the method returns indexes of cells (and sheets) which are referenced by this formula? Such API would allow creating visual guidance (in UI layer) in real time for any formula like e.g. in Excel/GSheets:

Or is there any other way to achieve the similar result without the above method?