【GAS】検索ワードの隣のセルの値をアクティブシートに転記する

検索ワードに一致する隣の値を転記

＊完全一致するセルのみ処理／一致する最初のセルの値のみ取得

function copyCells() {
  var ss = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = ss.getSheetByName("シート名");
  var data = sheet.getDataRange().getValues();
  var targetWord = "検索ワード";
  
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[0].length; j++) {
      if (data[i][j] === targetWord) {
        var row = i + 1;
        var column = j + 1;
        var value = sheet.getRange(row, column + 1).getValue();
        SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1").setValue(value);
        return;
      }
    }
  }
}

解説

対象のスプレッドシートを SpreadsheetApp.openById() メソッドを使用して開き、getSheetByName() メソッドを使用して特定のシートを取得します。
次に、 getDataRange().getValues() メソッドを使用してシートのすべてのデータを取得し、targetWord で定義された検索ワードと一致するデータを探します。
var value = sheet.getRange(row, column + 1).getValue();
一致するデータが見つかった場合、その隣のセルの値を取得し、アクティブシートの A1 セルに設定します。

特定ワードが複数あった場合にすべて転記する方法

function copyCells() {
  var ss = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = ss.getSheetByName("シート名");
  var data = sheet.getDataRange().getValues();
  var targetWord = "検索ワード";
  var values = [];
  
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[0].length; j++) {
      if (data[i][j] === targetWord) {
        var row = i + 1;
        var column = j + 1;
        var value = sheet.getRange(row, column + 1).getValue();
        values.push(value);
      }
    }
  }
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1").setValue(values.join(","));
}

解説

values 配列を定義して、一致するセルの値をすべてこの配列に追加しています。
最後に、 setValue() メソッドを使用して、配列を文字列に変換してアクティブシートの A1 セルに設定

＊join() メソッドは、配列の各要素を指定された区切り文字で区切って一つの文字列に変換するために使用されます。上記の例では、 , 区切りで文字列に変換されます。

転記する際に上から順に転記するセルをわけて値を転記する

function copyCells() {
  var ss = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = ss.getSheetByName("シート名");
  var data = sheet.getDataRange().getValues();
  var targetWord = "検索ワード";
  var values = [];
  
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[0].length; j++) {
      if (data[i][j] === targetWord) {
        var row = i + 1;
        var column = j + 1;
        var value = sheet.getRange(row, column + 1).getValue();
        values.push([value]);
      }
    }
  }
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, values.length, 1).setValues(values);
}

解説

values 配列を二次元配列として定義して、一致するセルの値をすべてこの配列に追加しています。
最後に、 setValues() メソッドを使用して、配列をアクティブシートの A1 セルから縦に連続するセルに設定しています。

＊setValues() メソッドは、引数として渡された二次元配列を指定された範囲に設定するために使用されます。上記の例では、 values 配列の各要素が縦に連続するように、 setValues() メソッドで範囲を指定しています。

部分一致でセルの値を取得する場合

function copyCells() {
  var ss = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = ss.getSheetByName("シート名");
  var data = sheet.getDataRange().getValues();
  var targetSubstring = "部分文字列";
  var values = [];

  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[0].length; j++) {
      if (data[i][j].toString().indexOf(targetSubstring) !== -1) {
        var row = i + 1;
        var column = j + 1;
        var value = sheet.getRange(row, column + 1).getValue();
        values.push([value]);
      }
    }
  }
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, values.length, 1).setValues(values);
}

解説

indexOf() メソッドを使用して、 targetSubstring 変数に格納された文字列がセルの値に含まれるかどうかを確認
indexOf() メソッドが -1 を返す場合、セルの値に部分文字列が含まれていないことを示します。
indexOf() メソッドは、文字列の中で検索する部分文字列が最初に現れるインデックスを返します。もし、部分文字列が含まれなければ、 -1 を返します。

＊特定の部分文字列の代わりに、部分一致で検索したい場合は、indexOf()メソッドをincludes()メソッドに変更してください。

転記先のシートの最後の次のセルから転記を行う

function copyCells() {
  var ss = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = ss.getSheetByName("シート名");
  var data = sheet.getDataRange().getValues();
  var targetSubstring = "部分文字列";
  var values = [];
  var lastRow = sheet.getLastRow();

  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[0].length; j++) {
      if (data[i][j].toString().indexOf(targetSubstring) !== -1) {
        var row = i + 1;
        var column = j + 1;
        var value = sheet.getRange(row, column + 1).getValue();
        values.push([value]);
      }
    }
  }
  
  var startRow = lastRow + 1;
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(startRow, 1, values.length, 1).setValues(values);
}

解説

getLastRow()メソッドを使って、転記先のシートの最後の行番号を取得し、その行番号の次の行から転記を行っています。
また、getRange(startRow, 1, values.length, 1)で、転記先のA列の範囲を取得して、その範囲にデータを転記しています。

空欄はつめて転記する

function copyCells() {
  var ss = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = ss.getSheetByName("シート名");
  var data = sheet.getDataRange().getValues();
  var targetSubstring = "部分文字列";
  var lastRow = sheet.getLastRow();
  
  // 空欄を詰めた最後の行を取得
  for (var i = lastRow; i > 0; i--) {
    if (sheet.getRange(i, 1).getValue() !== "") {
      lastRow = i + 1;
      break;
    }
  }
  
  var values = [];

  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[0].length; j++) {
      if (data[i][j].toString().indexOf(targetSubstring) !== -1) {
        var row = lastRow + values.length;
        var column = 1;
        var value = sheet.getRange(i + 1, column + 1).getValue();
        values.push([value]);
      }
    }
  }
  
  if (values.length > 0) {
    sheet.getRange(lastRow + 1, 1, values.length, 1).setValues(values);
  }
}