Kiến thức cơ bản về Apps Script với Google Trang tính #2: Bảng tính, Trang tính và Dải ô

1. Giới thiệu

Chào mừng bạn đến với phần thứ hai của danh sách phát lớp học lập trình Kiến thức cơ bản về Apps Script với Google Trang tính. Lớp học lập trình trước tập trung vào các khái niệm về trình chỉnh sửa tập lệnh, macro và hàm tuỳ chỉnh. Lớp học lập trình này đi sâu vào Dịch vụ bảng tính mà bạn có thể dùng để đọc, ghi và thao tác dữ liệu trong Google Trang tính.

Kiến thức bạn sẽ học được

  • Cách biểu thị bảng tính, trang tính và dải ô trong Apps Script.
  • Cách truy cập, tạo và đổi tên bảng tính đang hoạt động (đang mở) bằng lớp SpreadsheetApp và Spreadsheet.
  • Cách thay đổi tên của một trang tính và hướng cột/hàng của một dải ô bằng cách sử dụng lớp Sheet.
  • Cách chỉ định, kích hoạt, di chuyển và sắp xếp một nhóm ô hoặc dải dữ liệu bằng cách sử dụng lớp Range.

Trước khi bắt đầu

Đây là lớp học lập trình thứ hai trong danh sách phát Kiến thức cơ bản về Apps Script với Google Trang tính. Trước khi bắt đầu, hãy nhớ hoàn thành lớp học lập trình đầu tiên: Macro và hàm tuỳ chỉnh.

Bạn cần có

  • Hiểu rõ các chủ đề cơ bản về Apps Script đã được khám phá trong lớp học lập trình trước của danh sách phát này.
  • Hiểu biết cơ bản về trình chỉnh sửa Apps Script
  • Hiểu biết cơ bản về Google Trang tính
  • Có thể đọc Ký hiệu A1 của Trang tính
  • Hiểu biết cơ bản về JavaScript và lớp String của ngôn ngữ này

Phần tiếp theo giới thiệu các lớp cốt lõi của dịch vụ Bảng tính.

2. Giới thiệu về dịch vụ Bảng tính

Có 4 lớp bao gồm nền tảng của dịch vụ Bảng tính: SpreadsheetApp, Spreadsheet, Sheet và Range. Phần này mô tả các lớp này và mục đích sử dụng của chúng.

Lớp SpreadsheetApp

Trước khi đi sâu vào bảng tính, trang tính và dải ô, bạn nên xem xét lớp mẹ của chúng: SpreadsheetApp. Nhiều tập lệnh bắt đầu bằng cách gọi các phương thức SpreadsheetApp, vì các phương thức này có thể cung cấp điểm truy cập ban đầu vào các tệp Google Trang tính của bạn. Bạn có thể coi SpreadsheetApp là lớp chính của Dịch vụ bảng tính. Lớp SpreadsheetApp không được khám phá chuyên sâu ở đây. Tuy nhiên, ở phần sau của lớp học lập trình này, bạn có thể tìm thấy cả ví dụ và bài tập để giúp bạn hiểu rõ lớp này.

Bảng tính, trang tính và các lớp học của chúng

Theo thuật ngữ của Trang tính, bảng tính là một tệp Google Trang tính (được lưu trữ trong Google Drive) chứa dữ liệu được sắp xếp theo hàng và cột. Đôi khi, bảng tính được gọi là "Google Trang tính", tương tự như cách tài liệu được gọi là "Google Tài liệu".

Bạn có thể dùng lớp Spreadsheet để truy cập và sửa đổi dữ liệu tệp Google Trang tính. Bạn cũng có thể dùng lớp này cho các thao tác khác ở cấp tệp, chẳng hạn như thêm cộng tác viên.

f00cc1a9eb606f77.png

Trang tính là từng trang riêng lẻ của một bảng tính, đôi khi được gọi là "thẻ". Mỗi bảng tính có thể chứa một hoặc nhiều trang tính. Bạn có thể sử dụng lớp Sheet** để truy cập và sửa đổi dữ liệu cũng như chế độ cài đặt ở cấp trang tính, chẳng hạn như di chuyển hàng hoặc cột dữ liệu.

39dbb10f83e3082.png

Tóm lại, lớp Spreadsheet hoạt động trên tập hợp các trang tính và xác định một tệp Google Trang tính trong Google Drive. Lớp Sheet hoạt động trên từng trang tính trong một bảng tính.

Lớp Range

