How to Extract Comment Text from Selected cells in office js?

Question

I am working on an office js Add-in I am unable to get comments from multiple cells which already commented

screenshot

also, I have read office js document. there is only a single comment example available. My code only returns the first cells comment. I don't know what I am doing wrong.

 try {
      await Excel.run(async (context) => {
        let wb = context.workbook;
        let rang = wb.getSelectedRange();
        rang.load("rowCount, columnCount");
        await context.sync();
        let rangRowCount = rang.rowCount;
        let rangColCount = rang.columnCount;

        let rangeAddresses = [];
        for (let i = 0; i < rangRowCount; i++) {
          {
            for (let j = 0; j < rangColCount; j++) {
              let newRang = rang.getCell(i, j);
              rangeAddresses.push(newRang.load("address"));
            }
          }
        }
        await context.sync();
        rangeAddresses.forEach(async (e) => {
          console.log("address ", e.address);
          var comment = wb.comments.getItemByCell(e.address);
          comment.load("content");
          await context.sync();
          console.log("selected cell comment", comment.content);
        });
      });
    } catch (error) {
      ErrorProvider.getInstance().notify(JSON.stringify(error));
    }

Answer 1

Since you're iterating through the rangeAddresses array, you can create a new array (e.g. cellComments) using the map method and return the comments with the content property to be loaded. Once you've done that, execute another context.sync() statement to load the property and then iterate through the new array using foreach. You can do that like so:

async function run() {
  await Excel.run(async (context) => {
    let wb = context.workbook;
    let rang = wb.getSelectedRange();
    rang.load("rowCount, columnCount");
    await context.sync();
    let rangRowCount = rang.rowCount;
    let rangColCount = rang.columnCount;

    let rangeAddresses = [];
    for (let i = 0; i < rangRowCount; i++) {
      {
        for (let j = 0; j < rangColCount; j++) {
          let newRang = rang.getCell(i, j);
          rangeAddresses.push(newRang.load("address"));
        }
      }
    }
    await context.sync();
    let cellComments : Excel.Comment[] = rangeAddresses.map((e)=>
    {
      console.log("address ", e.address);
      let comment = wb.comments.getItemByCell(e.address);
      return comment.load("content");
    })
    await context.sync()
    cellComments.forEach((comment)=>console.log(comment.content))
  })
};

Using this method will prevent you from needing to execute a bunch of context.sync() statements in the loop. That will be better for performance.

Answer 2

The problem is that forEach is not promise-aware. It cannot support async and await. Please try the following snippet:

async function run() {
  await Excel.run(async (context) => {
    let wb = context.workbook;
    let rang = wb.getSelectedRange();
    rang.load("rowCount, columnCount");
    await context.sync();
    let rangRowCount = rang.rowCount;
    let rangColCount = rang.columnCount;

    let rangeAddresses = [];
    for (let i = 0; i < rangRowCount; i++) {
      {
        for (let j = 0; j < rangColCount; j++) {
          let newRang = rang.getCell(i, j);
          rangeAddresses.push(newRang.load("address"));
        }
      }
    }
    await context.sync();
    for (let index = 0; index < rangeAddresses.length; index++){
      console.log("address ", rangeAddresses[index].address);
      var comment = wb.comments.getItemByCell(rangeAddresses[index].address);
      comment.load("content");
      await context.sync();
      console.log("selected cell comment", comment.content);
    }
  });
}