Insert and Delete Rows from an Excel-like Custom View

Overview

This article introduces how to insert and delete rows from an Excel-like custom view using Handsontable.

The code used in this article is based on the Create an Excel-like Custom View article. It is recommended to read that article before this current article.

Initial Setup

Refer to the Initial Setup section of the Create an Excel-like Custom View article and follow the steps to build the Kintone App.

information

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 (External link) , and use it under their license terms.

Turn off the pagination

Since this sample does not support Enable pagination (External link) , turn the Enable pagination option off.

  1. Navigate to the App Settings and select the Views tab.
  2. Select Edit from the custom view made above.
  3. Uncheck Enable pagination and click Save.

Implement the Handsontable Customization

This section explains how to use JavaScript to insert and delete rows in the excel-like interface.

Sample code

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 (External link) Kintone Help 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.

  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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208

(() => {

  'use strict';

  let hot; // Handsontable variable

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

  // Methods to get records
  const getRecords = (callback, errorCallback) => {
    kintone.api('/k/v1/records', 'GET', {app: kintone.app.getId(), query: 'order by record_number asc limit 500'},
      (resp) => {
        callback(resp);
      },
      (resp) => {
        errorCallback(resp);
      }
    );
  };

  // Methods to insert rows
  const saveRecords = (records, changedDatas, callback, errorCallback) =>{
    const requests = [];
    const updateRecords = [];
    const insertRecords = [];
    let changedRows = [];

    // Get only the rows that have changed from the array of the changed datas
    for (let i = 0; i < changedDatas.length; i++) {
      changedRows.push(changedDatas[i][0]);
    }
    // Eliminate duplicate row numbers
    changedRows = changedRows.filter((x, i, self) => {
      return self.indexOf(x) === i;
    });

    // Determine the changed row whether it is a change or a new record, and then create a query
    for (let i = 0; i < changedRows.length; i++) {
      if (records[changedRows[i]].record_number.value === null) {
        insertRecords.push(
          setParams(records[changedRows[i]])
        );
      } else {
        updateRecords.push({
          id: records[changedRows[i]].record_number.value,
          record: setParams(records[changedRows[i]])
        });
      }
    }

    // An API request for updating records
    requests.push({
      method: 'PUT',
      api: '/k/v1/records.json',
      payload: {
        app: kintone.app.getId(),
        records: updateRecords
      }
    });

    // An API request for inserting records
    requests.push({
      method: 'POST',
      api: '/k/v1/records.json',
      payload: {
        app: kintone.app.getId(),
        records: insertRecords
      }
    });

    // Bulk insert and bulk update with bulkrequest
    // If it fails, it will be rolled back
    kintone.api('/k/v1/bulkRequest', 'POST', {requests: requests},
      (resp) => {
        console.dir(requests);
        console.dir(resp);
        callback(resp);
      },
      (resp) => {
        errorCallback(resp);
      }
    );
  };

  // Method to delete records
  const deleteRecords = (records, index, amount, callback, errorCallback) => {
    let i;
    const ids = [];
    for (i = index; i < index + amount; i++) {
      ids.push(records[i].record_number.value);
    }
    kintone.api('/k/v1/records', 'DELETE', {app: kintone.app.getId(), ids: ids},
      (resp) => {
        callback(resp);
      },
      (resp) => {
        errorCallback(resp);
      }
    );
  };

  // Auto sync data at regular intervals
  const autoload = () =>{
    getRecords((resp)=> {
      hot.loadData(resp.records);
    });
    setTimeout(()=> {
      autoload();
    }, 10000); // 10 seconds. Since there is the maximum number of API calls, change the number if needed
  };

  // Event handler
  kintone.events.on(['app.record.index.show'], (event) => {
    // Specify the View ID
    if (event.viewId !== {INSERT_VIEWID_HERE}) return;

    const container = document.getElementById('sheet');

    // Initialize handsontable
    hot = new Handsontable(container, {
      // At this point, do not input data so that it can be added later (to reload data every time the  data is updated)
      data: [],

      // Spare rows
      minSpareRows: 10,

      // Specify column headers
      colHeaders: ['Record Number', 'Company Name', 'Person in Charge', 'Date', 'Success Rate', 'Product Name', 'Price', 'Units', 'Subtotal'],

      // Specify the context menu (right-click menu)

      contextMenu: ['remove_row'],

      // 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 row is deleted, the following event is fired
      // index: row index to be deleted
      // amount: number of rows to be deleted
      beforeRemoveRow: (index, amount) => {
        // Delete Kintone records
        deleteRecords(hot.getSourceData(), index, amount,
          (deleteRecordsResp)=> {
            console.dir(deleteRecordsResp);
            getRecords((getRecordsResp)=> {
              // After deleting records, reload data
              hot.loadData(getRecordsResp.records);
            });
          },
          (resp)=> {
            console.dir(resp);
          }
        );
      },

      // When a cell in the spreadsheet is updated, the following event is fired
      afterChange: (change, source) => {
        console.log(source);

        if (source === 'loadData') {
          return;
        }

        // Insert and update Kintone records
        saveRecords(hot.getSourceData(), change,
          (resp)=> {
            console.dir(resp);
            getRecords((saveRecordsResp)=> {
              // After updating, reload the data
              hot.loadData(saveRecordsResp.records);
            },
            (getRecordResp)=> {
              console.dir(getRecordResp);
            });
          },
          (resp)=> {
            console.dir(resp);
          }
        );
      }
    });

    // Get records and reflect it to the Handsontable spreadsheet
    getRecords((resp)=> {
      hot.loadData(resp.records);
      autoload();
    });
  });
})();

