【GAS】特定の列で空白セルが見つかったらその行にデータを書き込む

GAS(GoogleAppsScript)

2023.04.102023.04.11

Google Apps Script（GAS）を使用して、Google Sheetsの特定の列で空白セルが見つかった場合に、その行にデータを書き込む方法について説明します。

以前、「シート内のチェックが入った行を別シートに転記したい」という話を書きましたが、その続きのような内容です。

チェックが入ったら別のシートに転記するスクリプト
発行IDを発番したい
前回までの復習
列内の空白のセルを探す
列内の最初の空白セルの行番号を返す
関数の返り値を取得して行番号にセットする
まとめ

チェックが入ったら別のシートに転記するスクリプト

以前、このような「チェックが入ったら別のシートに転記するスクリプト」を作りました。

チェックを入れた行が別のシートに転記されます。便利ですね。

発行IDを発番したい

今回の要件は、少し内容を変えて「予め発行されているID列を、チェックを入れたタイミングで次々に付与したい」という内容です。

実行：列のチェックが入る。

結果：別シートに、予め発行されたIDを付与して追加する。

このような感じの内容です。

前回までの復習

前回まではこのような感じの内容で作成しました。destValuesとsetValuesで別シートにデータを転記しています。

function copyData(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Sheet1'); // チェックボックスがあるシート名
  var sheet2 = ss.getSheetByName('Sheet2'); // コピー先のシート名
  var range = sheet1.getDataRange();
  var values = range.getValues();
  const cell = e.range;
  const column = cell.getColumn();
  const row = cell.getRow();
  const lastRow1 = sheet1.getLastRow();
  const lastCol1 = sheet1.getLastColumn();
  const lastRow2 = sheet2.getLastRow();
  const lastCol2 = sheet2.getLastColumn();
  var destValues = [];
  if (column === 1 && e.value === "TRUE") {
    destValues=sheet1.getRange(row, 1, 1, lastCol1).getValues();
    sheet2.getRange(lastRow2+1,1, 1, lastCol1).setValues(destValues);
  }
}

こちらを調整してみます。

列内の空白のセルを探す

まずは、今回の場合はB列の状態を見て末尾を判断したいので、getLastRow()がそのまま使用できません。getLastRow()を使ってそのまま書き込みをしまうと、自動的に末尾行を見つけてしまうので、最下部に追加されてしまいます。

末尾の行にデータが入ってしまいます。

そこで、別の列を見てみます。

この「B列を上から順番に確認していき一番最初の空白セルにデータを書き込む」といったプログラムを作ってみます。

列内の最初の空白セルの行番号を返す

こちらは以前作成した「列内の最初の空白セルの行番号を返す」関数です。こちらを使って、列内の最初の空白セルの行番号を取得します。

function findFirstEmptyCellInColumn() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet2");
  const lastRow = sheet.getLastRow();
  const colValue = sheet.getRange(1,3,lastRow,1).getValues();
  Logger.log("colValue:" + colValue);

for (let i = 0; i < colValue.length; i++) {
    if (colValue[i] == "") {
      return i+1;
    }
  }
}

情報	colValue:設置ID,1,2,14,12,3,,,,,,,,,,,,,,,,,,,,

配列の番号が0から始まるので、+1してreturnしています。

関数の返り値を取得して行番号にセットする

ここまでくれば、あとは関数の返り値を取得して行番号にセットすることで、空白セルの行から順番に値を書き込んでいくことができます。

function copyData(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Sheet1'); // チェックボックスがあるシート名
  var sheet2 = ss.getSheetByName('Sheet2'); // コピー先のシート名
  var range = sheet1.getDataRange();
  var values = range.getValues();
  const cell = e.range;
  const column = cell.getColumn();
  const row = cell.getRow();
  const lastRow1 = sheet1.getLastRow();
  const lastCol1 = sheet1.getLastColumn();
  const lastRow2 = sheet2.getLastRow();
  const lastCol2 = sheet2.getLastColumn();
  var destValues = [];
  if (column === 1 && e.value === "TRUE") {
    destValues = sheet1.getRange(row, 2, 1, lastCol1).getValues();
    sheet2.getRange(findFirstEmptyCellInColumn(), 2, 1, lastCol1).setValues(destValues);
  }
}

  function findFirstEmptyCellInColumn() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName("Sheet2");
    const lastRow = sheet.getLastRow();
    const colValue = sheet.getRange(1, 3, lastRow, 1).getValues();
    Logger.log("colValue:" + colValue);

    for (let i = 0; i < colValue.length; i++) {
      Logger.log("colValue[i]:" + colValue[i]);
      if (colValue[i] == "") {
        return i+1;
      }
    }
  }

チェックを入れます。