Sửa lỗi #REF trong Google Trang tính

Isanes Francois

13:57:50 Thứ Tư, 17 tháng 4, 2024

Tìm hiểu các vấn đề về tệp đính kèm của Google Trang tính

Khi làm việc với Google Sheets, một nhiệm vụ phổ biến là gửi dữ liệu trang tính dưới dạng tệp đính kèm Excel qua email. Quá trình này có thể được sắp xếp hợp lý bằng cách sử dụng Google Apps Script, cho phép người dùng gửi nhiều trang trong một email. Tuy nhiên, các vấn đề có thể phát sinh, chẳng hạn như lỗi #REF, lỗi này thường biểu thị sự cố tham chiếu trong dữ liệu đang được xuất.

Vấn đề này thường biểu hiện khi Google Trang tính sử dụng các công thức phức tạp như QUERY(), không dịch tốt khi trang tính được chuyển đổi sang định dạng Excel. Lỗi này làm gián đoạn tính toàn vẹn của dữ liệu trong tệp đính kèm, gây ra thách thức đáng kể cho người dùng dựa vào các bản xuất này để báo cáo hoặc phân tích.

Yêu cầu	Sự miêu tả
SpreadsheetApp.getActiveSpreadsheet()	Lấy bảng tính đang hoạt động mà tập lệnh được liên kết.
spreadSheet.getSheetByName(sheet).getSheetId()	Trả về mã định danh duy nhất cho một trang tính trong bảng tính.
UrlFetchApp.fetch(url, params)	Thực hiện yêu cầu tới một URL được chỉ định bằng cách sử dụng nhiều tham số khác nhau để quản lý yêu cầu HTTP.
Utilities.sleep(milliseconds)	Tạm dừng việc thực thi tập lệnh trong một số mili giây được chỉ định để tránh đạt giới hạn tốc độ API.
ScriptApp.getOAuthToken()	Truy xuất mã thông báo OAuth 2.0 cho người dùng hiện tại để xác thực yêu cầu.
getBlob()	Lấy dữ liệu của tệp được tìm nạp từ URL dưới dạng blob, được sử dụng để đính kèm tệp vào email.

Giải thích chức năng tập lệnh

Tập lệnh được cung cấp được thiết kế để tự động hóa quá trình gửi nhiều Trang tính Google dưới dạng tệp đính kèm Excel trong một email. Nó bắt đầu bằng việc khai báo một mảng tên trang tính dành cho xuất khẩu. Tập lệnh truy xuất bảng tính đang hoạt động và lặp qua mảng tên trang tính để tạo URL tải xuống cho mỗi trang tính. Các URL này được định dạng cụ thể để xuất trang tính dưới dạng tệp Excel. Việc sử dụng 'Utilities.sleep(10000);' Điều quan trọng ở đây là tạo ra độ trễ giữa các yêu cầu tìm nạp, giúp quản lý tải trên máy chủ của Google và ngăn tập lệnh đạt đến giới hạn tốc độ.

Mỗi URL tìm nạp trang tính tương ứng dưới dạng blob, sau đó được đặt tên theo mảng tên tệp được xác định trước. Bước này rất quan trọng vì nó chuyển đổi dữ liệu từ trang tính sang định dạng phù hợp với tệp đính kèm email. Sau khi chuẩn bị tất cả các đốm màu tệp, tập lệnh sẽ xây dựng một đối tượng email với người nhận được chỉ định, dòng chủ đề và nội dung thư. Các đốm màu được đính kèm vào email này, sau đó được gửi bằng 'MailApp.sendEmail(message);' yêu cầu. Chức năng này là một phần của dịch vụ MailApp của Google Apps Script, cho phép các tập lệnh gửi email, lời mời và thông báo.

Giải quyết lỗi #REF của Google Trang tính khi xuất

Giải pháp tập lệnh Google Apps