Hầu hết các thao tác xử lý dữ liệu (ví dụ: đọc, ghi hoặc định dạng dữ liệu ô) đều yêu cầu bạn xác định những ô mà thao tác đó áp dụng. Bạn có thể dùng lớp Range để chọn các nhóm ô cụ thể trong một bảng. Các thực thể của lớp này đại diện cho một dải ô – một nhóm gồm một hoặc nhiều ô liền kề trong một trang tính. Bạn có thể chỉ định các dải ô theo số hàng và số cột hoặc bằng cách sử dụng ký hiệu A1.

Phần còn lại của lớp học lập trình này cho thấy các ví dụ về tập lệnh hoạt động với các lớp và phương thức của chúng.

3. Thiết lập

Trước khi tiếp tục, bạn cần có một bảng tính chứa một số dữ liệu. Chúng tôi đã cung cấp một trang tính cho bạn: nhấp vào đường liên kết này để sao chép trang dữ liệu rồi nhấp vào Tạo bản sao.

5376f721894b10d9.png

Một bản sao của bảng tính mẫu để bạn sử dụng sẽ được đặt trong thư mục Google Drive của bạn và có tên là "Bản sao của Bảng tính chưa có tiêu đề". Hãy dùng bảng tính này để hoàn thành các bài tập trong lớp học lập trình này.

Xin lưu ý rằng bạn có thể mở trình chỉnh sửa tập lệnh trong Google Trang tính bằng cách nhấp vào Tiện ích > Apps Script.

Khi bạn mở một dự án Apps Script trong trình chỉnh sửa tập lệnh lần đầu tiên, trình chỉnh sửa tập lệnh sẽ tạo cả dự án tập lệnh và tệp tập lệnh cho bạn.

Phần tiếp theo sẽ hướng dẫn bạn cách sử dụng lớp Spreadsheet để cải thiện bảng tính này.

4. Truy cập và sửa đổi bảng tính

Trong phần này, bạn có thể tìm hiểu cách sử dụng các lớp SpreadsheetApp và Spreadsheet để truy cập và sửa đổi bảng tính. Cụ thể, các bài tập này sẽ hướng dẫn bạn cách đổi tên bảng tính và sao chép trang tính trong bảng tính.

Đây là những thao tác đơn giản, nhưng thường là một phần của quy trình làm việc lớn hơn và phức tạp hơn. Sau khi hiểu cách tự động hoá các tác vụ này bằng mã kịch bản, bạn sẽ dễ dàng tìm hiểu cách tự động hoá các thao tác phức tạp hơn.

Đổi tên bảng tính đang hoạt động

Giả sử bạn muốn thay đổi tên mặc định "Bản sao của Bảng tính không có tiêu đề" thành một tiêu đề phản ánh rõ hơn mục đích của bảng tính. Bạn có thể thực hiện việc này bằng các lớp SpreadsheetApp và Spreadsheet.

  1. Trong trình chỉnh sửa tập lệnh, hãy thay thế khối mã myFunction() mặc định bằng mã sau:
function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}
  1. Để lưu kịch bản, hãy nhấp vào biểu tượng Lưu lưu.
  2. Để đổi tên dự án Apps Script, hãy nhấp vào Dự án chưa có tên, nhập "Giá bơ" làm tên dự án mới rồi nhấp vào Đổi tên.
  3. Để chạy tập lệnh, hãy chọn renameSpreadsheet trong danh sách hàm rồi nhấp vào Chạy.
  4. Uỷ quyền cho macro bằng cách làm theo hướng dẫn trên màn hình. Nếu bạn nhận được thông báo "Ứng dụng này chưa được xác minh", hãy nhấp vào Nâng cao, sau đó nhấp vào Truy cập vào Giá của quả bơ (không an toàn). Trên màn hình tiếp theo, hãy nhấp vào Cho phép.

Sau khi hàm thực thi, tên tệp của bảng tính sẽ thay đổi:

226c7bc3c2fbf33e.png

Hãy xem mã bạn đã nhập. Phương thức getActiveSpreadsheet() trả về một đối tượng đại diện cho bảng tính đang hoạt động; tức là bản sao của bảng tính bài tập mà bạn đã tạo. Đối tượng bảng tính này được lưu trữ trong biến mySS. Gọi rename(newName) trên mySS sẽ thay đổi tên của tệp bảng tính trong Google Drive thành "2017 Avocado Prices in Portland, Seattle" (Giá bơ năm 2017 ở Portland, Seattle).

Vì biến mySS là một tham chiếu đến bảng tính, nên bạn có thể làm cho mã của mình gọn gàng và hiệu quả hơn bằng cách gọi các phương thức Spreadsheet trên mySS thay vì gọi getActiveSpreadsheet() nhiều lần.

