GAS Spreadsheets 讀取與寫入試算表

操作 Google 試算表是 Google Apps Script (GAS) 最常見的用途。透過 SpreadsheetApp 服務，你可以像操作 Excel 巨集一樣，全自動地讀取、過濾、修改或寫入資料。

核心階層概念

要操作資料前，必須先理解 GAS 操作試算表的物件層級：

1. Spreadsheet (試算表檔案)：整個 Google 試算表文件。
2. Sheet (工作表)：檔案下方的分頁 (tab)。
3. Range (範圍)：工作表上的單格、一列、一欄或一個區塊。

工作表管理 (Sheet Management)

在同一個試算表（Spreadsheet）檔案中，通常會有多個分頁（Sheet）。你可以透過腳本來新增、刪除或切換這些工作表。

取得與切換工作表

/**
 * 演示如何取得不同的工作表物件
 */
function sheetManagementDemo() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 1. 透過名稱取得工作表
  const sheet = ss.getSheetByName('資料庫');
  
  // 2. 取得目前使用者正在看的那張表
  const activeSheet = ss.getActiveSheet();
  
  // 3. 取得所有工作表（回傳陣列）
  const allSheets = ss.getSheets();
  console.log(`檔案中共有 ${allSheets.length} 張工作表`);
}

核心管理函數說明

• getSheetByName(name)：傳入工作表名稱（分頁標籤上的字），回傳該工作表物件。若找不到則回傳 null。
• insertSheet(name)：新增一張工作表，可指定名稱。
• deleteSheet(sheetObject)：刪除指定的工作表物件。
• setName(newName)：修改工作表的名稱。

跨工作表操作 (Cross-sheet Operations)

自動化最常見的情境就是：將資料從 A 工作表讀取處理後，寫入 B 工作表（例如從「訂單明細」匯總到「月報表」）。

/**
 * 將「資料庫」分頁的內容複製到「備份」分頁
 */
function copyDataBetweenSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('資料庫');
  let targetSheet = ss.getSheetByName('備份');

  // 如果備份分頁不存在，就建立一個
  if (!targetSheet) {
    targetSheet = ss.insertSheet('備份');
  }

  // 讀取來源資料
  const data = sourceSheet.getDataRange().getValues();

  // 清除目標分頁舊資料並寫入新資料
  targetSheet.clear(); 
  targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
  
  console.log('跨工作表複製完成');
}

跨工作表常用函數說明

• clear()：清除工作表中所有的內容與格式。
• getDataRange()：自動取得工作表中有資料的整個矩形範圍（非常方便，不用手動輸入 A1:Z100）。
• copyTo(destination)：將整個工作表複製到另一個試算表或本檔案中。

基礎讀取資料

讀取資料通常包含三個步驟：取得檔案 -> 取得工作表 -> 取得範圍 -> 取得值。

/**
 * 讀取試算表中的單格與範圍資料
 */
function readSheetData() {
  // 1. 取得當前啟用的試算表
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 2. 透過名稱切換到特定的工作表
  const sheet = ss.getSheetByName('工作表1');
  
  // 3. 取得 A1 儲存格的值
  const a1Value = sheet.getRange('A1').getValue();
  console.log('A1 的值為:', a1Value);
  
  // 4. 取得 A1:B10 整個區塊的資料 (回傳二維陣列)
  const dataRange = sheet.getRange('A1:B10').getValues();
  
  // 遍歷讀取到的二維陣列
  dataRange.forEach(row => {
    console.log('欄位 A:', row[0], '欄位 B:', row[1]);
  });
}

核心讀取函數說明

getRange()：取得範圍

這是操作試算表最常用到的起手式，有多種參數傳遞方式：

• sheet.getRange(address)：使用 A1 標記法，例如 getRange('A1:B10')。
• sheet.getRange(row, column)：取得單一儲存格，例如 getRange(1, 1) 是 A1。
• sheet.getRange(row, column, numRows)：取得特定列數的區塊。
• sheet.getRange(row, column, numRows, numColumns)：取得指定列數與欄數的區塊。

getValue() 與 getValues()

• getValue()：取得範圍中「最左上角」格子的值。
• getValues()：取得整個範圍的資料，回傳為「二維陣列」。

基礎寫入資料

寫入資料與讀取類似，差別在於最後使用 setValue() 或 setValues()。

/**
 * 將資料寫入試算表
 */
function writeSheetData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // 寫入單一儲存格
  sheet.getRange('C1').setValue('處理狀態');
  
  // 寫入整列資料 (傳入二維陣列，一組代表一列)
  const newData = [
    ['Mike', 25, '工程師'],
    ['Sara', 30, '設計師']
  ];
  
  // 指定寫入範圍：從 A2 開始，高度 2 列，寬度 3 欄
  sheet.getRange(2, 1, 2, 3).setValues(newData);
  
  // 在底端直接新增一列
  sheet.appendRow(['Jack', 28, '產品經理']);
}

寫入技巧：appendRow()

如果你只想在資料表的最後一列接續寫入，appendRow(array) 是最快的方式，它會自動找到最後一列並填入資料，不需要計算範圍。

核心寫入函數說明

setValue() 與 setValues()

• setValue(value)：將特定值寫入儲存格。
• setValues(values)：將「二維陣列」一次性寫入相對應的範圍。注意：陣列的大小必須與 getRange() 取得的範圍大小完全一致，否則會報錯。

appendRow(rowContents)

• 參數為一個「一維陣列」，代表整列的內容。例如 appendRow(['John', 25])。會自動在工作表的最後一列後方新增。

效能建議：減少呼叫次數

這是 GAS 開發中最重要的一點。每一次 getValue() 或 setValue() 都是一次網路請求，如果放在迴圈中執行，效能會非常差。

• Bad: 在 100 次的迴圈中分別呼叫 100 次 setValue()。
• Good: 先在 JavaScript 的陣列中處理好資料，最後一次呼叫 setValues() 寫入。

/**
 * 錯誤範例：在迴圈中頻繁寫入 (速度慢)
 */
function slowPerformance() {
  const sheet = SpreadsheetApp.getActiveSheet();
  for (let i = 1; i <= 100; i++) {
    sheet.getRange(i, 1).setValue(i); // 100 次網路請求
  }
}

/**
 * 推薦做法：批次處理 (速度快)
 */
function fastPerformance() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = [];
  for (let i = 1; i <= 100; i++) {
    values.push([i]); // 先存在陣列中
  }
  sheet.getRange(1, 1, 100, 1).setValues(values); // 僅 1 次網路請求
}