はじめに
大勢が共有して利用するシートにおいて、人的ミスは避けられない。（※繰り返し）
以前に投稿した『Google Apps Scriptを用いたスプレッドシート保護の設定を行う』では、編集可能な人を制限することでシートの不用意な改変の防止を試みた。
本稿では、指定範囲部分にドロップダウンリストを設定し、入力可能な値の制限を行う設定をGoogle Apps Script(GAS)で記載することで、入力データの定型化を図る。
概要
・ プログラミングに不慣れな人でも簡単にできるよう、
入力規則設定用シート（"[入力規則]Config"）を作成し、このシートをベースに入力規則の設定を行う
・ 青色部分 のシート範囲において、赤色部分 に記載された値のみを入力可能な状態へと設定をする
（= ドロップダウンリストの作成）
スクリプト
実行方法
dataValidation.gs
function dataValidation(){
addValidate('B2:B'); // "[入力規則]Config"シートから読み込む範囲を指定
addValidate('C2:C');
}
メインメソッド
dataValidation.gs
// Configシートを読み込む
var protectconfig_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('[入力規則]Config');
function addValidate(param_range){
// Underscoreライブラリ(M3i7wmUA_5n0NSEaa6NnNqOBao7QLBR4j)の導入が必要
var _ = Underscore.load();
/* 1. configシートから、入力規則を設けるシート名・範囲情報とドロップダウンリストを読み取る */
var validate_param = protectconfig_sheet.getRange(param_range).getValues();
var validate_sheetName = String(validate_param[0][0]);
var validate_range = String(validate_param[1][0]);
var validate_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(validate_sheetName).getRange(validate_range);
/* 2. ドロップダウンリストの配列を作成 (データ範囲を転置 → データ範囲の1列目２行目以降に値が入っているものを抽出して配列化) */
var valuelist = [];
var validate_paramTrans = _.zip.apply(_, validate_param).filter(String);
var validate_paramList = validate_paramTrans[0]
for (var i = 2; i < validate_paramList.length; i++){
if(validate_paramList[i] != null && validate_paramList[i] != "") {
valuelist.push(validate_paramList[i]);
}
}
/* 3. 指定範囲にドロップダウンリストを設定する */
var rule = SpreadsheetApp.newDataValidation().requireValueInList(valuelist, true).build();
validate_sheet.setDataValidation(rule);
}
結果
やったね(^^)
補足
メインメソッド最終行手前の .requireValueInList の部分を変更することで、より複雑な設定が可能。
詳しくは公式ドキュメントを参照してね。