none
Is there a working example of the Excel 2016 API for TrackedObjectsCollection? RRS feed

  • Question

  • I've made several attempts at trying to get this API to work with no luck and the doc page example doesn't work.  In addition, in the current version of the API it is "trackedObjects" and not TrackedObjectsCollection as the documentation page (below) indicates. I posted a comment on the GitHub doc page for this.

    I would like to see if this can be used to maintain the state of Excel.Range objects added to the collection as the user makes changes to the associiated range in the sheet and without having to call call Excel.Run(...) every time to get each of the users changes.

    To be more clear, after executing the follow code I don't see any changes in the trackedRange object.  It still has the old address A1:B2.

    var sheetName = "Sheet1";
    var rangeAddress = "A1:B2";
    

    var ctx = new Excel.RequestContext();

    trackedRange = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);

    ctx.trackedObjects.add(trackedRange);

    ctx.load(trackedRange);

    trackedRange.insert("Down");

    return ctx.sync().then(function ()

    {    

       console.log(trackedRange.address)// Address should be updated to A3:B4

    }

    ).catch(function(error)

    {    write(error);

    });

    https://msdn.microsoft.com/en-us/library/office/mt616482.aspx

    Thanks,

    Jim


    • Edited by Jim - Strive Monday, March 7, 2016 9:50 PM added example
    Monday, March 7, 2016 6:06 PM

All replies

  • Hi Jim,

    After I move the add tracked object to behind of insert and loading, it works well for me. Please let me know whether the code below works for you:

     var sheetName = "Sheet1";
            var rangeAddress = "A1:B2";
            var ctx = new Excel.RequestContext();
            var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
          
                   
            range.insert("Down");
            ctx.load(range);
            ctx.trackedObjects.add(range);
    
            ctx.sync().then(function () {
                console.log(range.address);
            }).catch(function (error) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
            });

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 8, 2016 7:57 AM
    Moderator
  • Hi Fei,

    Yes that does work as expected.  But I thought the point of the trackedObjects collection was that any objects in that collection would be automatically tracked "across" batch requests and the "state of the object would be updated".  I believe that is what the example was trying to do but the object wasn't getting updated after the batch request.  Notice that in my original example it does actually insert rows into the sheet but the object doesn't reflect the new address of the range.

    From the docs... "Any underlying changes across batch requests will be tracked and any follow-up updates will be applied to the current state of the range object."

    If I understand it correctly this would be a great feature if it worked as documented since you wouldn't have to create a binding for the range and handle binding change events to get updates for ranges that are in the trackedObjects collection as they would be updated automatically by the API.

    Is there a way to find out if this is indeed the intent of the API?  And if so, how to get it to work?

    Thanks a lot,

    Jim

    Tuesday, March 8, 2016 3:21 PM
  • Hi Jim,

    Thanks for the detail explanation and code sample. Since this issue is complex, 

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.
     
    Sorry for any inconvenience and have a nice day!
     
    Regards & Fei

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 10, 2016 7:29 AM
    Moderator
  • Hi Fei,

    No problem.  I appreciate you looking into this and look forward to your response.

    Thanks,

    Jim

    Thursday, March 10, 2016 2:37 PM
  • Hi Jim,

    I think you're right, TrackedObjectsCollection is not doing what it's supposed to.

    I add a Excel.Run(function (tcs)) around the batch code as the code sample suggests, still see the same issue.

    Excel.run(function (ctx) {
                var sheetName = "Sheet1";
                var rangeAddress = "A1:B2";
                var ctx = new Excel.RequestContext();
                var trackedRange = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
                ctx.trackedObjects.add(trackedRange);
                ctx.load(trackedRange);
                trackedRange.insert("Down");
                return ctx.sync().then(function () {
                    console.log(trackedRange.address); // Address should be updated to A3:B4
                }
                ).catch(function (error) {
                    write(error);
                });
            });


    • Edited by Jackie_ Wednesday, March 23, 2016 6:33 AM
    Wednesday, March 23, 2016 6:30 AM
  • Jackie,

    Thanks for the confirmation. It would be a great feature if implemented correctly.

    On another note, if you are using Excel.Run(...) in your Add-in at all please consider up-voting the following User Voice suggestion.  Basically any call to Excel.run(...) clears the Undo stack and basically causes Undo to stop working for the user in the workbook. This is a really bad bug that makes using the new API difficult to use and needs to be fixed. Also, please tell your colleagues.

    https://officespdev.uservoice.com/forums/224641-general/suggestions/10754784-fix-the-issue-with-new-javascript-api-for-excel-a

    Thanks,

    Jim

    Wednesday, March 23, 2016 5:19 PM
  • Hi, I would also need this functionality to work correctly, without it the actions are very limited. Also please consider correcting the Example and API documentation. It could save me hours.

    I expect you will let us know in this thread when this is fixed.

    Thank you very much.

    Tomas


    Tomas Paulas MCP,MCSA,MCSA-Messaging

    Tuesday, March 28, 2017 8:51 PM