Hướng dẫn how to delete rows in google sheets based on cell value - cách xóa hàng trong google sheet dựa trên giá trị ô

Mã dưới đây có thể xóa các hàng chứa ngày hơn 50 ngày trước ngày hôm nay trong một cột G cụ thể, di chuyển các giá trị hàng này để sao lưu trang và xóa các hàng khỏi bảng nguồn.

Mã tốt hơn vì nó xóa các hàng tại một lần thay vì xóa từng cái một. Chạy nhanh hơn nhiều.

Nó không sao chép các giá trị trở lại như một số giải pháp được đề xuất (bằng cách đẩy vào một mảng và sao chép lại vào trang tính). Nếu tôi tuân theo logic đó, tôi sẽ mất các công thức có trong các ô này.

Tôi chạy chức năng hàng ngày trong đêm (theo lịch trình) khi không có ai sử dụng tờ.

function delete_old(){
  //delete > 50 day old records and copy to backup
  //run daily from owner login
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var bill = ss.getSheetByName("Allotted");
  var backss = SpreadsheetApp.openById("..."); //backup spreadsheet
  var bill2 = backss.getSheetByName("Allotted");
  var today=new Date();
  //process allotted sheet (bills)
  bill.getRange(1, 1, bill.getMaxRows(), bill.getMaxColumns()).activate();
  ss.getActiveRange().offset(1, 0, ss.getActiveRange().getNumRows() - 1).sort({column: 7, ascending: true});
  var data = bill.getDataRange().getValues();
  var delData = new Array();
  for(n=data.length-1; n>1; n--){
    if(data[n][6] !=="" &&  data[n][6]  < today.getTime()-(50*24*3600*1000) ){  //change the condition as per your situation
      delData.push(data[n]);
    }//if
   }//for
   //get first and last row no to be deleted
   for(n=1;n1; n--){
    if(data[n][6] !=="" &&  data[n][6]  < today.getTime()-(50*24*3600*1000) ){ 
      var ltrow=n+1 ; //last row
      break
    }//if
   }//for
   var bill2lr=bill2.getLastRow();
   bill2.getRange((bill2lr+1),1,delData.length,delData[0].length).setValues(delData);
   bill.deleteRows(strow, 1+ltrow-strow);
   bill.getRange(1, 1, bill.getMaxRows(), bill.getMaxColumns()).activate();
   ss.getActiveRange().offset(1, 0, ss.getActiveRange().getNumRows() - 1).sort({column: 6, ascending: true}); //get back ordinal sorting order as per column F
}//function

Tập lệnh Google Apps, bảng tính, Google Sheet, Lặp lại

Một nhiệm vụ không quá hiếm đối với người dùng Google Sheets là xóa các hàng dựa trên các giá trị của ô 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ị để sắp xếp các giá trị và sau đó xóa chúng trong các khối đã bị tắt.

Tất cả những gì bạn muốn làm là lặp lại thông qua các hàng trong bảng tìm kiếm cột đã chọn của bạn cho văn bản hoặc giá trị sẽ kích hoạt mã của bạn để xóa hàng được 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.

Nhiệm vụ này có thể được tự động hóa với tập lệnh Google Apps theo một số cách tùy thuộc vào kích thước của bộ dữ liệu của bạn.

• Bộ dữ liệu nhỏ
  • deleteEachRow()
• Bộ dữ liệu lớn
  • Một ví dụ
  • Một cách tiếp cận tồi tệ
  • Một cách tiếp cận nhanh hơn
    • Xóa sau đó đặt các giá trị mới
    • Lọc ra giá trị và đặt giá trị mới trong trang tính
• Sự kết luận

Bộ dữ liệu nhỏ

Bộ dữ liệu lớn

Trong GlobalSsection, các dòng 5-7 có được bảng tính, bảng và dữ liệu phạm vi. Chúng tôi sử dụng lớp bảng tính để có được thông tin này. Để sử dụng cho riêng bạn, bạn sẽ thay đổi các biến SS và SHEET để phù hợp với dự án của bạn. Biến RANGE là toàn bộ dữ liệuGlobalssection, lines 5-7 get the Spreadsheet, Sheet and Range Data. We use the SpreadsheetApp Class to get this information. For your own use, you would change the SS and SHEET variables to suit your project. The RANGE variable is the full range of data

