※※※注意※※※
この記事はChatGPTにベースを書いてもらっています。

はじめに

Google Apps Script（GAS）を使用して、Google スプレッドシートを効果的なデータベースとして使いこなす方法を紹介します。スプレッドシートは、柔軟性とアクセシビリティを兼ね備えたデータ管理ツールです。GASは、これを更にパワーアップさせます。

＊＊＊

GASの基本

GASは、Googleのクラウド上で動作するJavaScriptベースのスクリプト言語です。Googleドキュメント、スプレッドシート、カレンダーなど、Googleのサービスと連携して動作します。

＊＊＊

スプレッドシートのセットアップ

データベースとして使用するスプレッドシートを準備します。テーブルのようにデータを整理し、各列にはデータの種類（名前、日付、金額など）を明確にします。

＊＊＊

GASでのスクリプト作成

1. スプレッドシートを開き、「拡張機能」→「Apps Script」を選択。

2. 新しいスクリプトファイルが開きます。ここに、データ操作のためのスクリプトを記述します。

＊＊＊

データの読み込み

GASを使って、スプレッドシートからデータを読み込む方法を示します。

スプレッドシートの読み込みの基本

Google Apps Scriptでスプレッドシートのデータを操作する前に、まずはスプレッドシートを読み込む基本的な方法を理解しましょう。

function myFunction() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('シート1');
}

この関数は、アクティブなスプレッドシートとその中のシートをシート名を使って取得します。これを基にして、データを読み込んだり、操作したりすることができます。

スプレッドシートデータをJSON形式で読み込む

スプレッドシートのデータをより扱いやすい形式に変換するため、JSON形式でデータを読み込む方法を紹介します。この方法は、データをプログラムで扱いやすくし、他のシステムやAPIで利用する際に便利です。

まず、ヘッダー行（カラム名）を取得し、それとデータ行をキーと値のペアとしてJSONオブジェクトに変換する関数を作成します。

function getHeaders(sheet) {
  var range = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  return range.getValues()[0]; // ヘッダー行のみを返す
}

function convertToJSON(headers, data) {
  return data.map(function(row) {
    var obj = {};
    headers.forEach(function(header, index) {
      obj[header] = row[index];
    });
    return obj;
  });
}

このコードでは以下の処理を行っています：

1. getHeaders関数でスプレッドシートの最初の行からカラム名を取得します。

2. convertToJSON関数は、ヘッダーとデータ行をマッピングしてJSONオブジェクトに変換します。

この方法を使用することで、スプレッドシートのデータをプログラムで扱いやすい形式に変換し、さまざまな用途に活用できます。

すべて読み込む

スプレッドシートのすべてのデータを読み込むには、先ほどのgetSheet関数を使ってシートを取得し、以下のようにデータを読み込みます。

function readAllData(sheet) {
  // データが2行以上ある場合のみ範囲を取得
  if (sheet.getLastRow() > 1) {
    var headers = getHeaders(sheet);
    var range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
    var data = range.getValues();
    return convertToJSON(headers, data);
  } else {
    return []; // データがない場合は空の配列を返す
  }
}

IDを使って検索する

Google Apps Scriptを使ってスプレッドシートの特定のID（たとえば、A列にあるID）を指定して、関連するデータを取得する方法を紹介します。これは、大量のデータの中から特定のレコードを簡単に見つけたい場合に特に便利です。

function searchDataById(sheet, id) {
  var range = sheet.getRange(2, 1, sheet.getLastRow() - 1);
  var textFinder = range.createTextFinder(id).matchEntireCell(true);
  var foundRange = textFinder.findNext();

  if (foundRange) {
    var headers = getHeaders(sheet);
    var range = sheet.getRange(foundRange.getRow(), 1, 1, sheet.getLastColumn());
    var data = range.getValues();
    return convertToJSON(headers, data)[0];
  } else {
    return null; // 指定されたIDが見つからない場合
  }
}

このコードでは、次の手順でデータを取得します：

1. searchDataById関数は、createTextFinderを使って指定されたIDを検索します。matchEntireCell(true)は完全なセルの一致を確認します。

2. 検索された範囲（行）が見つかれば、その行のデータを取得し、convertToJson関数を使用してJSON形式に変換します。

3. JSONオブジェクトが返され、これによりデータを簡単に扱うことができます。

