Stack Exchange
Export comments from Google Spreadsheet
I used the following formula to export the comments from Google Sheets:
function myFunction() {
const excludeSheetNames = ['START', 'MASTER', 'REPORT']; // Exclude sheet names.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets();
const sheett = sheets.find(s => !excludeSheetNames.includes(s.getSheetName()));
const [head, , , ...rows] = sheett.getRange("A1:A" + sheett.getLastRow()).getValues();
const res = DocsServiceApp.openBySpreadsheetId(ss.getId()).getComments();
res.forEach(({ sheetName, comments }, h) => {
sheetName = sheets[h].getSheetName();
if (!excludeSheetNames.includes(sheetName)) {
  head.push(sheetName);
  rows.forEach((e, i) => {
    const t = comments.find(f => i == f.range.row - 4);
    e.push(t ? t.comment[0].comment.trim() : null);
  });
}
});
const values = [head, ...rows];
const dstSheet = ss.getSheetByName("REPORT") || ss.insertSheet("REPORT");
dstSheet.clearContents().getRange(1, 1, values.length, values[0].length).setValues(values);
}

to create inside the same Google spreadsheet a REPORT out of them.
When I run the script I get the following errors:
9:50:13 AM  Error   
TypeError: Cannot read properties of undefined (reading 'setName')
(anonymous)         @ ExcelApp.gs:408
getImagesAsObject   @ ExcelApp.gs:379
(anonymous)         @ ExcelApp.gs:39
getAll              @ ExcelApp.gs:34
getComments         @ SpreadsheetAppp.gs:49
myFunction          @ REPORT.gs:8


I am sharing the Google Sheet file with the script:
https://docs.google.com/spreadsheets/d/1GRNbQevnleNTvWDPm8Pg1veBFm4_an6g5qvqL-SeGK4/edit?usp=sharing

I have to apologize. When I tested your showing script using your provided Spreadsheet, I could notice that when the inserted image has no binary data, such an error occurs. From this situation, I updated the library and modified your showing script. Please confirm it. When I tested the modified script with the updated library, I confirmed that no error occurred and the comments were retrieved.

@Tanaike Everything is working. Thank you once more, Mr. Tanaike

When I tested the script using your provided Spreadsheet. I confirmed the following modification points.

When the inserted image in the Spreadsheet has no binary data, an error occurs. This is the reason for your showing an error message.

This issue was on the library side. I updated the library to remove this issue.


As the next issue, when a comment was removed, an error occurs at e.push(t ? t.comment[0].comment.trim() : null); in the script.

This issue was removed by the following modified script.



Modified script:
I updated the library. So, please reinstall the library and test it again.
function myFunction() {
  const excludeSheetNames = ['START', 'MASTER', 'REPORT']; // Exclude sheet names.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const sheett = sheets.find(s => !excludeSheetNames.includes(s.getSheetName()));
  const [head, , , ...rows] = sheett.getRange("A1:A" + sheett.getLastRow()).getValues();
  const res = DocsServiceApp.openBySpreadsheetId(ss.getId()).getComments();
  res.forEach(({ sheetName, comments }, h) => {
    sheetName = sheets[h].getSheetName();
    if (!excludeSheetNames.includes(sheetName)) {
      head.push(sheetName);
      rows.forEach((e, i) => {
        const t = comments.find(f => i == f.range.row - 4);
        ee.push((t && t.comment && t.comment.length > 0 && t.comment[0].comment) ? t.comment[0].comment.trim() : null); // Modified
      });
    }
  });
  const values = [head, ...rows.filter(([, ...r]) => r.some(c => c))]; // or const values = [head, ...rows]; // Modified
  const dstSheet = ss.getSheetByName("REPORT") || dstSS.insertSheet("REPORT");
  dstSheet.clearContents().getRange(1, 1, values.length, values[0].length).setValues(values);
}


When I tested this modified script using the updated library, the comments were retrieved without errors.

Although I'm not sure whether I could correctly understand is it possible to retrieve the rows that only contain comments instead of the full list?, I reflected it in the above script. When you want all rows, please use const values = [head, ...rows];.


Reference:

DocsServiceApp (Author: me)


It is working correctly but is it possible to retrieve the rows that only contain comments instead of the full list?
If not, I am OK with this solution.

I tested to another file and returned the following errorsQ
2:48:02 PM Notice Execution started
2:48:46 PM Error
TypeError: Cannot read properties of undefined (reading 'comment')
(anonymous) @ REPORT.gs:15
(anonymous) @ REPORT.gs:13
myFunction @ REPORT.gs:9

@dek Thank you for replying. From It is working correctly, I confirmed that the script worked. About s it possible to retrieve the rows that only contain comments instead of the full list, I cannot understand it. Can I ask you about the details of it? About your new issue, this is your provided Spreadsheet. But, how about modifying e.push(t ? t.comment[0]?.comment.trim() : null); to e.push((t && t.comment && t.comment.length > 0 && t.comment[0].comment) ? t.comment[0].comment.trim() : null);?

@dek Although I'm not sure whether I could correctly understand your reply, I updated my answer. Please confirm it. If that was not useful, I apologize.

Mr. Tanaike thank you for your reply.
There is an error in the formula line 15 "ee.push((t && t.comment && t.comment.length > 0 && t.comment[0].comment) ? t.comment[0].comment.trim() : null); // Modified" that has double 'e' .
Also, the script includes the sheet tabs with no comments inside like 21/12 on the sample file. Can we change that? If not no problem. Thank you again very much!

@dek Thank you for replying. About your new issue, unfortunately, when I tested my proposed script, no error occurred. I deeply apologize for this. But, I would like to support you. So, can you post it as a new question by including more information for correctly replicating your new issue? By this, it will help users including me think of a solution.

Mr Tanaike there is no error in the formula. The formula works just fine. A minor trimming needs only. I will post a new question. Thank you for your support!

@dek Thank you for replying. I understand that no error occurs. About A minor trimming needs only, when I saw your new question and could correctly understand it, I would like to think of a solution.

Mr. Tanaike hi,
Is there any issue with the library I am using from you? Today I am getting error 500 when I open Apps Script in Google sheet.

@dek I guessed that your situation might be related to https://stackoverflow.com/q/78060477 and https://issuetracker.google.com/issues/326802543

Mr. Tanaike, once again you were right and wrong. The links you gave me described the situation exactly. The problem was fixed after a few hours.
Who would have thought that this could happen from Google
Thank you for your support.
