Spreadsheet Events

JavaScript's events are the perfect way to integrate the spreadsheet with your applications. There are three ways to handler those events, such as:

The global event dispatcher method;
Implementing methods for individual events;
Implementing the onevent method inside a spreadsheet plugin;

In this section, we provide more details about the first two methods. If you are interested in the events inside plugins, please visit the plugin's documentation.

Specific event handlers

There are several specific event handlers available in Jspreadsheet as shown below:

Event	description
onload	`onload(spreadsheet: Object) => void` This method is called when the data in the spreadsheet is ready.
onclick	`onclick(worksheet: Object, section: String, x: Number, y: Number, event: Object) => void` When the spreadsheet is clicked. Sections: nested \| header \| row \| cell \| selectall \| tabs \| cloning \| toolbar \| footer
onundo	`onundo(worksheet: Object, historyRecord: Object) => void` When undo is applied
onredo	`onredo(worksheet: Object, historyRecord: Object) => void` When redo is applied
onbeforesave	`onbeforesave(spreadsheet: Object, worksheet: Object, data: Object) => Boolean \| Object` Before any data is sent to the backend. Can be used to overwrite the data or to cancel the action when return false.
onsave	`onsave(spreadsheet: Object, worksheet: Object, data: Object) => void` After the data is sent to the server.
onbeforechange	`onbeforechange(worksheet: Object, cell: DOMElement, x: Number, y: Number, value: Any) => Boolean \| Any` Before a column value is changed. NOTE: It is possible to overwrite the original value, by returning a new value on this method. v3.4.0+
onchange	`onchange(worksheet: Object, cell: DOMElement, x: Number, y: Number, newValue: Any, oldValue: Any) => void` After a column value is changed.
onafterchanges	`onafterchanges(worksheet: Object, records: Array)` After all changes are applied in the table.
oncopy	`oncopy(worksheet: Object, selectedCells: Array, data: String) => String` When a copy is performed in the spreadsheet. Any string returned will overwrite the user data or return null to progress with the default behavior.
onbeforepaste	`onbeforepaste(worksheet: Object, data: Array, x: Number, y: Number, style: Array) => boolean \| Array` Before the paste action is performed. Can return parsed or filtered data, can cancel the action when return false.
onpaste	`onpaste(worksheet: Object, data: Array) => void` After a paste action is performed in the spreadsheet.
onbeforeinsertrow	`onbeforeinsertrow(worksheet: Object, rowNumber: Number, numOfRows: Number, insertBefore: Boolean) => boolean \| void` Before a new row is inserted. You can cancel the insert event by returning false.
oninsertrow	`oninsertrow(worksheet: Object, rowNumber: Number, numOfRows: Number, records: Array, insertBefore: Boolean) => void` After a new row is inserted.
onbeforedeleterow	`onbeforedeleterow(worksheet: Object, rowNumber: Number, numOfRows: Number) => boolean \| void` Before a row is deleted. You can cancel the delete event by returning false.
ondeleterow	`ondeleterow(worksheet: Object, rowNumber: Number, numOfRows: Number, rowDOMElements: Array, rowData: Array, cellAttributes: Array) => void` After a row is deleted.
onbeforeinsertcolumn	`onbeforeinsertcolumn(worksheet: Object, columnNumber: Number, numOfColumns: Number, insertBefore: Boolean) => boolean \| void` Before a new column is inserted. You can cancel the insert event by returning false.
oninsertcolumn	`oninsertcolumn(worksheet: Object, columnNumber: Number, numOfColumns: Number, historyRecords: Array, insertBefore: Boolean) => void` After a new column is inserted.
onbeforedeletecolumn	`onbeforedeletecolumn(worksheet: Object, columnNumber: Number, numOfColumns: Number) => Boolean \| void` Before a column is excluded. You can cancel the insert event by returning false.
ondeletecolumn	`ondeletecolumn(worksheet: Object, columnNumber: Number, numOfColumns: Number, affectedDOMElements: Array, historyProperties: Array, cellAttributes: Array) => void` After a column is excluded.
onmoverow	`onmoverow(worksheet: Object, origin: Number, destination: Number) => void;` After a row is moved to a new position.
onmovecolumn	`onmovecolumn(worksheet: Object, origin: Number, destination: Number) => void` After a column is moved to a new position.
onresizerow	`onresizerow(worksheet: Object, row: Mixed, height: Mixed, oldHeight: Mixed) => void` After a height change for one or more rows.
onresizecolumn	`onresizecolumn(worksheet: Object, column: Mixed, width: Mixed, oldWidth: Mixed) => void` After a column width change for one or more columns.
onselection	`onselection(worksheet: Object, px: Number, py: Number, ux: Number, uy: Number, origin?: Object) => void` When the selection is changed.
onbeforecomments	`onbeforecomments(worksheet: Object, newValues: Object) => Boolean \| void` Before a new comment is added or updated. Return false to cancel the event.
oncomments	`oncomments(worksheet: Object, cells: Object, oldValues: Object) => void` After a new comment is added or updated.
onbeforesort	`onbeforesort(worksheet: Object, column: Number, direction: Number, newOrderValues: Array) => Any` It runs before sorting a column. It should return an array with a custom sorting or false to cancel the user action.
onsort	`onsort(worksheet: Object, column: Number, direction: Number, newOrderValues: Array) => void` When a column is sorted.
onfocus	`onfocus(worksheet: Object) => void` When the spreadsheet gets the focus.
onblur	`onblur(worksheet: Object) => void` When the spreadsheet loses the focus.
onmerge	`onmerge(worksheet: Object, newValue: Object, oldValue: Object) => void` When merge cells is executed.
onchangeheader	`onchangeheader(worksheet: Object, column: Number, newValue: String, oldValue: String) => void` When the header title is changed.
onchangefooter	`onchangefooter(worksheet: Object, newValue: [], oldValue: []) => void` When the footers are created or updated.
onchangefootervalue	`onchangefootervalue(worksheet: Object, x: Number, y: Number, value: String) => void` When the value in a cell footer is changed.
onchangenested	`onchangenested(worksheet: Object, options: object) => void` When updating the nested headers
onchangenestedcell	`onchangenestedcell(worksheet: Object, x: Number, y: Number, properties: Object) => void` When updating a nested cell
oncreateeditor	`oncreateeditor(worksheet: Object, cell: DOMElement, x: Number, y: Number, element: DOMElement, options: Object) => void` When an editor is created.
oneditionstart	`oneditionstart(worksheet: Object, cell: DOMElement, x: Number, y: Number) => void` When the editor is opened.
oneditionend	`oneditionend(worksheet: Object, cell: DOMElement, x: Number, y: Number, newValue: Any, save: Boolean) => void` When the editor is closed.
onchangestyle	`onchangestyle(worksheet: Object, newValue: Object, oldValue: Object) => void` When the style of a cell is changed.
onchangemeta	`onchangemeta(worksheet: Object, newValue: Object) => void` When cell meta information is added or updated.
onbeforechangepage	`onbeforechangepage(worksheet: Object, pageNumber: Number, oldPage: Number, quantityPerPage: Number) => Boolean \| void` Before the page is changed. Can cancel the action by returning false.
onchangepage	`onchangepage(worksheet: Object, pageNumber: Number, oldPageNumber: Number, quantityPerPage: Number) => void` When pagination is enabled and the user changes the page.
onbeforecreateworksheet	`onbeforecreateworksheet(worksheetOptions: Object, position: Number) => boolean \| Any` Add or change the options for a new worksheet.
oncreateworksheet	`oncreateworksheet(worksheet: Object, worksheetOptions: Object, position: Number) => void` When the user creates a new worksheet.
onrenameworksheet	`onrenameworksheet(worksheet: Object, position: Number, newValue: String, oldValue: String) => void` When the user renames a worksheet.
ondeleteworksheet	`ondeleteworksheet(worksheet: Object, position: Number) => void` When the user deletes a worksheet.
onmoveworksheet	`onmoveworksheet(worksheet: Object, from: Number, to: Number) => void` When the user updates the worksheet tab position.
onopenworksheet	`onopenworksheet(worksheet: Object, worksheet: Number) => void` When the user opens a worksheet.
onchangerowid	`onchangerowid(worksheet: Object, rows: Array) => void` When there is a row id update.
onbeforefilter	`onbeforefilter(worksheet: Object, filters: Array, data: Array) => void` Action to be executed before filtering rows. It can cancel the action by returning false.
onfilter	`onfilter(worksheet: Object, filters: Array, data: Array) => void` After the filter has been applied to the rows.
oncreatecell	`oncreatecell(worksheet: Object, element: HTMLElement, x: Number, y: Number, value: String) => void` When a new cell is created.
oncreaterow	`oncreaterow(worksheet: Object, rowNumber: Number, tr: HTMLElement) => void` After a new row is inserted.
onbeforeformula	`onbeforeformula(worksheet: Object, expression: String, x: Number, y: Number) => void \| string \| boolean` Before executing a formula.
onformulachain	`onformulachain(worksheet: Object, expressions: Array) => void` Get the information about the expressions executed from the formula chain.
onopenfilter	`onopenfilter(worksheet: Object, column: number, options: Array) => options \| void` Customize the items available when the filter editor is open.
onresize	`onresize(worksheet: Object, w: number, h: number) => void` Viewport dimensions has changed.
onbeforechangereferences	`onbeforechangereferences(worksheet: Object, affectedTokens: [], deletedTokens: []) => void` Before references are updated.
onchangereferences	`onupdatereferences(worksheet: Object, affectedTokens: [], deletedTokens: []) => void` When references are updated.
onchangedefinednames	`onchangedefinednames(worksheet: Object, data: []) => void` When definedNames is updated.
oninput	`oninput(worksheet: Object, event: Object) => void` New char is entered on editor.
onchangerowvisibility	`onchangerowvisibility(worksheet: Object, state: boolean: affected: []]) => void` When the visibility of rows changes.
onchangecolumnvisibility	`onchangecolumnvisibility(worksheet: Object, state: boolean: affected: []]) => void` When the visibility of cols changes.
onsearchstart	`onsearchstart(worksheet: Object, terms: String)` It happens before all the search events.
onsearchrow	`onsearchrow(worksheet: Object, rowNumber: number, terms: String)` It helps to customize the searching process.
onbeforesearch	`onbeforesearch(worksheet: Object, terms: String, results: Array, search: Object)` Action to be executed before the search. It can be used to cancel or to intercept and customize the search process.
onsearch	`onbeforesearch(worksheet: Object, terms: String, rowNumber: Array, search: Object)` After the search process is completed.
oncreatecolumn	`oncreatecolumn(worksheet: Object, columnNumber: number, td: HTMLElement, options: Object)` When a new column is created.
oncreaterow	`oncreaterow(worksheet: Object, rowNumber: Number, tr: HTMLElement)` After a new row is inserted.
oncreaterowgroup	`oncreaterowgroup?: (worksheet: object, row: number, elements: number\|number[]) => void` When the user creates a group of rows
ondestroyrowgroup	`ondestroyrowgroup?: (worksheet: object, row: number) => void` When the user destroys a group of rows
onopenrowgroup	`onopenrowgroup?: (worksheet: object, row: number) => void` When the user opens a group of rows
oncloserowgroup	`oncloserowgroup?: (worksheet: object, row: number) => void` When the user closes a group of rows

