TL;DR: Cách khắc phục nhanh
Đừng cố định tọa độ dải ô của bạn nữa. Lỗi này hầu như luôn có nghĩa là mảng dữ liệu của bạn có số hàng khác với khu vực bảng tính mà bạn nhắm tới. Để khắc phục, hãy để dữ liệu tự động xác định kích thước dải ô.
// Tránh cách này: nó sẽ lỗi nếu dữ liệu của bạn tăng lên
// sheet.getRange("A1:B10").setValues(data);
// Sử dụng cách này: nó tự thích ứng với kích thước dữ liệu
if (data && data.length > 0) {
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
Nguyên nhân gốc rễ
Phương thức setValues() của Google cực kỳ khắt khe. Nó yêu cầu một sự khớp nối hình chữ nhật hoàn hảo giữa mảng 2D và dải ô đích. Nếu mảng của bạn chứa 500 hàng dữ liệu nhưng lệnh gọi getRange() chỉ bao phủ 499 hàng, tập lệnh sẽ dừng ngay lập tức.
Dưới đây là những trường hợp thường gây ra lỗi:
- Dải ô tĩnh: Bạn cố định một dải ô như
"A2:C100", nhưng nguồn dữ liệu của bạn vừa tăng lên 101 hàng. - Không khớp kích thước: Bạn đang truyền một danh sách đơn giản như
[1, 2, 3]thay vì định dạng mảng lồng nhau bắt buộc như[[1], [2], [3]]. - Bộ lọc trống: Bạn đã lọc tập dữ liệu của mình, nhưng kết quả trả về nhỏ hơn dải ô ban đầu mà bạn đã xóa.
Các giải pháp đáng tin cậy
1. Định cỡ dải ô động
Tính toán hàng hoặc cột cuối cùng bằng cách thủ công rất dễ gây ra lỗi. Thay vào đó, hãy sử dụng thuộc tính length của mảng. Điều này cho trang tính biết chính xác cần bao nhiêu không gian cho dữ liệu của bạn.
function updateSheetSafely(data) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Report");
// Câu lệnh bảo vệ để ngăn lỗi khi tập dữ liệu trống
if (!data || data.length === 0) return;
const startRow = 1;
const startCol = 1;
const numRows = data.length;
const numCols = data[0].length;
// Dải ô này sẽ luôn mở rộng hoặc thu hẹp để khớp với dữ liệu của bạn
sheet.getRange(startRow, startCol, numRows, numCols).setValues(data);
}
2. Ánh xạ mảng 1 chiều sang 2 chiều
Apps Script coi ["Value 1", "Value 2"] là một danh sách phẳng, không phải một lưới tọa độ. setValues() sẽ từ chối định dạng này. Bạn phải bao bọc dữ liệu của mình để tạo cho nó "chiều cao" hoặc "chiều rộng".
// Để ghi dưới dạng một HÀNG đơn lẻ (1 hàng, 3 cột)
const rowData = [["Value 1", "Value 2", "Value 3"]];
sheet.getRange(1, 1, 1, rowData[0].length).setValues(rowData);
// Để ghi dưới dạng một CỘT đơn lẻ (3 hàng, 1 cột)
const flatArray = ["A", "B", "C"];
const colData = flatArray.map(item => [item]); // Kết quả: [["A"], ["B"], ["C"]]
sheet.getRange(1, 1, colData.length, 1).setValues(colData);
3. Gỡ lỗi sai lệch một đơn vị (Off-by-one)
Khi tạo mảng bên trong các vòng lặp, bạn rất dễ vô tình thêm một hàng trống ở cuối. Sử dụng console.log() để kiểm tra kích thước ngay trước khi tập lệnh bị lỗi. Điều này giúp tiết lộ chính xác những gì tập lệnh đang thấy.
console.log("Số hàng của mảng: " + data.length);
console.log("Số cột của mảng: " + data[0].length);
// Nếu các con số này không khớp với dải ô của bạn, tập lệnh sẽ thất bại.
Cách xác minh bản sửa lỗi
Kiểm tra ba điều sau để đảm bảo tập lệnh của bạn đã sẵn sàng vận hành:
- Nhật ký thực thi: Tìm dấu kiểm màu xanh "Hoàn thành". Nếu bạn thấy "Thất bại", kích thước vẫn chưa khớp.
- Ranh giới trang tính: Xác minh hàng cuối cùng của dữ liệu. Một dải ô động đảm bảo dữ liệu khớp hoàn hảo mà không để lại khoảng trống hoặc ghi đè sai ô.
- Kiểm tra tải: Chạy tập lệnh với một hàng duy nhất, sau đó thử với 1.000 hàng. Một tập lệnh mạnh mẽ sẽ xử lý được cả hai trường hợp mà không cần thay đổi mã thủ công.
Mẹo chuyên nghiệp: Tránh dữ liệu "ma"
Dải ô động giải quyết được lỗi dừng chương trình, nhưng chúng có thể để lại dữ liệu cũ. Nếu tập dữ liệu mới của bạn có 10 hàng và tập dữ liệu cũ có 50 hàng, setValues() chỉ ghi đè lên 10 hàng đầu tiên. Hãy luôn xóa khu vực đích trước để giữ cho báo cáo của bạn sạch sẽ.
// Xóa mọi thứ trước khi ghi dữ liệu mới
sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).clearContent();
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);