Search code examples
google-apps-scriptcommentsgoogle-sheets-api

Extract Google Sheet Comments from cell to new Sheet

I have a inventory list with Google Sheet and used Comments as record for transaction. However, it's reaching the limit and i can't add more. Is that possible to make the script run automatically with all comments it has now and delete the comment once it's export? Then I could use the new sheet with v-lookup to generate the transaction record for each shipment

I did some research and saw below App Script could export the comments from the a Sheet to another one. I've tried but it only showing 20 comments and it's on the inventory list, not the sheet i want

Below is the link for the inventory file. https://docs.google.com/spreadsheets/d/1Ssb9zmlg4cixqe7fUcC7Uma1mizI7R3Y_ZETCcq4m-E/edit#gid=436319921

    function listComments() {
      // Change docId into your document's ID
      // See below on how to
      var docId = '1Ssb9zmlg4cixqe7fUcC7Uma1mizI7R3Y_ZETCcq4m-E'; 
      var comments = Drive.Comments.list(docId);
      var hList = [], cList = [];

      // Get list of comments
      if (comments.items && comments.items.length > 0) {
        for (var i = 0; i < comments.items.length; i++) {
          var comment = comments.items[i]; 
          // add comment and highlight to array's first element 
          hList.unshift([comment.context.value]);
          cList.unshift([comment.content]);
        }
        // Set values to A and B
        var sheet = SpreadsheetApp.getActiveSheet();
        sheet.getRange("A1:A" + hList.length).setValues(hList);
        sheet.getRange("B1:B" + cList.length).setValues(cList);
      }
    }
Solution

  • From your following reply,

    So can it be done with only comments retrieved and delete afterward? I've updated the output comment sheet for the result i want

    In this case, how about the following sample scripts?

    In these samples, Drive API is used. So, please enable Drive API at Advanced Google services.

    Sample script 1:

    In this sample, the comment is retrieved by "Method: comments.list" of Drive API. In the current stage, the cell coordinates of each comment cannot be identified. So, in this case, all comments are retrieved from all sheets in a Google Spreadsheet.

    function listComments1() {
  var docId = '1Ssb9zmlg4cixqe7fUcC7Uma1mizI7R3Y_ZETCcq4m-E';

  var res = [];
  var pageToken = "";
  do {
    var comments = Drive.Comments.list(docId, { includeDeleted: false, maxResults: 100, fields: "nextPageToken,items(content,context(value))", pageToken });
    res = [...res, ...comments.items.map(e => [e.content || null])];
    pageToken = comments.nextPageToken;
    console.log({ pageToken, len: comments.items.length, resLen: res.length });
  } while (pageToken);
  if (res.length == 0) return;
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, res.length).setValues(res);
}
    • When this script is run, the comments are retrieved from all sheets and the retrieved comments are put into column "A" of the active sheet.

    Sample script 2:

    In this sample, the comment is retrieved by "DocsServiceApp" of my Google Apps Script library. Ref When this library is used, the comments can be retrieved from the specific sheet.

    Before you use this script, please install this library "DocsServiceApp". You can see how to install it at here.

    function listComments2() {
  var docId = '1Ssb9zmlg4cixqe7fUcC7Uma1mizI7R3Y_ZETCcq4m-E';

  const obj = DocsServiceApp.openBySpreadsheetId(docId).getSheetByName("Current").getComments();
  const res = obj.reduce((ar, { comment }) => {
    if (comment.length > 0) {
      ar = [...ar, ...comment.map(c => [c.comment.trim().replace("----", "").trim()])];
    }
    return ar;
  }, []);
  if (res.length == 0) return;
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, res.length).setValues(res);
}
    • When this script is run, the comments are retrieved from the specific sheet and the retrieved comments are put into column "A" of the active sheet.

    References: