【GAS】入力規則(プルダウン)を作成する

今回はGASを使って入力規則(プルダウン)を作成する方法を説明します。

入力規則とは

入力規則とはセルに値を入力する際、あらかじめ設定されているリストの中から選択できる機能です。

直接入力をせずにリストから選択するため、入力ミスを防ぐことができます。

スプレッドシートで設定する場合はツールバーの「データ」→「データの入力規則」から行いますが、今回はGASを使って設定する方法を説明します。

想定場面

今回「企業一覧」シート、「タスク管理」シートが存在するとして、「企業一覧」シートの「企業名」列の内容を「タスク管理」シートのリストに設定します。

企業名を追加するとリストにも追加されるようにします。

スプレッドシート連携、シート名の指定

今回はコンテナバインドスクリプトで作成します。

スプレッドシート操作に関してはこちら(URL)の記事でも解説しています。

まずはスプレッドシートの連携と今回使うシート(「企業一覧」「タスク管理」)の指定を行います。

GASと紐づいているスプレッドシートをgetActiveSpreadsheet()メソッドを使って取得し、変数spreadsheetに格納しています。


// スプレッドシートと連携
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

シート名を変数に格納

今回使うシート名をそれぞれ変数に格納しています。

シート名を指定してシートを取得

spreadsheetオブジェクトに対してgetSheetByName()メソッドを使ってシートを取得しそれぞれ変数に入れています


 // シート名を変数に格納
 const scheduleSheetName = `タスク管理`;
 const corporateSheetName = `企業一覧`;
　　// シート名を指定してシートを取得
 const scheduleSheet = spreadsheet.getSheetByName(scheduleSheetName);
 const corporateSheet = spreadsheet.getSheetByName(corporateSheetName);

リストにする範囲を指定

リストの範囲を指定する方法ですが、配列を使用する方法、直接範囲を指定する方法の2通りを説明します。

まず準備として今回使用する行、列の番号を変数に入れておきます。

変数に入れておくことで修正が楽になります。変数についてはこちらの記事で(URL貼る)


// 「企業一覧」シートの「企業名」列の最初の行番号
const firstRow = 6;

「企業一覧」シートの「企業名」列は6行目から開始するため、変数firstRowに6を格納します


// 「企業一覧」シートの「企業名」列の最後の行番号
const lastRow = corporateSheet.getLastRow();

「企業一覧」シートの「企業名」列の最後の行番号はシートのオブジェクトに対してgetLastRow()メソッドを使うことで取得できます。

getLastRow()はシート内の値が入ったセルの最終行番号を取得するメソッドです。


// 「企業一覧」シートの「企業名」列番号
const corporateNameColumn = 2;

「企業一覧」シートの「企業名」の列番号は2(B)のため変数に格納します。

配列を使用する方法


// 企業名を格納する配列
 const corporateNames = [];

企業名を格納する空の配列を準備します。


// 「企業一覧」シートの企業名を配列に格納
for (let row=firstRow; row<=lastRow; row++) {
  // 「企業名」が入ったセルを指定
  const corporateNameCell = corporateSheet.getRange(row, corporateNameColumn);
	// 「企業名」が入ったセルの値を取得
  const corporateName = corporateNameCell.getValue();
	// 「企業名」を配列に追加
  corporateNames.push(corporateName);
}

for文を使って「企業名」一つずつ配列に追加していきます。

for文の条件


for (let row=firstRow; row<=lastRow; row++) {
	// 処理
}

企業名の入ったセルを1つずつ処理していくため、for文の()内で繰り返しの範囲を指定します。

処理の開始: firstRow(「企業一覧」シートの「企業名」列の最初の行番号)

処理の終了: lastRow(「企業一覧」シートの「企業名」列の最初の行番号)

増減: 1増やす

変数rowがfirstRowからlastRowまで1つずつ増加します。

「企業名」が入ったセルを指定


const corporateNameCell = corporateSheet.getRange(row, corporateNameColumn);

シートオブジェクトに対してgetRange(行番号, 列番号)メソッドを使ってセルを指定します。

