做報表時，最花時間的往往不是公式，而是把資料塞進表格。與其一格一格慢慢寫，不如一次把整塊丟進去──這就是 setValues() 的強項。

本篇文章盡可能說明內容，包括為什麼一定要二維陣列、尺寸怎麼對齊、日期和數字怎麼避免被當成文字；另外也整理了範例與踩雷清單：合併儲存格寫不進去、公式被覆蓋、區域格式害小數點亂掉、觸發器撞車等，都有對應修法。

想把腳本跑得又快又穩，本篇文章會是你少走彎路的捷徑。希望能幫助到需要的您。


function demoSetValuesQuickStart() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('測試') || ss.insertSheet('測試');
  const data = [
    ['品項', '數量', '單價', '小計'],
    ['鉛筆', 10, 5, '=B3*C3'],
    ['原子筆', 8, 12, '=B4*C4'],
    ['筆記本', 3, 45, '=B5*C5'],
    ['資料夾', 6, 20, '=B6*C6'],
  ];
  sh.getRange(2, 1, data.length, data[0].length).setValues(data);
}

觀察點：

陣列第一列是標題；後面三列是資料與公式。

公式會直接寫入並在工作表端計算。

常見情境與範例

覆寫一塊表格（固定範圍）


function writeFixedBlock() {
  const sh = SpreadsheetApp.getActiveSheet();
  const values = [
    ['部門', '人數', '平均績效'],
    ['銷售', 12, 4.2],
    ['行銷', 8, 4.0],
    ['研發', 15, 4.5],
  ];
  sh.getRange(1, 1, values.length, values[0].length).setValues(values);
}

依資料長度動態決定範圍


function writeDynamicBlock() {
  const sh = SpreadsheetApp.getActiveSheet();
  const rows = Array.from({ length: 500 }, (_, i) => [i + 1, `Item-${i+1}`, Math.random()]);
  sh.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
}

追加寫入（Append Rows）

setValues() 不會自動往下加行，需要計算起始列。


function appendRowsWithSetValues() {
  const sh = SpreadsheetApp.getActiveSheet();
  const last = Math.max(1, sh.getLastRow());
  const newRows = [
    ['2025-10-01', '訂單A', 1200],
    ['2025-10-02', '訂單B', 980],
  ];
  sh.getRange(last + 1, 1, newRows.length, newRows[0].length).setValues(newRows);
}

多工作表同步寫入


function writeMultipleSheets() {
  const ss = SpreadsheetApp.getActive();
  const data = [
    ['月份', '營收'],
    ['Jan', 120000],
    ['Feb', 135000],
  ];
  ['北區','中區','南區'].forEach(name => {
    const sh = ss.getSheetByName(name) || ss.insertSheet(name);
    sh.getRange(1, 1, data.length, data[0].length).setValues(data);
  });
}

由 JSON 陣列轉二維陣列


function writeFromJson() {
  const records = [
    { sku:'P-001', name:'Mouse', price:350, qty:3 },
    { sku:'P-002', name:'Keyboard', price:890, qty:1 },
  ];
  const header = ['SKU', '品名', '單價', '數量', '小計'];
  const body = records.map(r => [r.sku, r.name, r.price, r.qty, r.price * r.qty]);
  const data = [header, ...body];
  const sh = SpreadsheetApp.getActiveSheet();
  sh.getRange(1, 1, data.length, data[0].length).setValues(data);
}

寫入日期與時間（避免被當文字）


function writeDates() {
  const sh = SpreadsheetApp.getActiveSheet();
  const tz = Session.getScriptTimeZone(); // 與試算表時區一致較準
  const today = new Date(); // Date 物件才能被當作日期
  const rows = [
    ['發票日', '交付日'],
    [today, new Date(today.getTime() + 3*24*3600*1000)]
  ];
  sh.getRange(1,1,rows.length, rows[0].length).setValues(rows);
  sh.getRange(2,1,1,2).setNumberFormats([['yyyy-mm-dd','yyyy-mm-dd']]); // 避免顯示亂碼
}

寫入公式（含跨列計算）


function writeWithFormulas() {
  const sh = SpreadsheetApp.getActiveSheet();
  const values = [
    ['姓名','國文','英文','數學','平均'],
    ['小張', 80, 75, 90, '=AVERAGE(B2:D2)'],
    ['小美', 92, 88, 85, '=AVERAGE(B3:D3)'],
  ];
  sh.getRange(1,1,values.length, values[0].length).setValues(values);
}

清空／空值處理（null vs ' '）


function clearWithSetValues() {
  const sh = SpreadsheetApp.getActiveSheet();
  // null 會變成「真正的空白」；'' 是空字串（某些函數會視為長度 0 的文字）
  const values = [
    ['A','B','C'],
    [null, '', 123],
  ];
  sh.getRange(1,1,values.length, values[0].length).setValues(values);
}