Tiếp theo, chúng tôi có các tham số 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 DELETE_VAL là giá trị văn bản trong hàng bạn muốn xóa. Khi mã nhìn thấy điều này, nó sẽ xóa hàng.

Biến COL_TO_SEARCH cho biết mã nào sẽ tìm kiếm. Trong tờ Google của bạn, bạn sẽ có một lá thư trên đầu để xác định các cột. Chúng tôi muốn thay đổi điều này thành một số. Trong mã hóa, số bắt đầu từ 0. Vì vậy, nếu cột của bạn để tìm kiếm là, hãy nói cột D D, thì bạn sẽ đếm:

0: A, 1: B, 2: C, 3: D3:D

deleteEachRow()

Hàm deleteEachRow() của chúng tôi trước tiên có được giá trị trong phạm vi của chúng tôi. Điều này được trình bày dưới dạng mảng 2D như:

[["cat",1,100],["dog",34,565],["penguin",10,123]]

Và sẽ trông như thế này trên tờ Google của bạn:

Tiếp theo, trên dòng 19, chúng tôi sẽ đảo ngược ‘cho‘ vòng lặp.

Tại sao lại đảo ngược vòng lặp ’cho vòng lặp?

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 lặp đi lặp lại qua các hàng và cột từ trên xuống dưới. & NBSP; Sau khi bạn xóa hàng đầu tiên và lặp qua hàng tiếp theo, hàng của bạn sẽ được đặt một điểm bên dưới nơi nó có nghĩa là, xóa sai hàng. Một giải pháp cho điều này là đảo ngược các vòng lặp cho vòng lặp.

Để thực hiện điều này, đối số đầu tiên cho biến vòng lặp của chúng tôi là I I là bắt đầu ở cuối mảng:

var i =  rangeVals.length-1;

Sau đó, chúng tôi muốn đảm bảo biến dừng khi nó đạt đến giá trị Zeroeth:

i >= 0;

Cuối cùng, chúng tôi muốn lấy một cái ra khỏi độ dài của mảng mỗi khi chúng tôi lặp lại qua vòng lặp:

tôi-

Tiếp theo, chúng tôi muốn kiểm tra xem DELETE_VAL của chúng tôi có tồn tại trong cột SS1 của chúng tôi của mỗi hàng không, dòng 20. Nếu chúng tôi sẽ sử dụng Google Apps Scrips tùy chỉnh SS2Method (dòng 22).

Để sử dụng SS3, trước tiên chúng tôi lấy tấm chúng tôi đang làm việc. Sau đó, chúng tôi xác định hàng nào chúng tôi muốn xóa. Đối với chúng tôi, đây sẽ là Iterator của chúng tôi.

Bây giờ mỗi hàng với 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 thức hoạt động của Google với một tờ Google với 10.000 hàng nói.

Bộ dữ liệu lớn

Một ví dụ

Hãy tưởng tượng chúng ta có một bộ 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 cùng đi trước và thời gian toàn bộ quá trình để xem mã này mất bao lâu để chạy.Hematite (column 2, starting from zero). Let’s go ahead and time the whole process to see how long it takes for this code to run.

Một cách tiếp cận tồi tệ

Hãy để sử dụng mã ở trên một lần nữa. Hãy xem mã cập nhật. Tôi đã nhấn mạnh những thay đổi:

Trong GlobalSsection, các dòng 5-7 có được bảng tính, bảng và dữ liệu phạm vi. Chúng tôi sử dụng lớp bảng tính để có được thông tin này. Để sử dụng cho riêng bạn, bạn sẽ thay đổi các biến SS và SHEET để phù hợp với dự án của bạn. Biến RANGE là toàn bộ dữ liệu

Tiếp theo, chúng tôi có các tham số 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 DELETE_VAL là giá trị văn bản trong hàng bạn muốn xóa. Khi mã nhìn thấy điều này, nó sẽ xóa hàng.

Biến COL_TO_SEARCH cho biết mã nào sẽ tìm kiếm. Trong tờ Google của bạn, bạn sẽ có một lá thư trên đầu để xác định các cột. Chúng tôi muốn thay đổi điều này thành một số. Trong mã hóa, số bắt đầu từ 0. Vì vậy, nếu cột của bạn để tìm kiếm là, hãy nói cột D D, thì bạn sẽ đếm:

