gasスプレッドシート検索！高速化のテクニックも【コピペ可】

gasスプレッドシート検索 スプレッドシートとの連携
gasスプレッドシート検索
2023.06.14
ルリ
ルリ

GASを使って、キーワードに一致する文字がスプレッドシート内にあるかを確認し、その行と列を返す式を作成する方法を解説します。

gasスプレッドシート検索方法　～キーワードを含むデータの行列番号を返す

gasスプレッドシート検索！高速化のテクニックも【コピペ可】

以下のスクリプトは、指定したキーワードがスプレッドシート内に存在する場合、その行と列を返す関数です。

function searchKeyword(keyword) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var result = [];

  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] === keyword) {
        var row = i + 1;
        var column = getColumnLetter(j + 1);
        result.push("行: " + row + "、列: " + column);
      }
    }
  }

  return result;
}

// 列番号をアルファベットの列名に変換する関数
function getColumnLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

このスクリプトを使用するには、以下の手順を実行します。

  1. スプレッドシートを開き、メニューバーの「ツール」から「スクリプトエディタ」を選択します。
  2. スクリプトエディタが開かれたら、上記のスクリプトをコピーし、エディタ内に貼り付けます。
  3. 保存ボタンをクリックしてスクリプトを保存します。
  4. スクリプトエディタを閉じて、スプレッドシートに戻ります。

関数の使用方法は以下の通りです。

  1. スプレッドシート内の任意のセルに=searchKeyword("検索キーワード")と入力します（検索キーワードは自分が検索したいキーワードに置き換えてください）。
  2. Enterキーを押すと、一致するキーワードのある行と列が結果として表示されます。

この方法で、指定したキーワードがスプレッドシート内に存在する場合、その行と列が返されます。

複数の一致箇所がある場合、複数の行と列が返されます。

注意

gasスプレッドシート検索！高速化のテクニックも【コピペ可】

スクリプト内のSpreadsheetApp.getActiveSpreadsheet().getActiveSheet()では、現在アクティブなシートを対象としています。

キコ
キコ

必要に応じて、対象のスプレッドシートやシートを変更してください。

gasスプレッドシート検索方法　～処理高速化

gasスプレッドシート検索！高速化のテクニックも【コピペ可】

スプレッドシートに保管されているデータが3万行を超える場合などでは上記のコードだと処理が重くなります。

対応策と対応例を記載します。

データの範囲を制限する

検索対象の範囲を特定の列やセルに絞り込むことで処理速度を向上させることができます。

必要なデータのみを対象にすることで、不要な処理を省略できます。

function searchKeywordInRange(keyword) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // 検索対象の範囲を指定します（例: A2:C100）
  var range = sheet.getRange("A2:C100");
  
  var data = range.getValues();
  var result = [];

  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] === keyword) {
        var row = range.getRow() + i;
        var column = getColumnLetter(range.getColumn() + j);
        result.push("行: " + row + "、列: " + column);
      }
    }
  }

  return result;
}

// 列番号をアルファベットの列名に変換する関数
function getColumnLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

このサンプルコードでは、searchKeywordInRange関数内で、getRangeメソッドを使用して検索対象の範囲を指定しています（例: A2:C100）。

必要に応じて、検索対象の範囲を変更してください。

指定した範囲のデータを取得し、検索処理を行います。

検索結果として一致する行と列を結果の配列に追加していきます。

最終的に、結果の配列が返されます。

データのインデックス化

データのインデックスを作成することで検索速度を向上させることができます。

例えば、キーワードごとにインデックスを作成し、そのインデックスを使用して高速に検索する方法があります。

var dataIndex = {}; // インデックスを格納するオブジェクト

function buildIndex() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      var keyword = data[i][j];
      if (keyword !== "") {
        if (!dataIndex[keyword]) {
          dataIndex[keyword] = [];
        }
        var row = i + 1;
        var column = getColumnLetter(j + 1);
        dataIndex[keyword].push("行: " + row + "、列: " + column);
      }
    }
  }
}

function searchKeywordFromIndex(keyword) {
  if (!dataIndex[keyword]) {
    return []; // インデックスが存在しない場合は空の配列を返す
  }
  
  return dataIndex[keyword];
}

// 列番号をアルファベットの列名に変換する関数
function getColumnLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

このサンプルコードでは、buildIndex関数を呼び出してデータのインデックスを作成します。

buildIndex関数では、スプレッドシートのデータを走査し、キーワードごとに対応する行と列の情報をインデックスに格納します。

searchKeywordFromIndex関数では、指定されたキーワードをインデックスから検索し、対応する行と列の情報を配列として返します。

もしインデックスが存在しない場合は、空の配列を返します。

インデックスを作成することで、キーワードごとのデータへのアクセスが高速化されます。

インデックスは事前に作成しておく必要がありますが、その後の検索処理はインデックスを使用して効率的に行えます。

注意

スプレッドシートのデータが変更された場合には、インデックスを再構築する必要があります。

ルリ
ルリ

適宜、インデックスの更新処理を追加してください。

バッチ処理を利用する

全データを一度に処理するのではなく、複数のバッチに分割して処理する方法が考えられます。

データを分割して処理することで、一度に処理するデータ量を減らし、処理速度を向上させることができます。

キャッシュを使用する

データの一部をキャッシュに保持し、検索処理の際にキャッシュからデータを取得することで、処理速度を向上させることができます。キャッシュを使用することで、スプレッドシートへのアクセス回数を減らすことができます。

キャッシュを使用してデータを一部保持し、検索処理の際にキャッシュからデータを取得するサンプルコードを以下に示します。

// グローバル変数としてキャッシュを定義する
var dataCache = null;

// データをキャッシュにロードする関数
function loadCache() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  dataCache = data;
}

// キャッシュからデータを検索する関数
function searchKeywordWithCache(keyword) {
  // キャッシュが存在しない場合、キャッシュをロードする
  if (dataCache === null) {
    loadCache();
  }

  var result = [];

  for (var i = 0; i < dataCache.length; i++) {
    for (var j = 0; j < dataCache[i].length; j++) {
      if (dataCache[i][j] === keyword) {
        var row = i + 1;
        var column = getColumnLetter(j + 1);
        result.push("行: " + row + "、列: " + column);
      }
    }
  }

  return result;
}

// 列番号をアルファベットの列名に変換する関数
function getColumnLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

このサンプルコードでは、まずグローバル変数dataCacheを定義し、データをキャッシュとして保持します。

loadCache関数を呼び出すことで、スプレッドシートのデータをキャッシュにロードします。

searchKeywordWithCache関数では、まずキャッシュが存在しない場合にはキャッシュをロードします。

その後、キャッシュからデータを検索します。

キャッシュがあるため、スプレッドシートへのアクセス回数を減らすことができます。

キャッシュの更新が必要な場合（データが変更された場合など）、loadCache関数を再度呼び出してキャッシュを更新する必要があります。

データベースの検討

スプレッドシートのデータ量が膨大な場合は、データベースを利用することも考慮してください。データベースは高速な検索やデータ管理を提供するため、大量のデータを処理する際に効果的です。

gasスプレッドシート検索方法　～まとめ

gasスプレッドシート検索！高速化のテクニックも【コピペ可】

GASのスプレッドシート検索は処理対象のデータ量によって対応方法を工夫する必要があります。

キコ
キコ

プログラムは設計が肝です！適切なものを選びましょう

コメント

タイトルとURLをコピーしました