> For the complete documentation index, see [llms.txt](https://lochiwei.gitbook.io/web/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://lochiwei.gitbook.io/web/appendix/gas/app.md).

# app

[GAS](/web/appendix/gas.md) ⟩ app

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

* [SheetMethods](/web/appendix/gas/app/prototypes/app.sheet.prototype/sheetmethods.md) - mixin for Sheet objects.
  {% endtab %}

{% tab title="🔸 屬性" %}

* [app.dataRangeValuesFromSheet()](/web/appendix/gas/app/member/app.datarangevaluesfromsheet.md)
  {% endtab %}

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

```javascript
/**
 * app methods
 * ------------------
 * - spreadsheet
 * - sheets
 * - activeSheet
 * - sheetByName()
 * - deleteSheetByName()
 * - rangeByName()
 * - valueOfNamedRange()
 * - valuesOfNamedRange(): 2D array
 * - dataRangeValuesFromSheet(): 2D array
 * - ui
 * - alert()
 */
const app = {

  // containers
  sheet: {},          // for sheet-related functionality
  range: {},          // for range-related functionality
  
  // ----------------- spreadsheet -----------------

  // 🔸 app.spreadsheet
  spreadsheet: SpreadsheetApp.getActiveSpreadsheet(),
  
  // ----------------- sheet(s) -----------------
  
  // 🔸 app.sheets
  get sheets() { return app.spreadsheet.getSheets() },
  
  // 🔸 app.activeSheet
  get activeSheet() { return app.spreadsheet.getActiveSheet() },

  // 🔸 app.sheetByName()
  sheetByName(name) {

    const ss = app.spreadsheet;
    let sheet = ss.getSheetByName(name);

    // if no such sheet, append a new one.
    if(sheet === null){ 
      let i = ss.getNumSheets();          // 目前的有幾頁
      sheet = ss.insertSheet(name, i);    // 插入最後面 (⭐️ will activate automatically)
    }

    sheet.activate();                     // ⭐️ activate before returning        
    sheet.__proto__ = app.sheet.prototype; // ⭐️ extend sheet 
    return sheet;
  },
  
  // 🔸 app.deleteSheetByName()
  deleteSheetByName(name) {

    const ss = app.spreadsheet;
    let sheet = ss.getSheetByName(name);

    // if no such sheet, append a new one.
    if(sheet === null){ 
      Logger.log(`⚠️ app.deleteSheetByName：沒有「${name}」頁面。`);
      return;
    }

    return ss.deleteSheet(sheet); 
  },
  
  // ----------------- range -----------------

  // 🔸 app.rangeByName(name)
  rangeByName(name) { 
    const range = app.spreadsheet.getRangeByName(name);
    range.__proto__ = app.range.prototype;
    return range;
  },
  
  // ----------------- value(s) -----------------
  
  // 🔸 app.valueOfNamedRange(name)
  valueOfNamedRange(name) {
    return app.rangeByName(name).getValue();
  },
  
  // 🔸 app.valuesOfNamedRange(name): 2D array
  valuesOfNamedRange(name) {
    return app.rangeByName(name).getValues();
  },
  
  // 🔸 app.dataRangeValuesFromSheet(sheetName)
  // ⭐️ 注意：data range 都是從 "A1" 開始算❗️ 
  dataRangeValuesFromSheet(sheetName){
    return app.sheetByName(sheetName).getDataRange().getValues();
  },
  
  // ----------------- UI -----------------
  
  // 🔸 app.ui
  ui: SpreadsheetApp.getUi(),

  // 🔸 app.alert()
  alert(prompt){ app.ui.alert(prompt) },

};

// 🔸 app.color
app.color = {
  gray: '#eeeeee',
  lightRed: "#ffcccc",
  lightGreen: '#ccffcc',
  lightBlue: '#ccccff',
};

// ----------------------------- prototypes -------------------------------------

/**
 * common mehtods for 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);
  },

};

/**
 * custom prototype for Range
 * 
 * ### methods
 * 
 * - row()
 * - col()
 * - rows()
 * - cols()
 * - info()
 * 
 * - inset()
 * - nthRow(i)
 * - nthColumn(j)
 * - firstNRows(n)
 * - firstNColumns(n)
 * - lastNthRow(i)  <-------- #TODO
 * - lastNthColumn(j)
 * - lastNRows(n)   <-------- #TODO
 * - lastNColumns(n)
 * - cell(i, j)
 * 
 * @example
 * 
 *   range.__proto__ = app.range.prototype;
 *   range.alignCenter();
 */
app.range.prototype = {

  // ┌────────────┐
  // │  info      │
  // └────────────┘

  // GAS doesn't work well with getters❓
  // -------------------------------------
  // get row(){ return this.getRow() },           // ⛔ TypeError: this.getRow is not a function
  // get col(){ return this.getColumn() },
  // get rows(){ return this.numRows() },
  // get cols(){ return this.numColumns() },

  row(){ return this.getRow() },
  col(){ return this.getColumn() },
  rows(){ return this.getNumRows() },
  cols(){ return this.getNumColumns() },

  // 🔸 range.toString()
  // ⭐ 注意：
  //   Range 有自己的 toString() (returns "Range")，所以如果要用 prototype 的版本，必須用下列程式碼：
  //   • range.__proto__.toString.call(range)
  //   • use `range.info()` instead
  toString(){
    return `Range at: (${this.row()}, ${this.col()}), dim: ${this.rows()} ⨉ ${this.cols()}`;
  },

  // 🔸 range.info()
  info(){
    return `Range at: (${this.row()}, ${this.col()}), dim: ${this.rows()} ⨉ ${this.cols()}`;
  },

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

  // 🔸 range.inset()
  inset({top=0, bottom=0, left=0, right=0}={}){

    let row = this.row() + top;
    let col = this.col() + left;
    let rows = this.rows() - top - bottom;
    let cols = this.cols() - left - right;

    if (row < 0) throw new Error(`⛔ app.range.prototype.inset: new range can't start from row = ${row}`);
    if (col < 0) throw new Error(`⛔ app.range.prototype.inset: new range can't start from col = ${col}`);
    if (rows < 1) throw new Error(`⛔ app.range.prototype.inset: new range can't have ${rows} rows`);
    if (cols < 1) throw new Error(`⛔ app.range.prototype.inset: new range can't have ${cols} cols`);

    let range = this.offset(row - this.row(), col - this.col(), rows, cols);
    range.__proto__ = app.range.prototype;

    return range;
  },

  // 🔸 range.nthRow(i)
  nthRow(i){

    let row = this.row() + i - 1;
    if (row < 0) throw new Error(`⛔ app.range.prototype.nthRow(): new range can't start from row = ${row}`);

    let range = this.offset(row - this.row(), 0, 1, this.cols());
    range.__proto__ = app.range.prototype;
    return range;
  },

  // 🔸 range.nthColumn(j)
  nthColumn(j){
    let col = this.col() + j - 1;
    if (col < 0) throw new Error(`⛔ app.range.prototype.nthColumn(): new range can't start from col = ${col}`);

    let range = this.offset(0, col - this.col(), this.rows(), 1);
    range.__proto__ = app.range.prototype;
    return range;
  },
  
  // 🔸 range.firstNRows(n)
  firstNRows(n){
    let range = this.offset(0, 0, n, this.cols());
    range.__proto__ = app.range.prototype;
    return range;
  },

  // 🔸 range.firstNColumns(n)
  firstNColumns(n){
    let range = this.offset(0, 0, this.rows(), n);
    range.__proto__ = app.range.prototype;
    return range;
  },
  
  // 🔸 range.lastNthColumn(j)
  lastNthColumn(j){
    let range = this.offset(0, this.cols() - j, this.rows(), 1);
    range.__proto__ = app.range.prototype;
    return range;
  },
  
  // 🔸 range.lastNColumns(n)
  lastNColumns(n){
    let range = this.offset(0, this.cols() - n, this.rows(), n);
    range.__proto__ = app.range.prototype;
    return range;
  },

  // 🔸 range.cell(i, j)
  cell(i, j){

    let row = this.row() + i - 1;
    let col = this.col() + j - 1;
    if (row < 0) throw new Error(`⛔ app.range.prototype.cell(): new range can't start from row = ${row}`);
    if (col < 0) throw new Error(`⛔ app.range.prototype.cell(): new range can't start from col = ${col}`);

    let range = this.offset(row - this.row(), col - this.col(), 1, 1);
    range.__proto__ = app.range.prototype;
    return range;
  },

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

  // 🔸 range.alignCenter()
  alignCenter(){
    this
      .setVerticalAlignment('middle')
      .setHorizontalAlignment('center');
    return this;   // for chaining
  },

};
```

🚧 施工中

```javascript
// ----------------- URL -----------------