0: A, 1: B, 2: C, 3: D

Bây giờ, Google Apps Script Gods, tôi cảm thấy tồi tệ về bit tiếp theo này, bởi vì thời gian xử lý thực sự lãng phí này trên các máy chủ Google Cloud của bạn. Tôi chắc chắn một giọt nước ở đại dương, nhưng tôi vẫn cảm thấy khó chịu. Xin lỗi, nhưng nó là một lý do chính đáng. Hứa.

Vì lợi ích của bạn, người đọc thân mến, tôi đã chạy mã này 10 lần để có được mức 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 ngủ trưa). Kết quả tính bằng giây như sau:

Đúng là trung bình thời gian chạy là 205,584 giây để lặp qua 10.000 hàng và xóa các hàng hematit (khoảng 6oo hàng). Theo lời rất thật của một trong những độc giả của tôi:Hematite rows (approx 6oo rows). In the very true words of one of my readers:

Việc này sẽ kéo dài mãi mãi.

Vấn đề ở đây là chúng tôi đang thực hiện các cuộc gọi đến tờ Google để xóa một hàng mỗi khi tìm thấy giá trị mục tiêu của chúng tôi. Điều này là cực kỳ tốn kém cho việc xử lý.

Được rồi, sử dụng SS7 không phải là một lựa chọn tốt cho các bộ dữ liệu lớn. Giải pháp thay thế của chúng tôi là gì?

Nếu bạn đã xem hướng dẫn của tôi về cách lặp lại thông qua Google Sheets đúng cách, bạn có thể có một số manh mối.

Một cách tiếp cận nhanh hơn

Hãy nhớ rằng có lẽ chúng tôi không muốn sắp xếp dữ liệu trước khi chạy mã, tôi chỉ có thể nghĩ về một tùy chọn có thể được thực thi theo một số cách.

Thay vì thực hiện cuộc gọi đến tờ Google mỗi khi chúng tôi muốn xóa một hàng, chúng tôi:

1. Sử dụng mảng SS8 để tạo một & nbsp mới; Mảng các biến mà don lồng có mục tiêu, & nbsp; hematit, hàng trong đó.hematite, rows in it.
2. Xóa tất cả các dữ liệu cũ trong bảng Google để trang tính trống.
3. Thêm vào các giá trị mảng mới vào trang tính.

Đ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 ngàn cuộc gọi đến tờ Google, chúng tôi sẽ chỉ thực hiện bốn cuộc gọi.

Làm thế nào về chúng tôi nhìn vào hai cách tiếp cận điều này với mã:

Xóa sau đó đặt các giá trị mới

Chúng tôi sẽ sử dụng một chức năng mới ở đây được gọi từ chức năng SS4 trên dòng 17.

SHEET0 Đầu tiên nhận được tất cả các giá trị từ bảng tính (dòng 25).

Tiếp theo, chúng tôi tạo một biến để lưu trữ dữ liệu mới của chúng tôi không có các hàng hematit trong đó. Chúng tôi gọi đây là SHEET1, (dòng 27).Hematite rows in them. We call this SHEET1, (Line 27).

