Create an Excel-like Custom View

Handsontable

Overview

This article introduces Kintone's customization with Handsontable .

This guide explains how to implement Handsontable in the Custom View of a Kintone App and how to edit records using it.

What is Handsontable?

Handsontable is a JavaScript library that enables spreadsheet-like input, just like Excel.

Other features involve cell formatting, creating charts, autofill, and many other functions. Some of these features can be experienced on the Handsontable demo page .

Initial Setup

This section goes through the steps of building a Kintone App and the required settings before implementing the Handsontable customization.

Set up the Field Settings

First, create a new Kintone App.

Set the fields in the sample App as follows.

Field Type	Field Name	Field Code	Note
Record number	Record Number	record_number
Text	Company Name	company_name
Text	Person in Charge	person_in_charge
Date	Date	date
Radio button	Success Rate	success_rate	Specify any options, for example "A", "B", and "C"
Drop-down	Product Name	product_name	Specify any product name, for example "Kintone"
Number	Price	price
Number	Units	units
Calculated	Subtotal	subtotal	In the formula box enter "price*units"

Set up a Custom View

Navigate to the App settings, and click on the View tab.
Click the [+] button to create a new view, and choose the Custom View option. Take note of the View ID, as it is needed for the JavaScript customization in the later steps.
On the HTML Code option, enter the following HTML. Leave the other options as they are and click Save.
1

<div id="sheet"></div>
Click on Update App to apply the changes.

Refer to the following article for more details on custom views:

Create a Custom View

Set up the JavaScript/CSS Settings

The Kintone CDN provides JavaScript and CSS files for using Handsontable. This guide uses Handsontable version 6.2.2.

Navigate to the App settings, and click JavaScript and CSS Customization.
Click the Add Link button to add the following URL and click Save:
- JavaScript URL https://js.kintone.com/handsontable/6.2.2/handsontable.full.min.js
- CSS URL https://js.kintone.com/handsontable/6.2.2/handsontable.full.min.css
Click Save on the bottom right side of the screen

As of v7.0.0, Handsontable is no longer released under the MIT license. The latest version will not be hosted on the Kintone CDN. If you wish to use versions beyond v7.0.0, please purchase the necessary license from the Handsontable website and use it under their license terms.

Input Data

Enter a few records of sample data. This guide will convert this data into a spreadsheet-like interface.

Implement the Handsontable Customization

This section explains how to use JavaScript to implement the spreadsheet-like interface.
For more details on Handsontable, refer to the official document: Handsontable documentation (v6.2.2) .

Prepare the following JavaScript code in a text editor and upload the file into the JavaScript settings of the App with reference to the Uploading JavaScript and CSS files article. Note that the new JavaScript file needs to be located under the JavaScript library because the files are read sequentially, starting from the top file.

Display Data as a Spreadsheet

Data can be displayed as a spreadsheet by using the following code.

1
2
3
4
5
6
7
8


const container = document.getElementById('sheet');
new Handsontable(container, {
  data: data,         // specify data or object
  minSpareRows: 1,    // specify bottom margin
  rowHeaders: true,   // display row headers
  colHeaders: true,   // display column headers
  contextMenu: true   // display right-click context menu
});

Display Kintone App Records in the Spreadsheet

To display the records of the Kintone App in the spreadsheet, specify the Kintone record data in Handsontable's data options. Use [field name].value to specify columns and an array to specify column headers. For View ID, enter the View ID noted from the Setting a custom view section.

 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


(() => {
  'use strict';
  kintone.events.on(['app.record.index.show'], (event) => {
    // Specify the View ID
    if (event.viewId !== {INSERT_VIEWID_HERE}) return;
    const records = event.records;
    // Specify the HTML element created when setting the custom view.
    const container = document.getElementById('sheet');
    container.innerHTML = ''; // Initialize the page before displaying Handsontable
    // Handsontable instance
    new Handsontable(container, {
      // Specify Kintone record data
      data: records,
      minSpareRows: 0,
      // Specify column headers
      colHeaders: ['Record Number', 'Company Name', 'Person in Charge', 'Date', 'Success Rate', 'Product Name', 'Price', 'Units', 'Subtotal'],
      contextMenu: false,
      // Specify data options
      columns: [
        {data: 'record_number.value'},
        {data: 'company_name.value'},
        {data: 'person_in_charge.value'},
        {data: 'date.value'},
        {data: 'success_rate.value'},
        {data: 'product_name.value'},
        {data: 'price.value'},
        {data: 'units.value'},
        {data: 'subtotal.value'}
      ]
    });
  });
})();

