Google 試算表：取得儲存格範圍 getRange()

 

如果你常用 Google 試算表做報表、匯整名單或自動化，getRange() 幾乎每天都會碰到。它就像是「先把資料框起來」的那一步：你想讀值、改格式、丟公式、搬資料，都得先抓對區塊。

本篇文章會盡可能說明內容，說明A1 表示法怎麼寫、列欄為什麼從 1 開始、整塊讀寫為什麼快很多；也會示範常見情境，例如只取到最後一列的有效資料、一次處理多個不連續區域，還彙整常見地雷（例如 setValues 維度不合、範圍越界、被保護欄位寫不進去）。先穩穩抓到對的 Range，剩下的事就好辦了。希望本篇文章能夠幫助到需要的您。

目錄

{tocify} $title={目錄} 

為什麼是 getRange()？

在 GAS 操作 Google 試算表時，你做的 8 成動作都繞不開它：讀取儲存格值、整塊資料的批次存取、格式調整、公式填入、保護範圍、甚至資料清洗前的切片，第一步通常都是「先抓到一塊 Range」。抓得又快又準，寫起來就順、效能也會好。

快速上手：四種常用呼叫方式

getRange() 會因為你是對哪個物件呼叫而有些差異，但最常見的是對 Sheet 物件呼叫（某一個工作表）。以下為四個超高頻寫法：

1.    getRange(a1Notation: string)

    範例：sheet.getRange('B2')、sheet.getRange('A2:D100')、

                sheet.getRange('A:A')、sheet.getRange('2:2')

    特色：人類可讀性高、搭配命名範圍與工作表名稱方便（'工作表 1'!A1:C5）

2.    getRange(row: number, column: number)

    範例：sheet.getRange(2, 2) 代表 B2

    特色：索引從 1 開始（不是 0），適合計算後定位單一格

3.    getRange(row: number, column: number, numRows: number)

    範例：sheet.getRange(2, 1, 10) 代表自 A2 起往下 10 列（單欄）

    特色：易用於動態列數

4.    getRange(row: number, column: number, numRows: number, numColumns: number)

    範例：sheet.getRange(2, 1, 100, 4) 代表 A2:D101（100 列 × 4 欄）

    特色：最通用，後續 getValues()/setValues() 批次處理超順手

延伸：Spreadsheet 物件還有 getRangeByName(name) 可抓「命名範圍」，以及 getRangeList([a1, a1, ...]) 一次抓多塊不連續區域。

介面到程式：操作流程

步驟 1｜建立試算表與資料

    1.    在 Google 試算表建立一個活頁簿，新增工作表「資料」與「報表」。

    2.    在「資料」貼入範例資料（A1:D 若干列），第一列當標題。

步驟 2｜開啟 GAS 專案

    試算表上方選單：

        擴充功能 → App Script 進入編輯器（新介面叫 Apps Script Editor）。

步驟 3｜取得工作表物件與 Range

function demoGetRangeBasics() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('資料');

  // A1 表示法
  const r1 = sheet.getRange('B2');              // 單一儲存格
  const r2 = sheet.getRange('A2:D100');         // 連續區域
  const r3 = sheet.getRange('A:A');             // 整欄
  const r4 = sheet.getRange('2:2');             // 整列

  // 數字索引（1-based）
  const r5 = sheet.getRange(2, 2);              // B2
  const r6 = sheet.getRange(2, 1, 10);          // A2 開始往下 10 列
  const r7 = sheet.getRange(2, 1, 100, 4);      // A2:D101
}

步驟 4｜讀寫資料的正確姿勢（批次）

