第三週沒有控制好進度，而且連假後超忙😖
🚂今晚繼續趕進度，亂塞內容～

前言

今晚原本打算像昨天（Day24）一樣，藉著參加鐵人賽活動，整理並回顧自己這一年來用Google Sheets與GAS的歷程，但礙於時間不夠，這裡就抓三個優化大原則做分享。

正文

GAS腳本：效能&配額

個人建議

在GAS腳本，把減少GAS特有全域變數的使用視為最優先，可讀性與可維護性其次。

建議理由

這裡稍微重提第一週[^1]聊到的：

GAS環境基於V8引擎，當執行GAS腳本時，Google Cloud會建立一次性沙盒容器，運行完後，該沙盒就會被銷毀。[^2]

當呼叫到GAS環境特有的全域變數時，會向該沙盒外的伺服器發送請求，這會大幅影響腳本的執行速度，而且Google對此也有配額的限制，超過就必須付費。

總之，必須盡可能對於GAS特有全域變數的使用錙銖必較，沒辦法像前端專案一樣盡情的for loop，也無法像VBA[^3]一樣盡情增修查改Excel。

實例一

由於每當使用者編輯時都會頻繁觸發，所以需要盡可能降低API的呼叫次數：

function onEdit(e: GoogleAppsScript.Events.SheetsOnEdit): void {

const range = e.range;  
if (range.getNumColumns() !== 1) return;

// 不把所有變數宣告集中於第一段（稍微降低易讀性）
// 第一個檢查點通過後，才繼續call getRow
const row = range.getRow();
if (row === 1) return;

// 同理，這裡才call getColumn
const col = range.getColumn();
if (!LISTEN_COLUMNS.has(col)) return;

// 同理，這裡才call getRange與setValue
range.getSheet().getRange(row, COLUMNS.SYNC_STATUS).setValue("Modified");
}

接著，透過使用offset，再減少一次API呼叫：

const offset = COLUMNS.SYNC_STATUS - col;
range.offset(0, offset).setValue("Modified");
}

實例二

在這段程式碼裡，不僅有語法錯誤，而且當時我在map()迴圈裡重複呼叫了getLastColumn()：

function clearRowsWithBackgroundColor(backgroundColor) {

  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = activeSheet.getLastRow();
  const rangeToCheck = activeSheet.getRange("C2:C" + lastRow);
  const colors = rangeToCheck.getBackgrounds(); //String[][]
  const rowsToClear = [];
  
  colors.forEach((row, index) => {
    if (row[0] === backgroundColor) {
      rowsToClear.push(index + 2); 
    }
  });

  if (rowsToClear.length > 0) {
    const ranges = rowsToClear.map(
      (row) => `A${row}:${activeSheet.getLastColumn()}`
    );
    activeSheet.getRangeList(ranges).setBackground(null);
  }
}

通過先把getLastColumn()存入變數，將呼叫次數從rowsToClear.length 次降低為1次：

function clearRowsWithBackgroundColor(backgroundColor) {

  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = activeSheet.getLastRow();
  const lastColumn = activeSheet.getLastColumn();
  const lastColumnLetter = getColumnLetter(lastColumn);
  const rangeToCheck = activeSheet.getRange("C2:C" + lastRow);
  const colors = rangeToCheck.getBackgrounds(); // String[][]  

  const rowsToClear = [];

  colors.forEach((row, index) => {
    if (row[0] === backgroundColor) {
      rowsToClear.push(index + 2); 
    }
  });  

  if (rowsToClear.length > 0) {
    const ranges = rowsToClear.map(
      (row) => `A${row}:${lastColumnLetter}${row}`
    );
    activeSheet.getRangeList(ranges).setBackground(null);
  }
}

實例三

在forEach()迴圈裡重複呼叫getRange()和setBackground()：