Save this in a JavaScript file and upload it. Navigate to the App and select the Custom View from the dropdown on the top left. Now, the data should be displayed in Kintone like a spreadsheet.

Update Kintone App Records in the Spreadsheet

When updating data, the method specified in the afterChange option is called and is used to update Kintone. There is also a readOnly option to prevent columns from being edited.

 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


(() => {
  // Methods to save records
  'use strict';
  const saveRecords = (records, callback, errorCallback) => {
    kintone.api(kintone.api.url('/k/v1/records.json', true), 'PUT', {app: kintone.app.getId(), records: records},
      (resp) => {
        callback(resp);
      },
      (resp) => {
        errorCallback(resp);
      });
  };
  // When updating a record, there are some fields that cannot be updated, such as record number, so do the following method to exclude those fields
  const setParams = (record) => {
    const result = {};
    for (const prop in record) {
      if (['record_number', 'Created_datetime', 'Updated_datetime', 'Created_by', 'Updated_by'].indexOf(prop) === -1) {
        result[prop] = record[prop];
      }
    }
    return result;
  };
    // Event handler
  kintone.events.on(['app.record.index.show'], (event) => {
    // Specify the View ID
    if (event.viewId !== {INSERT_VIEWID_HERE}) return;
    const records = event.records;
    const container = document.getElementById('sheet');
    container.innerHTML = ''; // Specify the HTML element created when setting the custom view

    new Handsontable(container, {
      data: records,
      minSpareRows: 0,
      colHeaders: ['Record Number', 'Company Name', 'Person in Charge', 'Date', 'Success Rate', 'Product Name', 'Price', 'Units', 'Subtotal'],
      contextMenu: false,
      // Specify readOnly if necessary
      columns: [
        {data: 'record_number.value', readOnly: true},
        {data: 'company_name.value'},
        {data: 'person_in_charge.value'},
        {data: 'date.value'},
        {data: 'success_rate.value'},
        {data: 'product_name.value'},
        {data: 'price.value'},
        {data: 'units.value'},
        {data: 'subtotal.value', readOnly: true}
      ],
      // When a cell in the spreadsheet is updated, the following methods are called.
      // The afterChange method knows the details of the cell that has been changed from the "change" argument.
      // While the "source" argument shows what changed it.
      afterChange: (change, source) => {
        if (source === 'loadData') {
          return;
        }
        let i;
        const targets = [];
        // Get the data from the change argument, and send an update API to Kintone
        for (i = 0; i < change.length; i++) {
          targets.push({
            id: records[change[i][0]].record_number.value,
            record: setParams(records[change[i][0]])
          });
        }
        saveRecords(targets, (resp) => {
          console.dir(resp);
        }, (resp) => {
          console.dir(resp);
        });
      }
    });
  });
})();

The change argument in the afterChange method contains data of the updated cell. It contains the row number and column name of the cell, as well as the data before and after the change. Here is an example dataset:

1
2
3
4
5
6
7
8


[
  [
    9,                          // row number of the updated cell
    'person_in_charge.value',   // column name of the updated cell
    'Calvin',                   // before
    'Jessica'                   // after
  ]
];

In the sample code, the data from the afterChange method is used to perform Kintone's Update Records API, so that the related record is updated.

Limitations

This sample does not include any error handling. Entering the wrong data type will result in records to not be updated.
- Ex: placing a String type in the Date field.
The maximum number of records that can be displayed in one page is 100. This is due to the maximum number of records the event object can hold. If there are more than 100 records, the next set of records may be accessed by clicking on the pagination arrow.
To display more than 100 records, turn the Pagination option of the Custom View off. Then, update the code so that the records are retrieved using Kintone's REST APIs, rather than using the event object. Methods for retrieving records in bulk via REST API can be read in The Three Methods for Bulk Record Retrieval article.
The maximum number of records that can be updated at once is 100.