function readWriteBatch() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('資料');

  // 找到有效資料區（不含空白尾端）
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();  // 2D 陣列：rows × columns

  // 例：在最後一欄新增「合計 = 價格 × 數量」
  const headers = values[0];
  const priceIdx = headers.indexOf('價格');
  const qtyIdx   = headers.indexOf('數量');

  if (priceIdx === -1 || qtyIdx === -1) {
    throw new Error('找不到價格或數量欄位');
  }

  // 產出新矩陣（避免逐格 setValue，效能差）
  const out = values.map((row, i) => {
    if (i === 0) return row.concat(['合計']);
    const price = Number(row[priceIdx]) || 0;
    const qty   = Number(row[qtyIdx])   || 0;
    return row.concat([price * qty]);
  });

  // 把 out 一次寫回「報表」工作表
  const outSheet = ss.getSheetByName('報表') || ss.insertSheet('報表');
  outSheet.clearContents();
  outSheet.getRange(1, 1, out.length, out[0].length).setValues(out);
}

重點：避免在迴圈中逐格 getValue()/setValue()。用一次抓/一次寫的 2D 陣列，延遲授權與 IO 次數，速度會快非常多。

A1 表示法與命名範圍：寫得準、維護也輕鬆

A1 表示法訣竅

    工作表名稱含空白或特殊字元，要加單引號：sheet.getRange("'2025 報表'!A1:C5")

    整欄：A:A，整列：1:1

    不要混用逗號與冒號：範圍區間一律 起點:終點，例如 A1:D10

命名範圍

    在試算表：資料 → 命名範圍 新增，例如命名 DataArea

    GAS 讀取：

function readNamedRange() {
  const ss = SpreadsheetApp.getActive();
  const range = ss.getRangeByName('DataArea'); // 回傳 Range
  const values = range.getValues();
  Logger.log(values.length);
}

命名範圍的好處是：移動或擴充資料時，程式碼不用改 A1，維護省心。

動態範圍選取：最後一列／欄、去頭去尾、避開空列

最後一列與最後一欄

const lastRow = sheet.getLastRow();
const lastCol = sheet.getLastColumn();
const body = sheet.getRange(2, 1, Math.max(lastRow - 1, 0), lastCol); // 排除標題列

注意：若表中只有標題，lastRow 可能是 1，記得 Math.max() 避免負數列數。

以關鍵欄判斷資料長度

function getBodyByAnchorColumn(sheet, anchorCol = 1) {
  const colVals = sheet.getRange(2, anchorCol, sheet.getMaxRows() - 1, 1).getValues();
  const filled = colVals.findIndex(row => row[0] === '' || row[0] === null);
  const rows = (filled === -1) ? colVals.length : filled;
  return sheet.getRange(2, 1, rows, sheet.getLastColumn());
}

這種做法適合「中間可能有空白，但以第一欄為主索引」的表。

Offset 與交集

const base = sheet.getRange('A1:D20');
const sub  = base.offset(1, 0, base.getNumRows() - 1, base.getNumColumns()); // 去掉標題

效能與架構：避免慢到爆的 4 個原則

1.    減少呼叫次數

    把讀寫合成批次：同一塊 Range 一次 getValues()，處理成新 2D 陣列後一次 setValues()。

2.    少用逐格迴圈

    不要在 for 迴圈內每格 getValue()；把資料帶回本地記憶體處理。

3.    用 flush() 做必要的落盤

    在需要立即反映（例如立刻讀到剛寫入的公式結果）時再 SpreadsheetApp.flush()，不要濫用。

4.    運用觸發器與分段

    超大表可拆批或改為時間驅動觸發器定時處理，避免單次腳本超時（6 分鐘～30 分鐘上限視執行環境而定）。

情境實戰

實戰一：依條件複製指定列到報表

function filterAndCopy() {
  const ss = SpreadsheetApp.getActive();
  const src = ss.getSheetByName('資料');
  const dst = ss.getSheetByName('報表') || ss.insertSheet('報表');

  const srcData = src.getDataRange().getValues();
  const header = srcData[0];
  const statusIdx = header.indexOf('狀態');
  if (statusIdx === -1) throw new Error('缺少「狀態」欄');

  const rows = srcData.filter((r, i) => i === 0 || r[statusIdx] === '核准'); // 含標題
  dst.clearContents();
  dst.getRange(1,1,rows.length,rows[0].length).setValues(rows);
}

實戰二：整欄自動填入遞增代碼