columnBValues.forEach((row, index) => {
  const cellValue = row[0];
  const rowIndex = index + 2;
  const columnCValue = columnCValues[index][0];
  const columnDValue = columnDValues[index][0];

  if (
    cellValue === "" &&
    columnDValue === "" &&
    !columnCValue.includes("部門") &&
    !columnCValue.includes("公告")
  ) {
    const rowRange = activeSheet.getRange(rowIndex, 1, 1, lastColumn);
    rowRange.setBackground(highlightColors.other);
  }
});

使用getRangeList()，將呼叫setBackground()次數降低為1次：

  const rowsToHighlight = [];

  columnBValues.forEach((row, index) => {
    const columnBValue = row[0];
    const columnCValue = columnCValues[index][0];
    const columnDValue = columnDValues[index][0];
  
    if (
      columnBValue === "" &&  
      columnDValue === "" && 
      !columnCValue.includes("部門") &&
      !columnCValue.includes("公告")
    ) {
      rowsToHighlight.push(index + 2); 
    }
  });

  if (rowsToHighlight.length > 0) {
    const ranges = rowsToHighlight.map(
      (row) => `A${row}:${lastColumnLetter}${row}`
    );    activeSheet.getRangeList(ranges).setBackground(highlightColors.other);
  }

最後乾脆抽出來，重構成helper function：

/**
 * Sets the background color for specified rows in the active sheet.
 *
 * @param {number[]} rows - An array of row numbers to apply the background color.
 * @param {string|null} color - The background color to set (e.g., "#ff0000") or `null` to clear the background.
 */

function setRowBackgrounds(rows, color) {
  if (rows.length > 0) {
    const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const lastColumnLetter = getColumnLetter(activeSheet.getLastColumn());
    const ranges = rows.map((row) => `A${row}:${lastColumnLetter}${row}`);
    activeSheet.getRangeList(ranges).setBackground(color);
  }
}

GAS腳本：避免硬編碼

早期寫GAS腳本時，常常「硬編碼」。

以《實作回顧：用GAS腳本模仿Google Sheets既有功能》為例，我當年連動該表的GAS腳本，把處理的欄位寫死。儘管表格格式不會時常變動，但一旦變動，就必須修改GAS腳本。

我後來的做法是，在每個欄位加上可以被腳本讀取的id（例如加在首列，可以透過配色來隱藏id，又或是其它不會被使用者修改的固定位置），然後用腳本於初次載入或執行其它函式時，依id抓取欄位，就可以讓試算表更容易模板化，讓其它組也一起使用、自行擴充其它欄位：

export const COLUMN_NAMES = [
  "EVENT_ID",
  "TITLE",
  "START_DATE",
  "START_TIME",
  "END_DATE",
  "END_TIME",
  "ALL_DAY",
  "SYNC_STATUS",
  "ERROR_MSG",
  "ERROR_DETAILS",
] as const;

// 推導出型別："EVENT_ID" | "TITLE" | ...
type ColumnName = (typeof COLUMN_NAMES)[number];

// 對應結果：每個欄位名稱對應一個數字index
export let COLUMNS: Record<ColumnName, number> = {} as any;
export let LISTEN_COLUMNS: Set<number>;

