【GAS】 スプレッドシートの項目(カラム)位置の可変に対応できる列指定

はじめに

スプレッドシートで読み書きを行う際、セル位置を固定で指定していると、スプレッドシートが変更され項目の位置が可変した場合に、誤った箇所に読み書きされてしまいます。

そこで、項目(カラム)の位置が変わっても対応できるようなコードにしていきます。

処理したいこと

今回は以下のような表の 「済フラグ」 という項目の列に「済」と入力する という処理をします。
※行も可変すべきですが、分かりづらくならないよう4行目のみ指定します。

可変に対応できない固定の指定

まずは可変に対応できない固定の指定方法を記載しておきます。
「済フラグ」はD列にあるので 4 列目を指定します。

カラム位置_固定

function column_hard() {
  // スプレッドシート取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();    
  const sheet = ss.getSheetByName('シート1');

  // 書き込み
  sheet.getRange(4,4).setValue("済");
}

問題なく、意図したところに書き込まれます。

スプレッドシートが変更され 位置が変わる

B列に日付を記載するための列が追加されたとします。

すると列を4列目に固定していたため、意図しない列に書き込まれてしまいます。

このように固定で指定してしまうと、スプレッドシートが変更された際にGASもメンテナンスしないといけません。

可変に対応できる指定方法

項目(カラム)位置が可変した場合にも対応できるようにしたのがこちら

カラム位置_可変

function column_soft() {
  // スプレッドシート取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();    
  const sheet = ss.getSheetByName('シート1');

  // ヘッダー(項目)行の指定
  var headerRow = 3; // ここを変える

  // 最終列取得
  var lastCol = sheet.getLastColumn();

  // ヘッダー行を範囲取得
  var header = sheet.getRange(headerRow, 1, 1,lastCol).getDisplayValues();

  // 項目名の指定
  var colName = "済フラグ"; // ここを変える

  // カラム位置を取得
  var colIndex = header[0].indexOf(colName) + 1;

  //  書き込み
  sheet.getRange(4,colIndex).setValue("済");
}

すると、列が追加されても

減っても対応できます。

解説

書き込み処理を行う前に、項目名が何列目にあるかを確認するようにしています。
確認用にまず、ヘッダー行を範囲取得し配列に入れます。

ヘッダー行範囲取得

  var header = sheet.getRange(headerRow, 1, 1,lastCol).getDisplayValues();

以下のように２次元配列で取得されます。

header	[0]	[1]	[2]	[3]	[4]
[0]	No	請求日	請求先	金額	済フラグ

そして、取得した配列の何列目に指定したい項目名が存在するか確認します。

カラム位置を取得

  var colName = "済フラグ";
  var colIndex = header[0].indexOf(colName) + 1;

.indexOfメソッドを使い、配列の何列目に存在するか確認します。
変数 colName に "済フラグ" が入っているので、「4」が帰ってきます。
配列の開始値は「0」列番号の開始値は「1」からなので、 +1 して列番号にしています。

※colNameが "請求先" であれば「2」、存在しない項目名の場合「0」になります。

あとは、取得したカラム位置を使って書き込みするだけ。

取得したカラム位置を使って書き込み

  //  書き込み
  sheet.getRange(4,colIndex).setValue("済");

おわり

ひと手間加えて、メンテナンスしやすいコードにしましょう。