とある記事と出会う

上記の記事を参考に作ってみるか (ほぼコピペ)
となった。。。。

使い方

const sampleData = {
  col1 : "value1",
  col2 : "value2",
  col3 : "value3",
};

// 対象のスプレットシートを対象にDBライクに操作するインスタンス
const db = newSpreadsheetDB("スプレットシートのID");

// テーブルの作成
db.createTable("シート名", ["col1", "col2", "col3"]);

// データの新規登録
db.select("シート名").insert(sampleData);

// データの取得 
db.select("シート名").read("col1=value1");

// データの削除
db.select("シート名").delete("col1='value1' AND col2='value2'");

// データの更新
db.select("シート名").update(sampleData, "col3='value3'");

問題点 

• 必ずidというカラムが勝手に作れる

• idという列はidと言っておきながら何行目を示すカラムになっている

• プライマリーキーや重複不可などのオプションは存在していない。。。

• まぁ、いっぱい

ソースコード

Class SpreadsheetDB

ここでは外部から使う際にQueryクラスを制限しています。。。

/**
 * @param {string} sheetId
 * @return {SpreadsheetDBRepository}
 */
function newSpreadsheetDB(sheetId) {
  return new SpreadsheetDB(sheetId);
}

class SpreadsheetDB {

  /**
   * @param {string} spreadsheetId
   */
  constructor(spreadsheetId) {
    /** @type {string} */
    this.spreadsheetId = spreadsheetId;
  }

  /**
   * @param  {string} tableName
   * @return {Query}
   */
  select(tableName) {
    return Query.select(this.spreadsheetId, tableName);
  }

  /**
   * @param {string} tableName
   * @param {Array<string>} cols
   */
  createTable(tableName, cols) {
    Query.createTable(this.spreadsheetId, tableName, cols);
  }
}

Class Query

ここでは、シートに対しての操作を提供しています。

class Query {
  /**
   * @param {string} spreadsheetId
   * @param {string} tableName
   */
  constructor(spreadsheetId, tableName) { 
    /** @type {Table} */
    this.table = new Table(spreadsheetId, tableName);
  }
 
  /**
   * @param  {SpreadsheetApp.Spreadsheet} sheets
   * @param  {string} tableName
   * @return {Query}
   */
  static select(spreadsheetId, tableName) {
    return new Query(spreadsheetId, tableName);
  }

  /**
   * @param {SpreadsheetApp.Spreadsheet[]} sheets
   * @param {string} tableName
   * @param {Array<string>} cols
   */
  static createTable(spreadsheetId, tableName, cols) {
    const tmp = ["id"]
    tmp.push(...cols);
    Table.createTable(spreadsheetId, tableName);
    const query = new Query(spreadsheetId, tableName);
    query.table.setColNames(tmp);
  }
 
  /**
   * @param {object} value
   */
  insert(value) {
    const colNames = this.table.getColNames();
    const recod = colNames.map(colName => value[colName]);
    this.table.insertRow(recod);
  }

  /**
   * @param  {string} where
   * @return {Object.<string, any>[][]}
   */
  read(where) {
    return this.table.query(where);
  }

  /**
   * @param {string} where
   */
  delete(where) {
    const records = this.read(where);
    records.forEach(record => {
      const id = record[0];
      this.table.deleteById(id);
    });
  }

  /**
   * @param {Object.<string, any>} data
   * @param {string} where
   */
  update(data, where) {
    const records = this.read(where);
    const colNames = this.table.getColNames();
    records.forEach((record) => {
      const id = record[0];
      let row = colNames.map(colName => data[colName]);
      row = record.map((record, idx) => row[idx] !== undefined ? row[idx] : record);
      this.table.overwriteById(id, row);
    });
  }
}

Class Table

ここでは、スプレットシートの仕様に関することを吸収しています。