function fillIncrementalCode() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('資料');
  const body = sheet.getRange(2, 1, Math.max(sheet.getLastRow()-1, 0), 1);
  const n = body.getNumRows();
  if (n <= 0) return;

  const base = 1000; // 起始碼
  const codes = Array.from({length: n}, (_, i) => [`DOC-${base + i + 1}`]);
  body.setValues(codes);
}

實戰三：多區塊一次抓（getRangeList）

function multiRanges() {
  const sh = SpreadsheetApp.getActiveSheet();
  const ranges = sh.getRangeList(['A1:A10', 'C1:C10', 'E1:E10']).getRanges();
  ranges.forEach(r => r.setBackground('#FFFDE7'));
}

除錯與開發流程建議

Logger / Execution log：

         Logger.log(obj)，或用 console.log()（新執行記錄也支援），觀察輸入輸出長相。

試資料夠小：先用 10 筆驗證流程再擴大跑全表。

保留檔案備份：

        大動作前複製活頁簿，或用版本歷史記錄可逆。

權限宣告：

        第一次操作寫入/保護等動作會跳權限同意，務必在正式前跑一次綁定。

常見錯誤與雷點

1.「Range not found / 找不到範圍」

情境 : 

    getRange("'月報 2025'!A1:C5")，但工作表其實叫「月報2025」（少空格）

    A1 表示法打成 A1, C5（逗號）

修法 :

    確認工作表名稱並以單引號包起來（含空白或特殊字元）。

    範圍用冒號 A1:C5，多區塊要用 getRangeList()。

2.「The coordinates of the range are outside the dimensions of the sheet」

情境 :

    getRange(2,1,1000,10) 但工作表只有 200 列或 5 欄

修法 :

    先以 getMaxRows()/getMaxColumns() 或 getLastRow()/getLastColumn() 動態計算；必要時 insertRows/insertColumns 補齊。

3.「The number of columns in the data does not match the number of columns in the range」（setValues 維度不符）

情境 :

    range.setValues(out) 時，out[0].length !== range.getNumColumns() 或 out.length !== range.getNumRows()

修法 :

    先 Logger.log(out.length, out[0].length) 與 range.getNumRows()/getNumColumns() 比對。

    若要動態寫入，先依 out 的大小建立對應 Range：

    sheet.getRange(startRow, startCol, out.length, out[0].length).setValues(out)

4. 以為索引從 0 開始（其實從 1）

情境 : 

    getRange(0, 0) 直接炸

修法 :

    永遠記住：GAS 的列、欄皆為 1-based。

5. getValue()/setValue() 在大表中用迴圈造成超時

情境 :

    幾千幾萬格逐一讀寫，執行時間暴增

修法 :

    改一次 getValues() → 計算 → 一次 setValues()。

    真要逐格，多用陣列操作在本地組好再一次寫回。

6. 合併儲存格導致資料對齊錯位

情境 :

    合併後 getValues() 回傳的 2D 陣列不是你想的矩形分布

修法 : 

    先 range.breakApart() 取消合併，處理完再視需要重合併。

7. 篩選檢視（Filter Views）/ 隱藏列影響寫入位置

情境 :

    視覺上第 5 列是你看到的第 3 筆，但實際列號仍是 5

修法 :

    getRange() 永遠以真實列號為準；若要「看得到的」範圍，先以條件把資料抽出後寫入新表。

8. 保護範圍寫不進去

情境 :

    指定的 Range 被保護或你沒有權限

修法 :

    取消保護、或確認腳本執行身分（安裝觸發器可指定作者身分執行）；或改寫入不受保護的區塊。

9.  getLastRow() 被「格式」誤判

情境 :

    尾端有殘留格式（背景色/框線），getLastRow() 以為有資料

修法 :

    尾端清除格式 clearFormat()，或以關鍵欄內容判斷（讀取該欄 getValues() 檢查非空）。

10. 日期/數字型別錯亂

情境 :

    getDisplayValues() 拿到字串再計算失真；或 getValues() 的 Date 物件被當字串處理

修法 :

    計算用 getValues()；顯示或輸出 CSV 才用 getDisplayValues()。需要時明確 Number()/String()/Utilities.formatDate() 轉型。