上図のような編集をするには、まず一覧名が「Excel風編集」の一覧を作る。
PC用のJavaScript / CSSファイル を下図のようにする。
ライブラリは下記のページにある。
sample.js は以下のJavaScriptを使う。
sample.js
(() => {
'use strict';
// kintone REST API Clientのインスタンスを作成
const client = new KintoneRestAPIClient();
// 一覧画面を表示したときに処理開始
kintone.events.on('app.record.index.show', (event) => {
// 一覧名が「Excel風編集」の場合のみ実行
if (event.viewName !== 'Excel風編集') {
return event;
}
// Handsontableが読み込まれるまで待機
const checkHandsontable = setInterval(() => {
if (typeof Handsontable !== 'undefined') {
clearInterval(checkHandsontable);
initHandsontable(event);
}
}, 100);
return event;
});
const initHandsontable = (event) => {
// 標準機能の一覧を非表示にする
const listTable = document.querySelector('.recordlist-gaia');
if (listTable) {
listTable.style.display = 'none';
}
const listHeader = document.querySelector('.recordlist-header-gaia');
if (listHeader) {
listHeader.style.display = 'none';
}
const hideFixedHeaders = () => {
document.querySelectorAll('.gaia-app-recordlist-fixedheader').forEach((header) => {
header.style.display = 'none';
});
};
// 初回実行と定期的なチェックおよびMutationObserverで監視
hideFixedHeaders();
setInterval(hideFixedHeaders, 50);
const observer = new MutationObserver(hideFixedHeaders);
observer.observe(document.body, {
childList: true,
subtree: true,
attributes: true,
attributeFilter: ['style']
});
// Handsontable用のコンテナを作成してヘッダー下に配置
const container = document.createElement('div');
container.id = 'handsontable-container';
container.style.marginTop = '10px';
const targetSpace = kintone.app.getHeaderSpaceElement();
if (!targetSpace) return;
targetSpace.appendChild(container);
// アプリIDと現在のクエリを取得してlimitとoffsetを削除
const appId = kintone.app.getId();
let query = (kintone.app.getQuery() || '').replace(/limit\s+\d+/gi, '').replace(/offset\s+\d+/gi, '').trim();
// 全レコード取得
client.record.getAllRecordsWithCursor({ app: appId, query: query }).then((records) => {
// 一覧の設定からフィールドの並び順を取得
client.app.getViews({ app: appId }).then((viewResp) => {
const currentView = viewResp.views['Excel風編集'];
if (!currentView || !currentView.fields) {
alert('「Excel風編集」一覧の設定が見つかりません');
return;
}
const viewFieldCodes = currentView.fields;
// ユーザー情報を全件取得
const fetchAllUsers = async () => {
let allUsers = [];
let offset = 0;
const size = 100;
while (true) {
const usersResp = await kintone.api(kintone.api.url('/v1/users', true), 'GET', { size, offset });
allUsers = allUsers.concat(usersResp.users);
if (usersResp.users.length < size) break;
offset += size;
}
return allUsers;
};
// 組織情報を全件取得
const fetchAllOrganizations = async () => {
let allOrgs = [];
let offset = 0;
const size = 100;
while (true) {
const orgsResp = await kintone.api(kintone.api.url('/v1/organizations', true), 'GET', { size, offset });
allOrgs = allOrgs.concat(orgsResp.organizations);
if (orgsResp.organizations.length < size) break;
offset += size;
}
return allOrgs;
};
Promise.all([fetchAllUsers(), fetchAllOrganizations()]).then(([users, organizations]) => {
// ユーザーと組織の表示名↔コードのマッピング作成
const nameToCode = {};
const codeToName = {};
users.forEach(user => {
nameToCode[user.name] = user.code;
codeToName[user.code] = user.name;
});
const orgNameToCode = {};
const orgCodeToName = {};
organizations.forEach(org => {
orgNameToCode[org.name] = org.code;
orgCodeToName[org.code] = org.name;
});
// フィールド情報を取得
client.app.getFormFields({ app: appId }).then((fieldResp) => {
const fields = fieldResp.properties;
// 除外するレコード情報に関するフィールドのリスト
const excludeTypes = ['RECORD_NUMBER', 'CREATOR', 'CREATED_TIME', 'MODIFIER', 'UPDATED_TIME', 'CATEGORY', 'STATUS', 'STATUS_ASSIGNEE', 'SUBTABLE'];
// 編集可能なフィールドのコード、ヘッダー、列設定を抽出
const editableFieldCodes = [];
const headers = [];
const columns = [];
viewFieldCodes.forEach((fieldCode) => {
const field = fields[fieldCode];
if (field && excludeTypes.indexOf(field.type) === -1) {
editableFieldCodes.push(fieldCode);
headers.push(field.label);
// フィールドタイプに応じた列設定を作成
const colConfig = { data: fieldCode };
// 選択肢のあるフィールドの設定
if (field.type === 'CHECK_BOX' || field.type === 'MULTI_SELECT') {
const options = [];
for (const key in field.options) {
if (field.options.hasOwnProperty(key)) {
options.push({ label: key, value: key, index: field.options[key].index });
}
}
options.sort((a, b) => (typeof a.index === 'string' ? parseInt(a.index) : a.index) - (typeof b.index === 'string' ? parseInt(b.index) : b.index));
colConfig.renderer = (instance, td, row, col, prop, value) => {
td.textContent = (value && Array.isArray(value)) ? value.join(', ') : (value || '');
return td;
};
colConfig.editor = 'multiselectEditor';
colConfig.options = options;
} else if (field.type === 'RADIO_BUTTON' || field.type === 'DROP_DOWN') {
const options = [];
for (const key in field.options) {
if (field.options.hasOwnProperty(key)) {
options.push({ key, index: field.options[key].index });
}
}
options.sort((a, b) => (typeof a.index === 'string' ? parseInt(a.index) : a.index) - (typeof b.index === 'string' ? parseInt(b.index) : b.index));
colConfig.type = 'dropdown';
colConfig.source = options.map(opt => opt.key);
colConfig.allowInvalid = false;
} else if (field.type === 'DATE') {
colConfig.editor = 'datePickerEditor';
} else if (field.type === 'TIME') {
colConfig.editor = 'timePickerEditor';
} else if (field.type === 'DATETIME') {
colConfig.editor = 'dateTimePickerEditor';
}
// ユーザー選択、組織選択、グループ選択の表示
if (field.type === 'USER_SELECT' || field.type === 'ORGANIZATION_SELECT' || field.type === 'GROUP_SELECT') {
colConfig.renderer = (instance, td, row, col, prop, value) => {
if (value && Array.isArray(value)) {
td.textContent = value.map(v => v.name || v.code).join(', ');
} else {
td.textContent = value || '';
}
return td;
};
} else if (field.type === 'FILE') {
colConfig.readOnly = true;
colConfig.renderer = (instance, td, row, col, prop, value) => {
td.textContent = (value && Array.isArray(value)) ? (value.length + '個のファイル') : '';
return td;
};
} else if (field.type === 'DATETIME') {
// 日時の表示フォーマット
colConfig.renderer = (instance, td, row, col, prop, value) => {
if (value && typeof value === 'string') {
let match;
// UTC形式
match = value.match(/^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):\d{2}Z$/);
if (match) {
const utcDate = new Date(`${match[1]}-${match[2]}-${match[3]}T${match[4]}:${match[5]}:00Z`);
const jstYear = utcDate.getFullYear();
const jstMonth = String(utcDate.getMonth() + 1).padStart(2, '0');
const jstDay = String(utcDate.getDate()).padStart(2, '0');
const jstHour = utcDate.getHours();
const jstMinute = String(utcDate.getMinutes()).padStart(2, '0');
td.textContent = `${jstYear}-${jstMonth}-${jstDay} ${jstHour}:${jstMinute}`;
return td;
}
// JST形式
match = value.match(/^(\d{4})-(\d{2})-(\d{2})T(\d{1,2}):(\d{2})$/);
if (match) {
td.textContent = `${match[1]}-${match[2]}-${match[3]} ${parseInt(match[4])}:${match[5]}`;
return td;
}
td.textContent = value;
} else {
td.textContent = '';
}
return td;
};
}
columns.push(colConfig);
}
});
// フィールドの種類に応じて値を整形
const data = records.map((record) => {
const row = { $id: record.$id.value, $recordNumber: record.$id.value };
editableFieldCodes.forEach((fieldCode) => {
const value = record[fieldCode];
if (value) {
const field = fields[fieldCode];
if (field.type === 'CHECK_BOX' || field.type === 'MULTI_SELECT') {
row[fieldCode] = Array.isArray(value.value) ? value.value.join(', ') : '';
} else if (field.type === 'USER_SELECT' || field.type === 'ORGANIZATION_SELECT' || field.type === 'GROUP_SELECT') {
row[fieldCode] = Array.isArray(value.value) ? value.value.map(v => v.name || v.code).join(', ') : '';
} else {
row[fieldCode] = value.value || '';
}
} else {
row[fieldCode] = '';
}
});
return row;
});
// レコード番号を行ヘッダーに使用
const rowHeaders = data.map((row) => row.$recordNumber);
// 日付の値を整形
const normalizeDateFormat = (dateStr) => {
if (!dateStr || typeof dateStr !== 'string') return dateStr;
if (/^\d{4}-\d{2}-\d{2}$/.test(dateStr)) return dateStr;
const slashMatch = dateStr.match(/^(\d{4})\/(\d{1,2})\/(\d{1,2})$/);
if (slashMatch) {
return `${slashMatch[1]}-${slashMatch[2].padStart(2, '0')}-${slashMatch[3].padStart(2, '0')}`;
}
const hyphenMatch = dateStr.match(/^(\d{4})-(\d{1,2})-(\d{1,2})$/);
if (hyphenMatch) {
return `${hyphenMatch[1]}-${hyphenMatch[2].padStart(2, '0')}-${hyphenMatch[3].padStart(2, '0')}`;
}
return dateStr;
};
// JST形式をUTC形式に変換
const convertJSTToUTC = (jstDatetimeStr) => {
const jstDate = new Date(jstDatetimeStr);
return [
jstDate.getUTCFullYear(),
String(jstDate.getUTCMonth() + 1).padStart(2, '0'),
String(jstDate.getUTCDate()).padStart(2, '0')
].join('-') + 'T' + [
String(jstDate.getUTCHours()).padStart(2, '0'),
String(jstDate.getUTCMinutes()).padStart(2, '0'),
String(jstDate.getUTCSeconds()).padStart(2, '0')
].join(':') + 'Z';
};
// 日時の値を整形
const normalizeDatetimeFormat = (datetimeStr) => {
if (!datetimeStr || typeof datetimeStr !== 'string') return datetimeStr;
if (/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z$/.test(datetimeStr)) return datetimeStr;
let match = datetimeStr.match(/^(\d{4})\/(\d{1,2})\/(\d{1,2})\s+(\d{1,2}):(\d{1,2})(?::(\d{1,2}))?$/) || datetimeStr.match(/^(\d{4})-(\d{1,2})-(\d{1,2})\s+(\d{1,2}):(\d{1,2})(?::(\d{1,2}))?$/);
if (match) {
const jstDatetime = `${match[1]}-${match[2].padStart(2, '0')}-${match[3].padStart(2, '0')}T${match[4].padStart(2, '0')}:${match[5].padStart(2, '0')}:${(match[6] || '00').padStart(2, '0')}`;
return convertJSTToUTC(jstDatetime);
}
match = datetimeStr.match(/^(\d{4})-(\d{1,2})-(\d{1,2})T(\d{1,2}):(\d{1,2}):(\d{1,2})$/);
if (match) {
const jstDatetime = `${match[1]}-${match[2].padStart(2, '0')}-${match[3].padStart(2, '0')}T${match[4].padStart(2, '0')}:${match[5].padStart(2, '0')}:${match[6].padStart(2, '0')}`;
return convertJSTToUTC(jstDatetime);
}
match = datetimeStr.match(/^(\d{4})-(\d{1,2})-(\d{1,2})T(\d{1,2}):(\d{1,2})$/);
if (match) {
const jstDatetime = `${match[1]}-${match[2].padStart(2, '0')}-${match[3].padStart(2, '0')}T${match[4].padStart(2, '0')}:${match[5].padStart(2, '0')}:00`;
return convertJSTToUTC(jstDatetime);
}
return datetimeStr;
};
// 基本エディタクラスの共通処理を生成する関数
const createPickerEditor = (pickerType) => {
return class extends Handsontable.editors.BaseEditor {
init() {
this.picker = null;
this.container = document.createElement('div');
Object.assign(this.container.style, {
position: 'absolute',
zIndex: '10000',
backgroundColor: 'white',
border: '1px solid #ccc',
padding: '10px',
boxShadow: '0 2px 8px rgba(0,0,0,0.15)'
});
document.body.appendChild(this.container);
this.shouldClose = false;
this.outsideClickHandler = null;
}
prepare(row, col, prop, td, originalValue, cellProperties) {
super.prepare(row, col, prop, td, originalValue, cellProperties);
this.shouldClose = false;
}
open() {
// UTC形式の日時をJST形式に変換して編集用の値を作成
let currentValue = this.originalValue || '';
if (pickerType === 'datetime' && currentValue && /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z$/.test(currentValue)) {
const match = currentValue.match(/^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):\d{2}Z$/);
if (match) {
// UTC時刻をJSTに変換(日本は+9時間)
const utcDate = new Date(`${match[1]}-${match[2]}-${match[3]}T${match[4]}:${match[5]}:00Z`);
const jstDate = new Date(utcDate.getTime() + 9 * 60 * 60 * 1000);
const year = jstDate.getUTCFullYear();
const month = String(jstDate.getUTCMonth() + 1).padStart(2, '0');
const day = String(jstDate.getUTCDate()).padStart(2, '0');
const hour = String(jstDate.getUTCHours()).padStart(2, '0');
const minute = String(jstDate.getUTCMinutes()).padStart(2, '0');
currentValue = `${year}-${month}-${day}T${hour}:${minute}`;
}
}
// フィールドの種類に応じたピッカーコンポーネントを作成
const KucPicker = pickerType === 'date' ? Kuc.DatePicker : (pickerType === 'time' ? Kuc.TimePicker : Kuc.DateTimePicker);
this.picker = new KucPicker({ value: currentValue, language: 'ja' });
// ピッカーの値が変更されたときの処理
this.picker.addEventListener('change', () => {
this.shouldClose = true;
this.close();
this.instance.setDataAtCell(this.row, this.col, this.picker.value);
});
this.container.innerHTML = '';
this.container.appendChild(this.picker);
// セルの位置を取得して配置
const rect = this.TD.getBoundingClientRect();
this.container.style.left = rect.left + 'px';
this.container.style.top = (rect.bottom + window.scrollY) + 'px';
this.container.style.display = 'block';
// 外部クリックで閉じる処理
setTimeout(() => {
this.outsideClickHandler = (e) => {
if (!this.container.contains(e.target) && !this.picker.contains(e.target)) {
this.discardEditing();
}
};
document.addEventListener('click', this.outsideClickHandler);
}, 100);
}
close() {
if (this.shouldClose) {
this.container.style.display = 'none';
if (this.outsideClickHandler) {
document.removeEventListener('click', this.outsideClickHandler);
this.outsideClickHandler = null;
}
}
}
getValue() {
return this.originalValue;
}
setValue(newValue) {}
focus() {}
discardEditing() {
this.shouldClose = true;
this.close();
this.finishEditing();
}
};
};
// 複数選択とチェックボックスのエディタ
class MultiSelectEditor extends Handsontable.editors.BaseEditor {
init() {
this.multichoice = null;
this.container = document.createElement('div');
Object.assign(this.container.style, {
position: 'absolute',
zIndex: '10000',
backgroundColor: 'white',
border: '1px solid #ccc',
padding: '10px',
boxShadow: '0 2px 8px rgba(0,0,0,0.15)'
});
document.body.appendChild(this.container);
this.isFinishing = false;
this.shouldClose = false;
this.outsideClickHandler = null;
}
prepare(row, col, prop, td, originalValue, cellProperties) {
super.prepare(row, col, prop, td, originalValue, cellProperties);
this.cellProperties = cellProperties;
this.isFinishing = false;
this.shouldClose = false;
}
open() {
// 元の値をカンマ区切り文字列から配列に変換
let selectedValues = [];
if (this.originalValue && typeof this.originalValue === 'string') {
selectedValues = this.originalValue.split(',').map(v => v.trim()).filter(v => v);
} else if (Array.isArray(this.originalValue)) {
selectedValues = this.originalValue;
}
// 複数選択コンポーネントを作成してOKキャンセルボタンを配置
this.multichoice = new Kuc.MultiChoice({ items: this.cellProperties.options, value: selectedValues });
const okButton = new Kuc.Button({ text: 'OK', type: 'submit' });
okButton.addEventListener('click', () => {
this.confirmEditing();
});
const cancelButton = new Kuc.Button({ text: 'キャンセル', type: 'normal' });
cancelButton.addEventListener('click', () => {
this.discardEditing();
});
const buttonContainer = document.createElement('div');
buttonContainer.style.marginTop = '10px';
buttonContainer.style.display = 'flex';
buttonContainer.style.gap = '8px';
buttonContainer.appendChild(okButton);
buttonContainer.appendChild(cancelButton);
this.container.innerHTML = '';
this.container.appendChild(this.multichoice);
this.container.appendChild(buttonContainer);
// セルの位置を取得して配置
const rect = this.TD.getBoundingClientRect();
this.container.style.left = rect.left + 'px';
this.container.style.top = (rect.bottom + window.scrollY) + 'px';
this.container.style.display = 'block';
// 外部クリックで閉じる処理
setTimeout(() => {
this.outsideClickHandler = (e) => {
if (!this.container.contains(e.target)) {
this.discardEditing();
}
};
document.addEventListener('click', this.outsideClickHandler);
}, 100);
}
close() {
if (this.shouldClose) {
this.container.style.display = 'none';
if (this.outsideClickHandler) {
document.removeEventListener('click', this.outsideClickHandler);
this.outsideClickHandler = null;
}
}
}
getValue() {
if (this.multichoice && this.isFinishing) {
return this.multichoice.value.join(', ');
}
return this.originalValue;
}
setValue(newValue) {}
focus() {}
confirmEditing() {
this.isFinishing = true;
this.shouldClose = true;
const newValue = this.getValue();
this.close();
this.instance.setDataAtCell(this.row, this.col, newValue);
}
discardEditing() {
this.isFinishing = false;
this.shouldClose = true;
this.close();
this.finishEditing();
}
}
// 日付、時刻、日時のピッカーの登録
Handsontable.editors.registerEditor('multiselectEditor', MultiSelectEditor);
Handsontable.editors.registerEditor('datePickerEditor', createPickerEditor('date'));
Handsontable.editors.registerEditor('timePickerEditor', createPickerEditor('time'));
Handsontable.editors.registerEditor('dateTimePickerEditor', createPickerEditor('datetime'));
// Handsontableの初期化と設定
const hot = new Handsontable(container, {
data: data,
colHeaders: headers,
columns: columns,
rowHeaders: (index) => rowHeaders[index],
width: '100%',
height: 600,
licenseKey: 'non-commercial-and-evaluation',
stretchH: 'all',
contextMenu: true,
manualColumnResize: true,
manualRowResize: true,
filters: true,
dropdownMenu: true,
columnSorting: true,
afterChange: (changes, source) => {
// 編集、貼り付け、自動入力時にレコードを更新
if ((source === 'edit' || source === 'CopyPaste.paste' || source === 'Autofill.fill') && changes) {
changes.forEach(([row, prop, oldValue, newValue]) => {
if (oldValue === newValue) return;
const field = fields[prop];
if (!field) return;
const recordId = data[row].$id;
const record = {};
let value = newValue;
// フィールドの種類に応じた値の変換処理
if (field.type === 'CHECK_BOX' || field.type === 'MULTI_SELECT') {
// カンマ区切り文字列を配列に変換
const arrayValue = (typeof value === 'string' && value.trim()) ? value.split(',').map(v => v.trim()).filter(v => v) : [];
record[prop] = { value: arrayValue };
} else if (field.type === 'USER_SELECT' || field.type === 'ORGANIZATION_SELECT' || field.type === 'GROUP_SELECT') {
// 入力された表示名をコードに変換して配列にする
if (typeof value === 'string' && value.trim()) {
const inputs = value.split(',').map(v => v.trim()).filter(v => v);
const arrayValue = inputs.map(input => {
if (field.type === 'USER_SELECT' && nameToCode[input]) {
return { code: nameToCode[input] };
} else if (field.type === 'ORGANIZATION_SELECT' && orgNameToCode[input]) {
return { code: orgNameToCode[input] };
}
return { code: input };
});
record[prop] = { value: arrayValue };
} else {
record[prop] = { value: [] };
}
} else if (field.type === 'FILE') {
return;
} else if (field.type === 'DATE') {
value = normalizeDateFormat(value);
record[prop] = { value: value || '' };
} else if (field.type === 'DATETIME') {
// 日時が YYYY-MM-DDTHH:MM:SSZ 形式になっているか確認
value = normalizeDatetimeFormat(value);
if (value && !/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z$/.test(value)) {
hot.setDataAtCell(row, hot.propToCol(prop), oldValue, 'revert');
return;
}
record[prop] = { value: value || '' };
} else {
record[prop] = { value: value || '' };
}
// レコード更新リクエストを送信
client.record.updateRecord({ app: appId, id: recordId, record: record }).then(() => {
// 日時の場合はレンダラーで正しくフォーマットされるように整形後の値をdata配列に保存
if (field.type === 'DATETIME') {
data[row][prop] = value;
} else {
data[row][prop] = newValue;
}
// 再レンダリングして正しいフォーマットで表示
hot.render();
}).catch((error) => {
console.error(error);
console.error(`レコード番号 ${rowHeaders[row]} の「${field.label}」の保存に失敗:`, error.message || error);
// エラー時は元の値に戻す
hot.setDataAtCell(row, hot.propToCol(prop), oldValue, 'revert');
});
});
}
}
});
});
}).catch((error) => {
console.error(error);
alert('ユーザー・組織情報の取得に失敗しました');
});
});
}).catch((error) => {
console.error(error);
alert('レコードの取得に失敗しました');
});
};
})();