【GAS】スプレッドシートをデータベースとして扱う

GAS（Google Apps Script）の案件を手掛ける上でよく使い回す処理をファンクション化してみました。
コピーしてそのまま使うこともできますし、一部カスタマイズして使ってください。

「複数のサイトを横断的に見ないと答えにたどり着かない。。。」
そんな思いにこたえることができれば幸いです！

さて、今回は「スプレッドシートをデータベースとして扱う」処理を紹介します。

はじめに

今回紹介する処理はselect文のようなqueryを用いた方法ではありませんので、あらかじめご了承くださいね！
ただ、ヘッダー数が変わっても、ヘッダー順が変わってもプログラムを変更する必要がない神業的な方法を紹介します！！
（ヘッダーの名前が変わるとプログラムの変更が必要なのはqueryを用いた方法と同様です！）

用途に応じて２つのfunctionとヘッダー情報を生成するfunctionの合わせて3つのfunctionで作成します。
util.gasなどのファイルを作成してそのままコピペしてもらえれば使えるようになりますよ！！

基本的にはグローバル配列を用いてヘッダー情報を保持しているので、それほど遅くなりませんが、
インデックスを直接指定する方法に比べると処理が多少遅くなってしまいます。

注意事項

シート名が必ず一意になるようにシート名の設計を行う必要があります。
ただ、同一シート名で絶対にヘッダーが同じという場合は、複数スプレッドシート内に同一シート名があっても大丈夫です！

また、今回のプログラムはヘッダーが1行目にあることを想定していますので、複数行存在する場合はプログラムを変更する必要があります。
（ヘッダー情報生成を修正してください）

定義

// ヘッダー項目
let colItems = {};

グローバル変数として上記を記載しておいてください。

すべてのヘッダー情報はこの変数内で保持されますので、とても重要は変数です。
ヘッダー情報は連想配列として保持されます。
イメージとしてはこんな感じです。

{
  シート名: [ヘッダー項目の配列]
}

実際に例を挙げるとこんな感じになります。

{
    '商品リスト': ['商品番号', '商品名', '値段'],
    '顧客リスト': ['顧客コード', '顧客名', '電話番号', '住所'],
}

任意のスプレッドシートのシートをPDF化する

ヘッダー情報生成

処理概要

ヘッダーの項目（配列）を返す
（シート単位で初回のみシートを読み取るが、2回目以降は変数に保持している情報を利用する）

【引数】
sheet : ヘッダーを取得する対象のシートオブジェクト
【返り値】
対象となるシートオブジェクトのヘッダー項目の配列

ファンクション

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// ヘッダー項目を取得
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function getColItems(sheet) {
  let sheetName = sheet.getSheetName();
  if (!colItems[sheetName] || colItems[sheetName].length == 0) {
    colItems[sheetName] = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  }
  return colItems[sheetName];
}

内部処理の説明

ヘッダー情報生成とありますが、実際にはcolItems内のkey項目にシート名が存在したら、シート名に対応するvalueを返します。
colItems内のkye項目にシート名が存在しない場合は、シート名に対するvalueを作成してそのvlueを返しています。

もしヘッダーが1行目にない場合は、以下のコードの最初の行指定個所を変更してください。

colItems[sheetName] = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

ヘッダー項目からインデックスを取得（GASに取り込んだデータ上のインデックス）

処理概要

GASに取り込んだデータ上の任意の項目のインデックスを取得する

【引数】
sheet : ヘッダーを取得する対象のシートオブジェクト
colName : インデックスを取得したい任意の項目名
【返り値】
インデックス

ファンクション

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// ヘッダー項目からインデックスを取得(ヘッダー項目配列参照時)
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function getColIndex(sheet, colName) {
  return getColItems(sheet).indexOf(colName);
}

内部処理の説明

単純に getColItemsを呼び出し、取得したシート名のヘッダー項目の配列から任意の項目名がどこにあるかを返しています。
GASに取り込まれたデータのインデックスは0始まりなので、補正せずにそのまま返します。

ヘッダー項目からインデックスを取得（スプレッドシート上のインデックス）

処理概要

スプレッドシート上の任意の項目のインデックスを取得する

【引数】
sheet : ヘッダーを取得する対象のシートオブジェクト
colName : インデックスを取得したい任意の項目名
【返り値】
インデックス

ファンクション

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// ヘッダー項目からインデックスを取得(スプレッドシート参照時)
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function getColIndexSS(sheet, colName) {
  return getColIndex(sheet, colName) + 1;
}

内部処理の説明

単純に getColIndexを呼び出し、取得したシート名のヘッダー項目の配列から任意の項目名がどこにあるかを返しています。
スプレッドシート上ではインデックスは列番号、つまり1始まりなので、getColIndexで得られたインデックスに1を加算して返します。

呼び出し方法

先の例を参考に呼び出し方法を以下に記載しておきます。

今回は商品番号が「A00002」の商品を取り出す方法です。

function callTest() {
  let itemListSheet = 商品リストのシートオブジェクト
  let itemList = itemListSheet.getRange(2, 1, itemListSheet.getLastRow() - 1, itemListSheet.getLastColumn());
  let item = itemList.filter(function(rowData) { return rowData[getColIndex(itemListSheet, '商品番号')] == 'A00002' });
  let price = 0;
  if () {
    price = item[0][getColIndex(itemListSheet, '値段')];
  } else {
    Logger.log('対象の商品は見つかりませんでした');
  }
}

まとめ

今回は「スプレッドシートをデータベースとして扱う」処理について紹介しました。
スプレッドシートをデータベース代わりにした時って、意外とヘッダーの順番を変えられたり、増やしたり、減らしたりされることが多いので、
今回のような動的な対応ができるようになるとプログラム改修が最小限で済むことになるので、クライアントからも喜ばれるはずです！！

Google Apps Script は無料枠でも十分に使えとても便利です。
OSに依存せずに使えることから様々な環境で自動化・省力化してスローライフを楽しみましょう！

GASでの簡易システム制作に関するお問い合わせはこちらからお待ちしております。
LancersやMENTAでも活動していますので、そちらからお仕事を依頼して頂けます。