GAS Spreadsheets 試算表進階操作

除了基礎的讀寫，Google Apps Script (GAS) 還能讓你動態調整試算表的格式、建立自定義函數 (UDF)、處理複雜資料格式，甚至設定權限保護。

樣式與格式設定

透過 Range 物件提供的 API，你可以全自動化地美化報表。

/**
 * 格式化報表的標題與欄位
 */
function applyFormatting() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const headerRange = sheet.getRange("A1:E1");
  
  // 設定背景顏色 (Hex 格式)
  headerRange.setBackground("#4a86e8");
  
  // 設定字體顏色與粗細
  headerRange.setFontColor("#ffffff")
             .setFontWeight("bold")
             .setFontSize(12);
             
  // 設定對齊方式與框線
  headerRange.setHorizontalAlignment("center")
             .setBorder(true, true, true, true, null, null, "black", SpreadsheetApp.BorderStyle.SOLID);
}

格式化相關函數語法說明

setBackground(color)：設定範圍的背景顏色，支援名稱或 Hex 字串。
setFontColor(color) / setFontWeight(weight)：設定字體顏色與粗細（"bold" 或 "normal"）。
setHorizontalAlignment(alignment)：設定文字水平對齊，常見參數為 "center", "left", "right"。
setBorder(top, left, bottom, right, vertical, horizontal, color, style)：細膩設定框線。
setNumberFormat(format)：設定儲存格格式，如 "0.00%" (百分比) 或 "yyyy-MM-dd" (日期)。

自定義函數 (Custom Functions)

自定義函數能讓你寫入額外的運算邏輯，並像 =SUM() 一樣直接在儲存格中使用。

/**
 * 根據匯率將美元轉換為台幣
 * @param {number} usd 原始美元金額
 * @return {number} 轉換後的台幣金額
 * @customfunction
 */
function USD_TO_TWD(usd) {
  if (typeof usd !== 'number') return '輸入必須為數字';
  const rate = 31.8; // 假設匯率
  return usd * rate;
}

使用注意事項

純函數：自定義函數只能用於計算並返回一個值，不能在裡面執行 setValue()、appendRow() 或 MailApp.sendEmail()。
註解與文檔：使用 JSDoc 格式註解（如 @param, @return）並加上 @customfunction 標籤，這會讓使用者在試算表輸入公式時看到語法提示。
性能：自定義函數過多會導致試算表重算變慢，建議在大範圍資料時改用腳本批次寫入。

資料處理技巧：字典映射 (Dictionary Mapping)

處理試算表資料時，直接操作二維陣列（如 data[i][2]）容易混淆。將其轉換為 JavaScript 物件數組會更專業且易讀。

/**
 * 將試算表內容轉換為易於開發的物件格式
 */
function mapDataToObjects() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues(); // 取得所有資料
  
  const headers = data[0]; // 第一列是欄位名稱
  const rows = data.slice(1); // 剩餘的是資料主體列
  
  const records = rows.map(row => {
    let obj = {};
    headers.forEach((header, index) => {
      obj[header] = row[index];
    });
    return obj;
  });
  
  // 現在你可以直覺地存取 record.姓名 或 record.Email
  records.forEach(record => {
    if (record["狀態"] === "未付款") {
      processPayment(record);
    }
  });
}

資料驗證 (Data Validation)

這是設定下拉選單 (Dropdown) 的核心方法，可用於確保資料輸入的正確性。

/**
 * 為 B 欄的所有儲存格建立下拉選單
 */
function createDropdowns() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("B2:B10");
  
  // 建立驗證規則
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(["待處理", "執行中", "已完成"], true)
    .setAllowInvalid(false) // 禁止輸入清單外的內容
    .setHelpText("請從下拉選單中選取狀態")
    .build();
    
  range.setDataValidation(rule);
}

資料驗證函數語法說明

SpreadsheetApp.newDataValidation()：開始建立一個新的驗證建構器。
requireValueInList(values, showDropdown)：設定清單內容與是否顯示下拉箭頭。
requireNumberBetween(start, end)：設定數值範圍驗證。
setAllowInvalid(allow)：若設為 false，使用者輸入錯誤內容時會被拒絕。
build()：完成規則建構，返回 DataValidation 物件。

保護工作表與範圍 (Protection)

在多使用者協作環境中，你可能希望保護特定的公式欄位不被意外修改。

/**
 * 保護特定的儲存格範圍，僅限腳本所有者編輯
 */
function protectHeader() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("A1:E1");
  const protection = range.protect().setDescription("保護標題列");
  
  // 移除所有人的編輯權限
  const me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
  
  console.log("範圍保護已開啟");
}

保護函數語法說明

range.protect()：對該範圍開啟保護並返回 Protection 物件。
setDescription(desc)：在「保護工作表與範圍」清單中顯示的說明文字。
removeEditors(users)：移除特定使用者的編輯權限。
setWarningOnly(warning)：若設為 true，修改時只會跳出警告而非禁止。

條件格式設定 (Conditional Formatting)

根據數值自動變更顏色（如：低於 60 分變紅字）。

/**
 * 自動標註不及格的成績
 */
function setScoreWarning() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("C2:C100");
  
  // 若數值小於 60，背景變紅色
  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThan(60)
    .setBackground("#FF0000")
    .setFontColor("#FFFFFF")
    .setRanges([range])
    .build();
    
  const rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

條件格式設定函數語法說明

在 GAS 中設定條件格式需要透過「建立規則」與「套用至工作表」兩個步驟：

1. 建立規則建構器

SpreadsheetApp.newConditionalFormatRule()：啟動一個新的規則建立流程（Builder 模式）。

2. 定義觸發條件 (以下僅為常用示範)

whenNumberLessThan(number)：數值小於特定值時觸發樣式。
whenTextContains(text)：文字包含特定字串時觸發樣式。
whenDateAfter(date)：日期在特定時間之後觸發樣式。
whenFormulaSatisfied(formula)：使用自定義公式判斷（功能最廣，例如 =A1>B1）。

3. 設定樣式與套用範圍

setBackground(color) / setFontColor(color)：滿足條件時呈現的背景或字體顏色。
setRanges([range1, range2])：設定此規則要套用在哪些範圍。注意：參數必須是「Range 物件的陣列」。
build()：封裝所有設定並返回一個 ConditionalFormatRule 規則物件。

4. 套用至工作表

條件格式規則是儲存在工作表的一個屬性清單中，必須透過以下方法生效：

sheet.getConditionalFormatRules()：取得該工作表目前所有的條件格式規則（回傳陣列）。
sheet.setConditionalFormatRules(rules)：將規則陣列寫回工作表。