# app.sheet.prototype

[GAS](https://lochiwei.gitbook.io/web/appendix/gas) ⟩ [app](https://lochiwei.gitbook.io/web/appendix/gas/app) ⟩ [prototypes](https://lochiwei.gitbook.io/web/appendix/gas/app/prototypes) ⟩ sheet.prototype

{% tabs %}
{% tab title="🔴 主題" %}

* [sheet.setvalues](https://lochiwei.gitbook.io/web/appendix/gas/app/prototypes/app.sheet.prototype/sheet.setvalues "mention")
* [sheet.delete](https://lochiwei.gitbook.io/web/appendix/gas/app/prototypes/app.sheet.prototype/sheet.delete "mention")
* getting [range](https://lochiwei.gitbook.io/web/appendix/gas/classes/range "mention")
  * sheet.range()
  * [sheet.rangebyrect](https://lochiwei.gitbook.io/web/appendix/gas/app/prototypes/app.sheet.prototype/sheet.rangebyrect "mention") - 用 [rangerect](https://lochiwei.gitbook.io/web/appendix/gas/custom-objects/rangerect "mention") 來選 [range](https://lochiwei.gitbook.io/web/appendix/gas/classes/range "mention")
  * sheet.rangeListByRects()
* formatting [range](https://lochiwei.gitbook.io/web/appendix/gas/classes/range "mention")
  * [sheet.appendconditionalformatrule](https://lochiwei.gitbook.io/web/appendix/gas/app/prototypes/app.sheet.prototype/sheet.appendconditionalformatrule "mention")
    {% endtab %}

{% tab title="💾 程式" %}

```javascript
/**
 * 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);
  },

};
```

{% endtab %}

{% tab title="📘 手冊" %}

* [Range](https://developers.google.com/apps-script/reference/spreadsheet/range) ⟩
  * [getA1Notation()](https://developers.google.com/apps-script/reference/spreadsheet/range#geta1notation) - 回傳類似 <mark style="color:yellow;">`"A1:E2"`</mark> 的字串。
* [RangeList](https://developers.google.com/apps-script/reference/spreadsheet/range-list#setBackground\(String\)) - collection of one or more [`Range`](https://developers.google.com/apps-script/reference/spreadsheet/range) instances <mark style="color:yellow;">**in the same sheet**</mark>.
* [sheet](https://lochiwei.gitbook.io/web/appendix/gas/classes/sheet "mention") ⟩&#x20;
  * [getRangeList(a1Notations)](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangelista1notations)
  * [clear()](https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#clear)
  * [clear({ formatOnly: true, contentsOnly: true })](https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#clearoptions)
    {% endtab %}

{% tab title="👥 相關" %}

* [custom-prototypes](https://lochiwei.gitbook.io/web/appendix/gas/tips/custom-prototypes "mention") for [sheet](https://lochiwei.gitbook.io/web/appendix/gas/classes/sheet "mention")
* [conditionalformatrule](https://lochiwei.gitbook.io/web/appendix/gas/classes/conditionalformatrule "mention") - sheet can append conditional format rules.
* [range](https://lochiwei.gitbook.io/web/appendix/gas/classes/range "mention")
  {% endtab %}

{% tab title="⬆️ 需要" %}

* [arr.ismatrix](https://lochiwei.gitbook.io/web/js/val/builtin/arr/ext/arr.ismatrix "mention")
  {% endtab %}

{% tab title="⬇️ 應用" %}

* [class-average](https://lochiwei.gitbook.io/web/appendix/gas/projects/class-average "mention") - 運用 <mark style="color:blue;">`sheet.setValues()`</mark>填各班平均、前三名表格。
* [no-make-up-exam](https://lochiwei.gitbook.io/web/appendix/gas/projects/no-make-up-exam "mention")
* [app.sheetbyname](https://lochiwei.gitbook.io/web/appendix/gas/app/member/app.sheetbyname "mention") - 讓回傳的 Sheet 直接擁有外掛。
  {% endtab %}
  {% endtabs %}