Lần này, chúng tôi không cần lặp lại thông qua mảng 2D từ dưới lên. Chúng ta có thể lặp lại từ trên xuống với vòng lặp ‘cho vòng lặp (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ó & nbsp; hematite & nbsp; trong đó trong cột mong muốn của chúng tôi. Chúng tôi sử dụng không tương đương để so sánh nếu câu lệnh này (dòng 30).Hematite in it in our desired column. We use the not equate to comparison if statement for this (line 30).

Nếu hàng không chứa hematit, thì chúng tôi sẽ đẩy hàng đó vào mảng SHEET1 của chúng tôi (dòng 31).Hematite, then we push that row into our SHEET1 array (line 31).

Tiếp theo, chúng tôi muốn xóa tất cả nội dung khỏi Google Sheet đã chọn của chúng tôi, nhưng hãy giữ định dạng (dòng 36). Chúng tôi làm điều này với phương pháp SHEET3 tiện dụng. Phương pháp này được gọi từ lớp phạm vi. Nó có một loạt các cột và hàng, trong trường hợp của chúng tôi, toàn bộ phạm vi dữ liệu và chỉ xóa dữ liệu trong khi duy trì định dạng. & NBSP; Gọn gàng, phải không?

Bây giờ chúng tôi đã xóa dữ liệu mà chúng tôi có thể thêm vào tập dữ liệu mới của chúng tôi trừ đi hàng với giá trị hematit đáng sợ của chúng tôi. Đầu tiên, chúng ta cần có được phạm vi bằng chiều rộng và chiều cao của dữ liệu mới của chúng ta. Chúng tôi sử dụng phương pháp SHEET4 cho việc này. Phương thức này mất, hàng bắt đầu, cột bắt đầu, chiều dài hàng và độ dài cột.Hematite value. First, we need to get the range equal to the width and height of our new data. We use the SHEET4 method for this. This method takes, the start row, start column, row length and column length.

Cuối cùng, chúng tôi lấy phạm vi mới này và setValues ​​của dữ liệu mới của chúng tôi được đặt vào bảng của chúng tôi.

Xong! Và cũng nhanh hơn đáng kể.

Hãy cùng nhìn vào một cách tiếp cận khác.

Lọc ra giá trị và đặt giá trị mới trong trang tính

Trong cách tiếp cận này, chúng tôi tạo ra một mảng mới lọc ra các hàng & nbsp; hematite bằng cách sử dụng phương thức bộ lọc JavaScript. Hãy xem chức năng mới của chúng tôi. Không có nhiều thay đổi.Hematite rows by using JavaScript’s “filter” method. Take a look at our new function. Not much has changed.

Như bạn có thể thấy trên các dòng 26-29, chúng tôi đã loại bỏ vòng lặp ‘cho vòng lặp của chúng tôi và thêm‘ bộ lọc của chúng tôi. Bộ lọc có một chức năng với tham số của chúng tôi ‘Val. 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 nhìn vào cột có chứa & nbsp; hematite. Dòng 28, nói rằng hàm chỉ nên trả về các giá trị don don chứa giá trị đích.Hematite. Line 28, states that the function should only return the values that don’t contain the target value.

Tất cả điều này được lưu trữ trong biến SHEET5 của chúng tôi dưới dạng mảng 2D mới mà chúng tôi có thể đặt lại trong tờ Google của mình sau khi chúng tôi xóa nội dung.

Sự kết luận

Vì 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:

Mười một đến mười hai thời gian chạy thứ hai để cài đặt các giá trị so với hơn hai trăm giây để xóa từng hàng. 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 cho cách tiếp cận ‘bộ lọc. Mẫu thử đã 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 lời chỉ trích về những cách tiếp cận nhanh hơn này có thể là nguy cơ mất tất cả & NBSP của bạn; Dữ liệu nếu mã không thành công giữa pha SHEET6 và pha ____27. Tuy nhiên, xem xét lịch sử phiên bản tự động của Google Sheets và cực kỳ không có khả năng điều này sẽ xảy ra, tôi không coi đó là một vấn đề.

Vậy bạn nghĩ như thế nào? Bạn sẽ thay đổi cách bạn xóa hàng trong các bộ dữ liệu lớn, bây giờ? Bạn có một cách tiếp cận tốt hơn? Tôi rất thích nghe những suy nghĩ của bạn trong các bình luận dưới đây.

Tìm kiếm để tìm hiểu thêm về các tập lệnh của ứng dụng Google ở ​​định dạng có cấu trúc hơn? Udemyhas Một số khóa học tuyệt vời có thể đưa bạn từ những điều cơ bản đến một tập lệnh Google ứng dụng thực sự.Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Có một vấn đề cụ thể hơn mà bạn cần giúp đỡ, nhưng don có thời gian để phát triển các kỹ năng? Thực hiện một cuộc điều tra về 'thuê tôi của tôi!' trang. Tôi thỉnh thoảng nhận các dự án. Ngoài ra, Fiverr, đặt cược tốt nhất của bạn để tìm một nhà phát triển tập lệnh Google ứng dụng lành nghề để giải quyết vấn đề của bạn một cách nhanh chóng và chuyên nghiệp. *'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *

*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 các liên kết này, bạn sẽ tốn kém như 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í cho việc điều hành trang web này.