行番号にはfor文の中でfirstRowからlastRowまで1ずつ増えていく変数rowを指定します。

列番号には「企業一覧」シートの「企業名」の列番号を格納したcorporateNameColumnを指定します。

指定したセルを変数corporateNameCellに格納しています。

セルの値を取得


const corporateName = corporateNameCell.getValue();

取得したセルに対してgetValue()メソッドを使って値を取得します。

配列に企業名を追加


corporateNames.push(corporateName);

配列への追加は配列に対してpush(追加したい要素)メソッドを使うことで可能です。

範囲を直接指定する方法

会社名が記載されている範囲を取得


corporateRange = corporateSheet.getRange(firstRow, corporateNameColumn, lastRow-5, 1);

入力規則の作成

入力規則の作成はSpreadsheetAppに対してnewDataValidation().リストの値.build()で作成できます。

リストの値はリストにする範囲を指定した方法によって異なります。

それぞれ下記のメソッドで取得することが可能です。

配列を使用した場合


const rule = SpreadsheetApp.newDataValidation().requireValueInList(corporateNames).build();

requireValueInList()に先ほど作成した配列を指定しています。

直接指定した場合


const rule = SpreadsheetApp.newDataValidation().requireValueInRange(corporateRange).build();

requireValueInRange()に先ほど作成した範囲を指定しています。

入力規則を設定する範囲を指定

入力規則が作成できたら、設定する範囲を指定します。


let cell = scheduleSheet.getRange(scheduleSheet.getLastRow() + 1, corporateNameColumn);

変数cellにgetRange(行番号, 列番号)で取得した範囲を格納しています。

getLastRow()メソッドでscheduleSheetの最終行番号(上記の例では5)を取得できるので行番号にはその1行下の番号を指定します。

列番号には企業名の列番号corporateNameColumnを指定します。

指定した範囲に入力規則を設定する

範囲が指定できたので入力規則を設定します。


// 設定する範囲.setDataValidation(入力規則);
cell.setDataValidation(rule);

入力規則の設定は範囲のオブジェクトに対してsetDataValidation(入力規則)を用いることで可能です。

以上で入力規則を作成するプログラムが完成です。

実行してみると入力規則が設定されます。

企業名を選択してもう一度実行すると1行下のセルに入力規則が設定されます。

全体コード


// --------------------------- 会社リストを作成 -------------------------------
function createCorporateList() {
  // スプレッドシートと連携
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // シート名設定
  const scheduleSheetName = `タスク管理`;
  const corporateSheetName = `企業一覧`;
  const scheduleSheet = spreadsheet.getSheetByName(scheduleSheetName);
  const corporateSheet = spreadsheet.getSheetByName(corporateSheetName);

  // 「企業一覧」シートの「企業名」列の最初の行番号
  const firstRow = 6;
  // 「企業一覧」シートの「企業名」列の最後の行番号
  const lastRow = corporateSheet.getLastRow();
  // 「企業一覧」シートの「企業名」列番号
  const corporateNameColumn = 2;

  // ---------------------------- 配列を使用する場合↓ ----------------------------
  // 企業名を格納する配列
  const corporateNames = [];

  // 「企業一覧」シートの企業名を配列に格納
  for (let row=firstRow; row<=lastRow; row++) {
    const corporateNameCell = corporateSheet.getRange(row, corporateNameColumn);
    const corporateName = corporateNameCell.getValue();
    corporateNames.push(corporateName);
  }

  const rule = SpreadsheetApp.newDataValidation().requireValueInList(corporateNames).build();
  // ---------------------------- 配列を使用する場合↑ ----------------------------

  // ---------------------------- 範囲を直接指定する場合↓ ----------------------------
  // corporateRange = corporateSheet.getRange(firstRow, corporateNameColumn, lastRow-5, 1);
  // const rule = SpreadsheetApp.newDataValidation().requireValueInRange(corporateRange).build();
  // ---------------------------- 範囲を直接指定する場合↑ ----------------------------


  let cell = scheduleSheet.getRange(scheduleSheet.getLastRow()+1, corporateNameColumn)
  cell.setDataValidation(rule);
}