OfficeJs - Getting custom function's address property when using Streaming Invocation

Question

Currently, I'm working on changing from CustomFunction.Invocation to StreamingInvocation in a shared runtime add-in project because I will have to use Web socket. I have a use case that need to use custom function's address property as a key in a data object for further uses, and I found that it can't be used with StreamingInvocation. I've tried to implement a workaround with onChanged and onCalculated event, but it was not working very well. So, I'm not sure if there are some workarounds that I can use to get custom function's address property within itself.

Edit: Add code and more detail

export class TestExcelService {
  private static instance: TestExcelService;
  private dataEventEmtter?: EventEmitter;

  // prevent direct construction call with new keyword.
  // eslint-disable-next-line @typescript-eslint/no-empty-function
  private constructor() {
    this.registerOnCalculated();
  }

  public static getInstance(): TestExcelService {
    if (!TestExcelService.instance) {
        TestExcelService.instance = new TestExcelService();
    }
    return TestExcelService.instance;
  }

  public setDataEventEmtter(eventEmitter: EventEmitter): void {
    this.dataEventEmtter = eventEmitter;
  }

  public getDataEventEmtter(): EventEmitter | undefined {
    return this.dataEventEmtter;
  }

  private async registerOnCalculated(): Promise<void> {
    await Excel.run(async (context: Excel.RequestContext) => {
      context.workbook.worksheets.getActiveWorksheet().onCalculated.add(
        async (arg: Excel.WorksheetCalculatedEventArgs): Promise<void> => {
          const formulaAddresses: ICellAddress[] = await this.filterMyFormulaAddress(arg);
          if (this.dataEventEmtter && formulaAddresses.length > 0) {
            this.dataEventEmtter.emit('onExcelCalculated', formulaAddresses);
          }
        },
      );
    });
  }

  private async filterMyFormulaAddress(arg: Excel.WorksheetCalculatedEventArgs): Promise<ICellAddress[]> {
    // filter only some formulas
    return [];
  }
}


export class TestDataService {
  private eventEmitter: EventEmitter = new EventEmitter();
  private static instance: TestDataService;
  private dataQueue = [];

  // prevent direct construction call with new keyword.
  // eslint-disable-next-line @typescript-eslint/no-empty-function
  private constructor() {
    this.eventEmitter.addListener('onExcelCalculated', this.excelCalculatedHandler.bind(this));
  }

  public static getInstance(): TestDataService {
    if (!TestDataService.instance) {
        TestDataService.instance = new TestDataService();
    }
    return TestDataService.instance;
  }

  public addDataToQueue(data): void {
    const duplicatedIndex: number = this.dataQueue.findIndex(
      (queueData) => data.formulaId === queueData.formulaId,
    );
    if (duplicatedIndex < 0) {
      this.dataQueue.push(data);
    }
  }

  public getEventEmitter(): EventEmitter {
    return this.eventEmitter;
  }

  private async excelCalculatedHandler(formulaAddresses: ICellAddress[]): Promise<void> {
    if (Array.isArray(this.dataQueue) && this.dataQueue.length > 0) {
    // check that data queue is not empty and map formula addresses from onCalculated event to the data queue
    }
  }
}

I can't post the full code, so I will have to remove and modify some parts the code.

The general idea is I will add data objects that have to be mapped with their respective formula address to the data queue in DataService, and after onCalculated event is fired I should be able to get a list of calculated and filtered addresses.

From what I've tested, The order of formula addresses should match with the data queue's order. At first, this workaround seems to be working fine when I execute one formula at a time or recalculate a small number of formulas at the same time, but when numbers of formulas start to grow and I try to recalculate all formulas, the order of formula addresses and the data queue will not be matched as I expected.

Answer 1

The streaming function is special. If any set of streaming functions are using the same expression, those are sharing the same root (i.e. context). The scope will be good within a workbook. In another workbook, the context will be different. So there is no address information provided for streaming function. As you noticed, the onCalculated event will log all calculated addresses. However it needs extra logic for special requests on addresses, such as keeping the order of formula addresses to match the data queue in your case.