Can the Excel context be passed safely to a class?

Question

I'm working on an Excel AddIn using Javascript. I'm getting some very strange behavior, so I'm mainly concerned if I have to use context.trackedObjects.add() or not. The general flow of my code is ...

Get a range of data from the user. Uses Excel.run #1.
Call an Azure function using a simple AJAX call to calculate some statistics. Uses Excel.run #2
Write the results using code in a TypeScript class. Recieves context from step 2

The code is way to long to put here, this is a summarized version. Note that none of the objects the reference the context in the first Excel.run are used in the second Excel.run. The only variables being passed to the class are the Excel context and the JSON string.

function UserClicksButton() {
   await Excel.run(async (ctx) => {  **//Excel.run #1**
       let MyData:string = await GetDataFromSheet(ctx); //step 1, get stringified JSON object

        GetStatsFromServer(MyData,ReturnObject); //step 2

   }
}

async function GetStatsFromServer(In_Data,Return_Object) {
   var settings = {
                "async": true,
                "crossDomain": true,
                //others removved
         },
    "data": In_Data
    }
    $.ajax(settings).done(function(response) {
         Excel.run(async (ctx) => {             **//Excel.run #2**
                    let myChart= new MyChart(); 

                    myChart.CreateChart(ctx, response);
                });
    }).
    .fail {//deal with fail}
}

class MyChart {
  m_ctx:Excel.RequestContext;
  m_AnswerString: String;

  public async CreateChart(ctx: Excel.RequestContext, AnswerString:string) {
     this.m_ctx=ctx;
     this.m_AnswerString = AnswerString;

     //create chart using this.m_ctx
     //Excel.run is not called in class at all
  }
}

The reason I'm asking is that I'm getting some very strange errors. At one point I got "The object path Chart.add isn't working for what you're trying to do. If you're using the object across multiple \"context.sync\" calls and outside the sequential execution of a \".run\" batch, please use the \"context.trackedObjects.add()\" and \"context.trackedObjects.remove()\" methods to manage the object's lifetime."

However, depending on where I put in "await this.m_ctx.sync()" I get different errors or no errors at all. For example, the CreateChart funciton in the MyChart class is quite long as it does a ton of formatting of the chart. It could be abbreviated as (variable declarations omitted for brevity) ...

class MyChart {
  m_ctx:Excel.RequestContext;
  m_AnswerString: String;

  public async CreateChart(ctx: Excel.RequestContext, AnswerString:string) {
     this.m_ctx=ctx;
     this.m_AnswerString = AnswerString;

     //create chart using this.m_ctx
     newChart= sheet.charts.add("XYScatter", dataRange, "Auto");
     formatTitle(newChart);

     await this.formatAxis(newChart);
     //await this.m_ctx.sync(); //TODO: remove when done debugging
     await this.formatSeries0(newChart);
     //await this.m_ctx.sync(); //TODO: remove when done debugging
     await this.formatSeries1(newChart);
     //await this.m_ctx.sync(); //TODO: remove when done debugging
     await this.formatSeries2(newChart);
     //await this.m_ctx.sync(); //TODO: remove when done debugging
     await this.formatSeries3(newChart);
     //await this.m_ctx.sync(); //TODO: remove when done debugging
     await this.formatSeries4(newChart);

  }
}

The commented out line "await this.m_ctx.sync();" I added to find the offending line of code. However, it changes the behavior of the code. With the await() functions, I don't get an error. With the awaits, I sometimes get an error.

A couple of notes. The creation of the chart is all "output". I'm not loading anything from the Excel context. It is 100% writing to the worksheet. I don't really need the functions to be asynchronous, I only made them aysnc to be able to call await ctx.sync().

In searching for the problem, I found this article which doesn't seem to apply since I'm not sharing variables between multiple contexts.

Word Online Add-In: Using objects across multiple contexts

I know that I could make the function GetStatsFromServer return a promise, but per Zlatkovsky's book, maintaining compatibility with IE limits this to the Excel 1.2 API, using the Q library (which is failing to load on my PC), or get into a promise polyfill which is over my head right now. While making this function return a promise would be cleaner, right now I'm focused on this problem as my understanding is that it should work this way.

re. "maintaining compatibility with IE limits this to the Excel 1.2 API": this is not the case. If there's somewhere in my book where you got that impression from, let me know, so that I can correct it! For the Promise polyfill, the simplest polyfill is just to include a line like `window.Promise = Office.Promise`, which will work for what you need. — Michael Zlatkovsky - Microsoft, Jul 25 '19 at 22:53
Page 73, section 4.3.2 list three methods to implement a promise. I'm pretty sure my post list them exactly in order as your book, that is OfficeExtension.Promise which requires ExcelAPI 1.2, Q libarary, and promise polyfil. However, this aside, an answer to my question would be more helpful. The Office-js section of stackoverflow is a ghost town. If Microsoft people don't answer the questions, then they won't get answered. The system of validating manifest seems to be totally broken. I've posted multiple potential bugs with no reply. — Philip, Jul 26 '19 at 16:31
I see. OK, in terms of `OfficeExtension.Promise` that makes sense, I will update that section. It's changed and simplified, so now if you use `window.Promise = Office.Promise` (note: `Office` rather than `OfficeExtension`), you can actually use it even with older versions. — Michael Zlatkovsky - Microsoft, Jul 26 '19 at 20:09

score 4 · Answer 1 · answered Jul 26 '19 at 20:21

Let me answer the question in slightly general terms, which I think you can then adapt to your scenario.

First off: you should pretty much always (nay, just always!) use Excel.run. It will do a lot of the right things for you. I describe what exactly it does in chapter 5.2: Excel.run (Word.run, etc.) in my book Building Office Add-ins using Office.js. But suffice it to say, you are definitely best off doing all of your calls to the Excel OM within the Excel.run block.

Now, for your scenario: If your data-fetch step (step 1) is independent from the next time you are writing to the workbook (e.g., you're going to put the new chart on a brand new worksheet, so you don't need a reference to the original Range object corresponding to the selection), I wouldn't even worry about keeping the context around! Instead, just do your first Excel.run to suck up all the data, then do your AJAX call, and then do a new Excel.run to process the result. Should be pretty simple, and has the added advantage of keeping your middle step completely isolated (and thus independently-testable).

Conversely, if you do need to interact between the steps, you have two choices:

Do one large overarching Excel.run that fetches the data, then does your AJAX call, and then writes back to the workbook -- all in one Excel.run. Obviously, you can break it into helper functions, but it's still just one run. If we're talking about seconds rather than minutes, this is a totally valid option.
Do separate Excel.run's for each of step #1 and step #3, BUT store one of the objects (e.g., the Range) for later use in step #3. This is described in great detail in my book on chapter 10.2: Using objects outside the "linear" Excel.run or Word.run flow (e.g., in a button-click callback, in a setInterval, etc.). This is more difficult and requires more book-keeping, so I would only do this option if the user needs to keep returning to the original object, and/or if it's a user-initiated action (e.g., the elapsed time might be minutes or hours).

Hope this helps!

~ Michael

Can the Excel context be passed safely to a class?

1 Answers1