TextFinderを使用することで、特定のIDを持つデータを効率的に検索し、必要な情報を簡単に取り出すことができます。

キーワードを使って検索する

スプレッドシートから特定のキーワードに基づいて複数のデータを取得する方法を紹介します。これは、特定の条件を満たすデータを一度に取得したい場合に便利です。以下のコードは、TextFinderを使用してスプレッドシート内でキーワードを検索し、該当するすべての行のデータをJSON形式で取得します。

function searchDataByKeyword(sheet, column_no, keyword) {
  var range = sheet.getRange(2, column_no, sheet.getLastRow() - 1);
  var textFinder = range.createTextFinder(keyword).matchEntireCell(false);
  var foundRanges = textFinder.findAll();
  var headers = getHeaders(sheet);
  var result = [];

  foundRanges.forEach(function(foundRange) {
    var row = sheet.getRange(foundRange.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];
    result.push(convertToJSON(headers, row));
  });

  return result;
}

このコードでは、次の処理を行っています：

1. searchDataByKeyword関数は、createTextFinderを使って指定されたキーワードを検索します。ここではmatchEntireCell(false)を設定しているので、セル内の部分一致でも検索されます。

2. 検索されたすべての範囲（foundRanges）について、それぞれの行のデータを取得し、convertToJSON関数を使用してJSON形式に変換します。

3. 変換されたJSONオブジェクトが結果の配列に追加されます。

この方法を使用することで、スプレッドシート内のキーワードに基づいて複数のデータを効率的に取得することができます。

複数のキーワードを使って検索する

スプレッドシートから特定のキーワードに一致するデータを効率的に検索し、一致する頻度に基づいて結果を降順で取得する方法を紹介します。この方法は、特定の条件やキーワードが複数回出現するデータを優先的に抽出したい場合に特に有用です。

以下のコードは、TextFinderを使用してスプレッドシート内で複数のキーワードを検索し、各キーワードに一致する行の頻度（スコア）に基づいて結果をソートします。

function searchDataByMultipleKeywords(sheet, column_no, keywords) {
  var range = sheet.getRange(2, column_no, sheet.getLastRow() - 1);
  var headers = getHeaders(sheet);
  var rowMatches = new Map(); // 行のインデックスとスコアを記録するMap

  keywords.forEach(function(keyword) {
    var textFinder = range.createTextFinder(keyword).matchEntireCell(false);
    var foundRanges = textFinder.findAll();

    foundRanges.forEach(function(foundRange) {
      var rowIndex = foundRange.getRow();

      if (rowMatches.has(rowIndex)) {
        rowMatches.set(rowIndex, rowMatches.get(rowIndex) + 1);
      } else {
        rowMatches.set(rowIndex, 1);
      }
    });
  });

  var scoredData = Array.from(rowMatches, ([rowIndex, score]) => ({ score, rowIndex: rowIndex }));

  // スコアに基づいてソート
  scoredData.sort(function(a, b) {
    return b.score - a.score;
  });

  // JSON形式に変換
  return scoredData.map(function(item) {
    var data = sheet.getRange(item.rowIndex, 1, 1, sheet.getLastColumn()).getValues();
    return convertToJSON(headers, data)[0];
  });
}

このコードでは、与えられたキーワードに対してTextFinderを使用してスプレッドシート内を検索し、各行がキーワードにどれだけ一致するかに基づいてスコアを計算します。その後、スコアに基づいてデータを降順にソートし、より多くのキーワードに一致する行を優先して表示します。

この方法により、スプレッドシート内の関連性の高いデータを効率的に抽出し、その重要度に基づいて結果を整理することができます。

＊＊＊

まとめ

Google Apps Scriptを使って、スプレッドシートをデータベースのように扱う方法を学びました。データの読み込み、検索が簡単にできるようになります。さあ、あなたも試してみましょう！

＊＊＊

あとがき

今回はスプレッドシートをデータベースのように使う方法を書きました。
本当は以前書いたFAQのAPIの続きを書くはずだったのですが、より手軽に実装するならスプレッドシートの方がいいと思ったので、路線を変更しました。
次回はこれをAPIとして利用できるようにしたいと思います。

ここまでお読みいただきありがとうございました。
ではまた次回！
さよなら。