sheetDB.gs

class SheetDB {

  // Init sheet
  constructor(sheetName = null) {
    this.ss = SpreadsheetApp.getActiveSpreadsheet();
    this.sheet = this.ss.getActiveSheet();

    // change to sheet name
    if (sheetName) {
      this.sheet = this.ss.getSheetByName(sheetName);
      if (!this.sheet) {
        throw new TypeError(`Sheet [${sheetName}] is not exist`);
      }
    }

    this.lastRow = this.sheet.getLastRow();
    this.lastColumn = this.sheet.getLastColumn();

    this.mapHeader();

  }

  // init header of table
  mapHeader() {
    const headers = this.sheet.getRange(1, 1, 1, this.lastColumn).getValues();
    if (headers && Array.isArray(headers)) {
      this.header = headers[0];
    }
  }

  // map row to object
  mapObject(row) {
    const item = {};
    this.header.forEach((h, hIndex) => { item[h] = row[0][hIndex] });
    return item;
  }

  // find text in current sheet
  findText(txtFilter) {

    const listItem = [];
    const filter = this.sheet.createTextFinder(txtFilter).findAll();

    filter.forEach(cell => {
      const row = this.sheet.getRange(cell.getRow(), 1, 1, this.lastColumn).getValues();
      if (row && Array.isArray(row)) {
        const item = this.mapObject(row);
        item._pos = {
          row: cell.getRow(),
          col: cell.getColumn(),
          value: cell.getValue()
        };
        listItem.push(item);
      }
    });

    return listItem;
  }

  // find all row have field is value.
  findByField(field, value) {
    return this.findText(value).find(item => item[field] == value);
  }

  findByObject(cond) {
    return this.findText(value).find(item => {
      Object.entries(cond).map(([k, v]) => item[k] == v);
    });
  }

  // add
  addRow(item) {
    const arr = Object.values(this.header).map(k => {
      if (item[k]) return item[k];
      else return '';
    });
    this.sheet.appendRow(arr);
  }

  // update
  updateRow(item) {
    const range = this.sheet.getRange(item._pos.row, 1, 1, this.lastColumn);
    delete item._pos;
    const arr = Object.values(this.header).map(k => {
      if (item[k]) return item[k];
      else return '';
    });
    range.setValues([arr]);
  }

  // delete
  deleteRow(item) {
    this.sheet.deleteRow(item._pos.row);
  }

}