export function extractColumnPositions(): void {
  const sheet = getTargetSheet();
  const header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const missing: string[] = [];

  for (const name of COLUMN_NAMES) {
    const colIndex = header.findIndex((headerValue) =>
      String(headerValue).includes(name)
    );
    if (colIndex === -1) {
      missing.push(name);
    } else {
      COLUMNS[name] = colIndex + 1; // 1-based
    }
  }

  LISTEN_COLUMNS = new Set([
    COLUMNS.TITLE,
    COLUMNS.START_DATE,
    COLUMNS.START_TIME,
    COLUMNS.END_DATE,
    COLUMNS.END_TIME,
  ]);

  const ui = SpreadsheetApp.getUi();

  // log結果
  if (missing.length > 0) {
    ui.alert("⚠️以下欄位缺失:\n" + missing.join(", "));
    throw new Error("Missing required column");
  }

試算表：依功能拆分

GAS腳本終歸是服務Google Sheets，而好的Sheets設計其實常常更重要。當我可以從零開始設計一套試算表與流程時，我會盡可能將每個表格保持單一功能：

1. 說明文件（唯讀）
2. 可用資料（唯讀）
3. 輸入資料（特定範圍可編輯）
4. 輸出資料（唯讀）
5. 輸出參數（僅指定欄位可編輯）
6. 操作紀錄（隱藏）

以《實作回顧：流程優化評估》聊到的鬼故事為例，當試圖把原本只能由特定權限的人處理並負責的任務，分派給所有人員時，我會建議重新審視既有試算表，先重構成所有人員都可以正確操作的版本。[^4][^5]

後話

關於效能

這是我剛碰GAS專案時特別有感的部分，我當時恣意地用了很多GAS特有全域變數，導致執行時要跑好一陣子，才開始重構。

一來當下覺得怎麼快就碰到效能問題，我於《實作回顧：建置Web App》提到的、沒在優化效能的客服工具箱，在各種舊電腦都跑得挺順[^6]，懷疑GAS環境就算付費仍真的適合規模化嗎？[^7]

二來是，突然發覺以前真的浪費很多無謂的時間在重構。比如看到文章提到，前端專案的變數統一宣告在最頂端會效能更好，就開始重構。可是其實小專案根本影響微乎其微，包括撰寫這篇文章時，就算看到拿來當範例的after的程式碼，也覺得太可怕了好想重構。只能不斷提醒自己：能跑就不要浪費時間改。

關於減少修改

個人的歷程是，隨著修改的次數來到第三次、第四次，就會開始想有哪些類似的場景會需要修改、是否有一個規律或邏輯可以讓腳本自動判斷。

其實比起GAS腳本，我覺得試算表修改才更麻煩。尤其是試算表除了財務人員所需以外，其它大部分都是補足既有系統之不足；當系統汰換，新系統又再次無法滿足所有業務需求時，就必須再次修改或重新建立輔助用的試算表。

我覺得改前人的試算表跟工程師改前人的code一樣，當試算表擠滿一堆髒data與跳來跳去互相關聯的欄位，改起來真的非常痛苦。非常希望一般使用者也可以稍微借用程式設計的部分方法論，提高可維護性。[^8]

Annotations

[^1]： 例如：《基於V8引擎的GAS環境》&《GAS的變數之生命週期》。

[^2]： 誠如以前附註過的，嚴謹地說沙盒與伺服器之間的配置與互動並非官方contract，實際上的infra實作更複雜。

[^3]： 即便如此，我還是喜歡GAS勝過VBA，因為GAS讓我可以不用煩惱跨裝置一致性，並且能使用更現代的開發工具與配置，況且VBA已經長年沒更新feat。

[^4]： 其實GAS腳本也是，很多原本為了解決自己需求的小腳本，我可以相信自己的操作行為，所以寫得很粗糙；但是當把腳本提供給其他人使用時，就必須設想很多防呆機制。

[^5]： 就我自己個人的實務體驗，我發現很多陳年的Excel表會把所有操作、髒data與輸出集中在一個大表。我不禁猜測是否因為以前硬體限制，螢幕比較小或沒有雙螢幕，又或者不方便於單螢幕同時顯示多個表。

[^6]： 現在想來也理所當然，畢竟只是靜態SPA，不是2C行銷用途，所以除了必要的教育訓練圖檔以外，檔案超級小，而且call外部小API時GET的資料也很小。

[^7]： 就跟我目前對於Vercel的計費成本感到懷疑一樣，所以我還在用傳統的shared hosting。

[^8]： 不過這真的很看主管，不僅限於軟體工程領域。如果主管只求快，那就是隨便的人越快交付，在乎的人越慢交付；後者接手前者很慢，前者接手後者很快。（突然想到前陣子滑到的這則推特）