Export comments from Google Spreadsheet

Question

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

Answer 1

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)