🚧SheetMethods

GAS ⟩ app ⟩ sheet

利用 mixin 的方式，讓個別的 Sheet 物件擁有方便的 methods：

 Object.assign(sheet, SheetMethods);       // ⭐ extend `Sheet`

💾 程式

/**
 * common methods for a sheet
 * - deleteExtraColumns()
 * - deleteExtraRows()
 * - writeObjects(obj, {row=1, col=1, clearSheet=false})
 * @mixin
 */
const SheetMethods = {

  /**
   * 🔸 delete extra columns in a sheet
   * @example
   * sheet.deleteExtraColumns()
   */
  deleteExtraColumns(){
    const j = this.getMaxColumns(); 
    const i = this.getLastColumn();
    if (j > i) this.deleteColumns(i+1, j-i);
  },

  /**
   * 🔸 delete extra rows in a sheet
   * @example
   * sheet.deleteExtraRows()
   */
  deleteExtraRows(){
    var j = this.getMaxRows(); 
    var i = this.getLastRow();
    if (j > i) this.deleteRows(i+1, j-i);
  },

  /**
   * 🔸 write objects' data into sheet
   * @param {[object]} objs - array of objects (all with same properties)
   * @param {number} row - first row number of the range where data is written into.
   * @param {number} col - first column number of the range
   * @param {boolean} clearSheet - decide whether or not to clear entire sheet first.
   */
  writeObjects(objs, {
    row   = 1, 
    col   = 1, 
    clearSheet = false
  }={}) {

    // get keys from first object
    let keys = Object.keys(objs[0]);

    // dimensions of the data range.
    var rows = objs.length;
    var cols = keys.length;
    
    // +1 row for the header row.
    let range = this.getRange(row, col, rows + 1, cols);

    // objects -> 2D array
    const values = [];
    values.push(keys);        // 1st row: header

    objs.forEach( obj => {
      const row = keys.map(key => obj[key]);   // obj --> [v1, v2, ...]
      values.push(row);
    }); 
    
    // clear sheet if necessary
    if (clearSheet) this.clear();

    // write 2D values into range
    range.setValues(values);
  },

};

🔴 主題

• sheet.values()