スプレッドシートでSpreadJSライブラリを利用してFormaの画面に対して入力チェックを実行する方法

このCookBookでは、スプレッドシートでSpreadJSライブラリを利用して入力チェックを実行する方法について紹介しています。
SpreadJSライブラリはクライアントサイドで動作するため、サーバ通信せずに入力チェックを行うことが可能です。

SpreadJSライブラリについては以下のGrapeCity社のマニュアルを参照してください。
SpreadJS

サーバサイドでの入力チェックは以下の記事を参照してください。
スプレッドシートでForma入力チェックユーザプログラムを利用して入力チェックを実行する方法

完成イメージ

1. 入力値に対してリアルタイムで入力チェックを行います。

・表示されているダイアログのOKボタンをクリックすると再度セルにフォーカスが当たり、値を変更することを求めます。
・キャンセルボタンをクリックするとセルの入力内容を元の状態に戻します。

2. 登録ボタンのアクション設定で入力項目に対する必須チェックを行います。

完成サンプル

以下の完成サンプルをダウンロードしてご活用ください。

Forma定義 : im_cookbook_153611_forma
- Formaアプリケーション情報インポート機能からインポートしてください。
- 手順の詳細は以下のドキュメントを参照してください。
  - IM-FormaDesigner for Accel Platform 作成者操作ガイド - 「アプリケーション情報インポート」画面の機能と各部の説明
- サンプルに含まれる定義情報は以下の表の通りです。

種類	定義名	補足説明
Formaアプリケーション情報	SpreadJSを利用してスプレッドシートに対して入力チェックする	通勤交通費申請を行うアプリケーションです。

レシピ

Formaアプリケーションを作成し、通勤交通費申請書を表現したフォームにスプレッドシートアイテムを配置します。
スクリプトアイテムを配置し、スプレッドシートアイテムに対して入力チェック機能を実装します。
登録ボタンのアクション設定で入力項目に対する必須チェックを設定します。

完成サンプルを見ながら以下の解説を確認してください。

1. Formaアプリケーションを作成し、通勤交通費申請書を表現したフォームにスプレッドシートアイテムを配置します。

以下の項目をシート上のフィールドとして定義してください。

Applicant
Affiliation department
Applicant's address
Closest station

以下の項目をシート上のテーブルとして定義してください。

Public transport fare

以下の項目をテーブルに表示する列として設定してください。

Transportation to use
Boarding section (From)
Boarding section (To)
3 months (commuter pass)
1 month (commuter pass)
one day (round trip)

2. スクリプトアイテムを配置し、スプレッドシートアイテムに対して入力チェック機能を実装します。

入力チェックには、以下の SpreadJS API を使用しています。（GrapeCity社のマニュアルへ遷移します。）

詳しい処理内容はインラインでコメントを入れています。

// 入力値の桁数チェックを行うバリデータを生成して返却します。
var createTextLengthValidator = function(length) {
  var dv = GC.Spread.Sheets.DataValidation.createTextLengthValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.lessThanOrEqualsTo, length + '');
  dv.errorMessage('Number of characters must be less than ' + length + '.');
  return dv;
}

// 数値の最大値チェックを行うバリデータを生成して返却します。
var createNumberLimitValidator = function(limit) {
  var dv = GC.Spread.Sheets.DataValidation.createNumberValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.lessThanOrEqualsTo, limit, null, true);
  dv.errorMessage('Value must be less than ' + limit + '.');
  return dv;
}

// 0 から 999999 の範囲内の数値かどうかチェックを行うバリデータを生成して返却します。
// 数値以外の値が入力された場合もエラーと判定します。
var createNumberValidator = function() {
  var dv = GC.Spread.Sheets.DataValidation.createNumberValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, 0, 999999, true);
  dv.errorMessage('Value must be number.(and be less than 999999.)');
  return dv;
}

// ワークブックオブジェクトを取得するAPIです。
// パラメータにはスプレッドシートアイテムのDOMオブジェクトを指定します。
var spread = GC.Spread.Sheets.findControl(document.getElementById('spreadsheet1'));

// アクティブなシートを取得します。
var activeSheet = spread.getActiveSheet();

// バリデータによってエラーと判定された際に発生するイベントです。
activeSheet.bind(GC.Spread.Sheets.Events.ValidationError, function (sender, args) {
  if (args.validator.showErrorMessage()) {
    // imuiConfirm や imuiMessageDialog 等はフォーカスが外れてしまうため使用しません。
    if (confirm(args.validator.errorMessage())) {
      // 検証エラーがある場合、値をセルに適用するためにユーザに対して再度入力を要求します。
      args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.retry;
    } else {
      // 検証エラーがある場合、値を破棄します。
      args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.discard;
    }
    // validationResult に指定する値は以下のドキュメントを参照してください。
    // https://docs.grapecity.com/help/spread-js/sheets/#SpreadJS~GC.Spread.Sheets.DataValidation.DataValidationResult.html
  }
});