Sao chép trang tính đang hoạt động

Trong bảng tính hiện tại, bạn chỉ có một trang tính. Bạn có thể gọi phương thức Spreadsheet.duplicateActiveSheet() để tạo bản sao của trang tính:

  1. Thêm hàm mới sau đây bên dưới hàm renameSpreadsheet() đã có trong dự án tập lệnh của bạn:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}
  1. Lưu dự án tập lệnh.
  2. Để chạy tập lệnh, hãy chọn duplicateAndOrganizeActiveSheet trong danh sách hàm rồi nhấp vào Chạy.

Quay lại Trang tính để xem thẻ trang tính "Bản sao của Trang tính_Gốc" mới đã được thêm vào bảng tính của bạn.

d24f9f4ae20bf7d4.gif

Trong hàm mới này, phương thức duplicateActiveSheet() sẽ tạo, kích hoạt và trả về trang tính trùng lặp trong bảng tính của bạn. Trang tính kết quả này được lưu trữ trong duplicateSheet, nhưng mã chưa làm gì với biến đó.

Trong phần tiếp theo, bạn sẽ dùng lớp Sheet để đổi tên và định dạng trang tính trùng lặp.

5. Định dạng trang tính bằng lớp Trang tính

Lớp Sheet cung cấp các phương thức cho phép tập lệnh đọc và cập nhật trang tính. Trong phần này, bạn có thể tìm hiểu cách thay đổi tên trang tính và độ rộng cột bằng các phương thức của lớp Sheet.

Thay đổi tên trang tính

Việc đổi tên trang tính cũng đơn giản như đổi tên bảng tính trong renameSpreadsheet(). Bạn chỉ cần thực hiện một lệnh gọi phương thức duy nhất.

  1. Trong Google Trang tính, hãy nhấp vào trang tính Sheet_Original để kích hoạt trang tính đó.
  2. Trong Apps Script, hãy sửa đổi hàm duplicateAndOrganizeActiveSheet() sao cho khớp với hàm sau:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}
  1. Lưu và chạy hàm.

Trong Google Trang tính, một trang tính trùng lặp sẽ được tạo và đổi tên khi bạn chạy hàm:

91295f42354f62e7.gif

Trong mã đã thêm, phương thức setName(name) sẽ thay đổi tên của duplicateSheet bằng cách sử dụng getSheetID() để lấy số mã nhận dạng duy nhất của trang tính. Toán tử + nối mã nhận dạng trang tính vào cuối chuỗi "Sheet_".

Sửa đổi cột và hàng của trang tính

Bạn cũng có thể sử dụng lớp Sheet để định dạng trang tính. Ví dụ: chúng ta có thể cập nhật hàm duplicateAndOrganizeActiveSheet() để đổi kích thước các cột của trang tính trùng lặp, đồng thời thêm các hàng cố định:

  1. Trong Google Trang tính, hãy nhấp vào trang tính Sheet_Original để kích hoạt trang tính đó.
  2. Trong Apps Script, hãy sửa đổi hàm duplicateAndOrganizeActiveSheet() sao cho khớp với hàm sau:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}
  1. Lưu và chạy hàm.

Trong Google Trang tính, một trang tính trùng lặp sẽ được tạo, đổi tên, kích hoạt và định dạng:

2e57c917ab157dad.gif

Mã bạn đã thêm sử dụng autoResizeColumns(startColumn, numColumns) để đổi kích thước các cột của trang tính cho dễ đọc. Phương thức setFrozenRows(rows) sẽ cố định số lượng hàng nhất định (trong trường hợp này là 2 hàng), nhờ đó, hàng tiêu đề vẫn hiển thị khi người đọc cuộn xuống bảng tính.

Trong phần tiếp theo, bạn sẽ tìm hiểu về các dải ô và thao tác cơ bản với dữ liệu.

6. Sắp xếp lại dữ liệu bằng lớp Range

Lớp Range và các phương thức của lớp này cung cấp hầu hết các lựa chọn thao tác và định dạng dữ liệu trong Dịch vụ bảng tính.

Phần này giới thiệu thao tác cơ bản về dữ liệu bằng các dải ô. Các bài tập này tập trung vào cách sử dụng dải ô trong Apps Script, trong khi các lớp học lập trình khác trong danh sách phát này sẽ đi sâu hơn vào việc thao tác và định dạng dữ liệu.

Di chuyển dải ô

