How to create a report with all comments from google spreadsheet file

Question

I have a Google spreadsheet that I share with collaborators inside the company. The Google spreadsheet has inside 30-31 sheets (one for each day of the month).

Inside the sheet column "A" has the job description and column "D" has a drop-down list.

Column "D" has all the comments from the collaborators.

Is there a way I could automatically, upon request, with a script (assigned to a button) create a report on a new sheet inside the same spreadsheet file with all descriptions from column "A" and the corresponding comments from column "D" written from each sheet?

Answer 1

Issue and workaround:

In the current stage, there are no built-in methods for directly retrieving the comments from each cell on Google Spreadsheet. So, in order to achieve this, I created a Google Apps Script library DocsServiceApp (Author: me). In this answer, the library is used.

Usage:

1. Install library

You can see how to install this library DocsServiceApp at here.

2. Sample script

When your provided spreadsheet is used, the sample script is as follows. When I saw your Spreadsheet, I confirmed that the comments are put into column "D" of "29/01" and "28/01" sheets. This is also shown in your question.

Please copy and paste the following script to the script editor of Google Spreadsheet and save the script.

In this script, these sheets const excludeSheetNames = ['START', 'MASTER', 'REPORT'] are skipped. If you want to skip another sheet, please set the sheet name to this array.

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

Testing:

When this script is run, the result values are put into "REPORT" sheet as follows. It seems that this result is the same as your expected output.

Note:

• This sample script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this. So, please test this script on your provided Spreadsheet.

Reference:

• DocsServiceApp (Author: me)