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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

(() => {
  'use strict';

  const getCommentsData = async (opt_offset, opt_comments) => {
    const offset = opt_offset || 0;
    // Max number of comments to export
    const COMMENTS_LIMIT = 10;
    const body = {
      app: kintone.app.getId(),
      record: kintone.app.record.getId(),
      offset: offset
    };
    let comments = opt_comments || [];

    try {
      const resp = await kintone.api(kintone.api.url('/k/v1/record/comments.json', true), 'GET', body);

      if (!resp || !resp.comments) {
        throw new Error('Failed to retrieve comment data.');
      }

      comments = comments.concat(resp.comments);

      if (resp.older === true) {
        return await getCommentsData(offset + COMMENTS_LIMIT, comments);
      }

      return comments;
    } catch (error) {
      console.error('Error retrieving comments:', error);
      throw error;
    }
  };

  // Escape
  const escapeStr = (value) => {
    return '"' + (value ? value.replace(/"/g, '""') : '') + '"';
  };

  const createCSVData = (comments) => {
    const commentsCSV = [];
    // Name of the column in the CSV
    let column_row = ['Comment ID', 'Text', 'Created date and Time',
      'User code of commenter',
      'Display name of commenter',
      'Mentioned user', 'Mention type'];
    commentsCSV.push(column_row);

    for (let i = 0; i < comments.length; i++) {
      const comment = comments[i];
      const row = [];
      const mentionsCodes = [];
      const mentionsTypes = [];

      // Only process when mentions exists and is an array
      if (Array.isArray(comment.mentions) && comment.mentions.length > 0) {
        for (let k = 0; k < comment.mentions.length; k++) {
          if (comment.mentions[k]) {
            mentionsCodes.push(comment.mentions[k].code || '');
            mentionsTypes.push(comment.mentions[k].type || '');
          }
        }
      }

      row.push(escapeStr(comment.id)); // Comment ID
      row.push(escapeStr(comment.text)); // Text
      row.push(escapeStr(comment.createdAt)); // Created date and time
      row.push(escapeStr(comment.creator.code)); // User code of commenter
      row.push(escapeStr(comment.creator.name)); // Display name of commenter
      row.push(escapeStr(mentionsCodes.join(','))); // Mentioned user
      row.push(escapeStr(mentionsTypes.join(','))); // Mention type
      commentsCSV.push(row);
    }
    return commentsCSV;
  };

  // Record detail show
  kintone.events.on(['app.record.detail.show'], (event) => {
    // Create button in header
    const headerElement = kintone.app.record.getHeaderMenuSpaceElement();
    const csvButton = document.createElement('button');
    csvButton.id = 'export-comment-csv';
    csvButton.innerText = 'Export comments as CSV';
    csvButton.onclick = async function() {
      // Disable the button to prevent duplication.
      csvButton.disabled = true;
      csvButton.innerText = 'downloading...';

      try {
        const comments = await getCommentsData();
        // Create CSV data
        const csv = createCSVData(comments);

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

        // File name：[APPID]_[RecordNumber].csv
        const appId = kintone.app.getId();
        const recordId = kintone.app.record.getId();
        const fileName = `${appId}_${recordId}_comments.csv`;

        const link = document.createElement('a');
        const e = new MouseEvent('click', {
          view: window,
          bubbles: true,
          cancelable: true
        });
        link.download = fileName;
        link.href = url;
        link.dispatchEvent(e);

        // Revoke the object URL to prevent memory leaks.
        (window.URL || window.webkitURL).revokeObjectURL(url);
      } catch (error) {
        console.error('CSV download error:', error);
        alert('Failed to download comments.');
      } finally {
        // Enable the button.
        csvButton.disabled = false;
        csvButton.innerText = 'Export comments as CSV';
      }
    };
    headerElement.appendChild(csvButton);
    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
17
18
19
20
21
22
23
24
25
26
27
28
29
30

const getCommentsData = async (opt_offset, opt_comments) => {
  const offset = opt_offset || 0;
  // Max number of comments to export
  const COMMENTS_LIMIT = 10;
  const body = {
    app: kintone.app.getId(),
    record: kintone.app.record.getId(),
    offset: offset
  };
  let comments = opt_comments || [];

  try {
    const resp = await kintone.api(kintone.api.url('/k/v1/record/comments.json', true), 'GET', body);

    if (!resp || !resp.comments) {
      throw new Error('Failed to retrieve comment data.');
    }

    comments = comments.concat(resp.comments);

    if (resp.older === true) {
      return await getCommentsData(offset + COMMENTS_LIMIT, comments);
    }

    return comments;
  } catch (error) {
    console.error('Error retrieving comments:', error);
    throw error;
  }
};

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
38
39
40
41

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

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

  for (let i = 0; i < comments.length; i++) {
    const comment = comments[i];
    const row = [];
    const mentionsCodes = [];
    const mentionsTypes = [];

    // Only process when mentions exists and is an array
    if (Array.isArray(comment.mentions) && comment.mentions.length > 0) {
      for (let k = 0; k < comment.mentions.length; k++) {
        if (comment.mentions[k]) {
          mentionsCodes.push(comment.mentions[k].code || '');
          mentionsTypes.push(comment.mentions[k].type || '');
        }
      }
    }

    row.push(escapeStr(comment.id)); // Comment ID
    row.push(escapeStr(comment.text)); // Text
    row.push(escapeStr(comment.createdAt)); // Created date and time
    row.push(escapeStr(comment.creator.code)); // User code of commenter
    row.push(escapeStr(comment.creator.name)); // Display name of commenter
    row.push(escapeStr(mentionsCodes.join(','))); // Mentioned user
    row.push(escapeStr(mentionsTypes.join(','))); // Mention type
    commentsCSV.push(row);
  }
  return commentsCSV;
};

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56

// Record detail show
kintone.events.on(['app.record.detail.show'], (event) => {
  // Create button in header
  const headerElement = kintone.app.record.getHeaderMenuSpaceElement();
  const csvButton = document.createElement('button');
  csvButton.id = 'export-comment-csv';
  csvButton.innerText = 'Export comments as CSV';
  csvButton.onclick = async function() {
    // Disable the button to prevent duplication.
    csvButton.disabled = true;
    csvButton.innerText = 'downloading...';

    try {
      const comments = await getCommentsData();
      // Create CSV data
      const csv = createCSVData(comments);

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

      // File name：[APPID]_[RecordNumber].csv
      const appId = kintone.app.getId();
      const recordId = kintone.app.record.getId();
      const fileName = `${appId}_${recordId}_comments.csv`;

      const link = document.createElement('a');
      const e = new MouseEvent('click', {
        view: window,
        bubbles: true,
        cancelable: true
      });
      link.download = fileName;
      link.href = url;
      link.dispatchEvent(e);

      // Revoke the object URL to prevent memory leaks.
      (window.URL || window.webkitURL).revokeObjectURL(url);
    } catch (error) {
      console.error('CSV download error:', error);
      alert('Failed to download comments.');
    } finally {
      // Enable the button.
      csvButton.disabled = false;
      csvButton.innerText = 'Export comments as CSV';
    }
  };
  headerElement.appendChild(csvButton);
  return event;
});