class Table {
  /**
   * @param {SpreadsheetApp.Spreadsheet[]} sheets
   * @param {string} tableName
   */
  constructor(spreadsheetId, tableName) {
    /** @type {string} */
    this.name = tableName;

    /** @type {SpreadsheetApp.Spreadsheet[]} */
    this.sheets = SpreadsheetApp.openById(spreadsheetId);

    /** @type {SpreadsheetApp.Spreadsheet} */
    this.sheet = this.sheets.getSheetByName(tableName);

    /** @type {string} */
    this.querySheet = this.sheets.getSheetByName(QUERY_NAME);
   
    if (!this.sheet) throw new SheetNotFoundError(tableName);
    if (!this.querySheet) {
      const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      this.querySheet = activeSpreadsheet.insertSheet();
      this.querySheet.setName(QUERY_NAME);
      if (!this.querySheet) throw new QueryNotFoundError();
    }
  }

  /**
   * @param  {string} name
   * @return {SpreadsheetApp.Spreadsheet}
   */
  static createTable(spreadsheetId, name) {
    if (name === QUERY_NAME) throw new Error("Cannot create table name " + QUERY_NAME);
    const activeSpreadsheet =  SpreadsheetApp.openById(spreadsheetId);
    try {
      activeSpreadsheet.insertSheet(name);
    } catch (e) {
      Logger.log(e);
    }
  }

  /**
   * @param {Array<string>} colNames
   */
  setColNames(colNames) {
    colNames.forEach((colName, idx) => this.setValue(1, idx+1, colName));
  }

  /**
   * @param {number} row
   * @param {number} col
   * @param {any}    value
   */
  setValue(row, col, value) {
    this.sheet.getRange(row, col).setValue(value);
  }

