こんにちは！ウメハラ（plumfield56）です。
前回はスプレッドシートでプルダウンを連動させる設定を行いました。
スプレッドシートのプルダウンを連動させる方法 その②
前回はスプレッドシートでプルダウンを連動させる設定を行いました。今回はプルダウン設定をGASで行って業務効率化をしていくところをお伝えしていきたいと思います。汎用的にする汎用性が高く様々な条件で...
plumfield56.com
2021.01.09
今回はプルダウン設定をGASで行って業務効率化をしていくところをお伝えしていきたいと思います。
汎用的にする
汎用性が高く様々な条件で使用できるように 「設定箇所」 のみ変更すれば良いようにしました。
function myFunction() {
// 設定箇所
/*-------------------------------*/
// データの入力規則を設定する先の情報
const targetShtName = 'data';
const targetColumn = 'B';
const startRow = 2;
// データの入力規則の範囲
const ruleShtName = 'data';
const ruleColumns = 'E:J';
/*-------------------------------*/
const targetSht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetShtName);
const lastRow = targetSht.getMaxRows();
const ruleSht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(ruleShtName);
for(let i=startRow; i<=lastRow; i++){
// 入力規則でセットする範囲を取得
let range = ruleColumns.replace(':', i+ ':') + i;
range = ruleSht.getRange(range);
// 入力規則を作成
const rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
// 入力規則をセットするセル範囲を取得
const setCell = targetSht.getRange(targetColumn + i);
// セルに入力規則をセット
setCell.setDataValidation(rule);
}
}さらに汎用性高くする
HTMLで入力欄を設けることによってプログラミング不要でも設定することが可能なのでコードを書いてみました。
イメージは下記の動画になります。
function myFunction() {
const html = HtmlService.createHtmlOutputFromFile('index')
.setWidth(500);
SpreadsheetApp.getUi().showModalDialog(html, "設定");
}
function getShts() {
return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(val => val.getName());
}
function setRuls(obj) {
const targetSht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(obj['targetShtName']);
const lastRow = targetSht.getMaxRows();
const ruleSht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(obj['ruleShtName']);
for(let i=parseInt(obj['startRow']); i<=lastRow; i++){
// 入力規則でセットする範囲を取得
let range = obj['ruleColumn1'] + i + ':' + obj['ruleColumn2'] + i;
range = ruleSht.getRange(range);
// 入力規則を作成
const rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
// 入力規則をセットするセル範囲を取得
const setCell = targetSht.getRange(obj['targetColumn'] + i);
// セルに入力規則をセット
setCell.setDataValidation(rule);
}
}<!DOCTYPE html>
<html lang="ja">
<head>
<style>
#container {
width: 400px;
}
.item {
margin-top: 10px;
padding: 5px;
text-align: center;
background: #bbb;
}
.item-box {
margin-bottom: 10px;
padding: 20px;
background: #ddd;
}
.wrap {
margin-left: 20px;
margin-bottom: 20px;
}
.column, .row {
width: 40px;
text-align: center;
font: 15px/24px sans-serif;
transition: 0.3s;
letter-spacing: 1px;
border: none;
border-bottom: 1px solid #000;
}
</style>
</head>
<body onload="init()">
<div id="container">
<div class="item">入力規則を入れる列</div>
<div class="item-box">
<span class="item-name">シート名</span>
<div class="wrap">
<select class="select" id="targetShtName">
</select >
</div>
<span class="item-name">列</span>
<div class="wrap">
<input type="text" class="column" id="targetColumn" placeholder="AB">列
</div>
<span class="item-name">開始行</span>
<div class="wrap">
<input type="number" class="row" id="startRow" min="1" placeholder=5>
</div>
</div>
<div class="item">入力規則の範囲</div>
<div class="item-box">
<span class="item-name">シート名</span>
<div class="wrap">
<select class="select" id="ruleShtName">
</select >
</div>
<span class="item-name">列</span>
<div class="wrap">
<input type="text" class="column" id="ruleColumn1" placeholder="AB">～<input type="text" class="column" id="ruleColumn2" placeholder="AF">列
</div>
</div>
</div>
<button onclick="execute()">実行</button>
<script>
function init() {
google.script.run.withSuccessHandler(function(shts){
const selectClass = document.getElementsByClassName('select');
for(const select of selectClass) {
for(const sht of shts) {
const option = document.createElement('option');
option.text = sht;
select.appendChild(option);
}
}
}).getShts();
}
function execute() {
const obj = {};
const ids = ['targetShtName', 'targetColumn', 'startRow', 'ruleShtName', 'ruleColumn1', 'ruleColumn2']
for(const id of ids) {
obj[id] = document.getElementById(id).value;
}
google.script.run.withSuccessHandler(function(){
alert('実行完了');
}).setRuls(obj);
}
</script>
</body>
</html>
コメント