function sendExcelAttachmentsInOneEmail() {
  var sheets = ['OH INV - B2B', 'OH INV - Acc', 'OH INV - B2C', 'B2B', 'ACC', 'B2C'];
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadSheetId = spreadSheet.getId();
  var urls = sheets.map(sheet => {
    var sheetId = spreadSheet.getSheetByName(sheet).getSheetId();
    return \`https://docs.google.com/spreadsheets/d/${spreadSheetId}/export?format=xlsx&gid=${sheetId}\`;
  });
  var reportName = spreadSheet.getSheetByName('IMEIS').getRange(1, 14).getValue();
  var params = {
    method: 'GET',
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };
  var fileNames = ['OH INV - B2B.xlsx', 'OH INV - Acc.xlsx', 'OH INV - B2C.xlsx', 'B2B.xlsx', 'ACC.xlsx', 'B2C.xlsx'];
  var blobs = urls.map((url, index) => {
    Utilities.sleep(10000);  // Delay added to avoid hitting rate limits
    var response = UrlFetchApp.fetch(url, params);
    return response.getBlob().setName(fileNames[index]);
  });
  var message = {
    to: 'email@domain.com',
    cc: 'email@domain.com',
    subject: 'Combined REPORTS - ' + reportName,
    body: "Hi Team,\n\nPlease find attached Reports.\n\nBest Regards!",
    attachments: blobs
  }
  MailApp.sendEmail(message);
}

Thông tin chi tiết nâng cao về các vấn đề xuất của Google Trang tính

Việc xuất dữ liệu từ Google Trang tính sang định dạng Excel bằng tập lệnh có thể bộc lộ sự phức tạp tiềm ẩn trong việc quản lý dữ liệu, đặc biệt là khi sử dụng các hàm nâng cao như QUERY(). Lỗi #REF gặp phải trong các lần xuất như vậy thường biểu thị các tham chiếu chưa được giải quyết trong môi trường Excel, không xảy ra trong chính Google Trang tính. Sự chênh lệch này thường phát sinh do một số chức năng trong Google Trang tính, như một số thao tác QUERY() hoặc tập lệnh tùy chỉnh nhất định, không được hỗ trợ hoặc hoạt động khác trong Excel.

Vấn đề này nhấn mạnh tầm quan trọng của việc đảm bảo tính tương thích giữa các công thức của Google Trang tính và việc xử lý các công thức cũng như truy vấn dữ liệu của Excel. Các nhà phát triển thường phải triển khai các bước kiểm tra bổ sung hoặc các phương pháp thay thế để đảm bảo tính toàn vẹn của dữ liệu khi chuyển từ môi trường của Google sang môi trường của Microsoft, đặc biệt khi tự động hóa các quy trình như tệp đính kèm email của dữ liệu bảng tính.

Các truy vấn phổ biến trên Google Sheets Scripting

Câu hỏi: Tại sao xuất hiện lỗi #REF khi xuất từ Google Sheets sang Excel?
Trả lời: Lỗi #REF thường xảy ra do một số tham chiếu hoặc công thức nhất định trong Google Trang tính không được nhận dạng hoặc không tương thích với môi trường công thức của Excel.
Câu hỏi: Làm cách nào tôi có thể ngăn chặn việc đạt đến giới hạn tốc độ với Tập lệnh Google Apps?
Trả lời: Việc triển khai tạm dừng trong tập lệnh bằng Utilities.sleep(mili giây) có thể giúp quản lý tần suất yêu cầu và tránh vượt quá giới hạn tốc độ của Google.
Câu hỏi: muteHttpExceptions làm gì trong lệnh gọi tìm nạp URL?
Trả lời: Nó cho phép tập lệnh tiếp tục thực thi mà không đưa ra ngoại lệ nếu yêu cầu HTTP không thành công, hữu ích trong việc quản lý lỗi một cách khéo léo.
Câu hỏi: Tôi có thể tùy chỉnh tên file của từng sheet khi xuất sang Excel không?
Trả lời: Có, bạn có thể đặt tên tùy chỉnh cho từng đốm màu được chuyển đổi từ trang tính trước khi đính kèm chúng vào email, như minh họa trong tập lệnh.
Câu hỏi: Có cách nào để xuất trực tiếp Google Sheets sang Excel mà không cần tập lệnh trung gian không?
Trả lời: Có, bạn có thể tải xuống Google Trang tính ở định dạng Excel theo cách thủ công trực tiếp từ menu Tệp trong Google Trang tính, nhưng việc tự động hóa việc này yêu cầu phải viết tập lệnh.

Những hiểu biết cuối cùng về những thách thức khi xuất khẩu trang tính

Thông qua việc khám phá này, có thể thấy rõ rằng mặc dù Google Apps Script cung cấp các công cụ mạnh mẽ để tự động hóa và nâng cao các chức năng của Google Trang tính nhưng vẫn có một số vấn đề phức tạp nhất định phát sinh khi giao tiếp với các nền tảng khác nhau như Excel. Lỗi #REF là một cạm bẫy phổ biến, đặc biệt là khi xử lý các truy vấn phức tạp và tham chiếu dữ liệu không được dịch tốt bên ngoài hệ sinh thái của Google. Việc hiểu rõ những hạn chế này và lập kế hoạch khắc phục chúng trong tập lệnh có thể giảm đáng kể khả năng xảy ra các sự cố như vậy, giúp quy trình quản lý dữ liệu suôn sẻ hơn.

Bạn đang tìm kiếm thư tạm thời ẩn danh hoạt động tốt nhất trên Internet? Dịch vụ e-mail tạm thời miễn phí của chúng tôi là dịch vụ hoàn chỉnh nhất, Nhận và gửi e-mail, Tạo chuyển hướng đến các e-mail yêu thích của bạn. Giữ mật khẩu của bạn và sử dụng email tạm thời của bạn suốt đời. POP3, IMAP và SMTP có sẵn miễn phí.

Thử ngay bây giờ !