CODEWITHSUNDEEP
exceloffice-jsoffice-addinsexcel-addinsexcel-web-addins
Anselm Scholz
Anselm Scholz

Reputation: 478

How to list comments in a worksheet - Excel JS

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.

Upvotes: 0

Views: 689

Answers (2)

Anselm Scholz
Anselm Scholz

Reputation: 478

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);

  })
};

Upvotes: 0

Eugene Astafiev
Eugene Astafiev

Reputation: 49397

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.

Upvotes: 2

Related Questions