Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@BruceWeyne(Taku H.W.)

GAS でスプレッドシートを Database ライクに操作する

Last updated at Posted at 2023-11-30

概要

こちらの記事では、GAS（Google Apps Script）で Google スプレッドシートを Database のように直感的に操作する方法をご紹介します。

簡単に言うと、ヘッダー（1行目の項目名）を定義しさすえれば、そのヘッダーの項目名をキーとしてデータを検索したり、追加、更新、削除ができるようになります。

メリット

スプレッドシートのヘッダー（1行目）を基点にデータ操作を行うので、行列の構成を変更しても影響を受けません。
例えば、列の順番を入れ替えてもデータ操作に影響はありませんし、列の前後に新しい項目名の列を追加しても影響はありません。

こちらのテンプレートを使用することで、getRange() などのセルレベルで指定していたデータ操作の煩わしさから解放されます。

もくじ

はじめに

GAS（Google Apps Script）を時折作成する中で、スプレドシートのデータを操作したい場合があります。
公式ドキュメントや記事を読みながら SpreadsheetApp クラスや getRange(), getValues() メソッドなどの活用が必要になりますが、使い方を忘れてしまい調べ直すところから取り組むのが常でした。
それがとても煩わしく、データ操作の仕方に惑わされることなく気軽にアプリケーション開発がしたいな、と思い始め、思い切ってベース機能を開発することにしました。

目的

開発の目的は GAS からスプレッドシートのデータ操作を行う際に、毎度ドキュメントを読み返す必要なく直感的に操作できる仕組みを開発することです。
ここでの「データ操作」とは、データの「取得・登録・更新・削除」を意味します。

こんな方々へ

GAS は法人アカウントでも無料アカウントでも使える Google の素晴らしいサービスです。
業務効率化や独自サービスのために既にアプリケーションを開発されていたり、これから勉強がてら触れてみようと思われている方もいると思います。
色んな目的があると思いますが、こちらの記事を見つけてくださった方々の手間を省く便利機能として役立ってもらえたら嬉しいです。

ファイル構成

以下が開発したテンプレートファイルの一覧です。

- Config.gs     // 操作するスプレッドシートの ID や他の初期設定の定数類を管理する
- Controller.gs // ここでアプリケーションのコードを自由に書きます（サンプル）
- Database.gs   // スプレドシート（Database）を実際に操作する
- Model.gs      // Database ファイルを読み込み（インスタンス化）します

重要なのは Config, Database, Model ファイルの 3 つで、Controller ファイルは飽くまでサンプルなので使用しなくても大丈夫です。

ソースコード

■ソースコードは こちら の GitHub に格納しています。

使い方

実装方法

GASの初期設定

実装は簡単で、上記のソースコードをそのままご自身の GAS 環境にコピペしてください。
ファイル名は全く一緒にしなくても大丈夫ですが、Javascript のクラスを活用しているので、クラス名とファイル名を合わせていただくと理解しやすいかと思います。

gas-implement-sample.png

スプレッドシートの用意

データ操作に使用したいスプレッドシートをご自身の Google ドライブに用意してください。
その URL に記載のスプレッドシート ID を Config ファイルの spreadsheetId 変数に設定します。

spreadsheet-sample.png

config-sample.png

スプレッドシートにヘッダーを設定する

スプレッドシートを Database ライクに操作するので、ヘッダー（1行目の項目名）は必須です。
また、シート名もデータ操作の要になります。

関連イメージとしては、

スプレッドシート Database
シート（名） Table（名）
シートの1行目 Header（項目名）
2行目以降 データ

という位置付けです。

sheet-sample.png

データ操作の例

これで初期設定は完了しました。
あとはご自身の好きなアプリケーションを作るまでです。
ここからは、データ操作の一例をお見せします。

Modelインスタンスの生成

まず、データ操作をするために Model クラスのインスタンスを生成します。

インスタンスの生成
const mdl = new Model();

これで Config ファイルに設定したスプレッドシートがデフォルトとして読み込まれます。
もし他のスプレッドシートのデータ操作を行いたい場合は、

