🔳app.sheet.prototype

GAS ⟩ app ⟩ prototypes ⟩ sheet.prototype

🔴 主題

• 💾 sheet.setValues()

• 💾 sheet.delete()

• getting Range

  • sheet.range()

  • 💾 sheet.rangeByRect() - 用 RangeRect 來選 Range

  • sheet.rangeListByRects()

• formatting Range

  • sheet.appendConditionalFormatRule()

💾 程式

/**
 * write 2D data into sheet.
 * 
 * @example
 *   sheet.__proto__ = app.sheet.prototype
 *   sheet.setValues(data)
 */
app.sheet.prototype = {

  // 🔸 sheet.setValues(data)
  setValues(data, row=1, col=1){

    // ⬆️ require: Array.prototype.isMatrix (getter)
    if(!data.isMatrix) 
      throw new Error(`⛔️ app.sheet.prototype.setValues(): data 資料格式有問題，無法寫入試算表❗️`);

    // 資料表的「列數」與「欄數」
    let rows = data.length;
    let cols = data[0].length;

    // 寫入頁面
    this
      .getRange(row, col, rows, cols)
      .setValues(data);

    return this;   // for chaining
  },

  // ┌────────────────┐
  // │ getting Range  │
  // └────────────────┘

  // 🔸 sheet.range()
  range(row, col, rows, cols){
    const rng = this.getRange(row, col, rows, cols);
    Object.setPrototypeOf(rng, app.range.prototype);           // extend range
    return rng;
  },

  // 🔸 sheet.rangeByRect(rect: RangeRect)
  // require: RangeRect
  rangeByRect(rect){
    const range = this.getRange(...rect.toArray());
    range.__proto__ = app.range.prototype;           // extend range
    return range;
  },

  // 🔸 sheet.rangeListByRects(rects: [RangeRect])
  // require: RangeRect
  rangeListByRects(rects){
    const alNotations = rects.map(rect => this.rangeByRect(rect).getA1Notation());
    return this.getRangeList(alNotations);
  },

  // ┌────────────────────┐
  // │ formatting Ranges  │
  // └────────────────────┘

  // 🔸 sheet.appendConditionalFormatRule()
  appendConditionalFormatRule(rule){
    var rules = this.getConditionalFormatRules();
    rules.push(rule);
    this.setConditionalFormatRules(rules);
  },

};

📘 手冊

• Range ⟩

  • getA1Notation() - 回傳類似 "A1:E2" 的字串。

• RangeList - collection of one or more Range instances in the same sheet.

• Sheet ⟩

  • getRangeList(a1Notations)

  • clear()

  • clear({ formatOnly: true, contentsOnly: true })

👥 相關

• 💡 custom prototypes for Sheet

• ConditionalFormatRule - sheet can append conditional format rules.

• Range

⬆️ 需要

• arr.isMatrix

⬇️ 應用

• 各班平均及前三名 - 運用 sheet.setValues()填各班平均、前三名表格。

• 不能補考名單

• app.sheetByName() - 讓回傳的 Sheet 直接擁有外掛。