Mở rộng Google Trang tính

Sử dụng Google Apps Script để mở rộng Trang tính. Thêm trình đơn tuỳ chỉnh, hộp thoại và thanh bên vào Trang tính. Viết hàm tuỳ chỉnh cho Trang tính và tích hợp hàm đó với các dịch vụ khác của Google như Lịch Google, Google Drive và Gmail.

Hầu hết các tập lệnh được thiết kế cho Trang tính đều thao tác với mảng để tương tác với các ô, hàng và cột trong bảng tính. Nếu bạn chưa quen với mảng trong JavaScript, Codecademy sẽ cung cấp một mô-đun đào tạo tuyệt vời về mảng. Khoá học này không phải do Google phát triển và không liên kết với Google.

Để biết thông tin giới thiệu nhanh về cách sử dụng Apps Script với Trang tính, hãy xem hướng dẫn bắt đầu nhanh trong 5 phút về Macro, Trình đơn và Hàm tuỳ chỉnh.

Bắt đầu

Apps Script bao gồm các API đặc biệt để tạo, đọc và chỉnh sửa Trang tính theo phương thức lập trình. Apps Script tương tác với Trang tính theo hai cách: bất kỳ tập lệnh nào cũng có thể tạo hoặc sửa đổi bảng tính nếu người dùng tập lệnh có quyền thích hợp đối với bảng tính đó và tập lệnh cũng có thể được liên kết với bảng tính. Tập lệnh được liên kết có các khả năng đặc biệt để thay đổi giao diện người dùng hoặc phản hồi khi bảng tính được mở. Để tạo tập lệnh được liên kết, hãy chọn Tiện ích > Apps Script trong Trang tính.

Dịch vụ Bảng tính xử lý Trang tính dưới dạng lưới, hoạt động với mảng hai chiều. Để truy xuất dữ liệu từ bảng tính, hãy truy cập vào bảng tính nơi lưu trữ dữ liệu, lấy dải ô chứa dữ liệu, sau đó lấy giá trị của các ô. Apps Script hỗ trợ truy cập dữ liệu bằng cách đọc dữ liệu có cấu trúc trong bảng tính và tạo các đối tượng JavaScript cho dữ liệu đó.

Đọc dữ liệu

Giả sử bạn có một danh sách tên sản phẩm và số sản phẩm mà bạn lưu trữ trong bảng tính, như minh hoạ trong hình ảnh sau.

Ví dụ sau đây cho thấy cách truy xuất và ghi lại tên sản phẩm và số sản phẩm.

function logProductInfo() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

Xem nhật ký

Để xem dữ liệu đã được ghi lại, ở đầu trình chỉnh sửa tập lệnh, hãy nhấp vào Nhật ký thực thi.

Ghi dữ liệu

Để lưu trữ dữ liệu, chẳng hạn như tên và số sản phẩm mới vào bảng tính, hãy thêm mã sau vào cuối tập lệnh.

function addProduct() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

Mã trước đó sẽ thêm một hàng mới ở cuối bảng tính, với các giá trị được chỉ định. Nếu bạn chạy hàm này, một hàng mới sẽ được thêm vào bảng tính.

Trình đơn tuỳ chỉnh và giao diện người dùng

Tuỳ chỉnh Trang tính bằng cách thêm trình đơn, hộp thoại và thanh bên tuỳ chỉnh. Để tìm hiểu những điều cơ bản về cách tạo trình đơn, hãy xem hướng dẫn về trình đơn. Để tìm hiểu về cách tuỳ chỉnh nội dung của hộp thoại, hãy xem hướng dẫn về dịch vụ HTML.

Đính kèm hàm tập lệnh vào hình ảnh hoặc bản vẽ trong bảng tính; hàm này sẽ thực thi khi người dùng nhấp vào hình ảnh hoặc bản vẽ. Để tìm hiểu thêm, hãy xem bài viết Hình ảnh và bản vẽ trong Trang tính.

Nếu bạn dự định xuất bản giao diện tuỳ chỉnh dưới dạng một phần của tiện ích bổ sung, hãy làm theo hướng dẫn về kiểu để đảm bảo tính nhất quán với kiểu và bố cục của trình chỉnh sửa Trang tính.

Kết nối với Google Biểu mẫu

Kết nối Google Biểu mẫu với Trang tính thông qua các dịch vụ Biểu mẫu và Bảng tính. Tính năng này sẽ tự động tạo Biểu mẫu trên Google dựa trên dữ liệu trong bảng tính. Apps Script cũng cho phép bạn sử dụng các điều kiện kích hoạt, chẳng hạn như onFormSubmit để thực hiện một hành động cụ thể sau khi người dùng trả lời biểu mẫu. Để tìm hiểu thêm về cách kết nối Trang tính với Biểu mẫu, hãy dùng thử hướng dẫn bắt đầu nhanh trong 5 phút về cách Quản lý câu trả lời cho Biểu mẫu.

Định dạng dữ liệu

Lớp Range có các phương thức như setBackground để truy cập và sửa đổi định dạng của một ô hoặc dải ô. Ví dụ sau đây đặt kiểu phông chữ của một dải ô:

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

