Office Script-How to retrieve the “Author Name”, “Comment Content” from the “Comment” based relative column position

Question

Need some help. Office Script - How to retrieve the “Author Name” , “Comment Content” from the “Comment” entered onto the cell of a particular column if I only know the position of the column number from the first column? I have a Excel Table with many columns (1 day 1 column). I knew the relative position of the current day from the first column (ie 85). (Since it is 1 day 1 column, I will know the relative column no by subtracting the first date in the Table).

Thank you in advance.

I found example script to retrieve the details from “Comment” as follows. But it needs a exact cell address like B2. Anyway to retrieve the details by using the column no & row number by iterate through the Table?

function main(workbook: ExcelScript.Workbook) {

    let selectedSheet = workbook.getActiveWorksheet();
  
    let range = selectedSheet.getUsedRange()

    let comment_by=workbook.getCommentByCell(selectedSheet.getRange("B2")).getAuthorName();
    let comment_date=workbook.getCommentByCell(selectedSheet.getRange("B2")).getCreationDate();
    let comment_content = workbook.getCommentByCell(selectedSheet.getRange("B2")).getContent();
    console.log("Name: " + comment_by  + " , Date: " + comment_date + " , Content: "+ comment_content);

Answer 1

• getTables()[0] gets the first table (ListObject) on the active sheet
• getCell(rowIndex, colIndex) get the cell in the table. Note: rowIndex and colIndex are zero-base index. eg. the first cell (top-left) is getCell(0,0).

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let tabObj = selectedSheet.getTables()[0]; 
    const rowIndex = 1;  ' modify as needed, zero-base index
    const colIndex = 3;
    let cellRng = tabObj.getRange().getCell(rowIndex, colIndex);
    let cellCmt = workbook.getCommentByCell(cellRng)
    let comment_by = cellCmt.getAuthorName();
    let comment_date = cellCmt.getCreationDate();
    let comment_content = cellCmt.getContent();
    console.log("Name: " + comment_by + " , Date: " + comment_date + " , Content: " + comment_content);
}

---

• Get the comments of each cell in the table

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let tabObj = selectedSheet.getTables()[0];
  const rowCnt = tabObj.getRowCount();
  const colCnt = tabObj.getHeaderRowRange().getCellCount();
  for (let rowIndex = 0; rowIndex < rowCnt+1; rowIndex++) {
    for (let colIndex = 0; colIndex < colCnt; colIndex++) {
      let cellRng = tabObj.getRange().getCell(rowIndex, colIndex);
      try {
        let cellCmt = workbook.getCommentByCell(cellRng);
        let comment_by = cellCmt.getAuthorName();
        let comment_date = cellCmt.getCreationDate();
        let comment_content = cellCmt.getContent();
        console.log("Name: " + comment_by + " , Date: " + comment_date + " , Content: " + comment_content);
      } catch (error) {
        // no comments
      }
    }
  }
}