Kiến thức cơ bản về Apps Script với Google Trang tính số 2: Bảng tính, Trang tính và Phạm vi

1. Giới thiệu

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

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

  • Cách bảng tính, trang tính và dải ô được biểu thị trong Apps Script.
  • Cách truy cập, tạo và đổi tên bảng tính đang hoạt động (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ủa cột/hàng của 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 Kiến thức cơ bản về Apps Script với danh sách phát trên 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 tùy chỉnh.

Bạn cần có

  • Tìm hiểu về các chủ đề cơ bản của Apps Script đã được khám phá trong lớp học lập trình trước đây của danh sách phát này.
  • Làm quen với trình chỉnh sửa Apps Script
  • Làm quen cơ bản với Google Trang tính
  • Khả năng đọc Trang tính Ký hiệu A1
  • quen thuộc với JavaScript và String lớp

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

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

Bốn 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 học này và mục đích sử dụng.

Lớp TableApp

Trước khi tìm hiểu sâu hơn về bảng tính, trang tính và dải ô, bạn nên xem lại lớp mẹ: 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 tập lệnh 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. Bạn sẽ không thể tìm hiểu chi tiết về lớp SpreadsheetApp tại đây. Tuy nhiên, sau này trong 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 lớp học này.

Bảng tính, trang tính và lớp học

Ở dạng cụm từ 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 các hàng và cột. Bảng tính đôi khi được gọi là "Google Trang tính#39"; 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 trên Google Trang tính. Bạn cũng có thể sử dụng lớp này cho các hoạt động ở cấp độ tệp khác, chẳng hạn như thêm cộng tác viên.

f00cc1a9eb606f77.png

Trang tính** đại diện cho trang riêng lẻ của bảng tính, đôi khi được gọi là "tab." Mỗi bảng tính có thể chứa một hoặc nhiều trang tính. Bạn có thể 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 trang tính và xác định 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 Phạm vi

Hầu hết các thao tác thao tác với 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 ô áp dụng cho thao tác đó. Bạn có thể dùng lớp Range để chọn các nhóm ô cụ thể trong một trang tính. 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 dải ô theo số hàng và số của 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 nêu các ví dụ về tập lệnh hoạt động với các lớp này và phương thức của các tập lệnh đó.

3. Thiết lập

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

5376f721894b10d9.png

Bản sao của bảng tính mẫu để bạn sử dụng được đặt trong thư mục Google Drive của bạn và được đặt tên &&tt;Bản sao của bảng tính không có tiêu đề." Sử 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 từ Google Trang tính bằng cách nhấp vào Phần mở rộng > Apps Script.

Khi mở 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 sẽ hướng dẫn bạn cách đổi tên một bảng tính và sao chép các trang tính trong một bảng tính.

Đây là những hoạt động đơn giản, nhưng chúng thường nằm trong quy trình lớn hơn, phức tạp hơn. Sau khi hiểu được cách tự động hóa các tác vụ này bằng mã tập lệnh, bạn sẽ dễ dàng tìm hiểu cách tự động hóa 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 tốt 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, 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 tập lệnh, hãy nhấp vào Lưu pha bảo vệ khung thành.
  2. Để đổi tên dự án Apps Script của bạn, hãy nhấp vào Dự án không có tiêu đề, nhập "giá Avocado" làm tên dự án mới và nhấp vào Đổi tên.
  3. Để chạy tập lệnh, hãy chọn renameSpreadsheet từ danh sách hàm rồi nhấp vào Chạy.
  4. Cấp phép 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 &quat; Ứng dụng này chưa được xác minh" hãy nhấp vào Nâng cao, rồi nhấp vào Chuyển đến giá Avocado (không an toàn). Trên màn hình tiếp theo, hãy nhấp vào Cho phép.

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; nghĩa là bản sao của bảng tính bài tập thể dục mà bạn tạo. Đối tượng bảng tính này được lưu trữ trong biến mySS. Việc 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 Price in Portland, Seattle."

Do biến mySS là tệp tham chiếu đến bảng tính, bạn có thể giúp mã trở nên sạch hơn 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 của bạn, 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 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 của bạn.
  2. Để chạy tập lệnh, hãy chọn duplicateAndOrganizeActiveSheet từ danh sách hàm rồi nhấp vào Chạy.

Quay lại Trang tính để xem thẻ mới

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 nhận được này được lưu trữ trong duplicateSheet, nhưng mã này 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 chiều rộng của tên và trang tính của một trang tính bằng các phương thức từ lớp Sheet.

Thay đổi tên của trang tính

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

  1. Trong Google Trang tính, hãy nhấp vào trang tính Sheet_Original để kích hoạt.
  2. Trong Apps Script, hãy sửa đổi hàm duplicateAndOrganizeActiveSheet() để khớp với thông tin 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 đượ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() để nhận số nhận dạng duy nhất của trang tính. Toán tử + liên kết mã trang tính vào cuối chuỗi "Sheet_".

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

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

  1. Trong Google Trang tính, hãy nhấp vào trang tính Sheet_Original để kích hoạt.
  2. Trong Apps Script, hãy sửa đổi hàm duplicateAndOrganizeActiveSheet() để khớp với thông tin 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 được tạo, đổi tên, kích hoạt và định dạng:

2e57c917ab157dad.gif

Mã bạn thêm sẽ sử dụng autoResizeColumns(startColumn, numColumns) để thay đổi kích thước các cột của trang tính cho dễ đọc. Phương thức setFrozenRows(rows) đóng băng số hàng đã cho (trong hai trường hợp này), phương thức này giúp hiển thị các hàng tiêu đề 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ề phạm vi và thao tác dữ liệu cơ bản.

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

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 tùy chọn định dạng và thao tác dữ liệu trong dịch vụ Bảng tính.

Phần này giới thiệu về thao tác dữ liệu cơ bản với phạm vi. Các bài tập này tập trung vào cách sử dụng các phạm vi 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 đi sâu hơn vào việc thao tác dữ liệu và định dạng dữ liệu.

Di chuyển phạm vi

Bạn có thể kích hoạt và di chuyển các dải ô dữ liệu bằng các phương thức lớp và ký hiệu A1, một tên viết tắt để xác định các nhóm ô cụ thể trong bảng tính. Nếu cần làm quen với lại, bạn có thể xem 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 xung quanh:

  1. Trong Google Trang tính, hãy nhấp vào trang tính Sheet_Original để kích hoạt.
  2. Trong Apps Script, hãy sửa đổi hàm duplicateAndOrganizeActiveSheet() để khớp với thông tin 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 tính trùng lặp sẽ được tạo, kích hoạt và định dạng. Và nội dung của cột F được chuyển đến cột C:

10ea483aec52457e.gif

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

Sau khi xác định dải ô, 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 các nội dung đó. Đích (cột C) được chỉ định bằng ký hiệu A1 "C2". Đây là một ô thay vì một cột. Khi di chuyển dữ liệu, bạn không cần phải đối sánh kích thước với phạm vi mục tiêu và đích đến. Apps Script chỉ cần căn chỉnh ô đầu tiên của từng ô.

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 cách sử dụ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.
  2. Trong Apps Script, hãy sửa đổi hàm duplicateAndOrganizeActiveSheet() để khớp với thông tin 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.

Bây giờ, hàm, ngoài định dạng trước đây của chúng ta, sắp xếp tất cả dữ liệu trong bảng bằng cách sử dụng thông tin về giá trong 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ừ các tiêu đề cột). Sau đó, mã này sẽ gọi phương thức sort(sortSpecObj) để sắp xếp bảng. Ở đây, thông số sortSpecObj là số cột để sắp xếp. Phương thức này sắp xếp dải ô để những 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 các phương thức này ở đây. Bạn có thể xem tất cả các cách gọi phạm vi khác nhau trong tài liệu tham khảo về phương pháp.

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. Phần tiếp theo sẽ xem xét các điểm chính trong 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 đã kết thúc 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à điều khoản 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

Những điều chúng tôi đã đề cập

  • Cách bảng tính, trang tính và dải ô được biểu thị 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ào cách đọc, viết 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.