0

I'm develping an Excel addin with office.js and I want to track row change events.

I'm fetching data from a DBMS with technical Ids but I don't want to print ids into the sheet.

My main issue is to identify univocally the edited row: when user perform row sorting I'm loosing any reference.

I have tried several approaches:

  • Range excel.binding row by row (a binding for each row)
  • Table excel.binding on entire dataset
  • A combination of handlers such as:
    • BindingDataChanged
    • TableDataChanged
  • Named items

Below a gist that can be directly imported into ScriptLab with the first two options looking the most promising https://gist.github.com/webartoli/86a19ffa0e683aa1d204eabaaeb09dd1

Can anyone help me in being able to uniquely identify a row in events ?

Repro steps:

  • Click a button to create dataset and set handlers
  • Sort the second column ascending: the first rows becomes id=5
  • Edit the first row

How can i get from events that row with id=5 is edited without printing id column on sheet ? I except this from "binding" feature but I cannot find a way.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
Claudio
  • 3,060
  • 10
  • 17
  • I'm not clear on why you don't want to expose the DBMS IDs on the sheet. Also, I think https://learn.microsoft.com/en-us/javascript/api/office/officeextension.clientrequestcontext?view=common-js-preview#office-officeextension-clientrequestcontext-trackedobjects-member is intended for this kind of scenario. – Rick Kirkham Oct 19 '22 at 17:40
  • IDs are GUID and the user can edit existing rows or adds records. The common interaction that we have observed: is "copy paste" existing row, then change some specific cell. In this scenario the ID of the new row is "duplicate" becouse of copy paste, and a guid requires a non-small portion of the viewport. Removing the IDs from the sheet would be optimal for us. – Claudio Oct 20 '22 at 08:02
  • I think you should explore using tracked objects. – Rick Kirkham Oct 20 '22 at 18:47
  • Thanks for the hint. I'll explore tracked object! – Claudio Oct 20 '22 at 19:43
  • I have been exploring TrackedObject. As I understand it, the intent of TrackedObject is to keep instances of the object alive between different `context.sync()` and I cannot find a way to use it to implement this scenario. How were you thinking of applying it ? – Claudio Nov 08 '22 at 16:05
  • I haven't tried it. I was just thinking that if you had a tracked object reference to each row, that might enable you to keep rows identified without an ID column. – Rick Kirkham Nov 10 '22 at 04:53

0 Answers0