/**
 * app.fetch(url) -> JSON object
 */
app.fetch = function fetch(url){

  // connect to API endpoint
  var response = UrlFetchApp.fetch(url, {
    // this setting means your code won't halt ⭐️
    // if the request results in an API error. 
    // Instead, the error response (HTTPResponse) is returned.
    'muteHttpExceptions': true
  });
  
  // convert response to json object
  var json = response.getContentText();
  return JSON.parse(json); 
};

// ----------------- log -----------------

/**
 * app.log(msg)
 */
app.log = function(msg) {
  if (!app._logs) app._logs = [];
  app._logs.push(msg);
};

/**
 * app.printLogs()
 */
app.printLogs = function(){
  let sheet = app.sheetByName('系統訊息');
  sheet.clear();
  let values = app._logs.map(msg => [msg]);
  let range = sheet.getRange(1,1,values.length, 1);
  range.setValues(values);
};
```

{% endtab %}

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

* [Sheet](https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en) ⟩
  * [getDataRange()](https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#getdatarange) - from <mark style="color:yellow;">**A1**</mark> to the last cell with value.
  * [getLastRow()](https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#getlastrow) - index of the last row that <mark style="color:yellow;">**has content**</mark>.
* [Range](https://developers.google.com/apps-script/reference/spreadsheet/range?hl=en) ⟩
  {% endtab %}

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

* [app.valueOfNamedRange()](/web/appendix/gas/app/member/app.valueofnamedrange.md)
* [BorderStyle](/web/appendix/gas/custom-objects/borderstyle.md)
  {% endtab %}

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

* [str.replaceWhitespaces()](/web/js/val/prim/str/method/str.replacewhitespaces.md) 常用來「淨化」儲存格的值。
  {% endtab %}

{% tab title="💈範例" %}

* Google Sheets
  * [GAS Playground](https://script.google.com/u/1/home/projects/1BM-uKjWZdeywb0m-tpC59B6mb3iKpzKJpQf8HelzBCOQ0nfuxcb06fmx/edit)
  * [預警名單範本 (v1.1)](https://docs.google.com/spreadsheets/d/1G32mCUjxEQzJnxoHExcPvGNCgvGJ0oxM0twzRbitVjU/edit#gid=1230961158)
  * [高中獎學金名冊範本 (1.0)](https://docs.google.com/spreadsheets/d/13EmCMbsoksjbacSIGeC0sDOX1hgVEMXgRmkMLGLfTtk/edit#gid=1254539254)
  * [國中獎學金名冊 (1.0)](https://docs.google.com/spreadsheets/d/1XdrUCZS1_Ib5OnrLyU62iVKIoo30290Ac6mVAzKZY0M/edit#gid=582540594) ⭐️
* GAS
  * [高中預警名單](https://script.google.com/u/1/home/projects/1rzdOzgaeDH7t5l6s4l6aVAwDUsuyVhlM8HIFKWS14vB-Bo86-eQpeZnm/edit)
  * [高三成績確認表](https://docs.google.com/spreadsheets/d/1fKVssatYowWaViX0mOJkZVaR3g0W9uTIx_G0ZA9fxOg/edit#gid=886279731)
    {% endtab %}

{% tab title="📅 歷史" %}

* 2022.07.28：將 app.sheet/range.prototype, [app.color](/web/appendix/gas/app/member/app.color.md) 直接加入 app 檔。
  {% endtab %}
  {% endtabs %}

## Archive

{% tabs %}
{% tab title="style" %}

```javascript
// ----------------- 🍄 app.style -----------------

