Help us understand the problem. What is going on with this article?

Google Apps Scriptを用いたスプレッドシート入力規則の設定を行う

はじめに

大勢が共有して利用するシートにおいて、人的ミスは避けられない。(※繰り返し)

以前に投稿した『Google Apps Scriptを用いたスプレッドシート保護の設定を行う』では、編集可能な人を制限することでシートの不用意な改変の防止を試みた。

本稿では、指定範囲部分にドロップダウンリストを設定し、入力可能な値の制限を行う設定をGoogle Apps Script(GAS)で記載することで、入力データの定型化を図る。

概要

・ プログラミングに不慣れな人でも簡単にできるよう、
  入力規則設定用シート("[入力規則]Config"を作成し、このシートをベースに入力規則の設定を行う
青色部分 のシート範囲において、赤色部分 に記載された値のみを入力可能な状態へと設定をする
  (= ドロップダウンリストの作成)
n入力規則.png

スクリプト

実行方法

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列目2行目以降に値が入っているものを抽出して配列化) */

  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);

}

結果

ドロップダウンリスト.png
設定範囲.png

やったね(^^)

補足

メインメソッド最終行手前の .requireValueInList の部分を変更することで、より複雑な設定が可能。
詳しくは公式ドキュメントを参照してね。

参考サイト

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away