🍄app

GAS ⟩ app

🔴 主題

• SheetMethods - mixin for Sheet objects.

🔸 屬性

• app.dataRangeValuesFromSheet()

💾 程式

/**
 * 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
  },

};

🚧 施工中

// ----------------- 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);
};

📘 手冊

• Sheet ⟩

  • getDataRange() - from A1 to the last cell with value.

  • getLastRow() - index of the last row that has content.

• Range ⟩

⬇️ 應用

• app.valueOfNamedRange()

• BorderStyle

👥 相關

• str.replaceWhitespaces() 常用來「淨化」儲存格的值。

💈範例

• Google Sheets

  • GAS Playground

  • 預警名單範本 (v1.1)

  • 高中獎學金名冊範本 (1.0)

  • 國中獎學金名冊 (1.0) ⭐️

• GAS

  • 高中預警名單

  • 高三成績確認表

📅 歷史

• 2022.07.28：將 app.sheet/range.prototype, app.color 直接加入 app 檔。

Archive

style

// ----------------- 🍄 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
};

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) },

};