app.style = {};

app.style.border = {
    solid      : SpreadsheetApp.BorderStyle.SOLID,
    solidMedium: SpreadsheetApp.BorderStyle.SOLID_MEDIUM,
    solidThick : SpreadsheetApp.BorderStyle.SOLID_THICK,
    dashed     : SpreadsheetApp.BorderStyle.DASHED,
    dotted     : SpreadsheetApp.BorderStyle.DOTTED,
    double     : SpreadsheetApp.BorderStyle.DOUBLE
};
```

{% endtab %}

{% tab title="1" %}
archived on 2022.07.28

```
// app.spreadsheet
// app.sheets
// app.activeSheet
// app.sheetByName(sheetName)
// app.deleteSheetByName(sheetName)
// app.rangeByName(rangeName)
// app.valueOfNamedRange(rangeName)
// app.valuesOfNamedRange(rangeName): 2D array
// app.dataRangeValuesFromSheet(sheetName): 2D array
// app.ui
// app.alert(prompt)
const app = {

  // containers
  sheet: {},          // for sheet-related functionality
  range: {},          // for range-related functionality
  
  // ----------------- spreadsheet -----------------

  // 🔸 app.spreadsheet
  spreadsheet: SpreadsheetApp.getActiveSpreadsheet(),
  
  // ----------------- sheet(s) -----------------
  
  // 🔸 app.sheets
  get sheets() { return app.spreadsheet.getSheets() },
  
  // 🔸 app.activeSheet
  get activeSheet() { return app.spreadsheet.getActiveSheet() },

  // 🔸 app.sheetByName()
  sheetByName(name) {

    const ss = app.spreadsheet;
    let sheet = ss.getSheetByName(name);

    // if no such sheet, append a new one.
    if(sheet === null){ 
      let i = ss.getNumSheets();          // 目前的有幾頁
      sheet = ss.insertSheet(name, i);    // 插入最後面 (⭐️ will activate automatically)
    }

    sheet.activate();                     // ⭐️ activate before returning        
    sheet.__proto__ = app.sheet.prototype; // ⭐️ extend sheet 
    return sheet;
  },
  
  // 🔸 app.deleteSheetByName()
  deleteSheetByName(name) {

    const ss = app.spreadsheet;
    let sheet = ss.getSheetByName(name);

    // if no such sheet, append a new one.
    if(sheet === null){ 
      Logger.log(`⚠️ app.deleteSheetByName：沒有「${name}」頁面。`);
      return;
    }

    return ss.deleteSheet(sheet); 
  },
  
  // ----------------- range -----------------

  // 🔸 app.rangeByName(name)
  rangeByName(name) { return app.spreadsheet.getRangeByName(name) },
  
  // ----------------- value(s) -----------------
  
  // 🔸 app.valueOfNamedRange(name)
  valueOfNamedRange(name) {
    return app.rangeByName(name).getValue();
  },
  
  // 🔸 app.valuesOfNamedRange(name): 2D array
  valuesOfNamedRange(name) {
    return app.rangeByName(name).getValues();
  },
  
  // 🔸 app.dataRangeValuesFromSheet(sheetName)
  // ⭐️ 注意：data range 都是從 "A1" 開始算❗️ 
  dataRangeValuesFromSheet(sheetName){
    return app.sheetByName(sheetName).getDataRange().getValues();
  },
  
  // ----------------- UI -----------------
  
  // 🔸 app.ui
  ui: SpreadsheetApp.getUi(),

  // 🔸 app.alert()
  alert(prompt){ app.ui.alert(prompt) },

};
```

{% endtab %}
{% endtabs %}