RichText（連結與局部字型）


function writeRichText() {
  const sh = SpreadsheetApp.getActiveSheet();
  const rt = SpreadsheetApp.newRichTextValue()
    .setText('前往官網')
    .setLinkUrl('https://example.com')
    .build();
  sh.getRange(1,1,1,1).setValues([[rt]]);
}

效能最佳化：讓腳本穩、快、不超時

1. 一次取資料、一次寫資料：

把多次 getValue()/setValue() 改為單次 getValues()/setValues()。

2. 避免逐列呼叫：

不要在迴圈內一直 setValue()。先組成大二維陣列再一次 setValues()。

3. 分批寫入：>5 萬列或運算量大時，切成每批 1,000～5,000 列寫入。

4. 減少讀寫交錯：先讀完整、在記憶體處理好，再一次寫回。

5. 必要時 flush()：大量連續操作後需要確保生效再讀取結果時使用。

6. 避免 SpreadsheetApp.getUi() 等前端互動：會拖慢並影響自動化。

7. 控制觸發器頻率：時間驅動觸發器請留緩衝，避免同時段多腳本搶鎖。

資料型態與格式細節

文字：正常字串即可；需前置零（如代碼）請設格式為純文字或在字串前加 '（或後設 setNumberFormat('@')）。

數字：寫入 number；避免用字串 "123"，會受地區格式影響。

日期/時間：務必用 Date 物件；再用 setNumberFormat() 指定顯示樣式。

布林：true / false 可直接寫入並與 FILTER/QUERY 配合。

公式：直接放字串（如 '=SUM(A2:A10)'）。若需整欄延伸，評估在表內用 ARRAYFORMULA。

換行：字串內 \n 可在儲存格顯示換行，記得啟用「自動換行」。

合併儲存格：setValues() 寫合併區塊常噴錯，先取消合併再寫入，再視覺需求合併。

資料驗證/保護：受保護範圍或驗證規則可能擋寫入，先確認權限與規則。

錯誤訊息大全與排錯清單

A. 尺寸不一致

訊息：columns/rows do not match…

原因：getRange(r,c,rows,cols) 的 rows/cols 與 values 的二維尺寸不一致。

解法：統一用 data.length 與 data[0].length 取得尺寸，不手打數字。

B. 類型不支援 / 非法值

案例：傳入物件、函式或未定義（undefined）。

解法：轉成可寫型（字串、數字、布林、Date、RichTextValue、null）。

C. 目標範圍錯誤

訊息：Range not found / 寫到保護範圍

解法：確認工作表存在、範圍起點與尺寸、權限與保護設定。

D. 合併儲存格導致寫入失敗

解法：range.breakApart() 先取消合併，寫完再 range.merge()（視覺需求才合併）。

E. 超時／配額

症狀：腳本跑很久、觸發器經常失敗

解法：分批寫入、減少 API 往返、使用快取與中間計算、必要時改 Sheets API 批次。

常見雷點整理

1. 尺寸不合：setValues() 的陣列尺寸與範圍不一致 → 用 data.length / data[0].length 動態計，別手打。

2. 不規則列：有些列缺欄 → 在組陣列前 map 補 null。

3. 合併儲存格：批次寫入常失敗 → 先 breakApart() 再寫入。

4. 把日期當字串寫：導致排序/樞紐分析異常 → 用 Date 物件 + setNumberFormat()。

5. 逐格寫入：慢、容易超時 → 一次組好二維陣列再 setValues()。

6. 空白處理錯誤：'' 與 null 混用 → 規範：空格用 null，保留「空字串」才用 ''。

7. 受保護範圍：寫不進去 → 先檢查保護規則或請管理者放行。

8. 公式位移：寫入覆蓋到原公式 → 把公式搬到新欄或改用 ARRAYFORMULA。

9. 地區格式：不同地區小數點/千分位不同 → 統一 setNumberFormat() 與欄位格式。

10. 超大資料一次灌：超時或當機 → 分批寫（1k～5k 列/批），或換 Sheets API v4 批次。

11. 多重觸發器撞車：兩支程式同時寫 → 加鎖或錯峰執行。

12. 被條件式格式拖慢：海量條件式計算 → 先停用或改精簡規則，寫完再恢復。

Google 試算表：設定儲存格範圍的值 setValues()

目錄

什麼是 setValues() ? 為什麼它比逐格寫入快百倍 ?

基本觀念與語法：二維陣列、範圍尺寸必須完全吻合

新手快速上手：最小可行範例

把 A2:D6 一次寫滿資料。