【GAS】GASをDBライクにをオマージュ
とある記事と出会う
上記の記事を参考に作ってみるか (ほぼコピペ)
となった。。。。
使い方
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);
}
}
まとめ✅
特に意味もない記事ですかね。。。。
はい、ごめんなさい🙇♂️