Special events

Event	description
onevent	`onevent(worksheet: Object, method: Object, a?: any, b?: any, c?: any, d?: any, e?: any, f?: any) => any` General method to handler any event. The number of arguments depends on each event.
contextmenu	`contextmenu(worksheet: Object, x: Number, y: Number, e: Event, section: String, section_argument1?: Any, section_argument2?: Any) => Array \| boolean` Return false to cancel the contextMenu event, or return custom elements for the contextmenu.
updateTable	`updateTable(worksheet: Object, cell: Object, x: Number, y: Number, value: String) => void` Run every single table update action. Can bring performance issues if performing too much changes. It is recommended to use other events, such as oncreatecell.

The Event Dispatcher

The event dispatcher is a global-centralized method to handle all events. The number of arguments depends on the event triggered. NOTE: You can see events in the browser console.

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id='spreadsheet'></div>

<script>
let data = [
    ['Mazda', 2001, 2000],
    ['Peugeot', 2010, 5000],
    ['Honda Fit', 2009, 3000],
    ['Honda CRV', 2010, 6000],
];

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MjQ3YzA0MmMzNGQxNTBjNWJiMDI4MjIxNzU2MmY2YmIzY2VhNWY3Y2Y0ZTRkYWE2N2FhZDE0OTFkMjU0ZmZjZDdlODgwOTVhYzU1NzljMTZmYzU4ZDg4YTRlMTI0MTIxNjZlYzYwZjFiNWFlZGY4MTZhZmFlNWRiZmVjNWM0NGQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1UTXhNVE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ['Mazda', 2001, 2000],
            ['Peugeot', 2010, 5000],
            ['Honda Fit', 2009, 3000],
            ['Honda CRV', 2010, 6000],
        ],
        columns:[
            { title:'Model', width:'300px' },
            { title:'Year', width:'80px' },
            { title:'Price', width:'100px' },
        ],
    }],
    onevent: function(event,a,b,c,d,e,f) {
        console.log(event,a,b,c,d,e,f);
    }
});
</script>
</html>

Edit this page on GitHub