How to detect comments in a workbook sheet?

[Jeff-White-AZ]

Using wb$worksheets[[k]]$sheet_data$cc, I am able to retrieve the xml properties of the k-th sheet in a workbook, but for a sheet with several comments, the xml property c_cm for all cells is empty. ChatGPT tells me "c_cm: Represents the comment associated with the cell."
Am I looking in the wrong place or misunderstanding the xml properties?

[JanMarvin]

ChatGPT is doing what it usually does, inventing things out of thin air that sound nice. Have a look at wb$comments there might even be some function for this, I’m currently stuck in a train with bad internet access. But c_cm is for cell metadata. It has nothing to do with comments. Comments are text boxes embedded in vml code, but there might be other reasons for vml code in a workbook like images in page header or footer.

[Jeff-White-AZ]

Thanks for clarifying the issue with ChatGPT providing total fabrications. I've seen this with it recommending R functions that don't exist. A further drawback seems to be that ChatGPT 3.5 was trained on web pages from over a year ago, so it misses info on recent material such as for openxlsx2. Google's Gemini seems better in recognizing openxlsx2, but I am unsure about fabrications. The wb$comments statement retrieves the cells with comments within a list, which is what I mostly need, but I don't see the actual text of the comment. Also, the indexing of the list is a bit puzzling. The test comments appeared in sheet no. 2 and no. 5 of the workbook. Example entries with the correct commented cells from sheet 2 are: [[1]][[1]] [[1]][[1]]$ref [1] "B2" [[1]][[2]] [[1]][[2]]$ref [1] "B4" [[1]][[3]] [[1]][[3]]$ref [1] "B6" For sheet 5, they are: [[2]] [[2]][[1]] [[2]][[1]]$ref [1] "B2" [[2]][[2]] [[2]][[2]]$ref [1] "B4" Note that for clarity, I am excluding three other values provided for each level of the list with $ref (author, comment and style). I can select the desired cell reference with a statement like wb$comments[[2]][[7]]$ref, which returns the correct value "C27" (from sheet 5) but with no indication of the source sheet. I don't see anything that indexes the sheet numbers. I attach the workbook I am using for testing. The first sheet has no formulas, merged cells or comments. The subsequent sheets have comments [2], formulas [3], merged cells [4], and combinations of all three [5]. I've searched for alternate functions, but don't see anything within openxlsx2 and openxlsx but xlsx has a function getCellComment(cell). I'd prefer to work with as few packages as possible, but will try xlsx if a solution within openxlsx2 is not feasible. Thanks again for your assistance, Jeff White

…

On Sun, Feb 18, 2024 at 11:29 AM Jan Marvin Garbuszus < ***@***.***> wrote: ChatGPT is doing what it usually does, inventing things out of thin air that sound nice. Have a look at wb$comments there might even be some function for this, I’m currently stuck in a train with bad internet access. But c_cm is for cell metadata. It has nothing to do with comments. Comments are text boxes embedded in vml code, but there might be other reasons for vml code in a workbook like images in page header or footer. — Reply to this email directly, view it on GitHub <#949 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BGFFQ2A7JSEHRVM3XXBBMHDYUJCBRAVCNFSM6AAAAABDOKPZVCVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DKMBYHE4TI> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[JanMarvin]

@Jeff-White-AZ I thought there was a wb_get_comments() function, but it actually looks like there isn't. Take a look at wb$worksheets[[1]]$relships$comments, but a word of warning: you are inspecting the inside of a workbook. Our API stability promise does not extend to these internal objects. They can change whenever we deem it necessary. Not that I'm planning to at the moment. I just thought it would only be of interest to see if there are comments in the workbook. Then it would be enough to check wb$comments.

The macro xlsm files have something here wb$vbaProject. You should be able to utilize this for your macro check.

[JanMarvin]

The integer in wb$worksheets[[1]]$relships$comments will tell you which entry in wb$comments links to the sheet

[Jeff-White-AZ]

With the information from wb$worksheets[[1]]$relships$comments, I was able to create the full test for presence of cells with comments in each sheet of the test workbook. Thanks for your assistance in working with internal objects.
And I acknowledge the warning that the stability of the objects is not guaranteed.
Again, many thanks for your assistance!