How to list comments in a worksheet - Excel JS

Question

I am trying to list all comments and their positions of a excel workbook or sheet. I have found this resource but it does not explain how to get all comments of the workbook/worksheet.

Could you get me a hint on how to do this?

Clarification: I am trying to list all comments with their corresponding address. So I can add additional replies in context to their content.

That article seems to imply that `context.workbook.comments;` returns all comments in the workbook. — Rick Kirkham, Oct 17 '22 at 18:16
Hi Rick thank you for your answer, I have tried to play with this, but I tried to find a way to list all comments and the corresponding address. — Anselm Scholz, Oct 18 '22 at 10:15
After checking context.workbook.comments; once more I can access the collection of comments :-) — Anselm Scholz, Oct 18 '22 at 12:05

score 2 · Answer 1 · answered Oct 17 '22 at 21:49

Comments within a workbook are tracked by the Workbook.comments property. This includes comments created by users and also comments created by your add-in. The Workbook.comments property is a CommentCollection object that contains a collection of Comment objects. Comments are also accessible at the Worksheet level.

To edit the comment you can use the following code:

await Excel.run(async (context) => {
    // Edit the first comment in the workbook.
    let comment = context.workbook.comments.getItemAt(0);
    comment.content = "PLEASE add headers here.";
    await context.sync();
});

To edit a comment reply, set its CommentReply.content property:

await Excel.run(async (context) => {
    // Edit the first comment reply on the first comment in the workbook.
    let comment = context.workbook.comments.getItemAt(0);
    let reply = comment.replies.getItemAt(0);
    reply.content = "Never mind";
    await context.sync();
});

See Work with comments using the Excel JavaScript API for more information.

score 0 · Answer 2 · answered Oct 19 '22 at 10:00

just for reference. Thanks for the ideas. I solved it with the following function. I rely on two globales variables 'cms' and 'sheet_comments'. I know there is room for improvement ;-) ...

function getComments(){

  return new Promise((resolve) => {
    Excel.run(function (ctx) {
      
      cms = [];
      sheet_comments = [];
      let ct = ctx.workbook.comments.getCount();

      return ctx.sync()
          .then(function () {
            let ct_value = ct.value;
            
            for (let i = 0; i < ct_value; i++) {
              let cm = ctx.workbook.comments.getItemAt(i);
              cm.load("content");
              let loc = cm.getLocation();
              loc.load('address');
              cms.push({'content':cm,'address':loc});
            };
          }).then(ctx.sync).then(function () {
            for (let i = 0; i < cms.length; i++) {
              let c = cms[i];
              let c_content = c['content'].content;
              let l_address = c['address'].address;
              sheet_comments.push({'content':c_content,'address':l_address});
            };
            resolve();
          });
    }).catch(errorHandler);

  })
};

How to list comments in a worksheet - Excel JS

2 Answers2