Back to Documentation

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('YjUxMDRkM2I5MDNmMzNlMzRmM2QzMGU0MTBjNTE0MmZmNTY5NDI3OGIzOWE2YzQ3NmNhOTcxN2I1ZmIyYmE1OWI5NmViNjQ1OTY5Y2E1ZGE3N2VjNjg0YmQzMGZhMTY5NjhlNDgzY2UyZTVlNmU2NzY5M2VlMjY2MWFkMmFlZmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpJNE16QXdNamM1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// 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>