// 「Application date」に対して 2018/4/1 から 2020/12/31 までの日付のみを許可するバリデータを設定します。
var dv = GC.Spread.Sheets.DataValidation.createDateValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, new Date(2018, 3, 1), new Date(2020, 11, 31));
dv.errorMessage('Enter a date between 2018/4/1 and 2020/12/31.');
activeSheet.getCell(4, 5).validator(dv);

// 「Applicant」に対して 250 文字以下のみの入力を許可するバリデータを設定します。
activeSheet.setDataValidator(7, 3, createTextLengthValidator(250));
// 「Affiliation department」に対して 250 文字以下のみの入力を許可するバリデータを設定します。
activeSheet.setDataValidator(8, 3, createTextLengthValidator(250));
// 「Applicant's address」に対して 250 文字以下のみの入力を許可するバリデータを設定します。
activeSheet.setDataValidator(9, 3, createTextLengthValidator(250));

// 「one month(commuter pass)」に対して 30000以下かどうかチェックするバリデータを設定します。
activeSheet.setDataValidator(17, 5, createNumberLimitValidator(30000));
activeSheet.setDataValidator(18, 5, createNumberLimitValidator(30000));
activeSheet.setDataValidator(19, 5, createNumberLimitValidator(30000));
activeSheet.setDataValidator(20, 5, createNumberLimitValidator(30000));
activeSheet.setDataValidator(21, 5, createNumberLimitValidator(30000));

// 「three months(commuter pass)」、「one month(commuter pass)」、「1 day(round trip)」に対して数値であるかチェックするバリデータを設定します。
var rowRange = {start: 17, end: 21};
var columnRange = {start: 4, end: 6};
for (var rowIndex = rowRange.start; rowIndex <= rowRange.end; rowIndex++) {
    for (var columnIndex = columnRange.start; columnIndex <= columnRange.end; columnIndex++) {
        activeSheet.setDataValidator(rowIndex, columnIndex, createNumberValidator());
    }
}

// 入力値の桁数チェックを行うバリデータを生成して返却します。

var createTextLengthValidator = function(length) {

var dv = GC.Spread.Sheets.DataValidation.createTextLengthValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.lessThanOrEqualsTo, length + '');

dv.errorMessage('Number of characters must be less than ' + length + '.');

return dv;

}

// 数値の最大値チェックを行うバリデータを生成して返却します。

var createNumberLimitValidator = function(limit) {

var dv = GC.Spread.Sheets.DataValidation.createNumberValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.lessThanOrEqualsTo, limit, null, true);

dv.errorMessage('Value must be less than ' + limit + '.');

return dv;

}

// 0 から 999999 の範囲内の数値かどうかチェックを行うバリデータを生成して返却します。

// 数値以外の値が入力された場合もエラーと判定します。

var createNumberValidator = function() {

var dv = GC.Spread.Sheets.DataValidation.createNumberValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, 0, 999999, true);

dv.errorMessage('Value must be number.(and be less than 999999.)');

return dv;

}

// ワークブックオブジェクトを取得するAPIです。

// パラメータにはスプレッドシートアイテムのDOMオブジェクトを指定します。

var spread = GC.Spread.Sheets.findControl(document.getElementById('spreadsheet1'));

// アクティブなシートを取得します。

var activeSheet = spread.getActiveSheet();

// バリデータによってエラーと判定された際に発生するイベントです。

activeSheet.bind(GC.Spread.Sheets.Events.ValidationError, function (sender, args) {

if (args.validator.showErrorMessage()) {

// imuiConfirm や imuiMessageDialog 等はフォーカスが外れてしまうため使用しません。

if (confirm(args.validator.errorMessage())) {

// 検証エラーがある場合、値をセルに適用するためにユーザに対して再度入力を要求します。

args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.retry;

} else {

// 検証エラーがある場合、値を破棄します。

args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.discard;

}

// validationResult に指定する値は以下のドキュメントを参照してください。

// https://docs.grapecity.com/help/spread-js/sheets/#SpreadJS~GC.Spread.Sheets.DataValidation.DataValidationResult.html

}

});

// 「Application date」に対して 2018/4/1 から 2020/12/31 までの日付のみを許可するバリデータを設定します。

var dv = GC.Spread.Sheets.DataValidation.createDateValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, new Date(2018, 3, 1), new Date(2020, 11, 31));

dv.errorMessage('Enter a date between 2018/4/1 and 2020/12/31.');

activeSheet.getCell(4, 5).validator(dv);

// 「Applicant」に対して 250 文字以下のみの入力を許可するバリデータを設定します。

activeSheet.setDataValidator(7, 3, createTextLengthValidator(250));

// 「Affiliation department」に対して 250 文字以下のみの入力を許可するバリデータを設定します。

activeSheet.setDataValidator(8, 3, createTextLengthValidator(250));

// 「Applicant's address」に対して 250 文字以下のみの入力を許可するバリデータを設定します。

activeSheet.setDataValidator(9, 3, createTextLengthValidator(250));