他のスプレッドシートの読み込み
const spreadsheetId2 = "10LA-e_vJuFcN13HIwuRoJPZTehto17Z9lig-c424Ig0";
const mdl2 = new Model(spreadsheetId2);

と Model クラス引数にスプレッドシートの ID を設定することもできます。

スプレッドシートの ID は Config ファイルで管理していただくことを推奨します。

データの取得

それでは、実際にデータを読み込んでみます。
Controller ファイルにアプリケーションのコードを記述していきますが、飽くまでサンプルなので Controller ファイルではなく、ご自身のお好きなファイルに記述してください。

データの取得には getData() メソッドを使用します。

データの全取得
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();
    
    // データの全取得
    const data = mdl.getData("応募者");
    
    // ログ出力
    Logger.log(data);
    Logger.log("===============");
    Logger.log(data[0]["氏名"]);
    Logger.log(data[0]["年齢"]);
    Logger.log(data[0]["性別"]);
}

上記を実行すると以下のようになります。
データをオブジェクトの連想配列のリストで取得していることが分かると思います。

data-get-sample.png

データのAND検索

さらに、データ取得時に絞り込みをすることも可能です。

データの AND 検索取得
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();
    
    // 検索条件を設定
    const conditions = [
      { key: "氏名", value: "天野 結女" },
      { key: "年齢", value: 21 }
    ];
    
    // データの AND 取得
    const data = mdl.getData("応募者", conditions);
    
    // ログ出力
    Logger.log(data);
    Logger.log("===============");
    Logger.log(data[0]["氏名"]);
    Logger.log(data[0]["年齢"]);
    Logger.log(data[0]["性別"]);
}

get-sample-and.png

データのOR検索

もちろん、OR 条件でも絞り込みが可能です。
ただし、この場合は別のメソッド orGetData() を使用します。

データの OR 検索取得
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();
    
    // 検索条件を設定
    const conditions = [
      { key: "氏名", value: "天野 結女" },
      { key: "氏名", value: "結城 莉央" },
      { key: "性別", value: "非公開" }
    ];
    
    // データの OR 取得
    const data = mdl.orGetData("応募者", conditions);
    
    // ログ出力
    Logger.log(data);
    Logger.log("===============");
    Logger.log(data[2]["氏名"]);
    Logger.log(data[2]["年齢"]);
    Logger.log(data[2]["性別"]);
}

get-sample-or.png

offset,limitでのデータ取得

getData(), orGetData() の両方のメソッドで offset, limit を設定できます。

データの offset, limit 取得
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();
    
    // offset, limit の設定（ここでは固定値）
    const offset = 2;
    const limit = 2;
    
    // 全データから offset, limit による取得
    const data = mdl.getData("応募者", null, offset, limit);
    
    // ログ出力
    Logger.log(data);
    Logger.log("===============");
    Logger.log(data[0]["氏名"]);
    Logger.log(data[0]["年齢"]);
    Logger.log(data[0]["性別"]);
}

get-sample-offset-kimit.png

データの新規登録

スプレッドシートの最終行に指定したデータの値を追加することができます。
insertData() メソッドを使用します。

データの新規登録
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();
    
    // 新規登録するデータ（キーと値のセット）リストを設定
    const keyValuePairs = [
      { "氏名": "村田 一生　", "年齢": "19", "性別": "男性" },
      { "氏名": "Ryan Hyeisang　", "年齢": "22", "性別": "女性" },
      { "氏名": "浜塚 悠　", "年齢": "14" } // 未設定項目は 空 で設定されます
    ]
    
    // データの新規登録
    const result = mdl.insertData("応募者", keyValuePairs);
    
    // ログ出力
    Logger.log(result); // 実行結果（Boolean）が返されます
}

result-insert-sample.png

insert-spreadsheet-sample.png

データの更新

スプレッドシートに登録されているデータを検索し、対象データを更新することもできます。
データの検索は AND 条件で絞り込まれます。

updateData() を使用します。

update-sample-before.png

