第三週沒有控制好進度，而且連假後超忙😖
🚂今晚繼續趕進度，亂塞內容～
今晚原本打算像昨天（Day24）一樣，藉著參加鐵人賽活動，整理並回顧自己這一年來用Google Sheets與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腳本模仿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設計其實常常更重要。當我可以從零開始設計一套試算表與流程時，我會盡可能將每個表格保持單一功能：
以《實作回顧：流程優化評估》聊到的鬼故事為例，當試圖把原本只能由特定權限的人處理並負責的任務，分派給所有人員時，我會建議重新審視既有試算表，先重構成所有人員都可以正確操作的版本。[^4][^5]
這是我剛碰GAS專案時特別有感的部分，我當時恣意地用了很多GAS特有全域變數，導致執行時要跑好一陣子，才開始重構。
一來當下覺得怎麼快就碰到效能問題，我於《實作回顧：建置Web App》提到的、沒在優化效能的客服工具箱，在各種舊電腦都跑得挺順[^6]，懷疑GAS環境就算付費仍真的適合規模化嗎？[^7]
二來是，突然發覺以前真的浪費很多無謂的時間在重構。比如看到文章提到，前端專案的變數統一宣告在最頂端會效能更好，就開始重構。可是其實小專案根本影響微乎其微，包括撰寫這篇文章時，就算看到拿來當範例的after的程式碼，也覺得太可怕了好想重構。只能不斷提醒自己：能跑就不要浪費時間改。
個人的歷程是，隨著修改的次數來到第三次、第四次，就會開始想有哪些類似的場景會需要修改、是否有一個規律或邏輯可以讓腳本自動判斷。
其實比起GAS腳本，我覺得試算表修改才更麻煩。尤其是試算表除了財務人員所需以外，其它大部分都是補足既有系統之不足；當系統汰換，新系統又再次無法滿足所有業務需求時，就必須再次修改或重新建立輔助用的試算表。
我覺得改前人的試算表跟工程師改前人的code一樣，當試算表擠滿一堆髒data與跳來跳去互相關聯的欄位，改起來真的非常痛苦。非常希望一般使用者也可以稍微借用程式設計的部分方法論，提高可維護性。[^8]
[^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]： 不過這真的很看主管，不僅限於軟體工程領域。如果主管只求快，那就是隨便的人越快交付，在乎的人越慢交付；後者接手前者很慢，前者接手後者很快。（突然想到前陣子滑到的這則推特）
iThome鐵人賽
看影片追技術