⭐app.makeTable()

GAS ⟩ app ⟩ members ⟩ .makeTable()

💾 程式

💾 google apps script

⬆️ 需要：app.range.prototype, app.color

// 🔸 app.makeTable
app.makeTable = function(sheetName, data, {

  // internal parameters for `app.makeTable` function
  sheet = app.sheetByName(sheetName),
  rows = data.length,
  cols = data[0].length,
  dataRange = sheet.range(1,1,rows, cols),

  // ⭐ 欄位寬度：(dataRange) => [[startCol, numCols, width]]
  columnWidths,     

  // ⭐ subranges in dataRange
  ranges = {

    border,         // 粗框線：(dataRange) => [Range]
    gridlines,      // 細格線：(dataRange) => [Range]

    // ⭐ 儲存格格式：(dataRange) => [{format: '0.0', ranges: [Range]}]
    numberFormat,
       
    alignCenter,    // 置中 ：(dataRange) => [Range]

    gray,           // 灰色：(dataRange) => [Range]
    lightBlue,      // 淡藍：(dataRange) => [Range]
    lightGreen,     // 淡綠：(dataRange) => [Range]
    bold,           // 粗體：(dataRange) => [Range]

    mergeAcross,    // 水平合併：(dataRange) => [Range]

    // conditional formats
    scores,         // 分數區：不及格者淡紅 (dataRange) => [Range]
    top3,           // 班排前三：淡藍 (dataRange) => [Range]
    top30,          // 校排前三十：淡綠(dataRange) => [Range]
  },

}={ranges: {}}){

  // ----------------------- set values into sheet -----------------------

  sheet.clear();
  sheet.setValues(data);

  // ----------------------- format tables -----------------------

  // ┌───┐
  // │ 畫線 │
  // └───┘
  
  // grid lines first
  if (ranges.gridlines) {
    ranges.gridlines(dataRange).forEach(range => 
      range.setBorder(true, true, true, true, true, true)
    )
  }

  // border last
  if (ranges.border) {
    ranges.border(dataRange).forEach(range => 
      range
        .setBorder(true, true, true, true, null, null, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM)
    )
  }

  // ┌─────┐
  // │ 數字格式 │ 
  // └─────┘
  if (ranges.numberFormat) {
    ranges.numberFormat(dataRange).forEach(({format, ranges}) => 
      ranges.forEach(range => range.setNumberFormat(format))
    )
  }

  // ┌───┐
  // │ 對齊 │
  // └───┘
  if (ranges.alignCenter) {
    ranges.alignCenter(dataRange).forEach(range => 
      range.alignCenter()
    )
  }

  // ┌─────┐
  // │ 水平合併 │
  // └─────┘
  if (ranges.mergeAcross) {
    ranges.mergeAcross(dataRange).forEach(rng => rng.mergeAcross())
  }

  // ┌───┐
  // │ 粗體 │
  // └───┘
  if (ranges.bold) {
    ranges.bold(dataRange).forEach(rng => rng.setFontWeight('bold'))
  }

  // dataRange.nthRow(1)         // 標題
  //   .setFontSize(14)

  // ┌────┐
  // │ 背景色 │
  // └────┘
  if (ranges.gray) {
    ranges.gray(dataRange).forEach(range => 
      range.setBackground(app.color.gray)
    )
  }

  if (ranges.lightBlue) {
    ranges.lightBlue(dataRange).forEach(range => 
      range.setBackground(app.color.lightBlue)
    )
  }

  if (ranges.lightGreen) {
    ranges.lightGreen(dataRange).forEach(range => 
      range.setBackground(app.color.lightGreen)
    )
  }

  // ┌─────┐
  // │ 欄位寬度 │
  // └─────┘

  if (columnWidths) {
    columnWidths(dataRange).forEach(([start, cols, width]) => 
      sheet.setColumnWidths(start, cols, width)
    )
  }

  // ┌─────┐
  // │ 條件格式 │
  // └─────┘

  if (ranges.scores) {
    sheet.appendConditionalFormatRule(app.rules.redIfLessThan60(ranges.scores(dataRange)))
  }

  if (ranges.top3) {
    sheet.appendConditionalFormatRule(app.rules.blueIfTop3(ranges.top3(dataRange)))
  }

  if (ranges.top30) {
    sheet.appendConditionalFormatRule(app.rules.greenIfTop30(ranges.top30(dataRange)))
  }

};

💈範例

💾 google apps script

  app.makeTable(classNo, Student.dataForClassTable(classNo), {

    // column widths
    columnWidths: (r, {cols=r.cols()}={}) => [[1, cols, 60]],

    // ranges
    ranges: {

      // border
      border: (r) => [r.inset({top: 1, bottom: 1})],  // app.range.prototype

      // grid lines
      gridlines: (r) => [r.inset({top: 1})],

      // gray ranges
      gray: (r, {rows=r.rows()}={}) => [
        r.nthRow(2), 
        r.cell(rows, 3)  // <-- #TODO: r.bottomLeftCell(rowOffset, colOffset)
      ],

      // bold
      bold: (r) => [r.nthRow(1)],

      // merge across
      mergeAcross: (r) => [r.nthRow(1)],

      // align center
      alignCenter: (r) => [r],

      // ----- number format -----

      numberFormat: (r, {cols=r.cols()}={}) => [
        {format: '0.00', ranges: [
          r.inset({top: 2, left: 3, right: 2}),
        ]},
        {format: '00', ranges: [
          r.nthColumn(2).inset({top: 2, bottom: 1})
        ]},
        {format: '0', ranges: [
          r.lastNColumns(2).inset({top: 2, bottom: 1})
        ]},
      ],

      // ----- conditinoal format -----

      scores: (r, {cols=r.cols()}={}) => [
        r.inset({top: 2, left: 3, right: 4}),
        r.nthColumn(cols - 2).inset({top: 2, bottom: 1}),
      ],

      top3: (r) => [r.lastNthColumn(2).inset({top: 2, bottom: 1})],
      top30: (r) => [r.lastNthColumn(1).inset({top: 2, bottom: 1})],
    },

  });

⬇️ 應用

• 國中成績一覽表

👥 相關

• nested destructuring ⭐️