# split data into tabs

{% tabs %}
{% tab title="🔸 app" %}
💾 程式：[Google Sheet](https://docs.google.com/spreadsheets/d/1BWlw1TPFcAdcDePIbnUN9T9oX2RtLnbukfXifK8sUZo/edit#gid=545630193)

```javascript
// 🔸 app
const app = {};

// 🔸 app.spreadsheet
// const (所以直接存儲在 app 屬性中就好)
app.spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  

// 🔸 app.sheets()
app.sheets = function(){
  return app.spreadsheet.getSheets()
};

// 🔸 app.sourceSheet()
app.sourceSheet = function(){
  return app.sheets()[0]
};

// 🔸 app.sourceSheetName()
app.sourceSheetName = function(){
  return app.sourceSheet().getName()
};

// 🔸 unique course names
// 傳回 H 欄的所有科目（去除重複）
app.uniqueCourses = function uniqueCourses(){
  return app.sourceSheet()
    .getRange("H2:H")           // H column
    .getValues()                // 2D data
    .map(row => row[0])         // 每列取第一筆資料 (只有一筆資料)
    .unique()
    .filter(x => x);            // ⭐️ remove empty string
};


// 🔸 app.insertSheetByName(rawName)
// 將分科的資料放到新的頁面中
app.insertSheetByName = function (rawName, {
  autoresize = true         // resize cols
}={}) {

  // tab name 
  const name = tabName(rawName);

  // get worksheet by name
  let ws = app.spreadsheet.getSheetByName(name);

  // if already exists, quit.
  if(ws){ 
    log(`⚠️ 頁面「${name}」已經存在，取消插入試算表。`);
    return 
  };

  // source sheet name
  const sourceSheetName = app.sourceSheetName();

  // 1. create a worksheet
  ws = app.spreadsheet.insertSheet();  

  // 2. rename sheet
  // const rawName = "1011010101.國語文"
  
  ws.setName(name);

  // 3. set formula to A2 cell
  ws.getRange("A2")
    .setFormula(`=FILTER(${sourceSheetName}!B2:K,${sourceSheetName}!H2:H="${rawName}")`)

  // 4. copy title row from source sheet
  app.sourceSheet()
    .getRange("B1:L1")
    .copyTo(ws.getRange("A1:K1"))   // 注意：原始資料從 B 欄開始。

  // 5. 調整欄位寬度
  if(autoresize) app.resizeColumns(ws);

  // 6. 除了「補考成績」欄位外，鎖住其他欄位。
  app.protectSheet(ws);

};

// 🔸 protect sheet
// 6. 除了「補考成績」欄位外，鎖住其他欄位。
app.protectSheet = function(sheet){
  
  var protection = sheet.protect();                            // protect sheet
  protection.setUnprotectedRanges([sheet.getRange("I2:I")]);   // unprotect I column

  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());

  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
};

// 🔸 split data into separate tabs
app.splitIntoTabs = function(){

  const courses = app.uniqueCourses();
  log(`🚩 開始產生 ${courses.length} 個分頁。`);

  courses.forEach( rawName => {
    app.insertSheetByName(rawName);
  });

  log(`🏁 產生 ${courses.length} 個分頁完成。`);
};

// 🔸 delete all sheets execpt source sheet
app.deleleteAllGeneratedSheets = function(){
  app.sheets().forEach( sheet => {
    const name = sheet.getName();
    if(name !== app.sourceSheetName()) {
      app.spreadsheet.deleteSheet(sheet);
      log(`⚠️ 頁面「${name}」已刪除❗`);
    }
  })
};

// 🔸 autoresize all columns in `sheet`
// 似乎對中文不管用❓
app.resizeColumns = function(sheet) {
  // var sheet = SpreadsheetApp.getActiveSheet();
  // var dataRange = sheet.getDataRange();
  // var i = dataRange.getColumn();            // first col
  // var n = dataRange.getNumColumns();        // number of columns
  // sheet.autoResizeColumns(i, n);

  sheet.setColumnWidths(1, 6, 60);
  sheet.setColumnWidths(7, 1, 200);     // 科目
};

// 🔸 「科目」欄位名稱不能簡化，因為有的科目有不同科目代碼，
// 例如：101101040E.生物、105101040E.生物
function tabName(rawValue) {
  return rawValue;
  // 1. split by "."
  // 2. return last part
  // return rawValue.split(".")[1]
}
```

{% endtab %}

{% tab title="helpers" %}

```javascript
// log(...)
const {log} = Logger;

// ⭐️ array.unique() 
Array.prototype.unique = function(){
    return [... new Set(this)]            // (remove duplicates)
};
```

{% endtab %}

{% tab title="menu" %}

```javascript
// onOpen()
function onOpen() {
  
  var ui = SpreadsheetApp.getUi();    // app UI
  
  // ♥ 自製工具
  ui.createMenu('🧰 工具箱')
    .addItem('📥 原始報表 ➠ 分科', 'runApp')  
    .addItem('💾 分科頁面另存新檔', 'tabsToFiles')
    .addItem('🗑️ 刪除所有生成頁面❗', 'deleteSheets')      
    .addToUi();
}

function runApp(){
  app.splitIntoTabs();
}

function deleteSheets(){
  app.deleleteAllGeneratedSheets();
}

// create file for each tab
function tabsToFiles(){

  const sheets = app.sheets();

  log(`🚩 開始產生 ${sheets.length} 個檔案。`);

  sheets.forEach(sheet => {

    // get sheet's data, name
    const data = sheet.getDataRange().getValues();
    const name = sheet.getName();

    // get new file (with sheet name)
    // files will be saved to root folder by default. 
    const file = SpreadsheetApp.create(name);
    log(`✨ 產生新檔案：「${name}」。`);

    // copy data to new sheet
    const sheet2 = file.getSheets()[0];
    sheet2.setName(name);

    sheet2
      .getRange(1, 1, data.length, data[0].length)
      .setValues(data);

    // protect sheet
    app.protectSheet(sheet2);
  });

  log(`🏁 產生 ${sheets.length} 個檔案完成。`);
}
```

{% endtab %}

{% tab title="📗 參考" %}
{% embed url="<https://youtu.be/QTySwuhpHG0>" %}
完整的 Google Apps Scripts 解說
{% endembed %}

{% embed url="<https://youtu.be/4_9uAMsqz5o>" %}
一個一個慢慢做，或用他的插件
{% endembed %}
{% endtab %}

{% tab title="ℹ️ 操作提示" %}
{% hint style="info" %}

* Shift + Click：選取同一行(列)
* Shift + Ctrl + ⬇️：選到最後一行
  {% endhint %}

```excel-formula
// 抓出 H 行 = "1011010101.國語文" 的所有資料
//                 ╭─────── condition ────────╮
//      ╭─range─╮  ╭column─╮ ╭──── value ─────╮
=FILTER(Data!B2:K, Data!H2:H="1011010101.國語文")
//              ↑          ↑
// ⭐️ 最後一行的「行號」不要打，會節省不少事。
```

{% endtab %}
{% endtabs %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://lochiwei.gitbook.io/web/appendix/gas/examples/split-data-into-tabs.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.