Capture cell value prior to delete

Capture cell value prior to delete

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

• 

  mhill

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  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

• 

  mhill

  • Post Options:
  • Link
    Copy

  Posted 7 September 2023, 1:41 am EST

  That worked just great. Thank you.