Capture cell value prior to delete

Posted by: mhill on 5 September 2023, 9:25 pm EST

    • Post Options:
    • Link

    Posted 5 September 2023, 9:25 pm EST - Updated 5 September 2023, 9:27 pm EST

    Hi. I am using SpreadJS 14. I am trying to write a utility that captures and audits user edits on a spreadsheet - by binding to various eventypes like this:

    
    spread.bind( GC.Spread.Sheets.Events.CellChanged, function ( e, args ) {
                if ( args.sheetArea === GC.Spread.Sheets.SheetArea.viewport ) {
                    if ( args.propertyName === 'value' || args.propertyName === 'formula' ) {
                        // Capture before and after values
                        let cellRange = getA1CoordsCell( args.sheet, args.row, args.col );
                        let operation = args.propertyName === 'value' ? 'Value changed' : 'Formula changed';
                        let oldValue = args.oldValue;
                        let newValue = args.newValue;
                        spreadsheeteditor.addDynamicAudit( operation, args.sheetName, cellRange, oldValue, newValue );
                    }
                }
            } );

    My challenge (and question) is: how might I do something similar to trap when a user clears a cell using the delete button on their keyboard (i.e. without activating the cell)? Through trial and error, I have found that a “RangeChanged” event is triggered (which is fine) BUT how can I find out the value of the cell prior to it getting cleared (so I can audit the “oldvalue” and “newvalue”? There doesn’t seem to be a “RangeChanging” event that might give me access to the value before it gets removed.

    Any ideas how I might achieve this?

    Thanks,

    Mark

  • Posted 6 September 2023, 3:00 pm EST

    Hi Mark,

    Yes, the RangeChanged Event is triggered when you delete a cell value by “delete” button from the keyboard.

    RangeChanged is not a specificity event, it is a kind of data event, and will trigger after other command events like dragFill, dragDrop, paste…The range change is not caused by the range itself, but changed by other behavior and therefore, we don’t have something similar to RangeChanging Event

    You could use the following code snippet to get the old value using the RangeChanged Event.

        spread.bind(GC.Spread.Sheets.Events.RangeChanged, function (evt, args) {
            var sheet = args.sheet, changedCells = args.changedCells;
            changedCells.forEach(cell => {
                console.log(sheet.getDirtyCells(cell.row, cell.col));
            });
        })

    You could also refer to the attached sample. Let us know if you face any issues.

    References:

    RangeChanged Event: https://www.grapecity.com/spreadjs/api/v15/classes/GC.Spread.Sheets.Events#rangechanged

    getDirtyCells method: https://www.grapecity.com/spreadjs/api/v15/classes/GC.Spread.Sheets.Worksheet#getdirtycells

    Regards,

    Ankit

    SJSV14.2.5.zip

  • Posted 7 September 2023, 1:41 am EST

    That worked just great. Thank you.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels