Bạn có muốn tạo một bảng tính Google Sheets tự động cập nhật dữ liệu từ web mà không cần phải cập nhật thủ công? Hãy làm theo các bước đơn giản sau đây.
1. Tạo bảng tính Google Sheets
Truy cập vào Google Drive hoặc Google Sheets và tạo một bảng tính trống mới. Nếu bạn chưa có tài khoản Google, bạn có thể đăng ký miễn phí.
Đặt tiêu đề cho bảng tính của bạn và bạn đã sẵn sàng bắt đầu.
2. Tìm “XPath” cho phần của trang web mà bạn muốn lấy
Google Sheets cần một bộ chọn “XPath” để trích xuất một phần cụ thể của dữ liệu từ trang web. May mắn thay, có một công cụ dễ sử dụng để làm việc này, đó là SelectorGadget.
Cài đặt tiện ích mở rộng SelectorGadget trên trình duyệt Chrome, kích hoạt nó bằng cách nhấp vào biểu tượng của công cụ trên trình duyệt, sau đó nhấp vào phần của trang web mà bạn muốn lấy dữ liệu. Phần đó sẽ được tô sáng màu xanh lá cây. Hãy chắc chắn chỉ chọn phần đó. Có thể có các phần khác trên trang web cũng được tô sáng màu xanh lá cây hoặc màu vàng. Nếu có, hãy nhấp vào các phần mà bạn muốn loại bỏ khỏi lựa chọn của mình.
Dưới đây là ví dụ về tôi truy cập trang web của Dịch vụ Thời tiết Quốc gia cho Needham, MA, nơi đặt trụ sở của Foundry (công ty mẹ của Computerworld).
Ở góc dưới bên phải, một bộ chọn sẽ xuất hiện, nhưng .myforecast-current-lrg đó là một bộ chọn CSS. Tôi cần XPath, nó nằm ở góc dưới bên phải. Nhấp vào XPath và phiên bản XPath sẽ xuất hiện, trông như thế này:
//*[contains(concat( ' ', @class, ' ' ), concat( ' ', 'myforecast-current-lrg', ' ' ))]Nếu XPath của bạn chứa dấu nháy kép, bạn phải thay thế tất cả chúng bằng dấu nháy đơn để tránh gặp vấn đề.
3. Thêm công thức của bạn
Công thức trong bảng tính không chỉ để tính toán toán học, mà còn để trích xuất dữ liệu từ trang web. Đối với bảng tính Google, hàm để đọc một phần của trang web là:
=ImportXML("URL", "XPath selector")Trong ví dụ của tôi, tôi sử dụng trang web Dịch vụ Thời tiết Quốc gia Needham, MA và bộ chọn nhiệt độ, vì vậy công thức của tôi trông như sau:
=IMPORTXML("https://forecast.weather.gov/MapClick.php?lat=42.2803&lon=-71.2345", "//*[contains(concat( ' ', @class, ' ' ), concat( ' ', 'myforecast-current-lrg', ' ' ))]")Trong bảng tính của tôi, tôi sẽ liệt kê địa điểm mà tôi quan tâm trong cột đầu tiên. Điều này sẽ giúp tôi dễ dàng thêm các địa điểm khác vào bảng tính trong tương lai. Tôi sẽ đặt công thức vào cột thứ hai.
Và tôi cũng muốn có ngày và giờ trong cột C. Chúng ta sẽ làm điều đó trong một lát.
4. Viết một hàm để lấy và lưu dữ liệu
Bảng tính hiện đang được cấu hình để nhiệt độ không được lưu lại; nó sẽ thay đổi mỗi khi bạn mở bảng tính.
Để lưu trữ dữ liệu lịch sử, tôi sẽ phải sao chép và dán các giá trị vào một ô khác mỗi khi mở bảng tính. Điều này không tiện lợi! Thay vào đó, hãy tạo một hàm mới để: 1) tìm kiếm hàng trống đầu tiên của bảng tính và 2) sao chép giá trị từ ô B2 vào một ô trống khác để lưu trữ.
Để lưu trữ dữ liệu, chúng ta cần tạo một hàm bảng tính. Truy cập vào Phần mở rộng > Kịch bản ứng dụng để tạo các hàm cho bảng tính.
Một hàm mặc định có tên myFunction sẽ xuất hiện.
Đổi tên hàm này thành storeTemperature() (hoặc bất kỳ tên nào bạn muốn) và sử dụng mã sau:
function storeTemperature() {
var sheet = SpreadsheetApp.getActiveSheet();
var firstEmptyRow = sheet.getLastRow() + 1;
// Lấy nhiệt độ hiện tại từ ô B2
var currentTempCell = sheet.getRange(2, 2);
var currentTemp = currentTempCell.getValue();
// Tìm ô trống đầu tiên trong cột B và thêm giá trị vào đó
var newTempCell = sheet.getRange("B" + firstEmptyRow);
newTempCell.setValue(currentTemp);
// Lấy ngày và giờ hiện tại và thêm vào cùng dòng, cột C
var currentDate = Date();
var newDateCell = sheet.getRange("C" + firstEmptyRow);
newDateCell.setValue(currentDate);
// Thêm tên địa điểm vào cột A, tạm thời cứng
sheet.getRange("A" + firstEmptyRow).setValue("Needham, MA");
}Dòng code đầu tiên tạo một biến cho bảng tính đang hoạt động, tiếp theo là biến chứa số hàng trống đầu tiên của bảng tính đó.
Để đọc hoặc viết dữ liệu vào một ô của Sheets, ô đó phải được biểu diễn dưới dạng một đối tượng phạm vi (range object). May mắn thay, nó dễ dàng chuyển đổi vị trí của một ô trong bảng tính thành một đối tượng phạm vi ô với phương thức getRange(). Trong ví dụ này, tôi gọi đối tượng bảng tính của mình là sheet (bạn có thể đặt tên bất kỳ), vì vậy đoạn mã là sheet.getRange(). Phương thức getRange chấp nhận nhiều định dạng để xác định vị trí của một ô, bao gồm getRange(3,5) cho hàng 3, cột 5, hoặc getRange(“B” + 2) cho cú pháp thông thường của ô bảng tính như B2.
Với ý nghĩa đó, tôi có thể tạo một biến chứa phạm vi ô cho ô B2, nằm ở hàng 2 và cột 2, nơi tôi biết nhiệt độ hiện tại của mình, bằng cách sử dụng sheet.getRange(2, 2). Sau đó, tôi đọc giá trị này bằng get.Value() như bạn có thể thấy trong đoạn mã trên.
Tiếp theo, trong khối mã này, tôi tìm hàng trống đầu tiên trong cột B, nơi tôi muốn thêm nhiệt độ mới, và lưu trữ ô đó dưới dạng đối tượng phạm vi. Sau đó, chỉ cần đặt giá trị của ô này bằng nhiệt độ tôi vừa lưu trữ.
Dòng cuối cùng thực hiện điều tương tự để lưu trữ ngày và giờ hiện tại cũng như địa điểm được gắn cứng.
Có cách để làm mã này ngắn gọn hơn, nhưng tôi chia sẻ phiên bản chi tiết để dễ hiểu về những gì đang xảy ra. Trong dòng cuối cùng, tôi đã đơn giản hoá mã bằng cách kết hợp getRange và setValue trong một dòng duy nhất, thay vì tạo một biến riêng để chứa phạm vi ô. Hãy sử dụng cú pháp phù hợp với bạn nhất.
Lưu lại bằng cách nhấp vào biểu tượng đĩa trên mã hàm. Bạn có thể muốn đổi tên dự án mặc định thành một cái gì đó khác. Tôi đã đặt tên cho của mình là “Lunchtime Temps”.
Bạn có thể thử nghiệm script của mình bằng cách nhấp vào nút Chạy để xem điều gì xảy ra trong bảng tính. Nếu mọi thứ diễn ra suôn sẻ, bạn sẽ có một dòng dữ liệu mới với nhiệt độ hiện tại.
5. Lên lịch thực hiện tự động cho hàm của bạn
Bước cuối cùng: lên lịch thực hiện tự động cho hàm của bạn. Để làm điều này, nhấp vào biểu tượng đồng hồ bên trái để mở bảng điều khiển kịch bản của bạn hiển thị các trình kích hoạt cho dự án hiện tại của bạn. (Chưa có trình kích hoạt cho dự án của bạn cho đến khi bạn thiết lập chúng.) Bạn có thể cần cho phép điều này trong tài khoản Google của bạn trước.
Nhấp vào “Tạo một trình kích hoạt mới” và một menu sẽ xuất hiện.
Nếu bạn muốn bảng tính của bạn được cập nhật theo lịch trình cụ thể, hãy thay đổi nguồn sự kiện từ “Từ bảng tính” thành “Được kích hoạt theo thời gian” và sau đó chọn liệu bạn muốn nó chạy hàng giờ, hàng ngày, hàng tuần, hàng tháng hoặc các tùy chọn khác.
Và đó là xong! Một bảng tính tự động cập nhật và lưu trữ dữ liệu một cách tự động.
Bài viết này được xuất bản lần đầu vào tháng 4 năm 2017 và được cập nhật vào tháng 2 năm 2023.