The JavaScript and CSS Customization settings should look like the following:

Code Explanation

This section will explain the sample code above.

Insert rows

The saveRecords method is extended to support both inserting and updating data using the Bulk Request API.

The Bulk Request API can run multiple API requests sequentially to multiple Apps. Any error will result in a rollback.

The arguments are Kintone's record data (records) and an array retrieved when a cell is edited (changedDatas).

 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

// Methods to insert rows
const saveRecords = (records, changedDatas, callback, errorCallback) =>{
  const requests = [];
  const updateRecords = [];
  const insertRecords = [];
  let changedRows = [];

  // Get only the rows that have changed from the array of the changed datas
  for (let i = 0; i < changedDatas.length; i++) {
    changedRows.push(changedDatas[i][0]);
  }

  // Eliminate duplicate row numbers
  changedRows = changedRows.filter((x, i, self) => {
    return self.indexOf(x) === i;
  });

  // Determine the changed row whether it is a change or a new record, and then create a query
  for (let i = 0; i < changedRows.length; i++) {
    if (records[changedRows[i]].record_number.value === null) {
      insertRecords.push(
        setParams(records[changedRows[i]])
      );
    } else {
      updateRecords.push({
        id: records[changedRows[i]].record_number.value,
        record: setParams(records[changedRows[i]])
      });
    }
  }

  // An API request for updating records
  requests.push({
    method: 'PUT',
    api: '/k/v1/records.json',
    payload: {
      app: kintone.app.getId(),
      records: updateRecords
    }
  });

  // An API request for inserting records
  requests.push({
    method: 'POST',
    api: '/k/v1/records.json',
    payload: {
      app: kintone.app.getId(),
      records: insertRecords
    }
  });

  // Bulk insert and bulk update with bulkrequest
  // If it fails, it will be rolled back
  kintone.api('/k/v1/bulkRequest', 'POST', {requests: requests},
    (resp) => {
      console.dir(requests);
      console.dir(resp);
      callback(resp);
    },
    (resp) => {
      errorCallback(resp);
    }
  );
};
Delete rows

To delete records, users need to use the context menu (right-click menu) on the Excel-like interface. The context menu needs to be set up in the code. The context menu option is set to remove_row.

 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