Bạn có thể kích hoạt và di chuyển các dải dữ liệu bằng phương thức lớp và ký hiệu A1, một cách viết tắt để xác định các nhóm ô cụ thể trong bảng tính. Nếu cần xem lại, bạn có thể xem nội dung mô tả về ký hiệu A1 này.

Hãy cập nhật phương thức duplicateAndOrganizeActiveSheet() để di chuyển một số dữ liệu:

  1. Trong Google Trang tính, hãy nhấp vào trang tính Sheet_Original để kích hoạt trang tính đó.
  2. Trong Apps Script, hãy sửa đổi hàm duplicateAndOrganizeActiveSheet() sao cho khớp với hàm sau:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}
  1. Lưu và chạy hàm.

Khi bạn chạy hàm này, một trang trùng lặp sẽ được tạo, kích hoạt và định dạng. Ngoài ra, nội dung của cột F sẽ được di chuyển sang cột C:

10ea483aec52457e.gif

Mã mới sử dụng phương thức getRange(a1Notation) để xác định phạm vi dữ liệu cần di chuyển. Bằng cách nhập ký hiệu A1 "F2:F" làm tham số của phương thức, bạn chỉ định cột F (không bao gồm F1). Nếu dải ô được chỉ định tồn tại, phương thức getRange(a1Notation) sẽ trả về phiên bản Range của dải ô đó. Mã này lưu trữ thực thể trong biến myRange để dễ sử dụng.

Sau khi xác định phạm vi, phương thức moveTo(target) sẽ lấy nội dung của myRange (cả giá trị và định dạng) rồi di chuyển chúng. Đích đến (cột C) được chỉ định bằng ký hiệu A1 "C2". Đây là một ô duy nhất, chứ không phải một cột. Khi di chuyển dữ liệu, bạn không cần phải khớp kích thước với các dải ô đích và nguồn. Apps Script chỉ cần căn chỉnh ô đầu tiên của mỗi ô.

Sắp xếp dải ô

Lớp Range cho phép bạn đọc, cập nhật và sắp xếp các nhóm ô. Ví dụ: bạn có thể sắp xếp một dải ô dữ liệu bằng phương thức Range.sort(sortSpecObj):

  1. Trong Google Trang tính, hãy nhấp vào trang tính Sheet_Original để kích hoạt trang tính đó.
  2. Trong Apps Script, hãy sửa đổi hàm duplicateAndOrganizeActiveSheet() sao cho khớp với hàm sau:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}
  1. Lưu và chạy hàm.

Giờ đây, ngoài định dạng trước đó, hàm này còn sắp xếp tất cả dữ liệu trong bảng bằng thông tin về giá ở cột C:

a6cc9710245fae8d.png

Mã mới sử dụng getRange(a1Notation) để chỉ định một dải ô mới bao gồm A3:D55 (toàn bộ bảng, ngoại trừ tiêu đề cột). Sau đó, mã này sẽ gọi phương thức sort(sortSpecObj) để sắp xếp bảng. Trong đó, tham số sortSpecObj là số cột để sắp xếp. Phương thức này sắp xếp dải ô sao cho các giá trị cột được chỉ định đi từ thấp nhất đến cao nhất (giá trị tăng dần). Phương thức sort(sortSpecObj) có thể thực hiện các yêu cầu sắp xếp phức tạp hơn, nhưng bạn không cần đến phương thức này ở đây. Bạn có thể xem tất cả các cách gọi hàm sắp xếp dải ô trong tài liệu tham khảo về phương thức.

Xin chúc mừng, bạn đã hoàn tất thành công tất cả các bài tập trong lớp học lập trình này. Phần tiếp theo sẽ xem xét các điểm chính của lớp học lập trình này và xem trước lớp học lập trình tiếp theo trong danh sách phát này.

7. Kết luận

Bạn đã hoàn thành lớp học lập trình này. Giờ đây, bạn có thể sử dụng và xác định các lớp và thuật ngữ thiết yếu của Dịch vụ bảng tính trong Apps Script.

Bạn đã sẵn sàng chuyển sang lớp học lập trình tiếp theo.

Bạn có thấy lớp học lập trình này hữu ích không?

Có Không

Nội dung đã đề cập

  • Cách biểu thị bảng tính, trang tính và dải ô trong Apps Script.
  • Một số cách sử dụng cơ bản của các lớp SpreadsheetApp, Spreadsheet, Sheet và Range.

Bước tiếp theo

Lớp học lập trình tiếp theo trong danh sách phát này sẽ đi sâu hơn về cách đọc, ghi và sửa đổi dữ liệu trong bảng tính.

Tìm lớp học lập trình tiếp theo tại Làm việc với dữ liệu.