Xác thực dữ liệu

Truy cập vào các quy tắc xác thực dữ liệu hiện có trong Trang tính hoặc tạo quy tắc mới. Ví dụ: mẫu sau đây cho thấy cách đặt quy tắc xác thực dữ liệu chỉ cho phép nhập số từ 1 đến 100 vào một ô.

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  const cell = SpreadsheetApp.getActive().getRange('B4');
  const rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

Để biết thêm thông tin chi tiết về cách làm việc với các quy tắc xác thực dữ liệu, hãy xem SpreadsheetApp.newDataValidation, DataValidationBuilder, và Range.setDataValidation

Biểu đồ

Nhúng biểu đồ vào bảng tính để biểu thị dữ liệu trong một dải ô cụ thể. Ví dụ sau đây tạo biểu đồ thanh được nhúng, giả sử bạn có dữ liệu có thể tạo biểu đồ trong các ô A1:B15:

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

  const chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

Để tìm hiểu thêm về cách nhúng biểu đồ vào bảng tính, hãy xem EmbeddedChart và các trình tạo biểu đồ cụ thể, chẳng hạn như EmbeddedPieChartBuilder.

Hàm tuỳ chỉnh trong Google Trang tính

Hàm tùy chỉnh tương tự như hàm bảng tính tích hợp sẵn như =SUM(A1:A5) ngoại trừ việc bạn xác định hành vi của hàm bằng Apps Script. Ví dụ: bạn có thể tạo hàm tuỳ chỉnh in2mm() để chuyển đổi giá trị từ inch sang milimét, sau đó sử dụng công thức trong bảng tính bằng cách nhập =in2mm(A1) hoặc =in2mm(10) vào một ô.

Để tìm hiểu thêm về hàm tuỳ chỉnh, hãy dùng thử hướng dẫn bắt đầu nhanh trong 5 phút về Trình đơn và Hàm tuỳ chỉnh hoặc xem hướng dẫn chi tiết hơn về hàm tuỳ chỉnh.

Macro

Macro là một cách khác để thực thi mã Apps Script từ giao diện người dùng Trang tính. Không giống như hàm tuỳ chỉnh, bạn kích hoạt macro bằng phím tắt hoặc thông qua trình đơn Trang tính. Để biết thêm thông tin, hãy xem Macro trong Trang tính.

Tiện ích bổ sung dành cho Google Trang tính

Tiện ích bổ sung là các dự án Apps Script được đóng gói đặc biệt , chạy bên trong Trang tính và có thể được cài đặt từ cửa hàng tiện ích bổ sung của Trang tính. Nếu bạn đã phát triển một tập lệnh cho Trang tính và muốn chia sẻ tập lệnh đó, Apps Script sẽ cho phép bạn xuất bản tập lệnh dưới dạng tiện ích bổ sung để người dùng khác có thể cài đặt.

Hiệu suất và khả năng mở rộng

Khi tập dữ liệu của bạn tăng lên, bạn có thể gặp phải các vấn đề về hiệu suất. Cách tối ưu hoá bảng tính và tập lệnh:

Làm theo các phương pháp hay nhất: Đọc hướng dẫn Các phương pháp hay nhất để biết các mẹo về cách giảm thiểu số lần gọi dịch vụ và sử dụng các thao tác hàng loạt.
Tối ưu hoá công thức: Nếu bảng tính của bạn bị chậm do các công thức phức tạp (như VLOOKUP, ARRAYFORMULA, hoặc IMPORTRANGE), hãy cân nhắc sử dụng Apps Script để thực hiện các phép tính này trong bộ nhớ và ghi kết quả theo lô.
Cân nhắc các lựa chọn thay thế cho cơ sở dữ liệu: Đối với các tập dữ liệu rất lớn (gần 10 triệu ô) hoặc nhập dữ liệu tần suất cao (ví dụ: nhiều biểu mẫu được kết nối), hãy cân nhắc sử dụng Google Cloud SQL bằng JDBC hoặc BigQuery.

Điều kiện kích hoạt

Các tập lệnh được liên kết với tệp Trang tính có thể sử dụng các điều kiện kích hoạt đơn giản như các hàm onOpen() và onEdit() để tự động phản hồi khi người dùng có quyền chỉnh sửa bảng tính mở hoặc chỉnh sửa bảng tính. Giống như điều kiện kích hoạt đơn giản, điều kiện kích hoạt có thể cài đặt cho phép Trang tính tự động chạy một hàm khi một sự kiện nhất định xảy ra. Tuy nhiên, điều kiện kích hoạt có thể cài đặt mang lại tính linh hoạt hơn so với điều kiện kích hoạt đơn giản và hỗ trợ các sự kiện sau: mở, chỉnh sửa, thay đổi, gửi biểu mẫu và theo thời gian (đồng hồ).

Mở rộng Google Trang tính Sử dụng bộ sưu tập để sắp xếp ngăn nắp các trang Lưu và phân loại nội dung dựa trên lựa chọn ưu tiên của bạn.