データの更新
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();
    
    // 更新するデータ（キーと値のセット）を設定 : 必須
    const keyValuePair = { "性別": "女性" }; // 注意： オブジェクト、配列ではない

    // 更新するデータの絞り込み条件を設定 : 必須
    const conditions = [
      { key: "氏名", value: "浜塚 悠" },
      { key: "年齢", value: 14 }
    ];
    
    // データの更新
    const result = mdl.updateData("応募者", keyValuePair, conditions);
    
    // ログ出力
    Logger.log(result); // 実行結果（Boolean）が返されます
}

update-data-sample-result.png

update-sample-after.png

OR検索によるデータ更新

orUpdateData() メソッドを用いると、絞り込み条件を OR としてデータを抽出し更新することもできます。

orUpdate-data-before.png

OR 検索によるデータ更新
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();
    
    // 更新するデータ（キーと値のセット）を設定 : 必須
    const keyValuePair = { "年齢": "18" }; // 注意： オブジェクト、配列ではない

    // 更新するデータの絞り込み条件を設定 : 必須
    const conditions = [
      { key: "氏名", value: "天野 結女" },
      { key: "氏名", value: "結城 莉央" }
    ];
    
    // データの更新
    const result = mdl.orUpdateData("応募者", keyValuePair, conditions);
    
    // ログ出力
    Logger.log(result); // 実行結果（Boolean）が返されます
}

orUpdate-data-after.png

データの削除

スプレッドシートに登録されているデータを検索し、対象データを削除することもできます。
データの検索は AND 条件で絞り込まれます。

deleteData() メソッドを使用します。

delete-sample-before.png

データの削除
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();

    // 更新するデータの絞り込み条件を設定 : 必須
    const conditions = [
      { key: "氏名", value: "浜塚 悠" },
      { key: "年齢", value: 14 }
    ];
    
    // データの削除
    const result = mdl.deleteData("応募者", conditions);
    
    // ログ出力
    Logger.log(result); // 実行結果（Boolean）が返されます
}

delete-sample-after.png

OR検索によるデータの削除

orDeleteData() メソッドを使用します。

orDelete-sample-before.png

OR 検索によるデータの削除
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();

    // 更新するデータの絞り込み条件を設定 : 必須
    const conditions = [
      { key: "年齢", value: 18 },
      { key: "年齢", value: 22 }
    ];
    
    // データの削除
    const result = mdl.orDeleteData("応募者", conditions);
    
    // ログ出力
    Logger.log(result); // 実行結果（Boolean）が返されます
}

orDelete-sample-after.png

データの全削除

ヘッダー以外の全てのデータ行を削除します。
truncateData() メソッドを使用します。

データの全削除（ヘッダー以外）
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();

    // データの全削除（ヘッダーを除く）
    const result = mdl.truncateData("応募者");
    
    // ログ出力
    Logger.log(result); // 実行結果（Boolean）が返されます
}

truncate-sample.png

比較演算子によるデータの検索

最後に、少し応用編となりますが、データの絞り込みを比較演算子で行うこともできます。
方法は条件を設定するオブジェクト配列の key に比較演算子を含めて設定するものとなります。

比較演算子は、>, >=, <, <=, !=, !==, == を含めることができます。

比較演算子を指定しない通常の絞り込みは === がデフォルトとなっています。

この比較演算子によるデータの絞り込みは、前述したデータの更新・削除においても適用できます。

比較演算子によるデータ取得
function myFunction() {
    // インスタンスの生成
    const mdl = new Model();
    
    // 検索条件を設定
    const conditions = [
      { key: "年齢 >", value: 30 }, // 比較演算子の前に半角スペースを入れる
      { key: "年齢 <=", value: 18 } // 比較演算子の前に半角スペースを入れる
    ];
    
    // データの OR 取得
    const data = mdl.orGetData("応募者", conditions);
    
    // ログ出力
    Logger.log(data);
    Logger.log("===============");
    Logger.log(data[0]["氏名"]);
    Logger.log(data[0]["年齢"]);
    Logger.log(data[0]["性別"]);
}

get-operator-sample.png

おわりに

自分でも使用する中で機能の修正や追加をしてブラッシュアップしていきたいと思います。
最後までお読みいただきありがとうございました。

2
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?