Export Record Comments as CSV

Contents

Overview

This article introduces how to retrieve comment information from a record and output the information as a CSV file.
The sample in this article uses the Get Comments API and exports record comment data as CSV format.

Sample Image

Button placement for CSV output and a list of comments to be outputted:

Example of a CSV file that has been outputted (viewed through Excel):

Prepare the App

Create an App (External link) and place any number of fields inside. Add a record, and also add a few comments inside.

Sample Code

Prepare the following JavaScript code in a text editor and navigate to the Kintone App's settings. Upload the file into the Upload JavaScript for PC option of the JavaScript and CSS Customization settings (External link) .

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103

(function() {
  'use strict';

  function getCommentsData(opt_offset, opt_comments) {
    var offset = opt_offset || 0;
    var body = {
      'app': kintone.app.getId(),
      'record': kintone.app.record.getId(),
      'offset': offset
    };
    var comments = opt_comments || [];
    return kintone.api(kintone.api.url('/k/v1/record/comments.json', true), 'GET', body).then(function(resp) {
      comments = comments.concat(resp.comments);
      if (resp.older === true) {
        return getCommentsData(offset + 10, comments);
      }
      return comments;
    });
  }

  // Escape
  function escapeStr(value) {
    return '"' + (value ? value.replace(/"/g, '""') : '') + '"';
  }
  function createCSVData(comments) {
    var comments_csv = [];

    // Name of the column in the CSV
    var column_row = ['Comment ID', 'Text', 'Created date and Time',
      'User code of commenter',
      'Display name of commenter',
      'Mentioned user', 'Mention type'];
    comments_csv.push(column_row);

    for (var i = 0; i < comments.length; i++) {
      var row = [];
      var mentions_code = [];
      var mentions_type = [];

      if (comments[i].mentions[0] === undefined) {
        comments[i].mentions.code = null;
      }
      for (var k = 0; k < comments[i].mentions.length; k++) {
        mentions_code.push(comments[i].mentions[k].code);
        mentions_type.push(comments[i].mentions[k].type);
      }
      row.push(escapeStr(comments[i].id)); // Comment ID
      row.push(escapeStr(comments[i].text)); // Text
      row.push(escapeStr(comments[i].createdAt)); // Created date and time
      row.push(escapeStr(comments[i].creator.code)); // User code of commenter
      row.push(escapeStr(comments[i].creator.name)); // Display name of commenter
      row.push(escapeStr(mentions_code.join(','))); // Mentioned user
      row.push(escapeStr(mentions_type.join(','))); // Mention type
      comments_csv.push(row);
    }
    return comments_csv;
  }

  // Record detail show
  kintone.events.on(['app.record.detail.show'], function(event) {
    // Create button in header
    var header_element = kintone.app.record.getHeaderMenuSpaceElement();
    var csv_button = document.createElement('button');
    csv_button.id = 'export-comment-csv';
    csv_button.innerText = 'Export comments as CSV';
    csv_button.onclick = function() {
      getCommentsData().then(function(comments) {
        // Create CSV data
        var csv = createCSVData(comments);

        // Export CSV
        var csvbuf = csv.map(function(e) {
          return e.join(',');
        }).join('\r\n');
        var bom = new Uint8Array([0xEF, 0xBB, 0xBF]);
        var blob = new Blob([bom, csvbuf], {type: 'text/csv'});
        var url = (window.URL || window.webkitURL).createObjectURL(blob);

        // File name:[APPID]_[RecordNumber].csv
        var appId = kintone.app.getId();
        var recordId = kintone.app.record.getId();
        var fileName = appId + '_' + recordId + '_comments.csv';

        if (window.navigator.msSaveOrOpenBlob) {
          // Use IE
          window.navigator.msSaveOrOpenBlob(blob, fileName);
        } else {
          // Except IE
          var link = document.createElement('a');
          var e = document.createEvent('MouseEvents');
          e.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
          link.download = fileName;
          link.href = url;
          link.dispatchEvent(e);
        }
      });
    };
    header_element.appendChild(csv_button);
    return event;
  });

})();

After saving the settings and clicking on Update App, navigate to a record that includes comments inside. A button should appear at the top of the record. Clicking on the button should export the record comments as a CSV file.

Code Explanation

Retrieve Comment Data

The maximum number of comments that can be obtained with the Get Comments API is 10.
Therefore, recursion is used to obtain all of the comments in the record.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
function getCommentsData(opt_offset, opt_comments) {
  var offset = opt_offset || 0;
  var body = {
    'app': kintone.app.getId(),
    'record': kintone.app.record.getId(),
    'offset': offset
  };
  var comments = opt_comments || [];
  return kintone.api(kintone.api.url('/k/v1/record/comments.json', true), 'GET', body).then(function(resp) {
    comments = comments.concat(resp.comments);
    if (resp.older === true) {
      return getCommentsData(offset + 10, comments);
    }
    return comments;
  });
}

Convert the data format into CSV

Convert the comment data retrieved into CSV format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
// Escape
function escapeStr(value) {
  return '"' + (value ? value.replace(/"/g, '""') : '') + '"';
}
function createCSVData(comments) {
  var comments_csv = [];

  // Name of the column in the CSV
  var column_row = ['Comment ID', 'Text', 'Created date and Time',
    'User code of commenter',
    'Display name of commenter',
    'Mentioned user', 'Mention type'];
  comments_csv.push(column_row);

  for (var i = 0; i < comments.length; i++) {
    var row = [];
    var mentions_code = [];
    var mentions_type = [];

    if (comments[i].mentions[0] === undefined) {
      comments[i].mentions.code = null;
    }
    for (var k = 0; k < comments[i].mentions.length; k++) {
      mentions_code.push(comments[i].mentions[k].code);
      mentions_type.push(comments[i].mentions[k].type);
    }
    row.push(escapeStr(comments[i].id)); // Comment ID
    row.push(escapeStr(comments[i].text)); // Text
    row.push(escapeStr(comments[i].createdAt)); // Created date and time
    row.push(escapeStr(comments[i].creator.code)); // User code of commenter
    row.push(escapeStr(comments[i].creator.name)); // Display name of commenter
    row.push(escapeStr(mentions_code.join(','))); // Mentioned user
    row.push(escapeStr(mentions_type.join(','))); // Mention type
    comments_csv.push(row);
  }
  return comments_csv;
}

Add a button for exporting the CSV file

Add a button to export the CSV file.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
// Record detail show
kintone.events.on(['app.record.detail.show'], function(event) {
  // Create button in header
  var header_element = kintone.app.record.getHeaderMenuSpaceElement();
  var csv_button = document.createElement('button');
  csv_button.id = 'e-comment-csv';
  csv_button.innerText = 'Export comments as CSV';
  csv_button.onclick = function() {
    getCommentsData().then(function(comments) {
      // Create CSV data
      var csv = createCSVData(comments);

      // Export CSV
      var csvbuf = csv.map(function(e) {
        return e.join(',');
      }).join('\r\n');
      var bom = new Uint8Array([0xEF, 0xBB, 0xBF]);
      var blob = new Blob([bom, csvbuf], {type: 'text/csv'});
      var url = (window.URL || window.webkitURL).createObjectURL(blob);

      // File name:[APPID]_[RecordNumber].csv
      var appId = kintone.app.getId();
      var recordId = kintone.app.record.getId();
      var fileName = appId + '_' + recordId + '_comments.csv';

      if (window.navigator.msSaveOrOpenBlob) {
        // Use IE
        window.navigator.msSaveOrOpenBlob(blob, fileName);
      } else {
        // Except IE
        var link = document.createElement('a');
        var e = document.createEvent('MouseEvents');
        e.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
        link.download = fileName;
        link.href = url;
        link.dispatchEvent(e);
      }
    });
  };
  header_element.appendChild(csv_button);
  return event;
});