Làm cách nào để bạn tự động xóa các hàng trong trang tính google?
|
Một nhiệm vụ không quá xa lạ đối với người dùng Google Trang tính là xóa các hàng dựa trên các giá trị của một ô trong một cột nhất định. Thường thì bạn không muốn thay đổi vị trí hàng của các giá trị nên việc sắp xếp các giá trị rồi xóa chúng theo khối là không cần thiết Show Tất cả những gì bạn muốn làm là lặp qua các hàng trong Trang tính, tìm kiếm cột đã chọn của bạn để tìm văn bản hoặc giá trị sẽ kích hoạt mã của bạn để xóa hàng đã chỉ định. Sau đó xóa hàng Rửa sạch và lặp lại cho đến khi tất cả các hàng đã được kiểm tra Tác vụ này có thể được tự động hóa bằng Google Apps Script theo một số cách tùy thuộc vào kích thước tập dữ liệu của bạn Mục lục
Tập dữ liệu nhỏCách tiếp cận hợp lý nhất để xóa các hàng trong một tập dữ liệu nhỏ có thể như thế này Xóa hàng cho tập dữ liệu nhỏ 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 // Xóa hàng dựa trên giá trị ô
// TOÀN CẦU
var SS = Ứng dụng bảng tính. openById("ID BẢNG CỦA BẠN"); var TỜ = SS. getSheetByName("ID TAB TRANG TÍNH CỦA BẠN"); var RANGE = SHEET. getDataRange();
var DELETE_VAL = "TEXT KÍCH HOẠT XÓA"; var COL_TO_SEARCH = A NUMBER; // The column to search for the DELETE_VAL (Zero is first)
chức năng xóaEachRow(){
var rangeVals = RANGE.getValues();
//Đảo ngược vòng lặp 'for'. cho(var i = rangeVals.độ dài - 1; i >= 0; i--){ if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){
TỜI. deleteRow(i + 1); }; }; }; Trong phần Globals, dòng 5-7 lấy Dữ liệu Bảng tính, Trang tính và Phạm vi. Chúng tôi sử dụng Lớp SpreadsheetApp để lấy thông tin này. Đối với mục đích sử dụng của riêng bạn, bạn sẽ thay đổi các biến Tiếp theo, chúng tôi có các tham số mà chúng tôi sẽ sử dụng để quyết định những hàng nào chúng tôi sẽ xóa. Biến Biến 0. Một, 1. B, 2. C, 3. Đ xóaEachRow()Hàm
Và sẽ trông như thế này trên Google Trang tính của bạn Tiếp theo, ở dòng 19, chúng ta sẽ đảo ngược vòng lặp 'for' Tại sao đảo ngược vòng lặp 'for'? Khi bạn xóa một hàng, các vị trí giá trị bên dưới nó sẽ thay đổi. Hãy tưởng tượng làm điều này khi bạn đang lặp qua các hàng và cột từ trên xuống dưới. Sau khi bạn xóa hàng đầu tiên và lặp qua hàng tiếp theo, hàng cần xóa của bạn sẽ được định vị một điểm bên dưới vị trí của nó, xóa hàng sai. Một giải pháp cho vấn đề này là đảo ngược vòng lặp “for” Để làm điều này, đối số đầu tiên cho biến vòng lặp “i” của chúng ta là bắt đầu ở cuối mảng
Sau đó, chúng tôi muốn đảm bảo biến dừng khi nó đạt đến giá trị 0 ________số 8 Cuối cùng, chúng tôi muốn lấy đi một từ độ dài của mảng mỗi khi chúng tôi lặp qua vòng lặp tôi- Tiếp theo, chúng tôi muốn kiểm tra xem Để sử dụng Bây giờ, mỗi hàng có giá trị đã chọn của chúng tôi sẽ bị xóa cùng một lúc Tuyệt vời, nhưng hãy xem cách tính năng này hoạt động với Google Trang tính có 10.000 hàng Tập dữ liệu lớnMột ví dụHãy tưởng tượng chúng ta có một tập dữ liệu với 10.000 hàng. Hãy xem ví dụ dưới đây Chúng tôi muốn xóa các hàng có chứa từ Hematite (cột 2, bắt đầu từ 0). Hãy tiếp tục và tính thời gian cho toàn bộ quá trình để xem mã này chạy trong bao lâu Một cách tiếp cận tồiHãy sử dụng lại đoạn mã trên. Hãy xem mã được cập nhật. Tôi đã nhấn mạnh những thay đổi xóaEachRow 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 // Xóa hàng dựa trên giá trị ô
// TOÀN CẦU
var SS = Ứng dụng bảng tính. openById("1xWCiwR2mDEFTKEVeOv9CtgwYiFfTxWA_iazlNaKZW7M"); var TỜ = SS. getSheetByName("Sheet1"); var RANGE = SHEET. getDataRange();
var DELETE_VAL = "Hematit"; var COL_TO_SEARCH = 2; //Zero is first
chức năng chính() {
var thời gian bắt đầu = mới Date().getTime();
var deleteSelectedRows = deleteEachRow();
var thời gian chạy = (new Date().getTime() - startTime)/1000; Người ghi nhật ký. log("Thời gian chạy là. " + Thời gian chạy + " giây"); };
chức năng xóaEachRow(){
var rangeVals = RANGE.getValues();
//Đảo ngược vòng lặp 'for'. cho(var i = rangeVals.độ dài - 1; i >= 0; i--){ if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){
TỜI. deleteRow(i + 1); }; }; }; Trên các dòng 5-6, bạn có thể thấy chúng tôi đã thêm id bảng tính của riêng mình và tên tab Google Trang tính của chúng tôi DELETE_VAL của chúng tôi hiện là “Hematite” và cột tìm kiếm của chúng tôi ở cột hai, là cột C trên trang tính Chúng tôi đã tạo một chức năng mới gọi là Dòng 15, đặt thời gian bắt đầu của chúng tôi. Sau đó, chúng tôi chạy hàm Bây giờ, Google Apps Script Gods, tôi cảm thấy hơi tệ về phần tiếp theo này, vì điều này thực sự gây lãng phí thời gian xử lý trên Máy chủ Google Cloud của bạn. Tôi chắc chắn là một giọt nước trong đại dương, nhưng tôi vẫn cảm thấy khó chịu. Xin lỗi, nhưng đó là một lý do tốt. Hứa Vì lợi ích của bạn, bạn đọc thân mến, tôi đã chạy mã này 10 lần để lấy điểm trung bình (Tôi không khuyến khích bạn làm điều này trừ khi bạn cần chợp mắt). Kết quả tính bằng giây như sau Đúng, đó là thời gian chạy trung bình là 205. 584 giây để lặp qua 10.000 hàng và xóa các hàng Hematite (khoảng 6oo hàng). Theo những lời rất thật của một trong những độc giả của tôi
Vấn đề ở đây là chúng tôi đang thực hiện lệnh gọi tới Google Trang tính để xóa một hàng mỗi khi nó tìm thấy giá trị mục tiêu của chúng tôi. Điều này cực kỳ tốn kém cho việc xử lý Được rồi, sử dụng Nếu bạn đã xem hướng dẫn của tôi về cách lặp qua Google Trang tính đúng cách, bạn có thể có một số manh mối Một cách tiếp cận nhanh hơnLưu ý rằng có thể chúng ta không muốn sắp xếp dữ liệu trước khi chạy mã, tôi chỉ có thể nghĩ ra một tùy chọn có thể được thực thi theo một số cách Thay vì thực hiện các cuộc gọi tới Google Trang tính mỗi khi chúng tôi muốn xóa một hàng, chúng tôi
Điều này sẽ duy trì thứ tự của mảng. Hơn nữa, thay vì thực hiện hàng trăm hoặc hàng nghìn lệnh gọi tới Google Trang tính, chúng tôi sẽ chỉ thực hiện bốn lệnh gọi Còn về việc chúng ta xem xét hai cách tiếp cận vấn đề này bằng mã xóa rồi đặt giá trị mớiloại bỏ ThenSetNewVals 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 // Xóa hàng dựa trên giá trị ô
// TOÀN CẦU
var SS = Ứng dụng bảng tính. openById("1xWCiwR2mDEFTKEVeOv9CtgwYiFfTxWA_iazlNaKZW7M"); var TỜ = SS. getSheetByName("Sheet1"); var RANGE = SHEET. getDataRange();
var DELETE_VAL = "Hematit"; var COL_TO_SEARCH = 2; //Zero is first
chức năng chính() {
var thời gian bắt đầu = mới Date().getTime();
var deleteSelectedRows = remove ThenSetNewVals();
var thời gian chạy = (new Date().getTime() - startTime)/1000; Người ghi nhật ký. log("Thời gian chạy là. " + Thời gian chạy + " giây"); };
hàm remove ThenSetNewVals(){
var rangeVals = RANGE.getValues();
var newRangeVals = [];
cho(var i = 0; i < rangeVals.độ dài; i ++ ){ if(rangeVals[i][COL_TO_SEARCH] ! = DELETE_VAL){
newRangeVals. đẩy(rangeVals[i]); }; };
PHẠM VI. nội dung rõ ràng();
var newRange = SHEET.getRange(1,1 . ,newRangeVals.độ dài, newRangeVals[0 . ].độ dài); newRange. setValues(newRangeVals); }; Chúng ta sẽ sử dụng một hàm mới ở đây được gọi từ hàm Đầu tiên, Tiếp theo, chúng tôi tạo một biến để lưu trữ dữ liệu mới không có các hàng Hematite trong đó. Chúng tôi gọi đây là Khoảng thời gian này chúng ta không cần lặp qua mảng 2d từ dưới lên. Chúng ta có thể lặp từ trên xuống bằng vòng lặp 'for' (Dòng 29) Lần này, chúng tôi muốn thu thập bất kỳ hàng nào không có Hematite trong đó vào cột mong muốn của chúng tôi. Chúng tôi sử dụng câu lệnh if không bằng để so sánh cho điều này (dòng 30) Nếu hàng không chứa Hematite, thì chúng tôi đẩy hàng đó vào mảng Tiếp theo, chúng tôi muốn xóa tất cả nội dung khỏi Google Sheet đã chọn, nhưng giữ nguyên định dạng (dòng 36). Chúng tôi làm điều này với phương pháp tiện dụng Bây giờ chúng tôi đã xóa dữ liệu, chúng tôi có thể thêm vào tập dữ liệu mới của mình trừ đi hàng có giá trị Hematite đáng sợ của chúng tôi. Trước tiên, chúng ta cần lấy phạm vi bằng với chiều rộng và chiều cao của dữ liệu mới. Chúng tôi sử dụng phương pháp Cuối cùng, chúng tôi lấy phạm vi mới này và đặt Giá trị của tập dữ liệu mới vào trang tính của chúng tôi Xong. Và cũng nhanh hơn đáng kể Hãy xem xét một cách tiếp cận thay thế Lọc ra giá trị và đặt giá trị mới trong trang tínhTheo phương pháp này, chúng tôi tạo một mảng mới lọc ra các hàng Hematite bằng cách sử dụng phương thức "bộ lọc" của JavaScript. Hãy xem chức năng mới của chúng tôi. Không có nhiều thay đổi loại bỏFilterSetVals 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 // Xóa hàng dựa trên giá trị ô
// TOÀN CẦU
var SS = Ứng dụng bảng tính. openById("1xWCiwR2mDEFTKEVeOv9CtgwYiFfTxWA_iazlNaKZW7M"); var TỜ = SS. getSheetByName("Sheet1"); var RANGE = SHEET. getDataRange();
var DELETE_VAL = "Hematit"; var COL_TO_SEARCH = 2; //Zero is first
chức năng chính() {
var thời gian bắt đầu = mới Date().getTime();
var deleteSelectedRows = removeFilterSetVals();
var thời gian chạy = (new Date().getTime() - startTime)/1000; Người ghi nhật ký. log("Thời gian chạy là. " + Thời gian chạy + " giây"); };
hàm removeFilterSetVals(){ var rangeVals = RANGE.getValues();
var filteredRange = rangeVals.bộ lọc(chức năng(val){
return val[COL_TO_SEARCH] ! = DELETE_VAL ; });
PHẠM VI. nội dung rõ ràng();
var newRange = SHEET.getRange(1,1 . ,filteredRange.độ dài, Phạm vi được lọc[0 . ].độ dài); newRange. setValues(filteredRange); }; Như bạn có thể thấy ở dòng 26-29, chúng tôi đã xóa vòng lặp 'for' và thêm 'bộ lọc'. Bộ lọc nhận một chức năng với tham số 'val' của chúng tôi. Tham số ‘val’ sẽ đại diện cho từng hàng dữ liệu Trong mỗi hàng, chúng tôi chỉ muốn xem xét cột chứa Hematite. Dòng 28, nói rằng hàm chỉ nên trả về các giá trị không chứa giá trị đích Tất cả điều này được lưu trữ trong biến Sự kết luậnVì vậy, sự khác biệt trong hiệu suất là gì. Một đống. Kiểm tra bài kiểm tra Thời gian chạy từ mười một đến mười hai giây để đặt giá trị so với hơn hai trăm giây để xóa từng hàng riêng lẻ. Tôi sẽ lấy nó Như bạn có thể thấy, có rất ít sự khác biệt về thời gian giữa việc sử dụng cách tiếp cận vòng lặp 'for' so với cách tiếp cận 'bộ lọc'. Mẫu thử nghiệm không đủ lớn để xác định bất kỳ sự khác biệt đáng kể nào. Tôi thực sự tùy thuộc vào bạn những gì bạn thích sử dụng Một điểm đáng chỉ trích đối với các phương pháp tiếp cận nhanh hơn này là nguy cơ mất tất cả dữ liệu của bạn nếu mã bị lỗi giữa giai đoạn Vậy bạn nghĩ như thế nào? Bạn muốn tìm hiểu thêm về Google Apps Scripts ở định dạng có cấu trúc hơn? . Udemy has some great courses that can get you from the basics to a real Google Apps Script pro. *Các liên kết liên kết ở trên đã được nghiên cứu cẩn thận để đưa bạn đến những gì bạn cần cụ thể. Nếu bạn quyết định nhấp vào một trong những liên kết này, bạn sẽ phải trả giá tương đương với việc truy cập trang web. Nếu bạn quyết định đăng ký, tôi chỉ nhận được một ít tiền tiêu vặt để giúp trả chi phí điều hành trang web này |