  /**
   * @return {number}
   */
  getQueryLastRowIdx() {
    const maxRow = this.querySheet.getMaxRows();
    if(this.querySheet.getRange(maxRow,1).getValue()!== "") this.querySheet.insertRowsAfter(maxRow, 100);
    return this.querySheet.getRange(this.querySheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }

  /**
   * @return {number}
   */
  getQueryLastColIdx() {
    return this.querySheet.getRange(1, this.querySheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
  }

  /**
   * @return {number}
   */
  getLastRowIdx() {
    const maxRow = this.sheet.getMaxRows();
    if(this.sheet.getRange(maxRow,1).getValue()!== "") this.sheet.insertRowsAfter(maxRow, 100);
    return this.sheet.getRange(this.sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }

  /**
   * @return {number}
   */
  getLastColIdx() {
    return this.sheet.getRange(1, this.sheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
  }

  /**
   * @param  {number} idx
   * @return {Array<string>}
   */
  getRows(idx) {
    return this.sheet.getRange(idx, 1, 1, this.getLastColIdx()).getValues()[0];
  }

  /**
   * @return {Array<string>}
   */
  getColNames() {
    return this.getRows(1);
  }

  /**
   * @param {number} id
   * @param {Array<any>} values
   */
  overwriteById(id, values) {
    values.forEach((value, idx) => this.setValue(id, idx+1, value));
  }

  /**
   * @param {Array<object>} values
   */
  insertRow(values) {
    const lastRow = this.getLastRowIdx();
    values.forEach((value, idx) => this.setValue(lastRow+1, idx+1, value));
    this.setValue(lastRow+1, 1, "=row()");
  }

  /**
   * @param  {string} name
   * @return {int}
   */
  cnvColIdx(name) {
    const colNames = this.getColNames();
    return colNames.findIndex(colName => colName === name) + 1;
  }

  /**
   * @param  {string} name
   * @return {string}
   */
  cnvColIdxName(name) {
    const idx = this.cnvColIdx(name);
    return String.fromCharCode(65+idx-1);
  }

  /**
   * @param {string} where
   */
  query(where) {
    if (!this.querySheet) throw new QueryNotFoundError();

    this.getColNames()
      .forEach((colName) => where = where.replace(new RegExp( `${colName}`, 'g'), `${this.cnvColIdxName(colName)}`));
    const range = this.name + "!A2:" + this.getLastRowIdx();
    this.querySheet.getRange("A1").setValue(`=QUERY(${range}, "WHERE ${where}")`);
    return this.querySheet.getRange(1, 1, this.getQueryLastRowIdx(), this.getQueryLastColIdx()).getValues();
  }

  /**
   * @param {number} id
   */
  deleteById(id) {
    this.sheet.deleteRow(id);
  }

  /**
   * @param {Array<number>} id
   */
  deleteByIds(ids) {
    this.sheet.deleteRows(ids);
  }
}

全体のソースコード

const QUERY_NAME = 'QUERY';
class QueryNotFoundError extends Error {
  constructor() {
    super('Not found "QUERY" sheet')
  }
}
class SheetNotFoundError extends Error {
  constructor(sheetName) {
    super(`Not found ${sheetName} sheet`)
  }
}

/**
 * @param {string} sheetId
 * @return {SpreadsheetDBRepository}
 */
function newSpreadsheetDB(sheetId) {
  return new SpreadsheetDB(sheetId);
}

class SpreadsheetDB {
  /**
   * @param {string} spreadsheetId
   */
  constructor(spreadsheetId) {
    /** @type {string} */
    this.spreadsheetId = spreadsheetId;
  }
  /**
   * @param  {string} tableName
   * @return {Query}
   */
  select(tableName) {
    return Query.select(this.spreadsheetId, tableName);
  }

  /**
   * @param {string} tableName
   * @param {Array<string>} cols
   */
  createTable(tableName, cols) {
    Query.createTable(this.spreadsheetId, tableName, cols);
  }
}

class Query {
  /**
   * @param {string} spreadsheetId
   * @param {string} tableName
   */
  constructor(spreadsheetId, tableName) { 
    /** @type {Table} */
    this.table = new Table(spreadsheetId, tableName);
  }
 
  /**
   * @param  {SpreadsheetApp.Spreadsheet} sheets
   * @param  {string} tableName
   * @return {Query}
   */
  static select(spreadsheetId, tableName) {
    return new Query(spreadsheetId, tableName);
  }

  /**
   * @param {SpreadsheetApp.Spreadsheet[]} sheets
   * @param {string} tableName
   * @param {Array<string>} cols
   */
  static createTable(spreadsheetId, tableName, cols) {
    const tmp = ["id"]
    tmp.push(...cols);
    Table.createTable(spreadsheetId, tableName);
    const query = new Query(spreadsheetId, tableName);
    query.table.setColNames(tmp);
  }
 
  /**
   * @param {object} value
   */
  insert(value) {
    const colNames = this.table.getColNames();
    const recod = colNames.map(colName => value[colName]);
    this.table.insertRow(recod);
  }

  /**
   * @param  {string} where
   * @return {Object.<string, any>[][]}
   */
  read(where) {
    return this.table.query(where);
  }

  /**
   * @param {string} where
   */
  delete(where) {
    const records = this.read(where);
    records.forEach(record => {
      const id = record[0];
      this.table.deleteById(id);
    });
  }

  /**
   * @param {Object.<string, any>} data
   * @param {string} where
   */
  update(data, where) {
    const records = this.read(where);
    const colNames = this.table.getColNames();
    records.forEach((record) => {
      const id = record[0];
      let row = colNames.map(colName => data[colName]);
      row = record.map((record, idx) => row[idx] !== undefined ? row[idx] : record);
      this.table.overwriteById(id, row);
    });
  }
}
class Table {
  /**
   * @param {SpreadsheetApp.Spreadsheet[]} sheets
   * @param {string} tableName
   */
  constructor(spreadsheetId, tableName) {
    /** @type {string} */
    this.name = tableName;
    /** @type {SpreadsheetApp.Spreadsheet[]} */
    this.sheets = SpreadsheetApp.openById(spreadsheetId);
    /** @type {SpreadsheetApp.Spreadsheet} */
    this.sheet = this.sheets.getSheetByName(tableName);
    /** @type {string} */
    this.querySheet = this.sheets.getSheetByName(QUERY_NAME);
   
    if (!this.sheet) throw new SheetNotFoundError(tableName);
    if (!this.querySheet) {
      const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      this.querySheet = activeSpreadsheet.insertSheet();
      this.querySheet.setName(QUERY_NAME);
      if (!this.querySheet) throw new QueryNotFoundError();
    }
  }

  /**
   * @param  {string} name
   * @return {SpreadsheetApp.Spreadsheet}
   */
  static createTable(spreadsheetId, name) {
    if (name === QUERY_NAME) throw new Error("Cannot create table name " + QUERY_NAME);
    const activeSpreadsheet =  SpreadsheetApp.openById(spreadsheetId);
    try {
      activeSpreadsheet.insertSheet(name);
    } catch (e) {
      Logger.log(e);
    }
  }

  /**
   * @param {Array<string>} colNames
   */
  setColNames(colNames) {
    colNames.forEach((colName, idx) => this.setValue(1, idx+1, colName));
  }

  /**
   * @param {number} row
   * @param {number} col
   * @param {any}    value
   */
  setValue(row, col, value) {
    this.sheet.getRange(row, col).setValue(value);
  }

  /**
   * @return {number}
   */
  getQueryLastRowIdx() {
    const maxRow = this.querySheet.getMaxRows();
    if(this.querySheet.getRange(maxRow,1).getValue()!== "") this.querySheet.insertRowsAfter(maxRow, 100);
    return this.querySheet.getRange(this.querySheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }

  /**
   * @return {number}
   */
  getQueryLastColIdx() {
    return this.querySheet.getRange(1, this.querySheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
  }

  /**
   * @return {number}
   */
  getLastRowIdx() {
    const maxRow = this.sheet.getMaxRows();
    if(this.sheet.getRange(maxRow,1).getValue()!== "") this.sheet.insertRowsAfter(maxRow, 100);
    return this.sheet.getRange(this.sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }

  /**
   * @return {number}
   */
  getLastColIdx() {
    return this.sheet.getRange(1, this.sheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
  }

  /**
   * @param  {number} idx
   * @return {Array<string>}
   */
  getRows(idx) {
    return this.sheet.getRange(idx, 1, 1, this.getLastColIdx()).getValues()[0];
  }

  /**
   * @return {Array<string>}
   */
  getColNames() {
    return this.getRows(1);
  }

  /**
   * @param {number} id
   * @param {Array<any>} values
   */
  overwriteById(id, values) {
    values.forEach((value, idx) => this.setValue(id, idx+1, value));
  }

  /**
   * @param {Array<object>} values
   */
  insertRow(values) {
    const lastRow = this.getLastRowIdx();
    values.forEach((value, idx) => this.setValue(lastRow+1, idx+1, value));
    this.setValue(lastRow+1, 1, "=row()");
  }

  /**
   * @param  {string} name
   * @return {int}
   */
  cnvColIdx(name) {
    const colNames = this.getColNames();
    return colNames.findIndex(colName => colName === name) + 1;
  }

  /**
   * @param  {string} name
   * @return {string}
   */
  cnvColIdxName(name) {
    const idx = this.cnvColIdx(name);
    return String.fromCharCode(65+idx-1);
  }

  /**
   * @param {string} where
   */
  query(where) {
    if (!this.querySheet) throw new QueryNotFoundError();

    this.getColNames()
      .forEach((colName) => where = where.replace(new RegExp( `${colName}`, 'g'), `${this.cnvColIdxName(colName)}`));
    const range = this.name + "!A2:" + this.getLastRowIdx();
    this.querySheet.getRange("A1").setValue(`=QUERY(${range}, "WHERE ${where}")`);
    return this.querySheet.getRange(1, 1, this.getQueryLastRowIdx(), this.getQueryLastColIdx()).getValues();
  }

  /**
   * @param {number} id
   */
  deleteById(id) {
    this.sheet.deleteRow(id);
  }

  /**
   * @param {Array<number>} id
   */
  deleteByIds(ids) {
    this.sheet.deleteRows(ids);
  }
}

まとめ✅

特に意味もない記事ですかね。。。。
はい、ごめんなさい🙇‍♂️