// Initialize handsontable
hot = new Handsontable(container, {
  // At this point, do not input data so that it can be added later (to reload data every time the  data is updated)
  data: [],

  // Spare rows
  minSpareRows: 10,

  // Specify column headers
  colHeaders: ['Record Number', 'Company Name', 'Person in Charge', 'Date', 'Success Rate', 'Product Name', 'Price', 'Units', 'Subtotal'],

  // Specify the context menu (right-click menu)
  contextMenu: ['remove_row'],

  // 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}
  ],

Next, a delete method (deleteRecords) is created. The arguments are Kintone's record data (records), the row index (index) and the number of rows (amount) that are to be deleted. The record numbers that are to be deleted are retrieved, and the Delete Records API is called.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

// Method to delete records
const deleteRecords = (records, index, amount, callback, errorCallback) => {
  let i;
  const ids = [];
  for (i = index; i < index + amount; i++) {
    ids.push(records[i].record_number.value);
  }
  kintone.api('/k/v1/records', 'DELETE', {app: kintone.app.getId(), ids: ids},
    (resp) => {
      callback(resp);
    },
    (resp) => {
      errorCallback(resp);
    }
  );
};

When deleting a row, the method specified in the beforeRemoveRow (External link) event is called and is used to update the data in Kintone. Inside the event, the deleteRecords method is called, and the data is deleted.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

beforeRemoveRow: (index, amount) => {
  // Delete Kintone records
  deleteRecords(hot.getSourceData(), index, amount,
    (deleteRecordsResp)=> {
      console.dir(deleteRecordsResp);
      getRecords((getRecordsResp)=> {
        // After deleting records, reload data
        hot.loadData(getRecordsResp.records);
      });
    },
    (resp)=> {
      console.dir(resp);
    }
  );
},

Synchronize data

Data needs to be synchronized to prevent data discrepancy when inserting, updating, or deleting data.

First, the records are retrieved and sorted. As shown in the example below, the records are sorted in ascending order of record numbers, and the limit is specified to 500 to display up to 500 records. Descending order will cause the new row to be displayed at the top.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Methods to get records
const getRecords = (callback, errorCallback) => {
  kintone.api('/k/v1/records', 'GET', {app: kintone.app.getId(), query: 'order by record_number asc limit 500'},
    (resp) => {
      callback(resp);
    },
    (resp) => {
      errorCallback(resp);
    }
  );
};

Next, the data is synchronized using the callback method and Handsontable's loadData method (External link) . The retrieved Kintone record is specified as an argument to synchronize. By synchronizing, the default value specified in the form setting screen is displayed, and records in the Calculated field are calculated and displayed.

1
2
3
4
5
6
7
8

// Save and add records
saveRecords(hot.getSourceData(), change,
  (resp)=> {
    console.dir(resp);
    getRecords((saveRecordsResp)=> {
      // After updating, reload the data
      hot.loadData(saveRecordsResp.records);
    },

Synchronize data at regular intervals

Data discrepancy can occur if the screen is open for a long time. The example below shows how to auto-sync data at regular intervals.

1
2
3
4
5
6
7
8
9

// Auto sync data at regular intervals
const autoload = () =>{
  getRecords((resp)=> {
    hot.loadData(resp.records);
  });
  setTimeout(()=> {
    autoload();
  }, 10000); // 10 seconds. Since there is the maximum number of API calls, change the number if needed
};

Limitations

  • The maximum number of records that can be updated at once is 100.
  • This sample can display up to 500 records of data. The code can be further customized to display more data. Refer to the Code Examples For Bulk Record Retrieval article for more details.
    • This sample does not support enable pagination (External link) because the number of data that can be displayed is limited.
  • Note that Kintone has a maximum number of REST API calls it can call in a day. Since the sample code in this article uses a synchronization process, slow down the synchronization interval if necessary. Leaving the browser open with this page can result in unnecessary API calls.

References

information

The contents of this article were checked with the September 2023 version of Kintone.