// 「one month(commuter pass)」に対して 30000以下かどうかチェックするバリデータを設定します。

activeSheet.setDataValidator(17, 5, createNumberLimitValidator(30000));

activeSheet.setDataValidator(18, 5, createNumberLimitValidator(30000));

activeSheet.setDataValidator(19, 5, createNumberLimitValidator(30000));

activeSheet.setDataValidator(20, 5, createNumberLimitValidator(30000));

activeSheet.setDataValidator(21, 5, createNumberLimitValidator(30000));

// 「three months(commuter pass)」、「one month(commuter pass)」、「1 day(round trip)」に対して数値であるかチェックするバリデータを設定します。

var rowRange = {start: 17, end: 21};

var columnRange = {start: 4, end: 6};

for (var rowIndex = rowRange.start; rowIndex <= rowRange.end; rowIndex++) {

for (var columnIndex = columnRange.start; columnIndex <= columnRange.end; columnIndex++) {

activeSheet.setDataValidator(rowIndex, columnIndex, createNumberValidator());

}

3. 登録ボタンのアクション設定で入力項目に対する必須チェックを設定します。

登録ボタンのクリックイベントにカスタムスクリプトを指定し、以下のスクリプトを記述します。

セルの値取得には、以下の SpreadJS API を使用しています。（GrapeCity社のマニュアルへ遷移します。）

詳しい処理内容はインラインでコメントを入れています。

// ワークブックオブジェクトを取得するAPIです。
// パラメータにはスプレッドシートアイテムのDOMオブジェクトを指定します。
var spread = GC.Spread.Sheets.findControl(document.getElementById('spreadsheet1'));

// アクティブなシートを取得します。
var activeSheet = spread.getActiveSheet();

// エラーメッセージを格納します。
var errorMessageList = [];

// 必須チェック関数です。
var required = function(row, column, label, errorMessageList) {
    // activeSheet.getCell(rowIndex, columnIndex) でセルオブジェクトを取得し、 text() で値を取得できます。
    var value = activeSheet.getCell(row, column).text();
    if (value === '') {
        if (errorMessageList !== undefined) {
            errorMessageList.push(label + ' is required.');
        } else {
            return true;
        }
    }
};

// 単項目に対する必須チェックです。
required(4, 5, 'Application date', errorMessageList);
required(7, 3, 'Applicant', errorMessageList);
required(8, 3, 'Affiliation department', errorMessageList);
required(9, 3, 'Applicant\'s address', errorMessageList);
required(10, 3, 'Closest station', errorMessageList);

// テーブルに対する必須チェックです。
var rowRange = {start: 17, end: 21};
var columnRange = {start: 1, end: 6};
for (var rowIndex = rowRange.start; rowIndex <= rowRange.end; rowIndex++) {
    var requiredList = [];
    for (var columnIndex = columnRange.start; columnIndex  0 && requiredList.length !== 6) {
        errorMessageList.push('Please enter all items in the record.(line ' + (rowIndex - 16) + ')');
    }
}

// エラーメッセージ情報が存在する場合、メッセージを表示し、エラーとして返却します。
if (errorMessageList.length > 0) {
    imuiShowErrorMessage(
        '',
        errorMessageList,
        true,
        0
    );
    return true;
} else {
    return false;
}

// ワークブックオブジェクトを取得するAPIです。

// パラメータにはスプレッドシートアイテムのDOMオブジェクトを指定します。

var spread = GC.Spread.Sheets.findControl(document.getElementById('spreadsheet1'));

// アクティブなシートを取得します。

var activeSheet = spread.getActiveSheet();

// エラーメッセージを格納します。

var errorMessageList = [];

// 必須チェック関数です。

var required = function(row, column, label, errorMessageList) {

// activeSheet.getCell(rowIndex, columnIndex) でセルオブジェクトを取得し、 text() で値を取得できます。

var value = activeSheet.getCell(row, column).text();

if (value === '') {

if (errorMessageList !== undefined) {

errorMessageList.push(label + ' is required.');

} else {

return true;

}

};

// 単項目に対する必須チェックです。

required(4, 5, 'Application date', errorMessageList);

required(7, 3, 'Applicant', errorMessageList);

required(8, 3, 'Affiliation department', errorMessageList);

required(9, 3, 'Applicant\'s address', errorMessageList);

required(10, 3, 'Closest station', errorMessageList);

// テーブルに対する必須チェックです。

var rowRange = {start: 17, end: 21};

var columnRange = {start: 1, end: 6};

for (var rowIndex = rowRange.start; rowIndex <= rowRange.end; rowIndex++) {

var requiredList = [];

for (var columnIndex = columnRange.start; columnIndex 0 && requiredList.length !== 6) {

errorMessageList.push('Please enter all items in the record.(line ' + (rowIndex - 16) + ')');

}

// エラーメッセージ情報が存在する場合、メッセージを表示し、エラーとして返却します。

if (errorMessageList.length > 0) {

imuiShowErrorMessage(

'',

errorMessageList,

true,

);

return true;

} else {

return false;

}