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

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ỏ
  • xóaEachRow[]
• Tập dữ liệu lớn
  • Một ví dụ
  • Một cách tiếp cận tồi
  • Một cách tiếp cận nhanh hơn
    • xóa rồi đặt giá trị mới
    • Lọc ra giá trị và đặt giá trị mới trong trang tính
• Sự kết luận

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 SS và SHEET cho phù hợp với dự án của mình. Biến RANGE là toàn bộ phạm vi dữ liệu

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 DELETE_VAL là giá trị văn bản trong hàng bạn muốn xóa. Khi mã thấy điều này, nó sẽ xóa hàng

Biến COL_TO_SEARCH cho mã biết cột nào cần tìm kiếm. Trong Google Trang tính của bạn, bạn sẽ có một chữ cái ở trên cùng để 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, các số bắt đầu từ số không. Vì vậy, nếu cột cần tìm kiếm của bạn là cột “D”, thì bạn sẽ tính

0. Một, 1. B, 2. C, 3. Đ

xóaEachRow[]

Hàm deleteEachRow[] của chúng tôi trước tiên nhận giá trị trong RANGE của chúng tôi. Điều này được trình bày dưới dạng một mảng 2D như

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

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

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ị 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 DELETE_VAL của bạn có tồn tại trong cột ________ 10 của mỗi hàng, dòng 20 không. Nếu có, chúng tôi sẽ sử dụng phương thức SHEET1 tùy chỉnh của Google Apps Script [dòng 22]

Để sử dụng SHEET2, trước tiên chúng tôi lấy BẢNG 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 “i” cộng với một

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ớn

Mộ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ồi

Hã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";= 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à SHEET3. Chúng tôi sẽ sử dụng mã này để thêm mã hẹn giờ và tham chiếu hàm deleteEachRow[]

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 deleteEachRow[] của mình, dòng 17. Cuối cùng, chúng tôi lấy lại thời gian và trừ nó khỏi thời gian bắt đầu. Điều này sẽ cung cấp cho chúng tôi khoảng thời gian mã đã chạy tính bằng mili giây. Chúng tôi sẽ chia những mili giây đó cho 1.000 để có thời gian dễ đọc hơn tính bằng giây

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

Đây là lấy F. O. R. E. V. E. R

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 SHEET6 không phải là một lựa chọn tốt cho các tập dữ liệu lớn. lựa chọn 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 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ơn

Lư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

1. Sử dụng mảng mảng SHEET7 để tạo một mảng mới gồm các biến không có các hàng đích, hematite, trong đó
2. Xóa TẤT CẢ dữ liệu cũ trong Google Trang tính để trang tính trống
3. Thêm 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 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ới

loạ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";